Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
Hey,
 
I have created an application in vb.net 08, and database is sql services based database,
 
Now i have Code:
Public Class Form1
    Dim upd As New Boolean
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'MyDataBaseDataSet.tblUsers' table. You can move, or remove it, as needed.
        Me.TblUsersTableAdapter.Fill(Me.MyDataBaseDataSet.tblUsers)
    End Sub
 
    Private Sub TblUsersBindingSource_ListChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs) Handles TblUsersBindingSource.ListChanged
        If MyDataBaseDataSet.HasChanges Then
            upd = True
        End If
    End Sub
 
    Private Sub DataGridView1_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowValidated
        If upd = True Then
            TblUsersTableAdapter.Update(MyDataBaseDataSet.tblUsers)
        End If
    End Sub
End Class
 
Now this is the code of my project, when im editing in the datagridview, and press enter at that time its update successful, and then im closing that debugging and again run that application then its some time show the updated data or some time not, but only in datagridview, in the database its never update,
plz help me to update both datagridview and database table,
 
Thanks,,,
@Nilesh,,,
Posted 1-Feb-13 7:07am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
Before calling the update method of data adapter, you need to set the InsertCommand, UpdateCommand, DeleteCommand properties.
SqlCommandBuilder cb;
 cb = new SqlCommandBuilder(TblUsersTableAdapter);
 

TblUsersTableAdapter.DeleteCommand = cb.GetDeleteCommand(true);
TblUsersTableAdapter.UpdateCommand = cb.GetUpdateCommand(true);
TblUsersTableAdapter.InsertCommand = cb.GetInsertCommand(true);
 
call update()
TblUsersTableAdapter.Update(MyDataBaseDataSet.tblUsers)
 

Sample program
-------------
 
Imports System.Data.SqlClient
 
Public Class Form1
 
    Dim upd As New Boolean
    Dim cb As New SqlCommandBuilder
    Dim cn As New SqlConnection("server=.\sqlexpress;database=testdb;uid=sa;pwd=sa123")
    Dim dt As New DataTable
    Dim da As New SqlDataAdapter
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim squery As String
        squery = "Select * from dtl"
        da.SelectCommand = New SqlCommand(squery, cn)
        da.Fill(dt)
        MessageBox.Show(dt.Rows.Count.ToString())
    End Sub
 
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim dr As DataRow = dt.NewRow
        dr(0) = 4
        dr(1) = "Shasini"
        dr(2) = 5
 
        dt.Rows.Add(dr)
 
        MessageBox.Show(dt.Rows.Count.ToString())
 
        Dim cb As New SqlCommandBuilder(da)
 
        da.DeleteCommand = cb.GetDeleteCommand(True)
        da.UpdateCommand = cb.GetUpdateCommand(True)
        da.InsertCommand = cb.GetInsertCommand(True)
 
        da.Update(dt)
    End Sub
End Class
 
 
The above sample works fine and it is inserting the record in the db. Please make sure you are having a Primary Key field in the table.
 
Best Regards
Muthuraja
  Permalink  
v2
Comments
Muthuraja Irullandi at 2-Feb-13 13:27pm
   
Hi,
Please remove the semicolon in the below line,
TblUsersTableAdapter.DeleteCommand = cb.GetDeleteCommand(true);
TblUsersTableAdapter.UpdateCommand = cb.GetUpdateCommand(true);
TblUsersTableAdapter.InsertCommand = cb.GetInsertCommand(true);
 
Please see the updated solution for sample program
Nilesh Varchand at 3-Feb-13 2:37am
   
yeah, thanks alot
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Thanking a lot dude,
Finally i got the Perfect Answer,
Code For Update DataGridView at Run Time,,,
 
Imports System.Data.SqlClient
Public Class Form1
    Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\My Project\Practice Projects\DataGridViewFinal\DataGridViewFinal\MyDataBase.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
    Dim upd As New Boolean
    Dim ada As New SqlClient.SqlDataAdapter("Select * from tblusers", con)
    Dim tab As New DataSet
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'MyDataBaseDataSet.tblUsers' table. You can move, or remove it, as needed.
        Me.TblUsersTableAdapter.Fill(Me.MyDataBaseDataSet.tblUsers)
    End Sub
 
    Private Sub TblUsersBindingSource_ListChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ListChangedEventArgs) Handles TblUsersBindingSource.ListChanged
        If MyDataBaseDataSet.HasChanges Then
            upd = True
        End If
    End Sub
 
    Private Sub DataGridView1_RowValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.RowValidated
        If upd = True Then
            Dim cb As New SqlCommandBuilder(ada)
            ada.DeleteCommand = cb.GetDeleteCommand(True)
            ada.UpdateCommand = cb.GetUpdateCommand(True)
            ada.InsertCommand = cb.GetInsertCommand(True)
            ada.Update(MyDataBaseDataSet.tblUsers)
        End If
    End Sub
End Class

Thanks Again,,,
@Nilesh,,,
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 George Jonsson 175
1 Kornfeld Eliyahu Peter 169
2 Zoltán Zörgő 139
3 PIEBALDconsult 130
4 OriginalGriff 120
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,107
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,342


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 3 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100