INSERT INTO语句vb2008中的语法错误

i got error while inserting new record in database. any help please error is "Syntax error in INSERT INTO statement." ok i add the complete syntax.

我在数据库中插入新记录时出错。任何帮助请错误是“INSERT INTO语句中的语法错误。”好吧,我添加完整的语法。

Dim dbCommand As OleDbCommand
        Dim DBConn As OleDbConnection
        Dim DBInsert As New OleDbCommand()
        myword = "عابد"
        mypronounce = "عابِد [عا + بِد]"
        mylanguage = "(عربی)"
        mysiga = "(  مذکر - واحد  )"
        mymuradifat = "زاہِد, پُجاری, مُتَّقی, پارْسا, نیک, صالِح, تَپَسّی, ذاکِر, سَنْت, صُوفی, خُدا پَرَسْت"
        myenglish = "An adorer, worshipper, votary, devotee "
        mymurakbat = "لَکھْنَوی دَبِسْتان,  لَکھْنَوی سِکُول"
        myword = Replace(myword, "'", "''")
        mypronounce = Replace(mypronounce, "'", "''")
        mylanguage = Replace(mylanguage, "'", "''")
        mysiga = Replace(mysiga, "'", "''")
        mymuradifat = Replace(mymuradifat, "'", "''")
        myenglish = Replace(myenglish, "'", "''")
        mymurakbat = Replace(mymurakbat, "'", "''")
        '  & "(word,pronounce,language,siga,mutradifat,english,murakabat) " _
        DBConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & mypath & ";Jet OLEDB:Database Password=xxxxxxxx;")
        DBInsert.CommandText = "Insert Into data " _
             & "(word,pronounce,language,siga,mufradat,english,murakabat) " _
             & "Values (" _
             & "'" & myword & "', " _
             & "'" & mypronounce & "', " _
             & "'" & mylanguage & "', " _
             & "'" & mysiga & "', " _
             & "'" & mymuradifat & "', " _
             & "'" & myenglish & "', " _
             & "'" & mymurakbat & "')"
        DBInsert.Connection = DBConn
        DBInsert.Connection.Open()
        DBInsert.ExecuteNonQuery()
        DBConn.Close()

4 个解决方案

#1

There is no way to tell what the problem is without knowing what error you received. At first glance the statement looks correct. All I see is the variables for mymuradifat and mymurakbat do not match the spelling of mutradifat and murakabat which is no issue if that is what you named them. Are you sure the names are correct. What is the error?

在不知道收到的错误的情况下,无法确定问题所在。乍一看,声明看起来是正确的。我所看到的是mymuradifat和mymurakbat的变量与mutradifat和murakabat的拼写不匹配,如果这就是你命名的那个就没有问题。你确定名字是否正确。错误是什么?

#2

DBInsert.CommandText = "Insert Into data " _
             & "(word,pronounce,language,siga,mufradat,english,murakabat) " _

I dont think those 2 underlines "_" should be there.

我不认为那两个下划线“_”应该在那里。

#3

First, it looks like you would be wide open to a SQL Injection, and should use parameterized queries. That in itself would probably resolve it for you.

首先,看起来你对SQL注入是开放的,应该使用参数化查询。这本身可能会为你解决它。

However, as it stands, its probably due to one or more of the fields you have may have an embedded single quote, thus cancelling out (creating an unbalanced quotes) in the insert command... OR a semi-colon which indicates the end of one statement and begins the next.

但是,就目前而言,它可能是由于您拥有的一个或多个字段可能具有嵌入的单引号,因此在插入命令中取消(创建不平衡的引号)...或者表示结束的分号一个陈述,然后开始下一个。

EDIT..

Then is it perhaps a type-o between insert column

那么它可能是插入列之间的类型o

mutradifat

and VALUE field of

和VALUE字段

mymuradifat

one has a "muTr"... the other "mur"...

一个人有“muTr”......另一个人“mur”......

I would definitely change it over to parameterized queries, such as

我肯定会将其更改为参数化查询,例如

 DBInsert.CommandText = "Insert Into data " _
             & "(word, pronounce, language, siga, mufradat, english, murakabat) " _
             & "Values ( ?, ?, ?, ?, ?, ?, ? )";

DBInsert.Parameters.AddWithValue( "myWord", myword );
DBInsert.Parameters.AddWithValue( "myPronounce", mypronounce );
DBInsert.Parameters.AddWithValue( "myLanguage", mylanguage );
DBInsert.Parameters.AddWithValue( "mySiga", mysiga );
DBInsert.Parameters.AddWithValue( "myUradifat", mymuradifat );
DBInsert.Parameters.AddWithValue( "myEnglish", myenglish );
DBInsert.Parameters.AddWithValue( "myMurakbat", mymurakbat );

In addition, after a sample insert, you can probably get rid of the

另外,在插入样本后,你可以摆脱它

YourVariable = Replace( YourVariable, "'", "''")

YourVariable =替换(YourVariable,“'”,“''”)

instances since the parametrization won't have the issue of mis-matched quotes in the command itself.

实例,因为参数化不会在命令本身中出现错误匹配的引号问题。

#4

Diagnosing SQL like this can be tricky. The best way to start would be breakpoint the lines where it creates the insert command, then grab the SQL that is created and paste it into management studio (or anywhere you can view the text properly).

像这样诊断SQL可能很棘手。最好的方法是断点创建insert命令的行,然后获取创建的SQL并将其粘贴到管理工作室(或任何可以正确查看文本的地方)。

You might find that an extra ' is being added or a typo as suggested above. Recheck all your column names and examine the SQL you are going to execute carefully. Also check that you dont have any characters like -- which sql could consider as a comment. And that SQL can store the characters you are passing in.

您可能会发现正在添加额外的'或如上所述的拼写错误。重新检查所有列名并仔细检查要执行的SQL。还要检查你没有任何字符 - 哪个sql可以考虑作为评论。并且SQL可以存储您传入的字符。

Aside from this I strongly recommend using stored procedures and parameters, even if this is only for your own personal use it doesn't hurt to use good practice and its a good habit to build.

除此之外,我强烈建议使用存储过程和参数,即使这仅仅是为了您自己的个人用途,使用良好实践并且构建它的良好习惯也不会有害。

#1

#2

DBInsert.CommandText = "Insert Into data " _
             & "(word,pronounce,language,siga,mufradat,english,murakabat) " _