[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


inserting records into a sql table from a dataset

Posted on 2005-05-04
Medium Priority
Last Modified: 2010-04-07
I have a transactional table on my SQL Server. I have created a dataset and want to insert the dataset into the database. Can anyone help me with this or point me in the right direction?
Question by:NickMalloy
  • 4
  • 3
  • 2
  • +1
LVL 24

Expert Comment

by:Jeff Certain
ID: 13930968
By dataset, I'm assuming you really mean datatable (i.e. DataSet.Tables(0))

You need to either:
1. Create a dataadapter, use it to fill a table, add your existing rows to the table and then call the update method on the dataset
2. Loop through your existing dataset, using an INSERT command for each row.

Author Comment

ID: 13931328
When I add my rows to me dataset and call the following function. I get an error "Update requires a valid InsertCommand when passed DataRow collection with new rows. "  How would you build this command?

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 24

Expert Comment

by:Jeff Certain
ID: 13931410
You need to create an SQLCommand object that will allow you to perform the update

Dim cmd as System.Data.SQLClient.SQLCommand= New System.Data.SQLClient.SQLCommand("UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName WHERE CustomerID = @oldCustomerID", strconn)
cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")

Dim param as SQLParameter= cmd.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID")
param.SourceVersion = DataRowVersion.Original

empDA1.UpdateCommand = cmd

Author Comment

ID: 13931656
When your creating the command in this example

cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")

would CustomerID be the name of the datatable column?
LVL 13

Accepted Solution

Torrwin earned 500 total points
ID: 13935686
Assuming you already have your dataset and connection set up:

Dim myCommand as new SqlClient.SqlCommand
Dim myQuery as String
Dim RowCount, RowIndex as Integer

myCommand.Connection = <Your_Connection>

RowCount = Dataset.Tables(0).Rows.count
RowIndex = 0

While RowCount > RowIndex

     myQuery = "INSERT INTO TABLE_NAME VALUES " & dataset.tables(0).Rows(RowIndex).Items("COLUMN_NAME") & ", '" & dataset.tables(0).rows(RowIndex).Items("COLUMN_2_NAME") & "'"

          myCommand.CommandText = myQuery
          lblMessage = Err.Description
     End Try

     RowIndex += 1
End While

A note on the insert statement, numeric values would not have ' around them, like with the first column above, and the char values would have the ' around them like with column two above.  So, to the computer it would look like:

Author Comment

ID: 13935716
would you just skip the database storage then and just simply do a loop that inserts the value without storing them in a dataset first?
LVL 13

Expert Comment

ID: 13935771
No, my example uses a dataset.  Once you have the data in your dataset, then you perform the loop, which will loop through each row in your dataset. (dataset.tables(0).Rows(RowIndex).Items("COLUMN_NAME")

So it would first insert the value located at dataset.tables(0).Rows(0).Items("COLUMN_NAME")
and then dataset.tables(0).Rows(1).Items("COLUMN_NAME")

I could include some code on setting up your dataset if you need some help just let me know.  Also note that table(0) is just the first table in your dataset, you could also use dataset.Table("TABLE_NAME").Rows(RowIndex).Items("COLUMN_NAME")

Author Comment

ID: 13935817
well you definatly earned the points. one last question. what do you think about doing the insert through the dataadapter. what is the benefits of the looping compared to the way this article says to do it? Is there any performance issues to be aware of?


LVL 13

Expert Comment

ID: 13935946
I am unsure if there are any performance differences, I haven't really had any performance problems using my method though.  I think its more just a case of letting the computer do it for you, as opposed to doing it yourself, whichever you prefer.  

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 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