mysql - VB.net mysql 插入不工作

Question

我试图在 vb.net 中运行一个插入语句来添加一条记录。当我在 mysql 控制台上运行它时，应用程序提供的行有效 - mysql 查询在 adaptr.Fill(utable) 处出错，我现在认为这是因为没有任何数据可填充到我的数据表中。这种确切的格式在我的应用程序的其他地方使用 Select 和更新语句。有什么想法吗？

Dim con As New MySqlConnection()
    Dim adptr As New MySqlDataAdapter
    Public utable As New DataTable



            con.ConnectionString = "server=localhost;" _
& "user id=user;" _
& "password=password;" _
& "database=DMT;"
            adptr = New MySqlDataAdapter(String.Format("INSERT INTO users (uname, pword, fname, lname, Usertype) VALUES ({0}, md5('{1}'), {2}, {3}, {4});", MySqlHelper.EscapeString(TextBox4.Text), MySqlHelper.EscapeString(passw), MySqlHelper.EscapeString(TextBox1.Text), MySqlHelper.EscapeString(TextBox2.Text), ulevl), con)
            Try
                adptr.Fill(utable)
            Catch err As Exception
                Dim strError As String = "Exception: " & err.ToString()
                MsgBox(strError)
            End Try

Answer 1

我会考虑在构建 SQL 语句时使用参数。您可以使用下面的代码向数据库中插入一条记录。

Dim cb As New MySqlConnectionStringBuilder
cb.Database = "DMT"
cb.Server = "localhost"
cb.UserID = "user"
cb.Password = "password"

Using cnn As New MySqlConnection(cb.ConnectionString)
  Using cmd As New MySqlCommand("INSERT INTO users (uname, pword, fname, lname, Usertype) VALUES (@uname, md5(@pword), @fname, @lname, @usertype)", cnn)
    cmd.Parameters.AddWithValue("@uname", TextBox4.Text)
    cmd.Parameters.AddWithValue("@pword", passw)
    cmd.Parameters.AddWithValue("@fname", TextBox1.Text)
    cmd.Parameters.AddWithValue("@lname", TextBox2.Text)
    cmd.Parameters.AddWithValue("@usertype", ulevl)

    Try
      cnn.Open()
      cmd.ExecuteNonQuery()
    Catch ex As Exception
      MessageBox.Show(ex.ToString)
    End Try
  End Using
End Using