How to Save Data from the DataGridView into SQL Server

Good Day:

I am using VB.NET 2005 and SQL Server 2000. I am using the DataGridView. So far I have created the columns, but I need to get help in writing the code to save the data from the DataGridView into SQL Server.  I cannot use the BindingSource,BindingNavigator, and DataSet controls.  Can someone post sample code on how to save data from the DataGridView into SQL Server.

Thank You,
Denise
DeniseGoodheartAsked:
Who is Participating?
 
grayeConnect With a Mentor Commented:
Actually, this is kinda easy...   the issue has nothing to do with the DataGridView at all.    

Let's say that you bound the DataGridView with a DataTable that you originally retrieved from an SQL Server 2000 database.   Then you use the DataGridView to add/delete/modify rows.    This is no different that just editing the rows and columns of the DataTable directly.   In fact, the DataTable doesn't know or care which technique was used to edit it.

So, changes have already been made to the DataTable, and all you have to do now is update the SQL Server database.   The easiest way is to use the DataAdapter's Update method.   It generally goes like this:

Dim cb = New OleDbCommandBuilder(da)
da.Update(dt)

I'd suggest that you take a few minutes to review the following article: http://home.hot.rr.com/graye/Articles/SavingDataADO.htm
0
 
DeniseGoodheartAuthor Commented:
Hello Graye:

Thank you for your useful information.  Do I use the da.update when the user is inserting new rows to the database as well while using the DataGridView?

Thank You,
Denise
0
 
grayeCommented:
It really doesn't matter how the changes are made to the DataTable.    The DataAdapter's Update method will detect all of the changes (from whatever source they occurred) and will pass those changes back to the underlying database.   That's the beauty of the Update() method... it performs all of the magic behind the scenes.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DeniseGoodheartAuthor Commented:
Hello graye:

Thanks for your help.  The example you posted is for OleDb and I am using SqlDb.  Right now I am only interested in adding records.  Do I have to use the BindingSource control because we will be moving this to a production environment and the server name will change.  I have created a datatable by code; will this suffice?  Also if it does suffice; then I need help in saving the records added to my table.  My code that creates a datatable for my grid is as follows:
Private Sub LoadGrid()

        'Clear the grid
        Me.DataGridView1.DataSource = Nothing
        DataGridView1.ClearSelection()
        DataGridView1.Rows.Clear()
        DataGridView1.Columns.Clear()
        ds.Clear()

        Dim sSQL As String = "Select * from test where ID=0"
Dont want data to appear for adding records

        Dim sLoginID As String = "Paul"
        Dim sPassword As String = "McCartney"
        sCon = "Data Source=Beatles;Initial Catalog=Test;User Id=" & sLoginID & ";Password=" & sPassword & ";"


        Dim con As SqlConnection = New SqlConnection(sCon)
        'Assigned as Private - Dim cmd As New SqlCommand

        Me.DataGridView1.AutoGenerateColumns = False
        cmd.Connection = New SqlConnection(sCon)
        cmd.CommandText = sSQL

        'Assigned as Private - Dim da As New SqlDataAdapter(cmd)


        da.Fill(ds, 0)
        Me.DataGridView1.DataSource = ds.Tables(0)
        cmd.CommandType = CommandType.StoredProcedure


        Dim column As DataGridViewColumn = _
            New DataGridViewTextBoxColumn()
        column.DataPropertyName = "ID"
        column.Name = "ID"
        Me.DataGridView1.Columns.Add(column)
        DataGridView1.Columns("ID").Width = 60
        '2
        column = New DataGridViewTextBoxColumn()
        column.DataPropertyName = "Artist"
        column.Name = "Artist"
        Me.DataGridView1.Columns.Add(column)
        DataGridView1.Columns("Artist").Width = 70

        '3
        column = New DataGridViewTextBoxColumn()
        column.DataPropertyName = "Song"
        column.Name = "Song"
        Me.DataGridView1.Columns.Add(column)
        DataGridView1.Columns("Song").Width = 40

       
        Me.DataGridView1.Refresh()
        ' Initialize the form.
        Controls.Add(Me.DataGridView1)
        Me.AutoSize = True
        cmd.Connection.Close()

    End Sub

Thank You,
Denise
0
 
grayeCommented:
Looks good.... so now just add the two lines of code as suggested above

Dim cb = New SqlCommandBuilder(da)
da.Update(ds.Tables(0))

0
 
DeniseGoodheartAuthor Commented:
Hello,

It did not work.  I know there is a missing  piece because I want to insert records and not update.  Thank you for all your help. I will post a new question.

Cheers,
Denise
0
All Courses

From novice to tech pro — start learning today.