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.SqlPa rameter

pParms(0) = New SqlClient.SqlPa rameter("@ID", miID)
pParms(1) = New SqlClient.SqlPa rameter("@Vendo rID",
miVendorID)
pParms(2) = New SqlClient.SqlPa rameter("@Statu sID",
miStatusID)
pParms(3) = New SqlClient.SqlPa rameter("@PONum ber",
msPONumber)
pParms(4) = New SqlClient.SqlPa rameter("@Date" ,
mdPODate)
pParms(5) = New SqlClient.SqlPa rameter("@Conta ctName",
msContactName)
pParms(6) = New SqlClient.SqlPa rameter
("@ContactPhone ", msContactPhone)
pParms(7) = New SqlClient.SqlPa rameter("@Comme nt",
msComment)
pParms(8) = New SqlClient.SqlPa rameter("@Creat edByID",
miCreatedByID)
pParms(9) = New SqlClient.SqlPa rameter
("@ModifiedByID ", miModifiedByID)

Try
'perform insert
If miID = 0 Then
miID = SqlHelper.Execu teScalar(msConn ectionString,
CommandType.Sto redProcedure, "p_PurchaseOrde r_Insert",
pParms)
Else
'perform update
SqlHelper.Execu teNonQuery(msCo nnectionString,
CommandType.Sto redProcedure, "p_PurchaseOrde r_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********@pds pc.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.SqlPa rameter

pParms(0) = New SqlClient.SqlPa rameter("@ID", miID)
pParms(1) = New SqlClient.SqlPa rameter("@Vendo rID",
miVendorID)
pParms(2) = New SqlClient.SqlPa rameter("@Statu sID",
miStatusID)
pParms(3) = New SqlClient.SqlPa rameter("@PONum ber",
msPONumber)
pParms(4) = New SqlClient.SqlPa rameter("@Date" ,
mdPODate)
pParms(5) = New SqlClient.SqlPa rameter("@Conta ctName",
msContactName)
pParms(6) = New SqlClient.SqlPa rameter
("@ContactPhone ", msContactPhone)
pParms(7) = New SqlClient.SqlPa rameter("@Comme nt",
msComment)
pParms(8) = New SqlClient.SqlPa rameter("@Creat edByID",
miCreatedByID)
pParms(9) = New SqlClient.SqlPa rameter
("@ModifiedByID ", miModifiedByID)

Try
'perform insert
If miID = 0 Then
miID = SqlHelper.Execu teScalar(msConn ectionString,
CommandType.Sto redProcedure, "p_PurchaseOrde r_Insert",
pParms)
Else
'perform update
SqlHelper.Execu teNonQuery(msCo nnectionString,
CommandType.Sto redProcedure, "p_PurchaseOrde r_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********@pds pc.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.SqlPa rameter

pParms(0) = New SqlClient.SqlPa rameter("@ID", miID)
pParms(1) = New SqlClient.SqlPa rameter("@Vendo rID",
miVendorID)
pParms(2) = New SqlClient.SqlPa rameter("@Statu sID",
miStatusID)
pParms(3) = New SqlClient.SqlPa rameter("@PONum ber",
msPONumber)
pParms(4) = New SqlClient.SqlPa rameter("@Date" ,
mdPODate)
pParms(5) = New SqlClient.SqlPa rameter("@Conta ctName",
msContactName)
pParms(6) = New SqlClient.SqlPa rameter
("@ContactPhone ", msContactPhone)
pParms(7) = New SqlClient.SqlPa rameter("@Comme nt",
msComment)
pParms(8) = New SqlClient.SqlPa rameter("@Creat edByID",
miCreatedByID)
pParms(9) = New SqlClient.SqlPa rameter
("@ModifiedByID ", miModifiedByID)

Try
'perform insert
If miID = 0 Then
miID = SqlHelper.Execu teScalar(msConn ectionString,
CommandType.Sto redProcedure, "p_PurchaseOrde r_Insert",
pParms)
Else
'perform update
SqlHelper.Execu teNonQuery(msCo nnectionString,
CommandType.Sto redProcedure, "p_PurchaseOrde r_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!