Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Execute Stored Procedure

Reply
Thread Tools

Execute Stored Procedure

 
 
rn5a@rediffmail.com
Guest
Posts: n/a
 
      10-22-2006
In a shopping cart app, when a user finalizes his order, records from a
temporary table named 'TempCart' get inserted into another table name
'Orders' after which the records from 'TempCart' are deleted.
'TempCart' has the following columns:

UserID int (no NULLs)
Carpet int (NULLs allowed)
Color int (NULLs allowed)
Engine int (NULLs allowed)
Total decimal(10, 2) (NULLs allowed)

Just prior to finalizing his order, a user has to enter his address
where he wants the items to be shipped to. The address details are
inserted in another table named 'Address'. The 'Address' table has the
following columns:

AddrID int IDENTITY
UserID int (no NULLs)
OrderID int (no NULLs)
Addr varchar(100) (NULLs allowed)
City varchar(50) (NULLs allowed)
State varchar(50) (NULLs allowed)
Country varchar(50) (NULLs allowed)
Zip varchar(50) (NULLs allowed)

The entire app is ASP.NET based.

Since a user can place multiple orders, they have been given the
provision to view the various details of the orders they have placed.
One of those details is the address they had entered just before
finalizing an order. Before placing an order, a user has to register
himself during which his address details are mandatory (registration
details are inserted in a table named 'AddUsers'). Hence just before
finalizing an order, a user can choose whether the address he wants his
items to be shipped to remains the same or he wants to get the items
shipped to another address. If he opts for the former option, the
'Addr', 'City', 'State', 'Country' & 'Zip' values in the 'Address'
table will be NULL else these 5 columns will be inserted with the new
address. To accomplish this, I framed the following stored procedure:

ALTER PROCEDURE dbo.PlaceOrder
@UserID int,
@Total decimal,
@Addr varchar(100) = NULL,
@City varchar(50) = NULL,
@State varchar(50) = NULL,
@Country varchar(50) = NULL,
@Zip varchar(50) = NULL
AS
INSERT INTO Orders (UserID, Carpet, Color, Engine, Total, OrderDate)
SELECT tc.UserID, tc.Carpet, tc.Color, tc.Engine, @Total, GETDATE()
FROM
TempCart tc
WHERE
tc.UserID = @UserID

INSERT INTO Address (OrderID, UserID)
SELECT OrderID, UserID
FROM
Orders
WHERE
UserID = @UserID

INSERT INTO Address (Addr, City, State, Country, Zip) VALUES (@Addr,
@City, @State, @Country, @Zip)
WHERE
UserID = @UserID

DELETE FROM TempCart WHERE UserID = @UserID

Now what I find is when a user finalizes an order & the above stored
procedure gets executed, though the 'OrderID' & 'UserID' columns in the
'Address' table gets populated successfully with the 'OrderID' &
'UserID' values from the 'Orders' table (i.e. if the OrderID of an
order in the 'Orders' table is, say, 15 for UserID=7, then the
'OrderID' & 'UserID' columns in the 'Address' table also get correctly
populated with 15 & 7 respectively) but still the ASP.NET page
generates this error:

Cannot insert the value NULL into column 'OrderID', table
'dbo.Address'; column does not allow nulls. INSERT fails.
The statement has been terminated.

This is how I am invoking the above stored procedure from the ASPX
page:

Sub CheckOut(ByVal obj As Object, ByVal ea As EventArgs)
boShopCart = New ShopCart
iUserID = Request.Cookies("UserID").Value
If (rdlAddress.SelectedItem.Value = "no") Then
boShopCart.PlaceOrder(iUserID, txtAddress.Text, txtCity.Text,
txtState.Text, txtCountry.Text, txtZip.Text)
Else
boShopCart.PlaceOrder(iUserID, "", "", "", "", "")
End If
Response.Redirect("ThankYou.aspx")
End Sub

& this is the 'PlaceOrder' function in a VB class file:

