Problem with passing SqlParameter in VB.NET

[Niksa Baldun]

Check this out. I try to execute a stored procedure on SQL Server 2000 like
this:

cmdA = New SqlClient.SqlCommand("NP", cnnMain)
With cmdA
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlClient.SqlParameter("@Name", strName))
.Parameters.Add(New SqlClient.SqlParameter("@Type", 4))
.Parameters.Add(New SqlClient.SqlParameter("@ID", 0))
Try
.ExecuteNonQuery()
Catch excA As SqlClient.SqlException
MsgBox(excA.Message)
End Try
End With

I get the following error message:
Procedure 'NP' expects parameter '@ID', which was not supplied.

As you can clearly see, the parameter @ID *was* supplied with the value of
zero. If I put any value other than zero, it works fine. If I declare a
variable like this:

Dim intA As Integer = 0

and then pass intA as parameter value, it also works fine. Can anybody
explain this?


Thanks,

Niksa Baldun

 

[William Ryan]

..Parameters.Add(New SqlClient.SqlParameter("@ID"))
cmd.Paramaters(0).Value = 0

There's a problem with the overload, and 0 is a valid value but for some
reason it doesn't respond properly.
IF you check the value of the @ID parameter, you'll see it's nothing whereas
it shoudl be 0. So just set the value any other way and you'll be good to
go.

HTH,

Bill

 

[Miha Markic]

Hi Niksa,

A caution from .net help files:
Use caution when using this overload of the SqlParameter constructor to
specify integer parameter values. Because this overload takes a value of
type Object, you must convert the integral value to an Object type when the
value is zero, as the following C# example demonstrates.

Parameter = new SqlParameter("@pname", Convert.ToInt32(0));
If you do not perform this conversion, the compiler will assume you are
attempting to call the SqlParameter (string, SqlDbType) constructor
overload.

 

[Niksa Baldun]

I figured it would be something like that. I tried using CType(0, Int32),
but it didn't work Didn't think of Convert.

Anyway, thanks for the help.

Niksa