asked on

How do I insert/update a mysql database from the vb .net datagrid

I have a mysql database.

I have made an interface that populates a datagrid (DataGrid1) with data from the mysql database.

I would like to insert new data into the interface and have it insert or update data to the database depending on what button was pushed.

Ignore the myschema parts...I was attemtping to try things that failed. I think I might have to get hold of parameters and figure out how to make the insert statement understand that they need to use the boxes from the datagrid. How woudl the program know which one I want to insert. The datagird shows all form the source and it could theoretially insert old data in the wrong place. Do I have to look for what is changed first in the datagrid to identify what to insert?

Thanks for any help you can lend.

R

Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click
        Dim da As New MySql.Data.MySqlClient.MySqlDataAdapter
 
        myConnectionString = "data source=localhost;" _
            & "uid=root;" _
            & "pwd=******;" _
            & "database=**********;"
 
        Dim conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
        da.MissingSchemaAction = MissingSchemaAction.Add
 
        Try
            da.InsertCommand = New MySql.Data.MySqlClient.MySqlCommand("INSERT INTO checkbook_tbl (entry,chk,location,debit,credit,balance,category,reconciled) values (@id,@chk,@location,@debit,@credit,@balance,@category,@reconciled)", conn)
        Catch ex As MySql.Data.MySqlClient.MySqlException
            Select Case ex.Number
                Case 0
                    MessageBox.Show("Cannot connect to server. Contact administrator")
                Case 1045
                    MessageBox.Show("Invalid username/password, please try again")
                Case Else
                    MessageBox.Show(ex.ToString)
            End Select
        Finally
            conn.Dispose()
            conn.Close()
        End Try
 
    End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Dirk Haest

membership

This solution is only available to members.

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

Start Free Trial

rchrdrea

ASKER

Dhaest,
Thanks for the website. Unfrortunately it wasn't much help because I don't want to register so that I can download his example files.

Late last night I figured out part of the problem and was able to get the insert/update subroutine to work, but I am not finished with it yet because I still want to be able to query the dataset that I created for more updates...so I'll be making a seperate post on that.

I'm pretty sure I can't give myself the credit for the solution, so I'll pass the points to you becuase you were the only reply.

My solution works for both updates and inserts which I found surprising yet delightful.

I needed to pass the dataadapter like a global variable between my form load subroutine and the update subroutine so I was forced to declare the dataadapter as private above the class. I also need to properly declare the command builder.

Attached is the corrected code.

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim sqlcmd As New MySql.Data.MySqlClient.MySqlCommandBuilder
 
        Try
            sqlcmd.DataAdapter = da
            da.Update(CType(DataGrid1.DataSource, DataTable))
 
        Catch ex As MySql.Data.MySqlClient.MySqlException
            Select Case ex.Number
                Case 0
                    MessageBox.Show("Cannot connect to server. Contact administrator")
                Case 1045
                    MessageBox.Show("Invalid username/password, please try again")
                Case Else
                    MessageBox.Show(ex.ToString)
            End Select
        Finally
            conn.Dispose()
            conn.Close()
        End Try
 
End Sub

Open in new window