Save datatable changes to sql database

I have a dataset and data table that I have created and populated using code.  How do I save the data to the sql database?   It would be considered a new record and not an update to and existing record


I am using vb,net saving to sql server database.
Mike_StevensAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Mike EghtebasConnect With a Mentor Database and Application DeveloperCommented:
Because the dataset is untyped there is no row changing event for a specific table therefore you must use the concept of Dynamic even handling and delegates to handle the row changing event.  

Look at this example which is using txtBoxes no a gridview (I hope it helps):
Public Sub Update_Row()      
	drCust.Item("CustomerID") = txtCustomerID.Text
        drCust.Item("CompanyName") = txtCompanyName.Text
        drCust.Item("ContactName") = txtContactName.Text
 
        AddHandler dsNorthwind.Tables("Customers").RowChanging, _
             AddressOf Customer_RowChanging
 
        drCust.EndEdit()
        RemoveHandler dsNorthwind.Tables("Customers").RowChanging, _
            AddressOf Customer_RowChanging
 
End Sub

Open in new window


This is for validation.
Private Sub Customer_RowChanging(ByVal sender As Object, _
	ByVal e As System.Data.DataRowChangeEventArgs)
        '*** Data verification throw exception is invalid 
        Dim strErrors As String
        If txtCustomerID.Text = "" Then
            strErrors = "CustomerID Req."
        ElseIf txtCompanyName.Text = "" Then
            strErrors = "Name Req."
        End If
        If strErrors <> "" Then
            Throw New System.Exception(strErrors)
        End If
 
    End Sub

Open in new window

Maybe Fernando could shape this up to make it for your case.
0
 
Fernando SotoRetiredCommented:
That all depends on how you filled the DataSet / DataTable in code. Did you fill using SqlDataAdapter and filled the data table directly from using the Fill method? Did you just created a DataSet object and manually created a DataTable object and just added records?

Can you please post the code you used to do this.
0
 
Mike_StevensAuthor Commented:
I created the dataset using code and populated it using code.  

- Created using
           dt .Columns.Add("Date_Created", GetType(String))
           dt. Columns.Add("Title", GetType(String))
          ds.Tables.Add(dt)

- Added record
Dim dr As Data.DataRow = ds.Tables(0).NewRow.
     dr("Date_Created") = Now.AddHours(3)
     dr("Rec_Title") = Me.textbox1.Text
     ....
ds.Tables(0).Rows.Add(dr)
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Fernando SotoRetiredCommented:
Hi Mike;

OK, are you yet connecting to the database? If so what technology are you using? For example plane ADO.Net, Linq to SQL, Link To Entity Framework or some other technology.

What database will you be using, Microsoft SQL server, Oracle, MySql or some other?

Does the database and the table you wish to insert records into already exist?
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
I am here as Fernando's student.

I think Mike is using untyped dataSet where he starts with

Dim ds as New DataSet

and then he defines columns, rows to add to a table.       In addition to the Dataset object, the Connection, Command, and Data Adapter objects must also be created.

Basically, it seems he needs to build his InsertCommand object to do what he wants to accomplish.

Mike needs to post his complete code.
0
 
Mike_StevensAuthor Commented:
I post my complete code....I am creating the dataset and table using code and then adding records to it (normally only one or two rows of data).  No I need to save the data back to the sqlserver database table that has field names that are the same as the column names in the datatable.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
re:> I post my complete code
Where is your connection code?
Where is your insert command?
Where is your dataadapter?

Why aren't using typed dataset where you don't have to lift finger to save your data to the databse (data is bound to the dgv)?
0
 
Mike_StevensAuthor Commented:
I have a connection string that the project already uses.

- I did not post the insert command because I don't have one yet..
- I did not post the data adapter yet because I don't have one yet..

I posted my question asking why I need to do to save my data and showed what code I had.  It appears I need to create a insert command and a data adapter.
0
 
Fernando SotoRetiredCommented:
Hi Mike;

Try it this way, it is about the most direct way of doing it. Please note the comments in the code.

'' Insert your connection string in the below line of code.
Dim sqlConnection1 As New SqlConnection("Your connection string goes here")

Dim cmd As New SqlCommand
cmd.CommandType = System.Data.CommandType.Text
'' This your SQL Insert command. Make sure that all column names are spelled the same way as in the database
'' Change Buildings to your Table Name, the next list of comma separated names are the column names.
'' The next list of comma separated VALUES are the values to be inserted into the columns in the same order and
'' data types, strings have double quotes around it numeric values do not. 
cmd.CommandText = "INSERT INTO Buildings (LOC_ID, BLDG_CODE, ROOM, CAPACITY) VALUES (15, 'NorthWestern', 209, 50)"
cmd.Connection = sqlConnection1

sqlConnection1.Open()
cmd.ExecuteNonQuery()
sqlConnection1.Close()

Open in new window

Also in the above table Buildings the Primary Key is NOT marked as an Identity and so it needs to be specified as one of the columns in the Insert command. If your Primary Key is marked as an Identity then do NOT include it in the insert command because SQL server will do this automatically for you.
0
 
Mike_StevensAuthor Commented:
Thanks Fernando for your example.  That's what I was looking for .  Where do I call in from ?  What event in the grid?
?
0
 
Fernando SotoRetiredCommented:
Hi Mike;

To your question, "Where do I call it from ?", any place in your code that you wish to insert the data into the database.

To the question, "What event in the grid?", well one place in the grid you can use is the DataGridView.RowLeave Event.
0
All Courses

From novice to tech pro — start learning today.