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 don't 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 wasn't 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 don't 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 there's no chance of something being in between your double quotes.

 

Dim errMsg as String = String.Empty

Edited September 16, 2008 by Nate Bross