Ad:

VB.NET Discussion Thread
Marked Solved
Views: 4835

Jan 27th, 2010

Update MYSQL table with Datagridview

Expand Post »

I Use visual Basic 8.0 and Mysql 5.0
I have one Table1:
Table1:
RecordID , Integer = primary key auto_increment NOT NULL
Articlenr , Integer
Articlename, Char(20)

Table1: has two records:
RecordID, Articlenr ,Articlename
1 , 1000, Hamer
2 , 2000, saw

I want to use Datagridview to update Mysql table1.
All changes as update, change, delete automatically processed into Mysql table1.
Here by my code
really want to now can I link Mydataset to DataGridView1 and Mysql table1?
I now i must do something with MySqlCommandBuilder!

VB.NET Syntax (Toggle Plain Text)
 
Dim MyCommand1 As New MySql.Data.MySqlClient.MySqlCommand
Dim Myadapter As New MySql.Data.MySqlClient.MySqlDataAdapter
Dim MyBuilder As New MySql.Data.MySqlClient.MySqlCommandBuilder 
Dim MyDataTable As New DataTable
DIM MyDataset As Dataset
 
MyCommand1.CommandText = "select * from Table1"
MyCommand1.Connection = conn
 Myadapter.SelectCommand = MyCommand
Myadapter.Fill(MyDatatable)
Myadapter.Fill(MydataSet)
DataGridView1.DataSource = MyDatatable
 
 If MydataSet.GetChanges() Is Nothing Then
       MessageBox.Show("The table contains no changes to save.")
     Else
       Dim rowsAffected As Integer = Myadapter.Update(MyDatatable)
         If rowsAffected = 0 Then
           MessageBox.Show("No rows were affected by the save operation.")
        Else
           MessageBox.Show(rowsAffected & " rows were affected by the save operation.")
            End If
        End If
Dim MyCommand1 As New MySql.Data.MySqlClient.MySqlCommand
Dim Myadapter As New MySql.Data.MySqlClient.MySqlDataAdapter
Dim MyBuilder As New MySql.Data.MySqlClient.MySqlCommandBuilder 
Dim MyDataTable As New DataTable
DIM MyDataset As Dataset
        
MyCommand1.CommandText = "select * from Table1"
MyCommand1.Connection = conn
 Myadapter.SelectCommand = MyCommand
Myadapter.Fill(MyDatatable)
Myadapter.Fill(MydataSet)
DataGridView1.DataSource = MyDatatable

 If MydataSet.GetChanges() Is Nothing Then
       MessageBox.Show("The table contains no changes to save.")
     Else
       Dim rowsAffected As Integer = Myadapter.Update(MyDatatable)
         If rowsAffected = 0 Then
           MessageBox.Show("No rows were affected by the save operation.")
        Else
           MessageBox.Show(rowsAffected & " rows were affected by the save operation.")
            End If
        End If

Similar Threads

AJAX in Firefox doesnt update MYSQL! in JavaScript / DHTML / AJAX
i dont understand how to update 1 mysql field with ajax in JavaScript / DHTML / AJAX
update mysql table using javascript in PHP
how to update data in mysql table using data from an excel table in MySQL
updating multiple columns in single MySQL table in PHP

dre-logics

Reputation Points: 9

Solved Threads: 0

Junior Poster

Offline

102 posts
since Aug 2009

Permalink

Jan 27th, 2010

Re: Update MYSQL table with Datagridview

Table table1 must have a primary key.

adatapost

Moderator

Reputation Points: 2134

Solved Threads: 1227

Posting Genius

Offline

6,524 posts
since Oct 2008

Permalink

Jan 28th, 2010

Re: Update MYSQL table with Datagridview

Table1 has a primary key:

RecordID , Integer = primary key auto_increment NOT NULL

dre-logics

Reputation Points: 9

Solved Threads: 0

Junior Poster

Offline

102 posts
since Aug 2009

Permalink

Jan 28th, 2010

Re: Update MYSQL table with Datagridview

You have to split your code into two Sub (function).

VB.NET Syntax (Toggle Plain Text)
.....
Dim MyCommand1 As New MySql.Data.MySqlClient.MySqlCommand
Dim Myadapter As New MySql.Data.MySqlClient.MySqlDataAdapter
Dim MyBuilder As MySql.Data.MySqlClient.MySqlCommandBuilder
 
