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?
 
Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
Try something like this:

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

From novice to tech pro — start learning today.