Execute Stored Procedure

[rn5a]

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?

 

[Mark Rae]

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

 

[rn5a]

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]

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

 

[rn5a]

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]

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

 

[rn5a]

Wrapping the 4 separate statements in a transaction.......that's indeed
a great suggestion.