SQLHelper not allowing me to pass Null paramter

[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

[Brad Shook]

You need to use "nothing" insted of "null" in .net

"Brian" <bs********@pdspc.com> wrote in message
news:0b****************************@phx.gbl...

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

[Ron Allen]

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
"Brian" <bs********@pdspc.com> wrote in message
news:0b****************************@phx.gbl...

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 Salentine]

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!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!