Solved

Inserting rows from DataTable into SQL Server database table

Posted on 2008-10-16
9
2,219 Views
Last Modified: 2012-05-05
I am building a web site in ASP.NET with VB.NET on MS Visual Web Developer .NET.  After an online customers add products to the shopping cart (I'm using a DataTable) and is going through the Checkout process, I need help to insert the details from each row of the datatable into the Orders_detail table of my SQL Server 2005 database.

I start off with Page_Load event:
If Not IsNothing(Session("Cart")) Then
            objDT = Session("Cart")
           
            dg.DataSource = objDT
            dg.DataBind()
            ...
End If

I try to go through each datarow:
 For intCounter = 0 To objDT.Rows.Count - 1
            objDR = objDT.Rows(intCounter)
            strSKU = objDR("SKU")
        Next
But that throws an error.
And once I set each variable to the values in each field of the datatable, I insert it into the database:
Dim AddOrdersDetail As String = "Insert into orders_detail (id, sku, product_name, price, qty) Values ('ShoppingCartNr','sku','product_name','price','qty')"
        Dim Cmd7 As New SqlCommand(AddOrdersDetail, MyConn)
        MyConn.Open()
        Cmd7.ExecuteNonQuery()
        MyConn.Close()
0
Comment
Question by:OVC-it-guy
  • 6
  • 2
9 Comments
 

Author Comment

by:OVC-it-guy
ID: 22734776
I'm thinking I probably need something like this:

For Each objDR In objDT.Rows
            strSKU = objDR("Sku")
            strProduct = objDR("Product")
            strPrice = objDR("Cost")
            strQty = objDR("Quantity")
            Dim AddOrdersDetail As String = "Insert into orders_detail (id, sku, product_name, price, qty) Values ('ShoppingCartNr','strSKU','strProduct','strPrice','strQty')"
            Dim Cmd7 As New SqlCommand(AddOrdersDetail, MyConn)
            MyConn.Open()
            Cmd7.ExecuteNonQuery()
            MyConn.Close()
        Next
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 22734789
What error do you get when you are cycling through your data rows?
You'll need to fix up your values section of the insert string also.  
0
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 500 total points
ID: 22734811
Dim AddOrdersDetail As String = "Insert into orders_detail (id, sku, product_name, price, qty) Values ('" & ShoppingCartNr & "', '" & strSKU & "', '" & strProduct & "', '" & strPrice & "', '" & strQty & "')"

You can't just put single quotes around a string variable
0
 

Author Comment

by:OVC-it-guy
ID: 22734834
Something about it not being a collection or array or similar.  I went with the For Each objDR In objDT.Rows and got past the error.  Ok, fixing my string variables.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:OVC-it-guy
ID: 22734933
And here's what it looks like now:
For Each objDR In objDT.Rows
            strSKU = objDR("Sku")
            strProduct = objDR("Product")
            strPrice = objDR("Cost")
            strQty = objDR("Quantity")
            Dim AddOrdersDetail As String = "Insert into orders_detail (id, sku, product_name, price, qty) Values ('" & ShoppingCartNr & "','" & strSKU & "','" & strProduct & "','" & strPrice & "','" & strQty & "')"
            Dim Cmd7 As New SqlCommand(AddOrdersDetail, MyConn)
            MyConn.Open()
            Cmd7.ExecuteNonQuery()
            MyConn.Close()
        Next
0
 

Author Comment

by:OVC-it-guy
ID: 22735762
Ran into other errors (unrelated to this) that I have to clear before I know whether this works or not.
0
 

Author Comment

by:OVC-it-guy
ID: 22754780
Ok, still got a problem.  The code above (here in snippet) is only putting the first item/row from my shopping cart (datatable) into the orders table.  It's missing subsequent rows.  Help?
Dim strSKU As String

Dim strProduct As String

Dim strPrice As Decimal

Dim strQty As Decimal

        

        For Each objDR In objDT.Rows

            strSKU = objDR("Sku")

            strProduct = objDR("Product")

            strPrice = objDR("Cost")

            strQty = objDR("Quantity")

            

            Dim myCmd9 As New SqlCommand

            myCmd9.Connection = myConn2

            myCmd9.CommandType = CommandType.Text

            myCmd9.CommandText = "Insert into orders_detail (id, sku, product_name, price, qty) Values ('" & cartNr & "','" & strSKU & "','" & strProduct & "','" & strPrice & "','" & strQty & "')"

            myConn2.Open()

            myCmd9.ExecuteNonQuery()

            myConn2.Close()

        Next

Open in new window

0
 

Author Comment

by:OVC-it-guy
ID: 22754856
Not sure what I changed to make it right, but it's working now.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22754857
What is the key to the table orders_detail?  Is it the id or is it the id and sku combination?

Since you are passing the cartNr as id, so just checking to see if the subsequent rows are failing because the id already exists.  Given this is a cart, it makes sense that the cartNr should repeat for each different sku in the cart but make sure the database structure matches this.

Kev
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now