How to insert data from data table in one batch?

[Shelyn]

Hi All,

I have a data table with hundreds of records and would like to insert them to the database in one batch.

Below is my coding. But it gives me error at the
da.Update(dt). What's wrong?

Private Sub InsertGLTable(ByVal GLRec As DataTable)

Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("conGFIS"))
Dim cmd As New SqlCommand
Dim da As New SqlDataAdapter
Dim cb As SqlClient.SqlCommandBuilder

cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "Insert_Test"

cmd.Parameters.Add("@JournalDate", SqlDbType.DateTime)
cmd.Parameters.Add("@DocumentNo", SqlDbType.Char)

conn.Open()

'da.updatebatchsize()
da.InsertCommand = cmd
da.Update(GLRec)

conn.Close()

End Sub

 

[ThatRickGuy]

You'll have to run them one at a time. Or use some sort of batch processing application designed for your database (ie: SQL Loader, DTS packages, etc)

If you run them one at a time, open the connection before your loop and close it afterwards. Just update the parameters and executenonquery the procedure.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chrissie1]

are there only two columns in the datatable?

and what's the error?

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[Shelyn]

Hi Chrissie,

I was doing the testing on this, so I created a datatable with only 2 columns.

The error given is:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

Do you have any idea on this?


Hi ThatRickGuy,

"You'll have to run them one at a time. Or use some sort of batch processing application designed for your database (ie: SQL Loader, DTS packages, etc) "

Do you have sample code on this?

 

[ca8msm]

Do you have sample code on this?

It's just a simple for each loop on the rows of the datatable like the one in the DataTable example in faq855-5662



____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[chrissie1]

or replace this

cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "Insert_Test"

cmd.Parameters.Add("@JournalDate", SqlDbType.DateTime)
cmd.Parameters.Add("@DocumentNo", SqlDbType.Char)

with this

cmd = cb.getinsertcommand

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]