How to Save Data from the DataGridView into SQL Server

Good Day:

I am working on a Windows application using VB.NET 2005, SQL Server 2000, and the Microsoft DataGridView control.

I created code to load the DataGridView1 with no data to allow the user to add new data into the table named Test using the DataGridView1 control.

I created a command button called btnSave to save the records that the user types into the DataGridView1 control.
Can someone please provide me with code on how to save records from the DataGridView control?

My code to load the grid is as follows:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class frmGridTest

    Private sCon As String
    Private ds As New DataSet
    Private cmd As New SqlCommand
    Private da As New SqlDataAdapter(cmd)

Private Sub frmGridTest_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadGrid()
    End Sub

    Private Sub LoadGrid()

       Dim sSQL As String = "Select * from test where ID=0"
       Dim sLoginID As String = "denise"
       Dim sPassword As String = "goodheart"
       sCon = "Data Source=Denise;Initial Catalog=Test;User Id=" & sLoginID & ";Password=" & sPassword & ";"
       Dim con As SqlConnection = New SqlConnection(sCon)

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

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

        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
End Class

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.

Jorge PaulinoIT Pro/DeveloperCommented:
Try something like this:

Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
da.Update(ds)
ds.AcceptChanges()
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 jpaulino:

After I added a table name to the following code; it worked to my delight:    
   da.Update(ds, "Test")

Many Thanks,
Denise
       
0
Jorge PaulinoIT Pro/DeveloperCommented:
Gald I could help and thanks for the grade.

Depending of the stucture that you have you may need to add the table name. In the example I have copy/paste (small management application that I have made)  it's no need because it prepared to work with several tables.

jpaulino
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.