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!

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

Table1 has a primary key:

RecordID , Integer = primary key auto_increment NOT NULL

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

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

Edited 6 Years Ago by __avd: n/a

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 :

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.

This question has already been answered. Start a new discussion instead.