SQL语句中的语法错误 [英] Syntax Error in SQL Statement

问题描述

您好。我试图在用户单击按钮时运行以下代码，

但我在SQL中遇到语法错误。我有一种感觉，它必须用括号做
。任何人都可以帮忙吗？

这里是SQL（关注自动换行：

StrSql =" insert into [Products]（ProductName）"''SQL字符串执行
带有RunSQL语句的


16980 StrSql = StrSql&" Values（''"& TxtProductName.Text&

" ;''）;"


谢谢大家


科林


***已发送通过Devdex http://www.devdex.com ***

不要只是参加USENET ......获得奖励！

解决方案

嗨Colin


我喜欢分配一个变量来表示一个表格

控件所包含的值。

然后我完全分离SQL的文本部分中的任何变量/>
做这种事情的字符串。


为表单的控件分配变量也允许你检查我在休息模式下，t''s

内容。

在代码中设置断点，例如StrSql =;线。然后运行

程序，你会看到我的意思。

Hover将鼠标光标放在MyProductName上变量，并且它的内容将显示在工具提示中


我也建议使用.Execute方法（包括dbFailOnError）

而不是RunSQL。

使用RunSQL的问题是某些（但不是全部）记录将被动作更改为
查询，直到发生错误为止。

如果RunSQL发生错误，你必须查看表格并确定哪些记录有（或没有）已被更改。

dbFailOnError是一个全有或全无的更新记录的方法。如果发生

错误，所有更改都会回滚。


以上所有内容都是*我对*工作原理的理解*。 br />
话虽如此，试试这样：


********************** *******************************

....

Dim MyDB as DAO.Database

设置MyDB = CurrentDb


Dim MyProductName As String

MyProductName = Me！[TxtProductName]


StrSql =""

SrtSql = StrSql& INSERT INTO [Products]（ProductName）" ''你错过了

[Products]和（ProductName）之间的空间

SrtSql = StrSql& VALUES（"

SrtSql = StrSql& MyProductName'这里没有引号，只是变量名。

SrtSql = StrSql&"）;"


Debug.Print StrSql

''打开调试（立即）窗口

''复制生成的SQL字符串

''根据Products表创建一个新查询

''切换到SQL视图，并粘贴SQL字符串（从调试窗口复制）

''尝试切换到数据表视图。

''如果有任何错误，Access会告诉你，甚至会突出显示

违规部分


MyDB.Execute StrSql，dbFailOnEror


设置MyDB = Nothing

....

**************


-

HTH，

Don

========== ===================

使用我的** ***@Telus.Net 电子邮件

免责声明：

Professional PartsPerson