Public Sub PlaceOrder(ByVal UserID As Integer, ByVal Addr As String,
ByVal City As String, ByVal State As String, ByVal Country As String,
ByVal Zip As String)
Dim sqlCmd As SqlCommand
Dim dblTotal As Double = 0

dblTotal = GetTotal(UserID)
sqlCmd = New SqlCommand("PlaceOrder", sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure

Try
With sqlCmd
.Parameters.Add("@UserID", SqlDbType.Int).Value = UserID
.Parameters.Add("@Total", SqlDbType.Decimal).Value =
dblTotal

If (Addr <> "") Then
.Parameters.Add("Addr", SqlDbType.VarChar, 100).Value =
Addr
Else
.Parameters.Add("Addr", SqlDbType.VarChar, 100).Value =
DBNull.Value
End If

If (City <> "") Then
.Parameters.Add("City", SqlDbType.VarChar, 50).Value =
City
Else
.Parameters.Add("City", SqlDbType.VarChar, 50).Value =
DBNull.Value
End If

If (State <> "") Then
.Parameters.Add("State", SqlDbType.VarChar, 50).Value =
State
Else
.Parameters.Add("State", SqlDbType.VarChar, 50).Value =
DBNull.Value
End If

If (Country <> "") Then
.Parameters.Add("Country", SqlDbType.VarChar, 50).Value
= Country
Else
.Parameters.Add("Country", SqlDbType.VarChar, 50).Value
= DBNull.Value
End If

If (Zip <> "") Then
.Parameters.Add("Zip", SqlDbType.VarChar, 50).Value =
Zip
Else
.Parameters.Add("Zip", SqlDbType.VarChar, 50).Value =
DBNull.Value
End If

sqlConn.Open()
sqlCmd.ExecuteNonQuery()
sqlConn.Close()
Catch ex As Exception
Throw ex
End Try
End Sub

The error points to the Throw ex line within the Catch statement.

What am I doing wrong here?

 
Reply With Quote
 
 
 
 
Mark Rae
Guest
Posts: n/a
 
      10-22-2006
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...

> Now what I find is when a user finalizes an order & the above stored
> procedure gets executed, though the 'OrderID' & 'UserID' columns in the
> 'Address' table gets populated successfully with the 'OrderID' &
> 'UserID' values from the 'Orders' table (i.e. if the OrderID of an
> order in the 'Orders' table is, say, 15 for UserID=7, then the
> 'OrderID' & 'UserID' columns in the 'Address' table also get correctly
> populated with 15 & 7 respectively) but still the ASP.NET page
> generates this error:
>
> Cannot insert the value NULL into column 'OrderID', table
> 'dbo.Address'; column does not allow nulls. INSERT fails.
> The statement has been terminated.


> The error points to the Throw ex line within the Catch statement.
>
> What am I doing wrong here?


Is it possible that your code is trying to insert the record into the
Address table more times than it should...?

Pretty easy to find out...

1) Step through the code (!). You say that the record *DOES* get created
correctly, so you know at least that that part of the code works, but maybe
your loop has a bug in it...?

2) Set up an SQL Trace and inspect the raw SQL that ADO.NET is generating
and sending to SQL Server...


 
Reply With Quote
 
 
 
 
rn5a@rediffmail.com
Guest
Posts: n/a
 
      10-22-2006
Thanks for the prompt response, Mark, but which loop are you referring
to?

Do you find anything wrong in either the stored procedure or the ASPX
code?


Mark Rae wrote:
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) oups.com...
>
> > Now what I find is when a user finalizes an order & the above stored
> > procedure gets executed, though the 'OrderID' & 'UserID' columns in the
> > 'Address' table gets populated successfully with the 'OrderID' &
> > 'UserID' values from the 'Orders' table (i.e. if the OrderID of an
> > order in the 'Orders' table is, say, 15 for UserID=7, then the
> > 'OrderID' & 'UserID' columns in the 'Address' table also get correctly
> > populated with 15 & 7 respectively) but still the ASP.NET page
> > generates this error:
> >
> > Cannot insert the value NULL into column 'OrderID', table
> > 'dbo.Address'; column does not allow nulls. INSERT fails.
> > The statement has been terminated.

