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

 

[Mark Rae]

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

 

[Guest]

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