Pass NULL To Stored Procedure

[rn5a]

How do I pass a NULL value to a field while inserting records in a SQL
Server 2005 DB table using a stored procedure? I tried the following
but it inserts an empty string & not a NULL value:

ALTER PROCEDURE dbo.Purchase
@UserID int,
@Total decimal,
@Address varchar(250) = NULL,
@Country varchar(50) = NULL
AS

INSERT INTO Order (UserID, Address, Country, Total) VALUES (@UserID,
@Address, @Country, @Total)

I am invoking the above SP with this code in a class file:

Public Class Cart
Public Sub PlaceOrder(ByVa l UserID As Integer, ByVal Total As
Double, ByVal Address As String, ByVal Country As String)
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand

sqlConn = New SqlConnection(" .....")
sqlCmd = New SqlCommand("Pur chase", sqlConn)
sqlCmd.CommandT ype = CommandType.Sto redProcedure

With sqlCmd
.Parameters.Add ("@UserID", SqlDbType.Int). Value = UserID
.Parameters.Add ("@Total", SqlDbType.Decim al).Value = Total
.Parameters.Add ("@Address", SqlDbType.VarCh ar, 250).Value =
Address
.Parameters.Add ("@Country", SqlDbType.VarCh ar, 50).Value =
Country
End With

sqlConn.Open()
sqlCmd.ExecuteN onQuery()
sqlConn.Close()
End Sub
End Class

Using vbc, I compiled the above into a DLL named Cart.dll.

This is the ASPX code (if no values are supplied for the variables
'strAddress' & 'strCountry', those records should be inserted as NULLs
in the DB table):

Sub Submit_Click(.. ...)
Dim boCart As Cart
boCart = New Cart

If (strAddress = "") Then
strAddress = DBNull.Value.To String
End If

If (strCountry = "") Then
strCountry = DBNull.Value.To String
End If

boCart.PlaceOrd er(iUserID, dblTotal, strAddress, strCountry)
End Sub

[Mark Rae]

<rn**@rediffmai l.comwrote in message
news:11******** **************@ k70g2000cwa.goo glegroups.com.. .

How do I pass a NULL value to a field while inserting records in a SQL
Server 2005 DB table using a stored procedure? I tried the following
but it inserts an empty string & not a NULL value:

..Parameters.Ad d("@Address", SqlDbType.VarCh ar, 250).Value = DbNull.Value

[Muhammad Mosa]

Plus what Mark mention
I think you can set your instance to Nothing
like:
address = nothing;
but if you your object is value type, you have to use the way Mark Mentioned
--
Muhammad Mosa
Software Engineer & Solution Developer
MCT/MCSD.NET
MCTS: .Net 2.0 Web Applications
MCTS: .Net 2.0 Windows Applications
"rn**@rediffmai l.com" wrote:

How do I pass a NULL value to a field while inserting records in a SQL
Server 2005 DB table using a stored procedure? I tried the following
but it inserts an empty string & not a NULL value:

ALTER PROCEDURE dbo.Purchase
@UserID int,
@Total decimal,
@Address varchar(250) = NULL,
@Country varchar(50) = NULL
AS

INSERT INTO Order (UserID, Address, Country, Total) VALUES (@UserID,
@Address, @Country, @Total)

I am invoking the above SP with this code in a class file:

Public Class Cart
Public Sub PlaceOrder(ByVa l UserID As Integer, ByVal Total As
Double, ByVal Address As String, ByVal Country As String)
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand

sqlConn = New SqlConnection(" .....")
sqlCmd = New SqlCommand("Pur chase", sqlConn)
sqlCmd.CommandT ype = CommandType.Sto redProcedure

With sqlCmd
.Parameters.Add ("@UserID", SqlDbType.Int). Value = UserID
.Parameters.Add ("@Total", SqlDbType.Decim al).Value = Total
.Parameters.Add ("@Address", SqlDbType.VarCh ar, 250).Value =
Address
.Parameters.Add ("@Country", SqlDbType.VarCh ar, 50).Value =
Country
End With

sqlConn.Open()
sqlCmd.ExecuteN onQuery()
sqlConn.Close()
End Sub
End Class

Using vbc, I compiled the above into a DLL named Cart.dll.

This is the ASPX code (if no values are supplied for the variables
'strAddress' & 'strCountry', those records should be inserted as NULLs
in the DB table):

Sub Submit_Click(.. ...)
Dim boCart As Cart
boCart = New Cart

If (strAddress = "") Then
strAddress = DBNull.Value.To String
End If

If (strCountry = "") Then
strCountry = DBNull.Value.To String
End If

boCart.PlaceOrd er(iUserID, dblTotal, strAddress, strCountry)
End Sub