INSERT语句中的语法错误.................. [英] Syntax error in INSERT into statement..................

问题描述

Try
       Dim con As New OleDbConnection
       Dim cmd As New OleDbCommand
       Dim ddt As New OleDbDataAdapter
       Dim dtd As New DataSet
       con.ConnectionString = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Ticket.mdb")
       cmd.CommandText = "INSERT INTO user VALUES('" & TextBox1.Text & "' , '" & TextBox2.Text & "')"
       cmd.Connection = con
       con.Open()
       cmd.ExecuteNonQuery()
       MessageBox.Show("New User Created", "New User", MessageBoxButtons.OK, MessageBoxIcon.Information)
   Catch
       MsgBox("User Already Created")
   End Try

我尝试过：



没什么....只是一次又一次地得到这个错误.....我的代码似乎是正确的

What I have tried:

Nothing....Just getting this error again and again.....my code seems to be right

推荐答案

当你省略列名时确保按表的列顺序插入数据，最好还是使用语句中的列名：

SQL INSERT INTO语句 [ ^ ]

When you omit the column names make sure you insert the data in the column order of the table, better still use the column names in the statement :
SQL INSERT INTO Statement[^]

如查询值和目标字段的数量不同。 [ ^ ]您需要指定目标列。否则，当更改，添加或更改列时，您将遇到问题。



另一个潜在的错误来源是您没有使用参数。如果文本框中的数据有撇号（'），那么您的语句将失败。更不用说你容易受到SQL注入这一事实的问题。因此，请使用 OleDbParameter Class（System.Data。 OleDb） [ ^ ]



作为附注，当您捕获异常时，您认为发生异常是因为记录已存在。这只是声明失败的众多潜在原因之一。因此，最好的选择是显示异常的真实消息。我真的建议通过正确执行数据库操作 [ ^ ]

As explained in Number of query values and destination fields are not the same.[^] you need to specify the target columns. Otherwise you will have problems when columns are changed, added, or the order changes.

Another potential error source is the fact that you're not using parameters. If the data in the text box has an apostrophe (') then your statement will fail. Not to mention the problems for the fact that you're vulnerable to SQL injections. So make use of OleDbParameter Class (System.Data.OleDb)[^]

As a side note when you catch the exception, you assume that the exception happened because the record already exists. This is just one of many potential reasons for the statement to fail. So the best option would be to show the real message for the exception. I really suggest going through Properly executing database operations[^]

这篇关于INSERT语句中的语法错误..................的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！