VB.NET Insert datatable rows into SQL Database

Question

I have the following code to loop through my datatable (dtItem) and insert each row into the database. However, i can only insert the last row of the datatable. How do i insert all rows? Here is my code.

Dim dtRow As DataRow

            For Each dtRow In dtItem.Rows

                dtRow.ToString.Split("|")

                Dim xBinCode As String = dtRow(0)
                Dim xLocationCode As String = dtRow(7)
                Dim xItemNo As String = dtRow(1)
                Dim xQuantity As String = dtRow(2)
                Dim xCountNo As String = dtRow(8)

                cmd.CommandText = "INSERT INTO tblItems (BinCode, LocationCode, ItemNo, Quantity, CountNo) values('" & xBinCode & "','" & xLocationCode & "','" & xItemNo & "','" & xQuantity & "','" & xCountNo & "')"

            Next

Ram kiran Pachigolla · Accepted Answer · 2013-02-28 07:02:21Z

2

if you use data reader you need to open connection and use command

cmd.ExecuteNonQuery

after

cmd.CommandText = "INSERT INTO tblItems (BinCode, LocationCode, ItemNo, Quantity, CountNo) values('" & xBinCode & "','" & xLocationCode & "','" & xItemNo & "','" & xQuantity & "','" & xCountNo & "')"

statement

edited Feb 28, 2013 at 7:02

Ram kiran Pachigolla

21.3k15 gold badges60 silver badges78 bronze badges

answered Feb 28, 2013 at 6:45

Maadh

6434 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Pollan · Accepted Answer · 2012-11-01 09:31:41Z

0

It looks like you're missing something here.

You can do that concatening the SQL insert sentences for each row before sending to server.

If you want to send one row at time, configure and execute your command inside the for loop.

You can also use a DbDataAdapter. Loak at the following pages:

http://msdn.microsoft.com/es-es/library/at8a576f(v=vs.90).aspx

http://msdn.microsoft.com/es-es/library/system.data.sqlclient.sqldataadapter.insertcommand(v=vs.90).aspx

edited Nov 1, 2012 at 9:31

answered Nov 1, 2012 at 8:49

Alex Pollan

8735 silver badges13 bronze badges

Collectives™ on Stack Overflow

VB.NET Insert datatable rows into SQL Database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related