I have a DataTable which I've created from scratch as part of a dataset in a VB application. How can I write it back to the data source, in this case an Access file?

Thanks!

Aaron

Dear Aaron!

You can save the DataTable back 2 your SQL Server database in 2 ways:
1 - You can loop through each row in the DataTable, build a SQL statement dynamically and excute it against the database, OR
2- You can use a SqlDataAdapter object, configure its InsertCommand and call its update method passing it the DataTable object.

Assume the following:-
1 - You have an open conn named 'nwind' to the Northwind SQL Server db
2 - Your DataTable's name is custsTable and is populated with data
3 - The custsTable represents Customers table & contains these fields
    (a) CustomerID
    (b) CompanyName
    (c) ContactName
4 - You have a DataSet named nwindDataSet and you have added your table, custsTable, to this DataSet

Now here goes method 1:-

------------------------------------------------
Dim custsInsCmd As SqlClient.SqlCommand
Dim custRow As DataRow
Dim sql As String

custsInsCmd = nwind.CreateCommand()
For Each custRow In custsTable.Rows
    sql = "INSERT INTO Customers (CustomerID, " & _
        "CompanyName, ContactName) VALUES (" & _
        "'" & custRow("CustomerID") & "', " & _
        "'" & custRow("CompanyName") & "', " & _
        "'" & custRow("ContactName") & "')"
        custsInsCmd.CommandText = sql
    custsInsCmd.ExecuteNonQuery()
Next
------------------------------------------------

Now here goes method 2:-

------------------------------------------------
Dim custsAdapter As New SqlClient.SqlDataAdapter
Dim custsInsCmd As SqlClient.SqlCommand()

' Create Insert cmd and set its Command Text
custsInsCmd = nwind.CreateCommand()
custsInsCmd.CommandText = "INSERT INTO Customers (CustomerID, " & _
  "CompanyName, ContactName) VALUES (@CustomerID, @CompanyName, " & _
  "@ContactName)"
custsInsCmd.CommandType = CommandType.Text

' Add parameters to the Insert command
custsInsCmd.Parameters.Add("@CustomerID", SqlDbType.NChar, _
    5, "CustomerID")
custsInsCmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar, _
    40, "CompanyName")
custsInsCmd.Parameters.Add("@ContactName", SqlDbType.NVarChar, _
    30, "ContactName")

' Initialize the DataAdapter's Insert Command
custsAdapter.InsertCommand = custsInsCmd

' Send data table rows back to the data source
custsAdapter.Update(custsTable)
------------------------------------------------

I hope this code helps you.



ejan

Dear Aaron!

Method 1 will work 4 Access databases. I'm not sure about whether Method 2 will work 4 Access databases or not.

4 SQL server both Method 1 and 2 will work.

Ragards,


ejan

Thanks Ejan,

Both your solutions were excellent, but they also assume that there is already a table in the database that I want to Insert into, in the case of your example, the Customers table in Northwind.

But this is a brand new table I want to insert, or add to the database. I suppose I could just pass a Create Table SQL command using the OleDbCommand object, and then populate it using one of the methods you recommended. But I was hoping ADO.NET had a simple method to Add a DataTable from a Dataset to a database.

Cheers and Happy Holidays

Aaron

Well Aaron,

As long as my information is concerned, there is no object in the ADO.Net library to create a table the way you desire. If you must do it that way, checkout the ADOX library (cousin of the ADODB library:))

But wait man! have you not asked that you want a SIMPLE method for adding a table to a database? If so, then I assure you that the most efficient and easiest method of creating a table in the database from an application is to just pass a simple CREATE TABLE statement. I recommend using the CREATE TABLE statement rather than ADOX library or any other library around.

Regards,

ejan