inserting records into a sql table from a dataset

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?
NickMalloyAsked:
Who is Participating?
 
TorrwinConnect With a Mentor Commented:
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") & "'"

     Try
          myCommand.CommandText = myQuery
          myCommand.ExecuteNonQuery
     Catch
          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:
myQUERY = "INSERT INTO TABLE_NAME VALUES COLUMN_ONE_VALUE, 'COLUMN_TWO_VALUE'"
0
 
Jeff CertainCommented:
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
OR
2. Loop through your existing dataset, using an INSERT command for each row.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
NickMalloyAuthor Commented:
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?

       strconn.Open()
        empDA1.Update(empDS1.Tables("returnitems"))
        strconn.Close()
0
 
Jeff CertainCommented:
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
0
 
NickMalloyAuthor Commented:
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?
0
 
NickMalloyAuthor Commented:
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?
0
 
TorrwinCommented:
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")
0
 
NickMalloyAuthor Commented:
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?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconupdatingdatabasewithdataadapterdataset.asp

0
 
TorrwinCommented:
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.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.