在SQL中串联的字符串变量(用于访问DB)，由于引号引起的语法错误? [英] string variables concatenated in SQL (to access DB), Syntax errors due to quotations?

问题描述

我有2个列表框，其中定义了2个变量，这些变量需要通过SQL语句传递给OpenRecordSet.

输送机ID 是访问表名称
宽度是表Conveyor_ID
中的字段名称 Width_Row 是一个字符串值，其值为字段宽度"

Dim db As DAO.Database
Dim rs As DAO.Recordset
Const DbLoc As String = "DB location info"


Dim Conveyor_ID As String  ' Conveyor ID Table
Dim Width_Row As String    ' Width row, string values such as 12" or 12in

Set rs = db.OpenRecordset("SELECT * FROM " & [Conveyor_ID] & " Where Width = " & [Width_Row])

根据为Width_row选择哪个值，我将得到两个不同的错误.

如果选择了"14in"，则 错误在查询表达式'Width = 14in'中不带()的运算符中"

如果选择了"14"， 错误查询表达式'Width = 12中字符串的语法错误".

我相信我不理解将变量添加到传递给Openrecordset方法的SQL字符串中的语法.

解决方案

问题是您要传递不带引号的字符串，并将其与SQL串联.这将导致无效的SQL.

这意味着您的最终字符串可能类似于:

SELECT * FROM someTable Where Width = 12in

那不是有效的SQL. SQL中的字符串需要用单引号或双引号引起来.

有多种方法可以解决此问题.正确的方法是使用参数.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef
Const DbLoc As String = "DB location info"


Dim Conveyor_ID As String  ' Conveyor ID Table
Dim Width_Row As String    ' Width row, string values such as 12" or 12in
Set db = CurrentDb 'You were missing this in your sample
Set qd = db.CreateQueryDef("", "SELECT * FROM " & [Conveyor_ID] & " Where Width = @theWidth")
qd.Parameters("@theWidth") = [Width_Row]
Set rs = qd.OpenRecordset

I have 2 list boxes defining 2 variables that need to be passed to OpenRecordSet via a SQL statement.

Conveyor_ID is a access table name
Width is a field name in the table Conveyor_ID
Width_Row is a string value that values in the Field Width

Dim db As DAO.Database
Dim rs As DAO.Recordset
Const DbLoc As String = "DB location info"


Dim Conveyor_ID As String  ' Conveyor ID Table
Dim Width_Row As String    ' Width row, string values such as 12" or 12in

Set rs = db.OpenRecordset("SELECT * FROM " & [Conveyor_ID] & " Where Width = " & [Width_Row])

Depending on which value is selected for the Width_row, I will get two different errors.

If ' 14in ' is selected, error of " In operator without () in query expression 'Width = 14in' "

If ' 14" ' is selected, error of " Syntax error in string in query expression 'Width = 12"'.

I believe I don't understand the syntax of adding variables to a SQL string that is getting passed onto a Openrecordset method.

解决方案

The problem is you're passing the string without any quotation marks, and concatenating it with the SQL. This results in invalid SQL.

This means your final string might look something like:

SELECT * FROM someTable Where Width = 12in

That's not valid SQL. Strings in SQL need to be surrounded in single or double quotes.

There are multiple ways to go about this. The proper way is to use parameters.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef
Const DbLoc As String = "DB location info"


Dim Conveyor_ID As String  ' Conveyor ID Table
Dim Width_Row As String    ' Width row, string values such as 12" or 12in
Set db = CurrentDb 'You were missing this in your sample
Set qd = db.CreateQueryDef("", "SELECT * FROM " & [Conveyor_ID] & " Where Width = @theWidth")
qd.Parameters("@theWidth") = [Width_Row]
Set rs = qd.OpenRecordset

这篇关于在SQL中串联的字符串变量(用于访问DB)，由于引号引起的语法错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！