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
>

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

Pass Paramter from Repeater - Can't Figure This Out

Hi,
I have a repeater based on a stored procedure. It pulls the following:
catDesc Image
The user sees the category description (catDesc) , and then clicks on
the image (Image) which is bound to the catID. This catID (@dotnet.itags.org.catID --
an integer) is then to be passed to the next page, sculpture2.aspx for
use in a datagrid with a stored procedure.
I understand how to pass a parameter using response.redirect. I've
done so with a dropdownlist in the past by using OnSelectedIndexChanged
and just using dropdownlist.selecteditem.value as the parameter (an
integer in this case) which I use with response.redirect.
Is there an equivalent techinque for using a repeater?
Here is my code:
<asp:repeater runat="server" ID=repeaterOdd>
<itemtemplate>
<p align="left"><%#DataBinder.Eval(Container.DataItem,
"catDesc") %></p>
<p align="left">
<a href="http://links.10026.com/?link=sculpture2.aspx?catDesc=<%#DataBinder.Eval(Container.DataItem,
"catID")%>"
<img src="http://pics.10026.com/?src=<%# DataBinder.Eval(Container.DataItem, "image") %>"></p>
</itemtemplate>
</asp:repeater>
If I pass this parameter as CatID, then I can't retrieve it using a
querysting, and the whole goal is to use a querystring to pass the
parameter. (e.g int passedValue = Request.Querystring["catID"].
Is there some way to pull the selected value of catID out of the
repeater, put it in some kind of variable, and then run a method to
attach it to a response.redirect?
Is there another way to do this? Using a LinkButton?
Thanks in advance.you can replace the image tag with an asp:HyperLink that way the user can
click on the image and be redirected to a page with your catID accesible
thus:
<asp:HyperLink id="HyperLink1" runat="server" ImageUrl="<%#
DataBinder.Eval(Container.DataItem, "image") %>"
NavigateUrl=secondpage.aspx?caiID=<%#DataBinder.Eval(Container.DataItem,
"catID")%>"></asp:HyperLink>
"Ranginald" <davidwank@.gmail.com> wrote in message
news:1146692474.566945.201390@.y43g2000cwc.googlegroups.com...
> Hi,
> I have a repeater based on a stored procedure. It pulls the following:
> catDesc Image
> The user sees the category description (catDesc) , and then clicks on
> the image (Image) which is bound to the catID. This catID (@.catID --
> an integer) is then to be passed to the next page, sculpture2.aspx for
> use in a datagrid with a stored procedure.
> I understand how to pass a parameter using response.redirect. I've
> done so with a dropdownlist in the past by using OnSelectedIndexChanged
> and just using dropdownlist.selecteditem.value as the parameter (an
> integer in this case) which I use with response.redirect.
> Is there an equivalent techinque for using a repeater?
> Here is my code:
> <asp:repeater runat="server" ID=repeaterOdd>
> <itemtemplate>
> <p align="left"><%#DataBinder.Eval(Container.DataItem,
> "catDesc") %></p>
> <p align="left">
> <a href="http://links.10026.com/?link=sculpture2.aspx?catDesc=<%#DataBinder.Eval(Container.DataItem,
> "catID")%>"
> <img src="http://pics.10026.com/?src=<%# DataBinder.Eval(Container.DataItem, "image") %>"></p>
> </itemtemplate>
> </asp:repeater>
> If I pass this parameter as CatID, then I can't retrieve it using a
> querysting, and the whole goal is to use a querystring to pass the
> parameter. (e.g int passedValue = Request.Querystring["catID"].
> Is there some way to pull the selected value of catID out of the
> repeater, put it in some kind of variable, and then run a method to
> attach it to a response.redirect?
> Is there another way to do this? Using a LinkButton?
> Thanks in advance.
>
Thanks for your response. How would I then pull the value?
Still with respons.querystring?
Thanks again.

Pass Paramter from Repeater - Cant Figure This Out

Hi,

I have a repeater based on a stored procedure. It pulls the following:

catDesc Image

The user sees the category description (catDesc) , and then clicks on
the image (Image) which is bound to the catID. This catID (@dotnet.itags.org.catID --
an integer) is then to be passed to the next page, sculpture2.aspx for
use in a datagrid with a stored procedure.

I understand how to pass a parameter using response.redirect. I've
done so with a dropdownlist in the past by using OnSelectedIndexChanged
and just using dropdownlist.selecteditem.value as the parameter (an
integer in this case) which I use with response.redirect.

Is there an equivalent techinque for using a repeater?

Here is my code:

<asp:repeater runat="server" ID=repeaterOdd>
<itemtemplate>
<p align="left"><%#DataBinder.Eval(Container.DataItem,
"catDesc") %></p>
<p align="left">
<a href="http://links.10026.com/?link=sculpture2.aspx?catDesc=<%#DataBinder.Eval(Container.DataItem,
"catID")%>"
<img src="http://pics.10026.com/?src=<%# DataBinder.Eval(Container.DataItem, "image") %>"></p>
</itemtemplate>
</asp:repeater
If I pass this parameter as CatID, then I can't retrieve it using a
querysting, and the whole goal is to use a querystring to pass the
parameter. (e.g int passedValue = Request.Querystring["catID"].

Is there some way to pull the selected value of catID out of the
repeater, put it in some kind of variable, and then run a method to
attach it to a response.redirect?

Is there another way to do this? Using a LinkButton?

Thanks in advance.you can replace the image tag with an asp:HyperLink that way the user can
click on the image and be redirected to a page with your catID accesible
thus:
<asp:HyperLink id="HyperLink1" runat="server" ImageUrl="<%#
DataBinder.Eval(Container.DataItem, "image") %>"
NavigateUrl=secondpage.aspx?caiID=<%#DataBinder.Eval(Container.DataItem,
"catID")%>"></asp:HyperLink
"Ranginald" <davidwank@.gmail.com> wrote in message
news:1146692474.566945.201390@.y43g2000cwc.googlegr oups.com...
> Hi,
> I have a repeater based on a stored procedure. It pulls the following:
> catDesc Image
> The user sees the category description (catDesc) , and then clicks on
> the image (Image) which is bound to the catID. This catID (@.catID --
> an integer) is then to be passed to the next page, sculpture2.aspx for
> use in a datagrid with a stored procedure.
> I understand how to pass a parameter using response.redirect. I've
> done so with a dropdownlist in the past by using OnSelectedIndexChanged
> and just using dropdownlist.selecteditem.value as the parameter (an
> integer in this case) which I use with response.redirect.
> Is there an equivalent techinque for using a repeater?
> Here is my code:
> <asp:repeater runat="server" ID=repeaterOdd>
> <itemtemplate>
> <p align="left"><%#DataBinder.Eval(Container.DataItem,
> "catDesc") %></p>
> <p align="left">
> <a href="http://links.10026.com/?link=sculpture2.aspx?catDesc=<%#DataBinder.Eval(Container.DataItem,
> "catID")%>"
> <img src="http://pics.10026.com/?src=<%# DataBinder.Eval(Container.DataItem, "image") %>"></p>
> </itemtemplate>
> </asp:repeater>
> If I pass this parameter as CatID, then I can't retrieve it using a
> querysting, and the whole goal is to use a querystring to pass the
> parameter. (e.g int passedValue = Request.Querystring["catID"].
> Is there some way to pull the selected value of catID out of the
> repeater, put it in some kind of variable, and then run a method to
> attach it to a response.redirect?
> Is there another way to do this? Using a LinkButton?
> Thanks in advance.
Thanks for your response. How would I then pull the value?
Still with respons.querystring?

Thanks again.

Pass values

I have the following Stored procedure. There is column named
"tcktreceived" in my database and I want to pass all the rows one by
one to the parameter @dotnet.itags.org.starttime. I don't know how to do it.

CREATE PROCEDURE [twcsan].[usp_DateDiff]
-- Add the parameters for the stored procedure here
@dotnet.itags.org.starttime DateTime

AS
BEGIN
DECLARE @dotnet.itags.org.Diff Varchar(15)
DECLARE @dotnet.itags.org.Day INT
DECLARE @dotnet.itags.org.Hour INT
DECLARE @dotnet.itags.org.Minute INT
DECLARE @dotnet.itags.org.Start_Date DateTime
DECLARE @dotnet.itags.org.End_Date DateTime
DECLARE @dotnet.itags.org.itemReceived DateTime
DECLARE @dotnet.itags.org.ID INT
DECLARE @dotnet.itags.org.message VARCHAR(50)

DECLARE @dotnet.itags.org.table TABLE
(
ItemReceived DateTime,
ID INT,
message text,
Differnce VARCHAR(20)

)
SET NOCOUNT ON;
SET @dotnet.itags.org.Start_Date = @dotnet.itags.org.starttime

SET @dotnet.itags.org.End_Date = GETDATE()
SET @dotnet.itags.org.Day = DATEDIFF( day, @dotnet.itags.org.Start_Date, @dotnet.itags.org.End_Date)
SET @dotnet.itags.org.Hour = DATEDIFF(hour , @dotnet.itags.org.Start_Date, @dotnet.itags.org.End_Date)
SET @dotnet.itags.org.Minute = DATEDIFF(minute , @dotnet.itags.org.Start_Date, @dotnet.itags.org.End_Date)
SET @dotnet.itags.org.Minute = @dotnet.itags.org.Minute-(@dotnet.itags.org.HOUR* 60)
SET @dotnet.itags.org.Hour = @dotnet.itags.org.Hour-(24* @dotnet.itags.org.Day)
SET @dotnet.itags.org.Diff = CONVERT(Varchar, @dotnet.itags.org.Day) +'d ' + CONVERT(Varchar , @dotnet.itags.org.Hour) +
'h ' + CONVERT(Varchar , @dotnet.itags.org.Minute) +'m'

INSERT INTO @dotnet.itags.org.table(ItemReceived, ID, message, Differnce)
select tck.tcktreceived, tck.ticketid,tckmsg.tcktmessage,@dotnet.itags.org.Diff
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid

select * from @dotnet.itags.org.table

ENDI'm not sure what your after, but try looking at using "Cursor" that might
give you what you need.

Pass values

I have the following Stored procedure. There is column named
"tcktreceived" in my database and I want to pass all the rows one by
one to the parameter @dotnet.itags.org.starttime. I don't know how to do it.
CREATE PROCEDURE [twcsan].[usp_DateDiff]
-- Add the parameters for the stored procedure here
@dotnet.itags.org.starttime DateTime
AS
BEGIN
DECLARE @dotnet.itags.org.Diff Varchar(15)
DECLARE @dotnet.itags.org.Day INT
DECLARE @dotnet.itags.org.Hour INT
DECLARE @dotnet.itags.org.Minute INT
DECLARE @dotnet.itags.org.Start_Date DateTime
DECLARE @dotnet.itags.org.End_Date DateTime
DECLARE @dotnet.itags.org.itemReceived DateTime
DECLARE @dotnet.itags.org.ID INT
DECLARE @dotnet.itags.org.message VARCHAR(50)
DECLARE @dotnet.itags.org.table TABLE
(
ItemReceived DateTime,
ID INT,
message text,
Differnce VARCHAR(20)
)
SET NOCOUNT ON;
SET @dotnet.itags.org.Start_Date = @dotnet.itags.org.starttime
SET @dotnet.itags.org.End_Date = GETDATE()
SET @dotnet.itags.org.Day = DATEDIFF( day, @dotnet.itags.org.Start_Date, @dotnet.itags.org.End_Date)
SET @dotnet.itags.org.Hour = DATEDIFF(hour , @dotnet.itags.org.Start_Date, @dotnet.itags.org.End_Date)
SET @dotnet.itags.org.Minute = DATEDIFF(minute , @dotnet.itags.org.Start_Date, @dotnet.itags.org.End_Date)
SET @dotnet.itags.org.Minute = @dotnet.itags.org.Minute-(@dotnet.itags.org.HOUR* 60)
SET @dotnet.itags.org.Hour = @dotnet.itags.org.Hour-(24* @dotnet.itags.org.Day)
SET @dotnet.itags.org.Diff = CONVERT(Varchar, @dotnet.itags.org.Day) +'d ' + CONVERT(Varchar , @dotnet.itags.org.Hour) +
'h ' + CONVERT(Varchar , @dotnet.itags.org.Minute) +'m'
INSERT INTO @dotnet.itags.org.table(ItemReceived, ID, message, Differnce)
select tck.tcktreceived, tck.ticketid,tckmsg.tcktmessage,@dotnet.itags.org.Diff
from tbtickets tck inner join tbticketsmessages tckmsg
on tck.ticketid = tckmsg.ticketid
select * from @dotnet.itags.org.table
ENDI'm not sure what your after, but try looking at using "Cursor" that might
give you what you need.