Updating MySQL table with DataGridView Changes

[telmessos]

Hi all,

I searched internet including this forums to find a working example about the following subject but couldn't find a solution about my problem.

I have two main problems:

1) I have a DataGridView filled from the MySQL database table. I want code update the changed records, when a "Save Changes" button pressed by the user.

I created the code below to make it. It makes update on the changed row, but enters Null values. So after the update on the database, the values become null.

2) At "Private Sub cihazmodeli_SelectedIndexChanged" section my code reads the database and loads the values to the DataGridView. When I make another change on the Combobox, it adds the values of the new SQL results at the bottom of the previous records. I tried to clean the results using either Clear() or some other methods but got a message meaning "Content entered with DataSource can not be cleaned with Clear or other methods". What is the way of clearing the previous choice's records?

Many thanks
telmessos

Dim cn As New MySqlConnection("Data Source=localhost;Database=stokprogrami;User ID=root;Password=mypassword;")
    Dim adapter As New MySqlDataAdapter
    Dim command As New MySqlCommand
    Dim dtListe As New DataTable()
'Above section is just after the Public Class Form1 element.

Private Sub cihazmodeli_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cihazmodeli.SelectedIndexChanged
        'Datagridview'e girilir.
        If Not CStr(cihazmodeli.SelectedItem.Value) = "" Then
            Dim sorgu As String = "Select serinumarasi,garantisuresi,faturatarihi,servicetag,macid1,macid2,winlisans1,winlisans2 from stoklar where modelID='" & CStr(cihazmodeli.SelectedItem.Value) & "'"
            command.CommandText = sorgu
            command.Connection = cn
            adapter.SelectCommand = command
            adapter.Fill(dtListe)
            cn.Close()
            mevcutcihazlar.DataSource = dtListe
            mevcutcihazlar.Columns(0).HeaderText = "Seri Numarası"
            mevcutcihazlar.Columns(1).HeaderText = "Garanti Süresi"
            mevcutcihazlar.Columns(2).HeaderText = "Fatura Tarihi"
            mevcutcihazlar.Columns(3).HeaderText = "Service Tag"
            mevcutcihazlar.Columns(4).HeaderText = "Mac ID 1"
            mevcutcihazlar.Columns(5).HeaderText = "Mac ID 2"
            mevcutcihazlar.Columns(6).HeaderText = "Win Lisans 1"
            mevcutcihazlar.Columns(7).HeaderText = "Win Lisans 2"
            mevcutcihazlar.Visible = True
        Else
            mevcutcihazlar.DataSource = ""
            mevcutcihazlar.Visible = False
        End If
    End Sub

    Private Sub savechanges_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles savechanges.Click
        If Not dtListe.GetChanges() Is Nothing Then
            Dim update As New MySqlCommand("UPDATE stoklar SET serinumarasi = @serinumarasi, garantisuresi = @garantisuresi, faturatarihi = STR_TO_DATE(@faturatarihi,'%d.%m.%Y'), servicetag = @servicetag, macid1 = @macid1, macid2 = @macid2, winlisans1 = @winlisans1, winlisans2 = @winlisans2", cn)
            update.Parameters.Add("@serinumarasi", MySqlDbType.VarChar, 255, "serinumarasi")
            update.Parameters.Add("@garantisuresi", MySqlDbType.Int16, 3, "garantisuresi")
            update.Parameters.Add("@faturatarihi", MySqlDbType.String, 255, "faturatarihi")
            update.Parameters.Add("@servicetag", MySqlDbType.VarChar, 50, "servicetag")
            update.Parameters.Add("@macid1", MySqlDbType.VarChar, 50, "macid1")
            update.Parameters.Add("@macid2", MySqlDbType.VarChar, 50, "macid2")
            update.Parameters.Add("@winlisans1", MySqlDbType.VarChar, 50, "winlisans1")
            update.Parameters.Add("@winlisans2", MySqlDbType.VarChar, 50, "winlisans2")
            adapter.UpdateCommand = update
            Dim rowsAffected As Integer = adapter.Update(dtListe)
            MessageBox.Show(rowsAffected & " rows were affected by the save operation.")
        End If
    End Sub

[dglienna]

Don't save data there. Use a button to save a record, after the changes have been made.

[telmessos]

thanks. but it is already on a button click event.

[dglienna]

It's in the code that you posted, not the click event

[telmessos]

what do you mean by saying "save data" in my combobox change event it loads the database records to datagridview. And on Save Changes button click, it checks if there's a change on the records and update command comes. so I am not sure what exactly you mean.

thanks

[dglienna]

Where is it supposed to get the VALUE when up UPDATE? They are NULL in the new sub.

Create a SUB to SAVE the record.

Pass it the RECORD, and then use the RECORD.FIELDS() to SAVE the data.

[telmessos]

thanks for your effort for the help. but couldn't understand anything I wrote the code from the examples on the internet. I changed the MSSQL commands to MySQL and trying to make it work. Is it possible to send a working examples URL ?

[dglienna]

Hope you learn to code BETTER than you searched...

http://dev.mysql.com/tech-resources/...ew-vb-net.html

[telmessos]

I already found that on my previous searches. But it is a load of codes which is just complicated. There must be a shorter way to do it. But thanks for your insults. it really helped..