Stored Procedure isn't seeing a Parameter?

[kkonkle]

I am using the following Stored Procedure and I have seen it work. I have the database entries to prove it... however, most times the Catch block is triggered with the error:
"Procedure Insert_Results expects parameter @errMessage, which was not supplied."

Can anyone see why that would be? I am clearly creating and passing in a parameter named @errMessage.

With cmd3
   .CommandType = CommandType.StoredProcedure
   .CommandText = "Insert_Results"
   .Parameters.Clear()

   Dim param1 As New SqlParameter("@programName", SqlDbType.VarChar)
   param1.Value = PROGRAM_NAME
   .Parameters.Add(param1)

   Dim param2 As New SqlParameter("@statusMessage", SqlDbType.VarChar)
   param2.Value = statusMsg
   .Parameters.Add(param2)

   Dim param3 As New SqlParameter("@errMessage", SqlDbType.VarChar)
   param3.Value = errMsg
   .Parameters.Add(param3)
End With

Try
   cn3.Open()
   cmd3.ExecuteNonQuery()
   cn3.Close()

   cmd3.CommandType = CommandType.Text
Catch ex As Exception
   If cn3.State = ConnectionState.Open Then
      cn3.Close()
   End If

   cmd3.CommandType = CommandType.Text
End Try

 

[Nate Bross]

I generally use Parameters.AddWithValue() method and have never seen an issue. I dont think there should really be much difference though.

 

[kkonkle]

The weird part is that the above code I am using is copied from another one of my programs that is working just fine. It has never had trouble finding the @errMessage parameter, so I think there is a deeper issue going on here.

 

[kkonkle]

OK, I got this one figured out... the issue was that "errMsg" was never initialized to anything, so its value was "Nothing" and because of that the parameter wasnt registering that is got anything. Once I tried Dim errMsg as String = "" it worked.

So that was the issue. Passing a Value of Nothing in for a parameter is equivalent to not passing in the parameter at all.

 

[Nate Bross]

Yes, you should always initalize variables to a value. In Visual Baisc the compiler will set them to Nothing if you dont.

In languages like C or Java you will have a "garbage" value if you dont manually initalize, so it is almost required to initalize with a value. I recommend you always initalize to a value.

I like using "String.Empty" for Strings only because theres no chance of something being in between your double quotes.

Dim errMsg as String = String.Empty