mysql - vb.net 从组合框向数据库中的值(int)添加值(int)

Question

vb.net 从组合框向数据库中的值(int)添加值(int)

我试图在选民点击提交按钮时在数据库中添加投票，但它不起作用，投票部分似乎没有添加，看起来查询不起作用

我的数据库模式:

|西德 | cpos | cfname |名称 |名称 |赛尔 |派对 |投票 |
| 1 |总统 |约翰 |方舟 |史密斯 | 3 |荣耀 | |

Imports MySql.Data.MySqlClient
Public Class Form4

Dim con As New MySqlConnection
Dim cmd As New MySqlCommand
Dim da As New MySqlDataAdapter
Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    con.ConnectionString = ("server=localhost;user id=root;database=db")
    Try
        con.Open()

        With cmd
            .Connection = con
            .CommandText = "SELECT CONCAT_WS(' ', cid, cfname, cmname, clname, cparty) as cid, " & _
                           "cpos, cid from candidate WHERE cpos='Vice President'"
        End With
        Dim dt As New DataTable

        da.SelectCommand = cmd
        da.Fill(dt)
        With ComboBox1
            Dim dv1 = New DataView(dt, "cpos='Vice President'", "", DataViewRowState.CurrentRows)
            .DisplayMember = "cid"
            .ValueMember = "cid"
            .DataSource = dv1
        End With
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
    con.Close()
End Sub

Private Sub cmdsubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsubmit.Click
    Dim Query As String
    Query = "Update candidate SET votes = votes + 1 WHERE cid = '" & ComboBox1.SelectedItem(0).ToString & "')"

    Dim cmd As MySqlCommand = New MySqlCommand(Query, con)

    Try
        cmd.ExecuteNonQuery()
    Catch ex As Exception
    End Try
    Dim ds As New DataSet
    Dim dt As New DataTable

    da.Update(dt)
    MessageBox.Show("Query Completed")
    con.Close()
End Sub
End Class

Answer 1

您的查询末尾不需要右括号

 Query = "Update candidate SET votes = votes + 1 " & _ 
         "WHERE cid = '" & ComboBox1.SelectedItem(0).ToString & "'"

如果你不吞下异常，这个错误应该很容易被注意到。
不要创建空的Try/Catch block ，至少打印异常捕获的错误信息

Try
     cmd.ExecuteNonQuery()
 Catch ex As Exception
     MessageBox.Show(ex.Message)
 End Try

然而，虽然在这种情况下并没有真正的 Sql 注入(inject)危险，但我仍然建议使用参数化查询。

con.Open()
Query = "Update candidate SET votes = votes + 1 WHERE cid = @id"
Dim cmd As MySqlCommand = New MySqlCommand(Query, con)
cmd.Parameters.AddWithValue("@id", ComboBox1.SelectedItem(0).ToString)
Try
    cmd.ExecuteNonQuery()
Catch ex As Exception
     MessageBox.Show(ex.Message)
End Try
con.Close()

' remove the subsequent lines with the adapter because they are useless

最后一点，您确定数据表字段 cid 是字符串吗？如果它是一个数字，那么您需要将此处传递的值转换为整数，否则您可能会遇到数据类型不匹配错误。

编辑 有了错误消息，很明显连接已被先前的命令关闭，因此该命令无法使用。在这种情况下，我建议删除保留对连接的引用的全局对象，并始终在需要时创建连接对象并在使用后立即关闭它。 Using Statement非常适合这种情况

Query = "Update candidate SET votes = votes + 1 WHERE cid = @id"
Using con = new MySqlConnection("server=localhost;user id=root;database=db")
Using cmd = New MySqlCommand(Query, con)
    con.Open()
    cmd.Parameters.AddWithValue("@id", ComboBox1.SelectedItem(0).ToString)
    Try
        cmd.ExecuteNonQuery()
     Catch ex As Exception
        MessageBox.Show(ex.Message)
     End Try
 End Using
 End Using