Adding Parameters to SQLCommand in .NET

[michael pitt]

I have many pages in my ASP.NET (VB) which use the SQL command to call
Stored Procedures. They create the parameters and set their value
using the following syntax

cmd.parameters.add("@ParameterName", value)

One page though crashes because the parameters have not been set up,
their value is nothing. The only reason I can think of is the the
parameters.add method has a couple of syntaxes ie

cmd.parameters.add("@ParameterName", value)
cmd.parameters.add("@ParameterName", Type)
etc.

I have fixed the problem by using

cmd.Parameters.Add("@ParameterName", SqlDbType.Int).Value = value

But I am now concerned that .NET cant always tell which syntax I am
using and may be mistaking my values for parameter types espcially
when the value matches a sqldbtype value. Im loathed to change all
the other pages to this new syntax but Im worried if I leave it there
may be some problems in the future.

Any advice ?

 

[Rajesh Patel]

yes. it's better to change. it's a good practice if you always assign the
datatype to the parameter.

Rajesh Patel

 

[michael pitt]

I can narrow down the problem to

cmd.Parameters.Add("@XX", 0)

Being treated as
'Create a parameter of type sqldbtype.bigint'
rather than
'Create a parameter @XX with value 0'

This seems to be a fairly scarey 'feature'