SQLHelper not allowing me to pass Null paramter

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I am trying to pass a null value into a stored procedure
so that it can save the data. I am using Microsoft's
SQLHelper dll to do this. My example code is below. How
do I pass in a null value as a parameter? For example, in
the code below, miVendorID is declared as an Object. The
save attempt fails if the object has not been populated
(for example, the person choose not to select a vendor
from the dropdown list) I have the same problem for both
numeric and string fields.

Public Function Save() As String

Dim pParms(9) As SqlClient.SqlParameter

pParms(0) = New SqlClient.SqlParameter("@ID", miID)
pParms(1) = New SqlClient.SqlParameter("@VendorID",
miVendorID)
pParms(2) = New SqlClient.SqlParameter("@StatusID",
miStatusID)
pParms(3) = New SqlClient.SqlParameter("@PONumber",
msPONumber)
pParms(4) = New SqlClient.SqlParameter("@Date",
mdPODate)
pParms(5) = New SqlClient.SqlParameter("@ContactName",
msContactName)
pParms(6) = New SqlClient.SqlParameter
("@ContactPhone", msContactPhone)
pParms(7) = New SqlClient.SqlParameter("@Comment",
msComment)
pParms(8) = New SqlClient.SqlParameter("@CreatedByID",
miCreatedByID)
pParms(9) = New SqlClient.SqlParameter
("@ModifiedByID", miModifiedByID)

Try
'perform insert
If miID = 0 Then
miID = SqlHelper.ExecuteScalar(msConnectionString,
CommandType.StoredProcedure, "p_PurchaseOrder_Insert",
pParms)
Else
'perform update
SqlHelper.ExecuteNonQuery(msConnectionString,
CommandType.StoredProcedure, "p_PurchaseOrder_Update",
pParms)
End If
Catch ex As Exception
Save = "Duplicate"
End Try


End Function
 
Brian,
Have you tried setting the appropriate parameter to DBNull.Value vs.
null? This is what is passed back from a database for a null value. Also,
are you sure that your stored procedure will accept a null parameter? Try
testing it in QueryAnalyzer to see.

Ron Allen
 
Wow!! It worked! It's ALIVE!! Using DBNull.value appears to allow
the SQLHelper DLL know to pass in a null value for both string and
numeric data. Thanks a lot Ron!
 
Back
Top