VB.NET mySQL 插入命令 [英] VB.NET mySQL insert command

问题描述

这是我的数据库:

我正在尝试向我的状态表添加一个新行，如下所示:我没有收到任何 VB 错误或 mySQL 错误，所以我想我在这里缺少一些重要的内容.

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.

你们能帮我吗?提前致谢

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

推荐答案

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

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()

无法确定来自您的图像，但如果 idpresence 是一个 AUTOINCREMENT 字段，那么您不应该尝试使用您的命令和参数设置其值，而是让数据库为您完成.(如果多个用户同时插入记录，可能会出现重复键问题)

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)

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

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

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))

这篇关于VB.NET mySQL 插入命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！