Data din't Update in Database Table

Question

4.00/5 (1 vote)

See more:

Hey,

I have created an application in vb.net 08, and database is sql services based database,

Now i have Code:

VB

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 6:07am

Nilesh Varchand

Add a Solution

2 solutions

Solution 3

Thanking a lot dude,
Finally i got the Perfect Answer,
Code For Update DataGridView at Run Time,,,

VB

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,,,

Posted 2-Feb-13 20:33pm

Nilesh Varchand

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Muthuraja Irullandi · Accepted Answer · 2013-02-02T06:25:00

Hi,

Before calling the update method of data adapter, you need to set the InsertCommand, UpdateCommand, DeleteCommand properties.

C#

SqlCommandBuilder cb;
 cb = new SqlCommandBuilder(TblUsersTableAdapter);


TblUsersTableAdapter.DeleteCommand = cb.GetDeleteCommand(true);
TblUsersTableAdapter.UpdateCommand = cb.GetUpdateCommand(true);
TblUsersTableAdapter.InsertCommand = cb.GetInsertCommand(true);

call update()

C#

TblUsersTableAdapter.Update(MyDataBaseDataSet.tblUsers)

Sample program
-------------

VB

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