473,842 Members | 1,372 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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").Valu e
If (rdlAddress.Sel ectedItem.Value = "no") Then
boShopCart.Plac eOrder(iUserID, txtAddress.Text , txtCity.Text,
txtState.Text, txtCountry.Text , txtZip.Text)
Else
boShopCart.Plac eOrder(iUserID, "", "", "", "", "")
End If
Response.Redire ct("ThankYou.as px")
End Sub

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

Public Sub PlaceOrder(ByVa l 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("Pla ceOrder", sqlConn)
sqlCmd.CommandT ype = CommandType.Sto redProcedure

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

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

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

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

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

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

sqlConn.Open()
sqlCmd.ExecuteN onQuery()
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 2630
<rn**@rediffmai l.comwrote in message
news:11******** **************@ f16g2000cwb.goo glegroups.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**@rediffmai l.comwrote in message
news:11******** **************@ f16g2000cwb.goo glegroups.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**@rediffmai l.comwrote in message
news:11******** **************@ e3g2000cwe.goog legroups.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**@rediffmai l.comwrote in message
news:11******** **************@ e3g2000cwe.goog legroups.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**@rediffmai l.comwrote in message
news:11******** **************@ e3g2000cwe.goog legroups.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**@rediffmai l.comwrote in message
news:11******** **************@ e3g2000cwe.goog legroups.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
15493
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 & customer_number, customer_name, customer_address, customer_city, customer_state, customer_zip, customer_phone, vin, acct_number, tag, title_number, tag_type_number, branch_number, institution_number, yr_make, state_from, type_number)...
3
7511
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 getting time out errors on my ASP page. The problem is that when I call the stored procedure from the ASP page the server waits for the procedure to complete before returning the ASP page.. Is there any way to avoid this? I would like the user to...
2
13621
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 * from Employee;" <% Dim objRS, sqlStmt set objRS = Server.CreateObject("ADODB.Recordset") Dim conn Set conn = Server.CreateObject("ADODB.Connection")
3
13055
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, Procedure Add_Adjustment, Line 18. I'm zhoskin. I am the dbo and created the procedure, and when I look at its properties, I have EXEC permission. Line 18 is just the return statement. The values are all appropriate for the table. So what is...
1
1665
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 procedures that do selects.
1
12244
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 click on the Tables icon. Here's my DTS statement: exec omsd..OMS_TECO.SP_Callback_Update_Pkg(116); omsd is the linked server
2
2045
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 up a custom form to accept the input parameters (MS Feature Request!) for the Stored Procedure. We had two problems with MS's
2
3339
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 several stored procedures to Insert and update datas in our SQL database. Some stored procedures are smaller (insert datas in only one table) and some of them are quite big (insert datas in several
7
3227
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, …? Thanks, Jim.
0
2338
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 & _ " WHERE (((IDU) Like 'czz*' Or (IDU) Like 'cam*' Or (IDU) _ Like 'szz*' Or (IDU) Like 'J*' Or (IDU) Like 'G*' Or (IDU) _ Like 'U*'))" & " ORDER BY IDU;"
0
9717
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10618
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10683
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10320
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9459
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7045
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5699
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5887
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4096
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.