业余数据库程序员{：o）


我是Access97用户，因此所有发布的代码

样本也是基于Access97的

除非另有说明。


Do SinksIn = True

文件/保存，< ;手指放在书桌抽屉里>

循环


====================== ==========

" ColinWard" < JE ********* @ hotmail.com>在消息中写道

news：40 ********************** @ news.newsgroups.ws ..。

嗨。我试图在用户单击按钮时运行以下代码，
但我在SQL中遇到语法错误。我有一种感觉它必须用括号做。任何人都可以帮忙吗？

这里是SQL（关注自动换行：

StrSql =" insert into [Products]（ProductName）"''SQL字符串执行
使用RunSQL语句
16980 StrSql = StrSql&" Values（''"& TxtProductName.Text&
"''）;"
<感谢你们

科林

***通过Devdex发送 http://www.devdex.com ***
不要只是参加USENET ......获得奖励！

感谢您的帮助。我将在周一试用。当然您知道

这意味着我将不得不更改所有的runSQL语句

arrgh !!! LOL


***通过Devdex发送 http://www.devdex.com ***

不要只参加USENET ......获得奖励！

你好，


这就是我现在所拥有的但是我得到了一个''Mising Operator''错误

SQL语句的最后一部分。


********************** **************************** ******

Dim MyDB作为DAO.Database

设置MyDB = CurrentDb

Dim strSql As String

Dim MyProductName As String

MyProductName = Me！[TxtProductName]

strSql =""

strSql = strSql& INSERT INTO [Products]（ProductName）"

strSql = strSql& VALUES（"

strSql = strSql& MyProductName''这里没有引号，只是变量名。

strSql = strSql&"）;"


Debug.Print strSql

''打开调试（立即）窗口

''复制生成的SQL字符串

''根据Products表创建一个新查询

''切换到SQL视图，并粘贴到SQL字符串中（从Debug中复制

窗口）

''尝试切换到数据表视图。

''如果有任何错误，Access会告诉你，甚至会突出显示



''违规部分


MyDB.Execute strSql，dbFailOnError

设置MyDB = Nothing


******************************************** *


感谢Don


Colin

***通过Devdex发送 http://www.devdex.com ***

不要刚参加USENET ......获得奖励！

Hi. I am trying to run the following code when the user clicks a button,
but I am getting a syntax error in the SQL. I have a feeling it has to
do with brackets. Can anyone help?
here is the SQL(watch for word wrap:
StrSql = "insert into[Products](ProductName)" ''SQL string which executes
with the RunSQL statement
16980 StrSql = StrSql & "Values (''" & TxtProductName.Text &
"'');"

thanks guys

Colin

*** Sent via Devdex http://www.devdex.com ***
Don''t just participate in USENET...get rewarded for it!

解决方案

Hi Colin

I like to assign a variable that represents the value contained by a form
control.
Then I totally seperate any variables from the textual portion of the SQL
string when doing this kind of thing.

Assigning a variable to a form''s control also allows you to inspect it''s
contents while in break mode.
Set a breakpoint in your code, at say the "StrSql = " line . Then run the
procedure, and you''ll see what I mean.
"Hover" your mouse cursor over the "MyProductName" variable, and it''s
contents will displayed in a "tooltip"

I''d also suggest using the .Execute method ( including the dbFailOnError)
rather than RunSQL.
The problem with using RunSQL is that some (but not all) records will be
changed by the action query, until such time that an error occurs.
If an error occurs with RunSQL, you have to look through the table and
determine which records have (or have not) been changed.
The dbFailOnError is an "all-or-nothing" approach to updating records. If an
error should occur, all changes are "rolled back".

All of the above is *my understanding* of how things work.
That being said, try it like this:

*************************************************
....
Dim MyDB as DAO.Database
Set MyDB = CurrentDb

Dim MyProductName As String
MyProductName = Me![TxtProductName]

StrSql = ""
SrtSql = StrSql & "INSERT INTO [Products] (ProductName) " ''You were missing
a space between [Products] and (ProductName)
SrtSql = StrSql & "VALUES ("
SrtSql = StrSql & MyProductName ''No quotes here, JUST the variable name.
SrtSql = StrSql & ");"

Debug.Print StrSql
''Open the Debug (Immediate) Window
''Copy the resulting SQL string
''Create a new query based on the Products Table
''Switch to SQL View, and paste in the SQL string (copied from Debug Window)
''Attempt to switch to Datasheet view.
'' If there are any errors, Access will tell you and will even highlight the
offending portion

MyDB.Execute StrSql, dbFailOnEror

Set MyDB = Nothing
....
*************************************************

--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I''m an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================
"ColinWard" <je*********@hotmail.com> wrote in message
news:40**********************@news.newsgroups.ws.. .

Hi. I am trying to run the following code when the user clicks a button,
but I am getting a syntax error in the SQL. I have a feeling it has to
do with brackets. Can anyone help?
here is the SQL(watch for word wrap:
StrSql = "insert into[Products](ProductName)" ''SQL string which executes
with the RunSQL statement
16980 StrSql = StrSql & "Values (''" & TxtProductName.Text &
"'');"

thanks guys

Colin

*** Sent via Devdex http://www.devdex.com ***
Don''t just participate in USENET...get rewarded for it!

Thanks for your help don. I will try this on Monday. Of course you know
that this means that I will have to change all my runSQL statements
arrgh!!! LOL

*** Sent via Devdex http://www.devdex.com ***
Don''t just participate in USENET...get rewarded for it!

Hi Don,

This is what I currently have but I get a ''Mising Operator'' error with
the last part of the SQL statement.

************************************************** ******
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim strSql As String
Dim MyProductName As String
MyProductName = Me![TxtProductName]
strSql = ""
strSql = strSql & "INSERT INTO [Products] (ProductName) "
strSql = strSql & "VALUES("
strSql = strSql & MyProductName ''No quotes here, JUST the variable name.
strSql = strSql & ");"

Debug.Print strSql
''Open the Debug (Immediate) Window
''Copy the resulting SQL string
''Create a new query based on the Products Table
''Switch to SQL View, and paste in the SQL string (copied from Debug
Window)
''Attempt to switch to Datasheet view.
'' If there are any errors, Access will tell you and will even highlight
the
''offending portion

MyDB.Execute strSql, dbFailOnError
Set MyDB = Nothing

*********************************************

thanks Don

Colin
*** Sent via Devdex http://www.devdex.com ***
Don''t just participate in USENET...get rewarded for it!

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