Dim MyDataTable As New DataTable
DIM MyDataset As Dataset
 
 
Private Sub Form1_Load(...)
 
MyCommand1.CommandText = "select * from Table1"
MyCommand1.Connection = conn
Myadapter.SelectCommand = MyCommand
Mybuilder=new MySql.Data.MySqlClient.MySqlCommandBuilder(Myadapter)
 
 
Myadapter.Fill(MydataSet,"Table1")
MyDataTable = MyDataSet.Tables("Table1")
 
DataGridView1.DataSource = MyDatatable
End Sub
 
private Sub Button1_Click(....)
 If MydataSet.GetChanges() Is Nothing Then
       MessageBox.Show("The table contains no changes to save.")
     Else
       Dim rowsAffected As Integer = Myadapter.Update(MyDatatable)
         If rowsAffected = 0 Then
           MessageBox.Show("No rows were affected by the save operation.")
        Else
           MessageBox.Show(rowsAffected & " rows were affected by the save operation.")
            End If
        End If
End Sub
.....
Dim MyCommand1 As New MySql.Data.MySqlClient.MySqlCommand
Dim Myadapter As New MySql.Data.MySqlClient.MySqlDataAdapter
Dim MyBuilder As MySql.Data.MySqlClient.MySqlCommandBuilder
 
Dim MyDataTable As New DataTable
DIM MyDataset As Dataset


Private Sub Form1_Load(...)

MyCommand1.CommandText = "select * from Table1"
MyCommand1.Connection = conn
Myadapter.SelectCommand = MyCommand
Mybuilder=new MySql.Data.MySqlClient.MySqlCommandBuilder(Myadapter)
 

Myadapter.Fill(MydataSet,"Table1")
MyDataTable = MyDataSet.Tables("Table1")

DataGridView1.DataSource = MyDatatable
End Sub

private Sub Button1_Click(....)
 If MydataSet.GetChanges() Is Nothing Then
       MessageBox.Show("The table contains no changes to save.")
     Else
       Dim rowsAffected As Integer = Myadapter.Update(MyDatatable)
         If rowsAffected = 0 Then
           MessageBox.Show("No rows were affected by the save operation.")
        Else
           MessageBox.Show(rowsAffected & " rows were affected by the save operation.")
            End If
        End If
End Sub

Last edited by adatapost; Jan 28th, 2010 at 3:17 am.

adatapost

Moderator

Reputation Points: 2134

Solved Threads: 1227

Posting Genius

Offline

6,524 posts
since Oct 2008

Permalink

Jan 28th, 2010

Re: Update MYSQL table with Datagridview

thanks , thus works fine!!

dre-logics

Reputation Points: 9

Solved Threads: 0

Junior Poster

Offline

102 posts
since Aug 2009

Permalink

Feb 11th, 2010

Re: Update MYSQL table with Datagridview

I start my application with form1 and go to form2.
In form2 i put in this code .
I just use exactly the code as shown above!

Now something strange happens?
The datagridview1 remains empty.
While Table1 certainly filled with data!!! (But it has worked a few days ago)
Get no error message !

If i use this code :

VB.NET Syntax (Toggle Plain Text)
 
MyCommand1.CommandText = "select * from Table1"
MyCommand1.Connection = conn 
Myadapter.SelectCommand = MyCommand
Myadapter.Fill(MyDatatable)
DataGridView1.DataSource = MyDatatable
 
MyCommand1.CommandText = "select * from Table1"
MyCommand1.Connection = conn 
Myadapter.SelectCommand = MyCommand
Myadapter.Fill(MyDatatable)
DataGridView1.DataSource = MyDatatable

The datagridview1 is filled with data from table1.

dre-logics

Reputation Points: 9

Solved Threads: 0

Junior Poster

Offline

102 posts
since Aug 2009

Permalink

Feb 14th, 2010

Re: Update MYSQL table with Datagridview

sorry it was problem with my system!!!

dre-logics

Reputation Points: 9

Solved Threads: 0

Junior Poster

Offline

102 posts
since Aug 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: Find Logic for reading SQL data in VB.NET

Next Thread in VB.NET Forum Timeline: oledb database update problem

DaniWeb Message

Cancel Changes

User Name
Password