Pass NULL To Stored Procedure

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
@dotnet.itags.org.UserID int,
@dotnet.itags.org.Total decimal,
@dotnet.itags.org.Address varchar(250) = NULL,
@dotnet.itags.org.Country varchar(50) = NULL
AS

INSERT INTO Order (UserID, Address, Country, Total) VALUES (@dotnet.itags.org.UserID,
@dotnet.itags.org.Address, @dotnet.itags.org.Country, @dotnet.itags.org.Total)

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

Public Class Cart
Public Sub PlaceOrder(ByVal 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("Purchase", sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure

With sqlCmd
.Parameters.Add("@dotnet.itags.org.UserID", SqlDbType.Int).Value = UserID
.Parameters.Add("@dotnet.itags.org.Total", SqlDbType.Decimal).Value = Total
.Parameters.Add("@dotnet.itags.org.Address", SqlDbType.VarChar, 250).Value =
Address
.Parameters.Add("@dotnet.itags.org.Country", SqlDbType.VarChar, 50).Value =
Country
End With

sqlConn.Open()
sqlCmd.ExecuteNonQuery()
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.ToString
End If

If (strCountry = "") Then
strCountry = DBNull.Value.ToString
End If

boCart.PlaceOrder(iUserID, dblTotal, strAddress, strCountry)
End Sub<rn5a@.rediffmail.comwrote in message
news:1160169983.937789.306620@.k70g2000cwa.googlegr oups.com...

Quote:

Originally Posted by

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.Add("@.Address", SqlDbType.VarChar, 250).Value = DbNull.Value
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

"rn5a@.rediffmail.com" wrote:

Quote:

Originally Posted by

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(ByVal 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("Purchase", sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure
>
With sqlCmd
.Parameters.Add("@.UserID", SqlDbType.Int).Value = UserID
.Parameters.Add("@.Total", SqlDbType.Decimal).Value = Total
.Parameters.Add("@.Address", SqlDbType.VarChar, 250).Value =
Address
.Parameters.Add("@.Country", SqlDbType.VarChar, 50).Value =
Country
End With
>
sqlConn.Open()
sqlCmd.ExecuteNonQuery()
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.ToString
End If
>
If (strCountry = "") Then
strCountry = DBNull.Value.ToString
End If
>
boCart.PlaceOrder(iUserID, dblTotal, strAddress, strCountry)
End Sub
>
>