How to Edit DataGridview1 in vb.Net Windows Form and save it to database.

searchsanjaysharma
searchsanjaysharma used Ask the Experts™
on
I have the button btnsave, and btnfind and txtid.Text.

The users enters the id in txtid, clicks on btnfind and the respective record is binded to the DataGridView1

The users clicks on any cell and modifies the existing values.

Now i have btnsave, when the user clicks on the this, the new values of that Datagridview1 s should be stored in database.

1. How to do this. The current code is as follows.


Imports System.Data
Imports System.Data.SqlClient
Public Class Form3
    Dim con As SqlConnection
    Dim dt As DataTable
    Dim da As New SqlDataAdapter
    Dim s As String

    Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
        'call update procedure where id= txtid.Text
    End Sub

    Private Sub btnfind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnfind.Click
        Try
            DataGridView1.Height = Me.Height
            DataGridView1.Width = Me.Width
            con = New SqlConnection("Data Source=172.16.3.17;Initial Catalog=testadms;User Id=sa;Password=admin#cgc;")
            If (con.State = ConnectionState.Closed) Then
                con.Open()
            End If
            s = "SELECT id,firstname,lastname from mstusers where id=" & Convert.ToInt32(txtid.Text.Trim())
            da = New SqlDataAdapter(s, con)
            dt = New DataTable()
            da.Fill(dt)
            DataGridView1.DataSource = dt
        Catch ex As Exception
            MsgBox("Error occured " & ex.ToString())
        Finally
            con.Close()
        End Try
    End Sub
End Class
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
Try following in btnSave

da.Update(dt)

Author

Commented:
But this will update all records isnt it.
But how to update only that record which has been changed.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Author

Commented:
da.update(ds)
can' i write my own update query
Most Valuable Expert 2012
Top Expert 2014

Commented:
It will only update modified records.

Author

Commented:
But i have to do it on btnsave.
Or no need of that would it automatically save.

Author

Commented:
Not w0oking


 Private Sub btnfind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnfind.Click
        Try
            DataGridView1.Height = Me.Height
            DataGridView1.Width = Me.Width
            con = New SqlConnection("Data Source=JAI-PC\SQLEXPRESS;Initial Catalog=testadms;Integrated Security=true;")
            If (con.State = ConnectionState.Closed) Then
                con.Open()
            End If
            s = "SELECT state,district from statedistrict"
            da = New SqlDataAdapter(s, con)
            dt = New DataTable()
            da.Fill(dt)
            DataGridView1.DataSource = dt
            da.Update(dt)

        Catch ex As Exception
            MsgBox("Error occured " & ex.ToString())
        Finally
            con.Close()

        End Try
    End Sub
Most Valuable Expert 2012
Top Expert 2014

Commented:
Its not working because

da.Update(dt)

needs to be called AFTER user has made changes.

Author

Commented:
so if i call on btnsave.
then again connection needs to be established. is it so

Author

Commented:
or on which event of gridview it should be called.
Most Valuable Expert 2012
Top Expert 2014
Commented:
Yes in btnSave. You would need to either reinitialize the adapter or declare it at class level so you can use it across multiple subs.

Author

Commented:
ok

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial