Need Help for Updating database in vb.net

Question

Someone knows why I got an exception when the code running at cmd.ExecuteNonQuery()? when I update Date, it runs perfectly but when I update the Finish date, I got that error. This is the code

Call connection()
    If txt_UpCom.Text = "" Or cbCom.SelectedItem = "" Then
        If txt_ComNew.Enabled = True Then
            MessageBox.Show("Please fill the entire data!")
        End If
    Else
        Try
            Dim str As String
            If cbCom.SelectedItem = "Date" Or cbCom.SelectedItem = "Finish Date" Then
                Dim comdate As String = dateCom.Value.ToString("yyyy-MM-dd")
                str = "UPDATE complain SET " & cbCom.SelectedItem & "= '" & comdate & "' WHERE ComplainID = '" & txt_UpCom.Text & "'"
            Else
                str = "UPDATE complain SET " & cbCom.SelectedItem & " = '" & txt_ComNew.Text & "' WHERE ComplainID = '" & txt_UpCom.Text & "'"
            End If
            cmd = New MySql.Data.MySqlClient.MySqlCommand(str, conn)
            cmd.ExecuteNonQuery()
            msgBoxSuccess.ShowDialog()
            conn.Close()
        Catch ex As Exception
            MessageBox.Show("Unable to Update")
        End Try
    End If

This is the table in database

Finish Date column name should be enclosed by square brackets like [Finish Date] or quotes like "Finish Date". — Jacek Wróbel, Oct 06 '19 at 08:41
@Rhapsody that is the message Exception thrown: 'MySql.Data.MySqlClient.MySqlException' in MySql.Data.dll — Parama Artha, Oct 06 '19 at 10:14

UNOPARATOR · Answer 1 · 2019-10-06T11:27:13.410

0

As @Jacek Wróbel mentioned in the comments, you should keep the column names between [] (square brackets) or "" (double quots). The same thing goes for table names (or any other SQL objects for that matter).

Long story short, change this part of your code:

            If cbCom.SelectedItem = "Date" Or cbCom.SelectedItem = "Finish Date" Then
                Dim comdate As String = dateCom.Value.ToString("yyyy-MM-dd")
                str = "UPDATE complain SET " & cbCom.SelectedItem & "= '" & comdate & "' WHERE ComplainID = '" & txt_UpCom.Text & "'"
            Else
                str = "UPDATE complain SET " & cbCom.SelectedItem & " = '" & txt_ComNew.Text & "' WHERE ComplainID = '" & txt_UpCom.Text & "'"
            End If

to this:

            If cbCom.SelectedItem = "Date" Or cbCom.SelectedItem = "Finish Date" Then
                Dim comdate As String = dateCom.Value.ToString("yyyy-MM-dd")
                str = "UPDATE `complain` SET `" & cbCom.SelectedItem & "` = '" & comdate & "' WHERE `ComplainID` = '" & txt_UpCom.Text & "'"
            Else
                str = "UPDATE `complain` SET `" & cbCom.SelectedItem & "` = '" & txt_ComNew.Text & "' WHERE `ComplainID` = '" & txt_UpCom.Text & "'"
            End If

Note: I prefer to keep things consistant, so I added [] to each object whether it contains spaces or not.

edited Oct 06 '19 at 11:27

answered Oct 06 '19 at 09:07

UNOPARATOR

688
1
6
18

Can you provide the Exception's message like @Rhapsody asked? Also the table's schema would be helpful also. – UNOPARATOR Oct 06 '19 at 09:35
Exception thrown: 'MySql.Data.MySqlClient.MySqlException' in MySql.Data.dll – Parama Artha Oct 06 '19 at 10:13
Put a breakpoint (F9) in the line below Catch and while debugging right click "ex" and select "Quick Watch" from the context menu. Then expand the result (if it is not already expanded), and tell us what the ex.Message says. – UNOPARATOR Oct 06 '19 at 10:21
{"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[complain] SET [Finish Date] = '2019-10-05' WHERE [ComplainID] = 'COMP003'' at line 1"} – Parama Artha Oct 06 '19 at 11:20
Line 1 only imports the SQL – Parama Artha Oct 06 '19 at 11:21
Accoring to [this](https://stackoverflow.com/questions/14190798/how-to-select-a-column-name-with-a-space-in-mysql), for MySql you must use backtick (`). I'm updating the answer according to this information. – UNOPARATOR Oct 06 '19 at 11:26
it solved already, I change the column name in the database, and it works, thanks – Parama Artha Oct 06 '19 at 11:32

Need Help for Updating database in vb.net

1 Answers1