469,289 Members | 2,330 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,289 developers. It's quick & easy.

SQLHelper not allowing me to pass Null paramter

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
Jul 19 '05 #1
3 8200
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

Jul 19 '05 #2
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

Jul 19 '05 #3
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!
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Neal | last post: by
reply views Thread by Rajesh Kumar | last post: by
7 posts views Thread by Neven Klofutar | last post: by
1 post views Thread by John A Grandy | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.