Link to home
Start Free TrialLog in
Avatar of DeniseGoodheart
DeniseGoodheart

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Jorge Paulino
Jorge Paulino
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DeniseGoodheart
DeniseGoodheart

ASKER

Hello jpaulino:

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

Many Thanks,
Denise
       
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