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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

grayeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.