asked on

problem saving datagridview back to mysql vb.net

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

13598

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

ASKER CERTIFIED SOLUTION

Shahan Ayyub

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steven Debock

ASKER

@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 Ayyub

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;"