I have never used stored functions before so trying to use them has become very frustrating. I am using an IBuySpy variant called EShopServer. Right now a customer puts all of their personal information into a form with inputs and it is put into the database.
Simple enough. But, I want the form to include their email address and I have no idea why I can't get it to put it in the database. I keep getting the EShopServer's default program error so I have no idea what is causing it. I have narrowed it down to when
the page tries to insert the data in the database though the stored function. Perhaps someone can figure out what I am doing wrong. Any help would be greatly appreciated. Here is the page and the stored function: *********************************************************
******** Stored Function ****************** ********************************************************* SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ALTER PROCEDURE dbo.OrdersAdd ( @UserID int, @CartID nvarchar(50), @OrderDate datetime, @ShipDate datetime,
@FirstName nvarchar(50), @LastName nvarchar(50), @Street nvarchar(50), @City nvarchar(50), @Country nvarchar(50), @State nvarchar(50), @PostalCode nvarchar(50), @Telephone nvarchar(50), @EmailAddress nvarchar(50), @sFirstName nvarchar(50), @sLastName nvarchar(50),
@sStreet nvarchar(50), @sCity nvarchar(50), @sCountry nvarchar(50), @sState nvarchar(50), @sPostalCode nvarchar(50), @sTelephone nvarchar(50), @Inquiries ntext, @PayMethod bit, @ShipRate money, @OrderID int OUTPUT ) AS BEGIN TRAN AddOrder /* Create the Order
header */ INSERT INTO dbo.Orders ( UserID, OrderDate, ShipDate, FirstName, LastName, Street, City, Country, State, PostalCode, Telephone, EmailAddress, sFirstName, sLastName, sStreet, sCity, sCountry, sState, sPostalCode, sTelephone, Inquiries, PayMethod,
ShipRate, Delivered ) VALUES ( '2', @OrderDate, @ShipDate, @FirstName, @LastName, @Street, @City, @Country, @State, @PostalCode, @Telephone, @EmailAddress, @sFirstName, @sLastName, @sStreet, @sCity, @sCountry, @sState, @sPostalCode, @sTelephone, @Inquiries,
@PayMethod, @ShipRate, 0 ) SELECT @OrderID = @@Identity /* Copy items from given shopping cart to OrdersDetail table for given OrderID*/ INSERT INTO dbo.OrderDetails ( OrderID, ProductID, Quantity, Title, SKU, UnitCost ) SELECT @OrderID, ShoppingCart.ProductID,
ShoppingCart.Quantity, Products.Title + ' ' + ProductOptions.Title AS Title, SKU, Products.UnitCost - Products.Discount + ProductOptions.Amount AS UnitCost FROM Products INNER JOIN ShoppingCart ON Products.ProductID = ShoppingCart.ProductID INNER JOIN ProductOptions
ON ShoppingCart.OptionID = ProductOptions.OptionID WHERE CartID = @CartID /* Removal of items from user's shopping cart will happen on the business layer*/ EXEC ShoppingCartEmpty @CartId COMMIT TRAN AddOrder GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON
GO ********************************************************* ***************** Webpage ********************* ********************************************************* <script runat="server"> Private sdResults As StringDictionary '*******************************************************
' ' The Page_Load event on this page is used to populate ' the controls with order details ' '******************************************************* Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) SubmitButton.ImageUrl = Application("ImagePath")
& "/bt_submit.gif" Dim coll As NameValueCollection = Request.QueryString Dim arr() As String = coll.GetValues(0) DecodeUrl(HttpUtility.UrlDecode(arr(0))) If Page.IsPostBack = False Then ' Obtain a single row of store information Dim store As New ASPNetStore.Store()
Dim dr As SqlDataReader = store.StoreDetails() ' Load first row into DataReader dr.Read() termsofPayment.Text = Server.HtmlDecode(CType(dr("PaymentTerm"), String)) ' Close the datareader dr.Close() ' Hide if no information If (termsofPayment.Text.Length =
0) Then termsPanel.Visible = False ' Calculate end-user's shopping cart ID dim cart as ASPNETStore.ShoppingCart = New ASPNETStore.ShoppingCart() Dim cartId As String = cart.GetShoppingCartId() ' Populate datagrid with shopping cart data ProductGrid.DataSource
= cart.GetItems(cartId) ProductGrid.DataBind() With CustomerDetails .FirstName = sdResults("fname") .LastName = sdResults("lname") .Street = sdResults("street") .City = sdResults("city") .Country = sdResults("country") .State = sdResults("state") .PostalCode
= sdResults("zip") .Telephone = sdResults("phone") .EmailAddress = sdResults("EmailAdd") .sFirstName = sdResults("sfname") .sLastName = sdResults("slname") .sStreet = sdResults("sstreet") .sCity = sdResults("scity") .sCountry = sdResults("scountry") .sState
= sdResults("sstate") .sPostalCode = sdResults("szip") .sTelephone = sdResults("sphone") .Inquiries = sdResults("inq") End With ' Update Sub Total Price Label lblSubTotal.Text = String.Format("{0:c}", cart.GetTotal(cartId)) ' Update shipping total price label
Dim shipRate As New ASPNetShip.NetShip() lblShipping.Text = String.Format("{0:c}", shipRate.GetShipRate(CType(lblSubTotal.Text, Double))) ' Update Total Price Label lblTotal.Text = String.Format("{0:c}", CType(lblSubTotal.Text, Double) + CType(lblShipping.Text,
Double)) ' Show/Hide CC Panel CCPanel.Visible = Not CType(sdResults("pay"), Boolean) End If End Sub '********************************************************************* ' ' DecodeUrl method ' ' Decode the URL and store the value pairs in a ' StringDictinary
Collection. ' '********************************************************************* Private Sub DecodeUrl(results As String) Dim rsltValues() As String Dim result As Integer = 0 sdResults = New StringDictionary() sdResults.Clear() rsltValues = results.Split(New
Char() {"&"c}) Dim rValue As String For Each rValue In rsltValues Dim valPair() As String valPair = rValue.Split(New Char() {"="c}) sdResults(valPair(0)) = valPair(1) Next rValue End Sub '******************************************************* ' ' The PlaceOrder
method places the order and redirects ' to the thank you page ' '******************************************************* Private Sub PlaceOrder() Dim cart As ASPNETStore.ShoppingCart = New ASPNETStore.ShoppingCart() ' Calculate end-user's shopping cart ID
Dim cartId As String = cart.GetShoppingCartId() ' Calculate end-user's customerID Dim customerId As String = Nothing Dim users As New ASPNETPortal.UsersDB() Dim dr As SqlDataReader = users.GetSingleUser(context.User.Identity.Name) ' Read first row from database
If (dr.Read()) Then customerId = CType(dr("UserID"), String) ' Close datareader dr.Close() 'People can make orders without logging in WAS If (Not cartId Is Nothing) And (Not customerId Is Nothing) Then If (Not cartId Is Nothing) Then ' Place the order Dim
ordersDatabase As ASPNETStore.Orders = New ASPNETStore.Orders() Dim orderId As Integer = ordersDatabase.PlaceOrder(customerId, _ cartId, _ CustomerDetails.FirstName, _ CustomerDetails.LastName, _ CustomerDetails.Street, _ CustomerDetails.City, _ CustomerDetails.Country,
_ CustomerDetails.State, _ CustomerDetails.PostalCode, _ CustomerDetails.Telephone, _ CustomerDetails.EmailAddress, _ CustomerDetails.sFirstName, _ CustomerDetails.sLastName, _ CustomerDetails.sStreet, _ CustomerDetails.sCity, _ CustomerDetails.sCountry, _
CustomerDetails.sState, _ CustomerDetails.sPostalCode, _ CustomerDetails.sTelephone, _ Server.HtmlEncode(CustomerDetails.Inquiries), _ CType(sdResults("pay"), Integer), _ CType(lblShipping.Text, Double)) Dim mailService As New ASPNetMail.MailWebService() Dim
emailType As New ASPNetMail.MailWebService.EmailTypeEnum() mailService.SendMail(context.User.Identity.Name.ToLower(), emailType.OrderPlaced, "", "") ' Redirect to the check out complete page Response.Redirect("~/storemodules/thankyou.aspx?orderid=" & orderId.ToString())
End If End Sub '******************************************************* ' ' The SubmitBtn_Click event submits the order for ' processing ' '******************************************************* Sub SubmitBtn_Click(ByVal sender As Object, ByVal e As ImageClickEventArgs)
If Not (AuthorizationField.Checked) Then reqAuthorization.Text = "You need to confirm your order" reqAuthorization.IsValid = False Else reqAuthorization.IsValid = True End If ' Only process order if the Entered Data is Valid If Page.IsValid = True Then PlaceOrder()
End If End Sub </script> <form ID="CheckOut" runat="server">
ThomasLamar
Member
10 Points
2 Posts
Insert into SQL with stored functions
Sep 10, 2003 06:32 PM|LINK
01 02 03 04 05 06 07 08 09 10 11 12 / 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012
Authorization: