473,320 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Execute Stored Procedure

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?

Oct 22 '06 #1
6 2580
<rn**@rediffmail.comwrote in message
news:11**********************@f16g2000cwb.googlegr 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...
Oct 22 '06 #2
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:
<rn**@rediffmail.comwrote in message
news:11**********************@f16g2000cwb.googlegr 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...
Oct 22 '06 #3
<rn**@rediffmail.comwrote in message
news:11**********************@e3g2000cwe.googlegro 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...
Oct 22 '06 #4
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:
<rn**@rediffmail.comwrote in message
news:11**********************@e3g2000cwe.googlegro 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...
Oct 22 '06 #5
<rn**@rediffmail.comwrote in message
news:11**********************@e3g2000cwe.googlegro 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...
Oct 22 '06 #6
Wrapping the 4 separate statements in a transaction.......that's indeed
a great suggestion.
Mark Rae wrote:
<rn**@rediffmail.comwrote in message
news:11**********************@e3g2000cwe.googlegro 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...
Oct 22 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: PJ | last post by:
<% Set Conn = Server.CreateObject("ADODB.Connection") Conn.open application("dtat_motor_connectionstring") set rs = new adodb.recordset 'Set RS = Conn.Execute(' "exec spcn_update_transactions &...
3
by: Richard Morey | last post by:
Hi.. I have written a stored procedure that take 5 - 8 minutes to fully execute.. I wrote this routine as a stored procedure because I started to create all the functionality via ASP but I kept...
2
by: Matt | last post by:
I want to exexute stored procedure in ASP, but it has error "Microsoft VBScript compilation (0x800A0401) Expected end of statement" on line (1). The stored procedure "sp_emp" contain "select *...
3
by: Zeke Hoskin | last post by:
Suddenly a stored procedure, very much like several others, is giving EXECUTE permission denied on object 'Add_Adjustment', database 'InStab', owner 'zhoskin'. server:Msg 229, Level 14, State 5,...
1
by: HD | last post by:
Is there a way to let an account have execute permission on a stored procedure but not let that stored procedure run insert , delete, or update records. Basically only let them run or create stored...
1
by: robin via SQLMonster.com | last post by:
I've tried several different way to execute a oracle stored procedure from a DTS package but to no avail. I have a Linked Server setup which does bring back Oracle tables from the server when I...
2
by: Noloader | last post by:
Hello, Access XP, SQL Server 2000 Is it possible to hide a SP under Queries in Access, yet still be able to Execute it from Access? (Similar to hiding Tables, then using Views) We hooked...
2
by: Eli | last post by:
Hi all We currently have a strange problem with calling a Stored Procedure (SQL Database) in our C# Project. The only error I get is "System error" which says a lot :) Background: We have...
7
by: JIM.H. | last post by:
Hello, Is there any difference to between SLQ string in the code and call execute query and call a stored procedure and execute the query that way concerning speed, effectiveness, reliability,...
0
by: franjorge | last post by:
Hi, I have created two stored procedures via VB using this code: sql = "CREATE PROC " & nombre_proc & " AS SELECT *" & _ " From MBM_PUNTOS_SCE_SIN_COINCIDIR_SIEGE_FALTA_PM_NE_" & mes & _ "...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.