VB.NET mySQL 插入命令

VB.NET mySQL insert command

提问by ExtremeSwat

this is my DB :

这是我的数据库：

I am trying to add to my presence table a new row, here it is : I don't get any VB errors nor mySQL errors, so I guess I am missing something important to add here.

我正在尝试向我的状态表添加一个新行，它是：我没有收到任何 VB 错误或 mySQL 错误，所以我想我错过了在这里添加的重要内容。

Can you guys help me please ? Thanks in advance

你们能帮我吗？提前致谢

  Try

        Dim SqlQuery As String = "INSERT INTO presence(id_presence,id,hours,date) " & _
            "SELECT DISTINCT @Id_presence,@Id,@Hours,@Date FROM presence"

        Using con = New MySqlConnection("Server = localhost;Database = accounts; Uid=root; Pwd = password")
            Using SQLcmd = New MySqlCommand(SqlQuery, con)
                con.Open()
                SQLcmd.Parameters.Add(New MySqlParameter("@Id_presence", TextBox1.Text))
                SQLcmd.Parameters.Add(New MySqlParameter("@Id", TextBox2.Text))
                SQLcmd.Parameters.Add(New MySqlParameter("@Hours", TextBox3.Text))
                SQLcmd.Parameters.Add(New MySqlParameter("@Date", TextBox4.Text))
            End Using


        End Using




    Catch ex As Exception

    End Try

回答by Steve

The correct syntax for adding a NEW record to your table should be

向表中添加新记录的正确语法应该是

 Dim SqlQuery As String = "INSERT INTO presence(id_presence,id,hours,date) " & _
                          "VALUES (@Id_presence,@Id,@Hours,@Date)"

And, of course, every command should be executed otherwise it is just a uselese lines of code.
You are missing this line after the creation of the parameters.

而且，当然，每个命令都应该执行，否则它只是无用的代码行。
创建参数后，您将缺少此行。

  SQLcmd.ExecuteNonQuery()

From your image is not possible to be sure, but if the idpresence is an AUTOINCREMENT field, then you should not try to set its value with your command and parameter but let the database do it for you. (Duplicate keys problems could arise if more than one user inserts records at the same time)

从你的图像是不可能确定的，但如果 idpresence 是一个 AUTOINCREMENT 字段，那么你不应该尝试用你的命令和参数设置它的值，而是让数据库为你做。（如果多个用户同时插入记录，可能会出现重复键问题）

As a last thing, your parameters' type are not specified so they don't match the underlying datatable schema. You could use AddWithValue and converting the input textbox values to the correct database type

最后，未指定参数的类型，因此它们与基础数据表架构不匹配。您可以使用 AddWithValue 并将输入文本框值转换为正确的数据库类型

SQLcmd.Parameters.AddWithValue("@Id_presence", Convert.ToInt32(TextBox1.Text))
SQLcmd.Parameters.AddWithValue("@Id", Convert.ToInt32(TextBox2.Text))
SQLcmd.Parameters.AddWithValue("@Hours", Convert.ToInt32(TextBox3.Text))
SQLcmd.Parameters.AddWithValue("@Date", Convert.ToDateTime(TextBox4.Text))

回答by Adel

Add SQLcmd.ExecuteNonQuery();to execute it before End Using

添加SQLcmd.ExecuteNonQuery();以在结束使用之前执行它

Example

例子

Public Sub CreateCommand(ByVal queryString As String, _
  ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        command.Connection.Open()
        command.ExecuteNonQuery()
    End Using 
End Sub

reference: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2

参考：http: //msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code -snippet-2