VB.NET Refresh DataGridView function works with insert and delete commands but not with update

Question

I have a form on VB.NET with which I manage data for a mysql database. The form, in addition to the various fields for entering data (textboxes + 1 datepicker), includes a "Save" button (which turns into "Update" when I press the "Edit" button), an "Edit" button, a "Delete" button and a DataGridView.

I have created a ShowData () to display the data in the DataGridView. This function works on the form load, it works also when I use the "Save" command (with the mysql Insert command) and the "Delete" command (with the mysql Delete command) but it seems to create problems when I use the "Update" command.

When I use the "Update" command the data is updated in the database but the "ShowData ()" function gives me the error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 and, consequently, the data inside the DataGridView is not updated.

The "ShowData ()" function is also present on the "CellMouseDown" event of the DataGridView. The same error only shows up when I use the "Update" command first and then I move through the rows.

Public Class

Dim Connection As New MySqlConnection("server=localhost; user=root; password=; database=dbname")
    Dim MySQLCMD As New MySqlCommand
    Dim MySQLDA As New MySqlDataAdapter
    Dim DT As New DataTable

    Dim Table_Name As String = "tablename"
    Dim Dati As Integer

    Dim LoadImagesStr As Boolean = False
    Dim data1Ram, data2Ram As String
    Dim StatusInput As String = "Save"
    Dim SqlCmdSearchstr As String

ShowData()

Try
            Connection.Open()
        Catch ex As Exception
            MessageBox.Show("Connection failed !!!" & vbCrLf & "Please check that the server is ready !!!", "Error Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
        End Try

        Try
            MySQLCMD.CommandType = CommandType.Text
            MySQLCMD.CommandText = "SELECT data1,data2 FROM " & Table_Name & " WHERE cid=" & cid.Text & ""
            MySQLDA = New MySqlDataAdapter(MySQLCMD.CommandText, Connection)
            DT = New DataTable
            Dati = MySQLDA.Fill(DT)
            If Dati > 0 Then
                DataGridView1.DataSource = Nothing
                DataGridView1.DataSource = DT

                DataGridView1.DefaultCellStyle.ForeColor = Color.Black
                DataGridView1.ClearSelection()
            Else
                DataGridView1.DataSource = DT
            End If
        Catch ex As Exception
            MsgBox("Connection Error !!!" & vbCr & ex.Message, MsgBoxStyle.Critical, "Error Message")
            Connection.Close()
            Return
        End Try

        DT = Nothing
        Connection.Close()

Save/Update Button Click

Dim mstream As New System.IO.MemoryStream()    

        If StatusInput = "Save" Then

            Try
                Connection.Open()
            Catch ex As Exception
                MessageBox.Show("Connection Failed" & vbCrLf & "Check internet connection !!!", "No connection", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return
            End Try

            Try
                MySQLCMD = New MySqlCommand
                With MySQLCMD
                    .CommandText = "INSERT INTO " & Table_Name & " (data1, data2) VALUES (@data1, @data2)"
                    .Connection = Connection
                    .Parameters.AddWithValue("@data1", textbox1.Text)
                    .Parameters.AddWithValue("@data2", textbox2.Text)
                    .ExecuteNonQuery()
                End With
                MsgBox("Data saved successfully", MsgBoxStyle.Information, "Information")
                ClearInputUpdateData()
            Catch ex As Exception
                MsgBox("Data failed to save !!!" & vbCr & ex.Message, MsgBoxStyle.Critical, "Error Message")
                Connection.Close()

                Return
            End Try
            Connection.Close()

        Else

            Try
                Connection.Open()
            Catch ex As Exception
                MessageBox.Show("Connection failed !!!" & vbCrLf & "Please check that the server is ready !!!", "Error Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return
            End Try

            Try
                MySQLCMD = New MySqlCommand
                With MySQLCMD
                    .CommandText = "UPDATE " & Table_Name & " SET `data1` = @data1, `data2` = @data2 WHERE id=@id"
                    .Connection = Connection
                    .Parameters.AddWithValue("@data1", textbox1.Text)
                    .Parameters.AddWithValue("@data2", textbox2.Text)
                    .Parameters.AddWithValue("@id", id.Text)
                    .ExecuteNonQuery()
                End With
                MsgBox("Data updated successfully", MsgBoxStyle.Information, "Information")
                ButtonSave.Text = "Save"
                DatePicker1.Enabled = True
                ClearInputUpdateData()
            Catch ex As Exception
                MsgBox("Data failed to Update !!!" & vbCr & ex.Message, MsgBoxStyle.Critical, "Error Message")
                Connection.Close()
                Return
            End Try

            Connection.Close()
            StatusInput = "Save"

        End If

        ShowData()

Edit Button Click

If DataGridView1.RowCount = 1 Then
            textbox1.Text = data1Ram
            textbox2.Text = data2Ram
            ButtonSave.Text = "Update"
            StatusInput = "Update"
            Return
        End If

DataGridView1 CellMouseDown

Try
            If AllCellsSelected(DataGridView1) = False Then
                If e.Button = MouseButtons.Left Then
                    DataGridView1.CurrentCell = DataGridView1(e.ColumnIndex, e.RowIndex)
                    Dim i As Integer
                    With DataGridView1
                        If e.RowIndex >= 0 Then
                            i = .CurrentRow.Index
                            LoadImagesStr = True
                            data1Ram = .Rows(i).Cells("data1").Value.ToString
                            data2Ram = .Rows(i).Cells("data2").Value.ToString
                            ShowData()
                        End If
                    End With
                End If
            End If
        Catch ex As Exception
            Return
        End Try

EDIT 1

As the error message explains, this is a command syntax problem. In fact, I tried replacing this string in ShowData()

"SELECT data1,data2 FROM " & Table_Name & " WHERE cid=" & cid.Text & ""

with this

"SELECT data1,data2 FROM tablename WHERE cid = 10"

and no errors appear. So I need to figure out how to put the table name and conditions inside the command string

Well, sorry to point out the obvious. You've got a syntax error in the update SQL. Are the ' around the field names really needed? Also notice, your WHERE id=@id, not seeing where your defining the parameter @id nor adding it to the MySQLCMD.Parameters collection before executing it — Hursey
Connections and Commands need to be declared and disposed in the method where they are used. Do not declare then outside this method. — Mary
Learn how to use Using...End Using blocks to ensure that database objects are closed and disposed. — Mary
@Mary I tried the "using-end using" method but the same error appears. Check this screenshot: Image — Claudio Liverano
@Hursey the "update" syntax works fine. The data is updated in the database. The error appears when the datagridview tries to update itself using ShowData() and ONLY when the previous command was "UPDATE". In summary: With insert command + showdata () it works With the command delete + showdata () it works With the command update + showdata () it doesn't work and returns the error "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1" — Claudio Liverano

Claudio Liverano Claudio Liverano · Accepted Answer · 2021-05-04T13:45:21

As the error message explains, this is a command syntax problem. In fact, I tried replacing this string in ShowData()

"SELECT data1,data2 FROM " & Table_Name & " WHERE cid=" & cid.Text & ""

with this

"SELECT data1,data2 FROM tablename WHERE cid = 10"

and no errors appear. So I need to figure out how to put the table name and conditions inside the command string

Edit Solved. Thanks to comments. Connections and Commands need to be declared and disposed in the method where they are used. Do not declare then outside this method.

VB.NET Refresh DataGridView function works with insert and delete commands but not with update

1 Answers