We help IT Professionals succeed at work.

problem saving datagridview back to mysql vb.net

Steven Debock
Steven Debock used Ask the Experts™
on
Hy,

I can load the db into the datagridview.
i'm trying to save changes made in the datagridview to be stored back to the mysql db.
But i'm not getting any errors and its not saving.

Any id ?

vb.net vs 2008
Public Class Form1
    Dim connetionString As String
    Dim connection As MySqlConnection
    Dim adapter As MySqlDataAdapter
    Dim cmdBuilder As MySqlCommandBuilder
    Dim ds As New DataSet
    Dim changes As DataSet
    Dim sql As String
    Dim i As Int32

Private Sub BekijkAllesToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BekijkAllesToolStripMenuItem.Click
        Dim connetionString As String = ("Data Source=site;Initial Catalog=catalog;User ID=id;Password=pw")

        connection = New MySqlConnection(connetionString)
        sql = "select * from Herstellingen2"
        Try
            connection.Open()
            adapter = New MySqlDataAdapter(sql, connection)
            adapter.Fill(ds)
            connection.Close()
            Herstellingen.DataSource = ds.Tables(0)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub SaveToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveToolStripButton.Click
        Try
            cmdBuilder = New MySqlCommandBuilder(adapter)
            changes = ds.GetChanges()
            If changes IsNot Nothing Then
                adapter.Update(changes)
            End If
            MsgBox("Opgeslagen")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Try adding this after your adapter update:
If changes IsNot Nothing Then
                adapter.Update(changes)
ds.Acceptchanges
            End If

Author

Commented:
@Shahan
Thnx for the link.
To bad the example was made with option strict off.
But i think i managed.
But in the example a connection string is used with Microsoft.Jet.OLEDB.4.0
Can i use Microsoft.Jet.OLEDB.4.0 to connect to a remotly hosted mysql server?
Iv looked at connectionstrings.com and sorts but i can't find the correct combination for the connection string.

or can't i use Microsoft.Jet.OLEDB.4.0  to do this ?

Sorry for being a noob ;)
Shahan AyyubSenior Software Engineer

Commented:
It is normally done by using odbc connectionstrings, but try the below links may be it can help you:
Could you please see the connectionstring from here:
http://forums.aspfree.com/visual-basic-programming-38/vb-on-web-238092.html 
OR
from:
http://www.dofactory.com/Connect/Connect.aspx#mysql 
using System.Data.OleDb;

ConnectionString =
            "Provider=MySqlProv;" +
            "Data Source=ServerName;" +
            "User id=UserName;" +
            "Password=Secret;"