>
> > The error points to the Throw ex line within the Catch statement.
> >
> > What am I doing wrong here?

>
> Is it possible that your code is trying to insert the record into the
> Address table more times than it should...?
>
> Pretty easy to find out...
>
> 1) Step through the code (!). You say that the record *DOES* get created
> correctly, so you know at least that that part of the code works, but maybe
> your loop has a bug in it...?
>
> 2) Set up an SQL Trace and inspect the raw SQL that ADO.NET is generating
> and sending to SQL Server...


 
Reply With Quote
 
Mark Rae
Guest
Posts: n/a
 
      10-22-2006
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed) ups.com...

> Thanks for the prompt response, Mark, but which loop are you referring
> to?


You mention that the user can make multiple orders on the same page - aren't
you going through some sort of logic loop to enter these one by one...?

> Do you find anything wrong in either the stored procedure or the ASPX
> code?


Not at first glance...


 
Reply With Quote
 
rn5a@rediffmail.com
Guest
Posts: n/a
 
      10-22-2006
You hit the nail on the head, Mark.....the looping logic was going
wrong....a few changes in the stored procedure took care of the problem

ALTER PROCEDURE dbo.PlaceOrder
@UserID int,
@Total decimal,
@Addr varchar(100) = NULL,
@City varchar(50) = NULL,
@State varchar(50) = NULL,
@Country varchar(50) = NULL,
@Zip varchar(50) = NULL
AS
INSERT INTO Orders (UserID, Carpet, Color, Engine, Total, OrderDate)
SELECT tc.UserID, tc.Carpet, tc.Color, tc.Engine, @Total, GETDATE()
FROM
TempCart tc
WHERE
tc.UserID = @UserID

INSERT INTO Address (OrderID, UserID)
SELECT OrderID, UserID
FROM
Orders
WHERE
UserID = @UserID AND
OrderID = (SELECT MAX(OrderID) FROM Orders WHERE UserID = @UserID)

UPDATE Address
SET
Addr = @Addr,
City = @City,
State = @State,
Country = @Country,
Zip = @Zip,
WHERE
UserID = @UserID AND
OrderID = (SELECT MAX(OrderID) FROM Orders WHERE UserID = @UserID)

DELETE FROM TempCart WHERE UserID = @UserID

Thanks for pointing out the flaw, Mark


Mark Rae wrote:
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) ups.com...
>
> > Thanks for the prompt response, Mark, but which loop are you referring
> > to?

>
> You mention that the user can make multiple orders on the same page - aren't
> you going through some sort of logic loop to enter these one by one...?
>
> > Do you find anything wrong in either the stored procedure or the ASPX
> > code?

>
> Not at first glance...


 
Reply With Quote
 
Mark Rae
Guest
Posts: n/a
 
      10-22-2006
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed) ups.com...

> You hit the nail on the head, Mark.....the looping logic was going
> wrong....
> Thanks for pointing out the flaw, Mark


Welcome.

BTW, you could make the SP much more robust by wrapping the four separate
statements in a transaction...


 
Reply With Quote
 
rn5a@rediffmail.com
Guest
Posts: n/a
 
      10-22-2006
Wrapping the 4 separate statements in a transaction.......that's indeed
a great suggestion.


Mark Rae wrote:
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) ups.com...
>
> > You hit the nail on the head, Mark.....the looping logic was going
> > wrong....
> > Thanks for pointing out the flaw, Mark

>
> Welcome.
>
> BTW, you could make the SP much more robust by wrapping the four separate
> statements in a transaction...


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
stored procedure takes too much time to execute nicholas ASP .Net 7 08-03-2005 07:20 PM
Stored Procedure vs direct execute SQL =?Utf-8?B?SklNLkgu?= ASP .Net 7 05-27-2005 10:12 PM
Help with Code to Execute a Stored Procedure Jeff Thur ASP .Net 2 02-07-2005 08:58 PM
How can I execute a stored procedure in .net? FireListen ASP .Net 1 06-27-2003 01:39 PM



Advertisments