By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,152 Members | 1,647 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,152 IT Pros & Developers. It's quick & easy.

Pass NULL To Stored Procedure

P: n/a
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

Oct 6 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
<rn**@rediffmail.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.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
Oct 6 '06 #2

P: n/a
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**@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

Oct 6 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.