OVC-it-guy
asked on
Inserting rows from DataTable into SQL Server database table
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','p roduct_nam e','price' ,'qty')"
Dim Cmd7 As New SqlCommand(AddOrdersDetail , MyConn)
MyConn.Open()
Cmd7.ExecuteNonQuery()
MyConn.Close()
I start off with Page_Load event:
If Not IsNothing(Session("Cart"))
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','p
Dim Cmd7 As New SqlCommand(AddOrdersDetail
MyConn.Open()
Cmd7.ExecuteNonQuery()
MyConn.Close()
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.
You'll need to fix up your values section of the insert string also.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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
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.Open()
Cmd7.ExecuteNonQuery()
MyConn.Close()
Next
ASKER
Ran into other errors (unrelated to this) that I have to clear before I know whether this works or not.
ASKER
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
ASKER
Not sure what I changed to make it right, but it's working now.
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
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
ASKER
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'
Dim Cmd7 As New SqlCommand(AddOrdersDetail
MyConn.Open()
Cmd7.ExecuteNonQuery()
MyConn.Close()
Next