repeat always first product in shopping cart (vb)

astimas · January 24th, 2010, 05:09 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?

chroniclemaster1 · January 25th, 2010, 01:49 AM

I hope someone who knows more about your situation stops by. However... if they don't, I've found that the best way to work with an Access database it using the SQL Server providers (not Access or OleDb). You need to use Access's custom JET 4.0 SQL for your INSERT, UPDATE, SELECT, and DELETE statements, but otherwise I've had very good success and have a lot of experience running websites on shared hosting with limited resources that way.

timeware · January 26th, 2010, 08:32 AM

Can you details for use the entries you are trying to make and the actual entries recorded please?

One would assume that the line

Code:

For Each item As CartItem In Profile.Cart.Items

is the one causing the error.

I haven't used access for some time now but always found it best to write the lines to be reated to a screen to see the real data before enabling the insert lines.