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.com> wrote in message
news:1160169983.937789.306620@.k70g2000cwa.googlegroups.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.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:

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