Chapter 13 Shopping Cart

michaelcode · #1 (**permalink**) September 2nd, 2006, 05:57 PM

In Checkout.aspx the line below

OrderID = Convert.ToInt32(cmd.ExecuteScalar())

OrderID is always 0 when inserted into table OrderLines

Can anyone please advise.

thanks,
Michael.

Alexi · #2 (**permalink**) September 11th, 2006, 02:41 AM

Hi,
Might be because you haven't debugged and there is an error on p.479 - Evel should have brackets around.i.e. Eval("ProductID", "WroxShopItem.aspx?ProductID={0}")

Alexi · #3 (**permalink**) October 5th, 2006, 06:43 AM

Hey, I finally found the answer if you are still interested:

you add this to the end of the first INSERT SQL statement: & _
"SELECT @@IDENTITY AS OrderID;" and it works fine

DanM · #4 (**permalink**) October 13th, 2006, 10:09 AM

michaelcode,

As noted in this thread - http://p2p.wrox.com/topic.asp?TOPIC_ID=40977 - you might also want to try using

'SELECT SCOPE_IDENTITY() AS OrderID'

as well.

dan

Dan Maharry
Read the feed at http://feeds.feedburner.com/dansarchive

Zhu-ShengJun · #5 (**permalink**) May 24th, 2007, 07:57 PM

Hi,
I'm using VWD 2005 Express with C#.
cmd.CommandText = "INSERT INTO Orders(MemberName,OrderDate,Name,Address,County,Po stCode,Country,Total)" +
"VALUES (@MemberName,@OrderDate,@Name,@Address,@County,@Po stCode,@Country,@Total)"+"SELECT @@IDENTITY AS OrderID";
It is OK now.

astimas · #6 (**permalink**) January 24th, 2010, 05:06 PM

Hello, I'm trying to adapt the code for the shopping cart to a database in Access instead of SQL Express.

the code is as follows :

Code:

 
Dim OrderID As Integer
Dim strConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Database1.mdb")
Dim mySql As String = "INSERT INTO Orders(OrderDate, Company, Contact, Email, Phone, Address, City, PostCode, Country, Shipment, Notes) " & _
           "VALUES (Now(), @Company, @Contact, @Email, @Phone, @Address, @City, @PostCode, @Country, @Shipment, @Notes)"
   
        Dim cmd As New OleDbCommand(mySql, strConn)
        
        Try
            strConn.Open()

            'cmd.Parameters.AddWithValue("MemberName", User.Identity.Name)
            cmd.Parameters.AddWithValue("Company", txtCompany.Text)
            cmd.Parameters.AddWithValue("Contact", txtContact.Text)
            cmd.Parameters.AddWithValue("Email", txtEmail.Text)
            cmd.Parameters.AddWithValue("Phone", txtPhone.Text)
            cmd.Parameters.AddWithValue("Address", txtAddress.Text)
            cmd.Parameters.AddWithValue("City", txtCountry.Text)
            cmd.Parameters.AddWithValue("PostCode", txtPostCode.Text)
            cmd.Parameters.AddWithValue("Country", txtCountry.Text)
            cmd.Parameters.AddWithValue("Shipment", txtShipment.Text)
            cmd.Parameters.AddWithValue("Notes", txtNote.Text)
         
            cmd.ExecuteNonQuery()    'This executes the first command
            cmd.CommandText = "SELECT @@Identity As OrderID"
            
            OrderID = cmd.ExecuteScalar() 'This Retrieves the OrderID that was created 
           
            cmd.CommandText = "INSERT INTO OrderLines(OrderID, ProductID, ProductName, Quantity, Measure, Package) " & _
             "VALUES (@OrderID, @ProductID, @ProductName, @Quantity, @Measure, @Package)"
            
            cmd.Parameters.Clear()
            cmd.Parameters.AddWithValue("@OrderID", OrderID)
           
            For Each item As CartItem In Profile.Cart.Items
           
                cmd.Parameters.AddWithValue("@ProductID", item.ProductID)
                cmd.Parameters.AddWithValue("@ProductName", item.ProductName)
                cmd.Parameters.AddWithValue("@Quantity", item.Quantity)
                cmd.Parameters.AddWithValue("@Measure", item.Measure)
                cmd.Parameters.AddWithValue("@Package", item.Package)
                
                cmd.ExecuteNonQuery()
            Next
          
            ' clear the cart
            Profile.Cart.Items.Clear()


        Catch ex As Exception
          
            Throw

        Finally
            strConn.Close()

        End Try

work pretty well, the problem is that if I put in the cart (for example) 5 products, he writes in the database 5 times the first product.

What am I doing wrong?

Imar · #7 (**permalink**) January 24th, 2010, 05:50 PM

Hi there,

Try calling cmd.Parameters.Clear() as the first line in your for loop and see of that makes a difference.

Chers,

Imar

astimas · #8 (**permalink**) January 25th, 2010, 03:27 AM

Thanks

I tried, but I get the error "OleDBException. No value specified for some required parameters."

Imar · #9 (**permalink**) January 25th, 2010, 03:32 AM

In that case, take a look here:

http://social.msdn.microsoft.com/for...5-fc726bb62e09

Create the parameters outside the loop, and then set their value using cmd.Parameters.Item(0).Value inside the loop.

Cheers,

Imar

astimas · #10 (**permalink**) January 25th, 2010, 03:50 AM

I modified code as follows :

Code:

  For Each item As CartItem In Profile.Cart.Items
                cmd.Parameters.Clear()
                
                cmd.CommandText = "INSERT INTO OrderLines(OrderID, ProductID, ProductName, Quantity, Measure, Package) " & _
             "VALUES (@OrderID, @ProductID, @ProductName, @Quantity, @Measure, @Package)"
     
                cmd.Parameters.AddWithValue("@OrderID", OrderID)
                cmd.Parameters.AddWithValue("@ProductID", item.ProductID)
                cmd.Parameters.AddWithValue("@ProductName", item.ProductName)
                cmd.Parameters.AddWithValue("@Quantity", item.Quantity)
                cmd.Parameters.AddWithValue("@Measure", item.Measure)
                cmd.Parameters.AddWithValue("@Package", item.Package)
                
                cmd.ExecuteNonQuery()
            Next

and seems to work

thanks anyway for the help

Need to download code?

Navigation