mysql - 使用带有 VS2010 的 vb.net 在 mysql 上插入数据

Question

我试图在 mysql 中插入数据，但我就是做不到 我尝试在网上搜索答案并尝试了我的代码中的所有内容，但它就是不会插入 here is the image of the error希望有人能帮助我并提前感谢...圣诞快乐!

这是代码

    myconn = New MySqlConnection
    myconn.ConnectionString = "host=127.0.0.1;user=root;password=;database=engr_log"
    Dim Reader As MySqlDataReader
    Try
        myconn.Open()
        Dim query As String
        query = "insert into log_tbl ('ID', 'owner_name', 'business_name', 'Amount_paid', 'Location', 'Date') values (NULL, '" & txtname.Text & "','" & txtbus.Text & "','" & txtamount.Text & "', '" & txtloc.Text & "','" & dtp1.Value & "');"
       command = New MySqlCommand(query, myconn)
        Reader = command.ExecuteReader
        MessageBox.Show("Entry Saved!!","SAVE", MessageBoxButtons.OK, MessageBoxIcon.Information)
        myconn.Close()
    Catch ex As Exception
        MsgBox(ex.Message.ToString)
    End Try
    getlist()
End Sub

Answer 1

删除插入语句中列周围的单引号:

插入 log_tbl (ID, owner_name, business_name, Amount_paid, Location, Date)...

但更大的问题是您应该使用带参数的查询，不仅可以避免错误，还可以避免 SQL 注入(inject)。

例子:

Dim connectionString = "host=127.0.0.1;user=root;password=;database=engr_log"
Dim query = "insert into log_tbl (owner_name, business_name, Amount_paid, Location, Date) values (@owner_name, @business_name, @Amount_paid, @Location, @Date);"
Using connection As New MySqlConnection(connectionString)
    Dim command As New MySqlCommand(query, connection)
    command.Parameters.AddWithValue("@owner_name", txtname.Text)
    command.Parameters.AddWithValue("@business_name", txtbus.Text)
    command.Parameters.AddWithValue("@Amount_paid", txtamount.Text)
    command.Parameters.AddWithValue("@Location", txtloc.Text)
    command.Parameters.AddWithValue("@Date", dtp1.Text)
    command.Connection.Open()
    command.ExecuteNonQuery()
End Using

更好的是，您可以明确指定类型:

command.Parameters.Add("@owner_name", SqlDbType.VarChar)
command.Parameters.Add("@business_name", SqlDbType.VarChar)
command.Parameters.Add("@Amount_paid", SqlDbType.Float)
command.Parameters.Add("@Location", SqlDbType.VarChar)
command.Parameters.Add("@Date", SqlDbType.DateTime)