vba 's' 附近的语法不正确。字符串后的非封闭引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25573541/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Incorrect syntax near 's'. Unclosed quotation mark after the character string
提问by user3991041
I'm using a query to pull data from an SQL database, at times the last dropdown im using to get the record i'm looking for has a single quote, when it does I get the following error: Incorrect syntax near 's'. Unclosed quotation mark after the character string
我正在使用查询从 SQL 数据库中提取数据，有时我用来获取我正在查找的记录的最后一个下拉列表有一个单引号，当我收到以下错误时：'s' 附近的语法不正确. 字符串后的非封闭引号
This is the code I have:
这是我的代码：
Using objcommand As New SqlCommand("", G3SqlConnection)
Dim DS01 As String = DDLDS01.SelectedItem.Text
Dim State As String = DDLState.SelectedItem.Text
Dim Council As String = DDLCouncil.SelectedItem.Text
Dim Local As String = DDLLocal.SelectedItem.Text
Dim objParam As SqlParameter
Dim objDataReader As SqlDataReader
Dim strSelect As String = "SELECT * " & _
"FROM ConstitutionsDAT " & _
"WHERE DS01 = '" & DS01 & "' AND STATE = '" & State & "' AND COUNCIL = '" & Council & "' AND LOCAL = '" & Local & "' AND JURISDICTION = '" & DDLJurisdiction.SelectedItem.Text & "' "
strSelect.ToString.Replace("'", "''")
objcommand.CommandType = CommandType.Text
objcommand.CommandText = strSelect
Try
objDataReader = objcommand.ExecuteReader
DDLJurisdiction.Items.Add("")
While objDataReader.Read()
If Not IsDBNull(objDataReader("SUBUNIT")) Then
txtSubUnit.Text = (objDataReader("SUBUNIT"))
End If
If Not IsDBNull(objDataReader("DS02")) Then
lblDS02.Text = (objDataReader("DS02"))
End If
If Not IsDBNull(objDataReader("LEGISLATIVE_DISTRICT")) Then
txtALD.Text = (objDataReader("LEGISLATIVE_DISTRICT"))
End If
If Not IsDBNull(objDataReader("REGION")) Then
txtRegion.Text = (objDataReader("REGION"))
End If
If DDLState.SelectedItem.Text <> "OTHER" Then
If Not IsDBNull(objDataReader("UNIT_CODE")) Then
txtUnitCode.Text = (objDataReader("UNIT_CODE"))
End If
End If
End While
objDataReader.Close()
Catch objError As Exception
OutError.Text = "Error: " & objError.Message & objError.Source
Exit Sub
End Try
End Using
Not all records contain a single quote, only some, so i'd need something that would work if a single quote is present or not. Thanks.
并非所有记录都包含单引号，只有一些记录，所以如果单引号存在与否，我需要一些可以工作的东西。谢谢。
回答by Michael Dunlap
Your problem is this line here:
你的问题是这里的这一行：
strSelect.ToString.Replace("'", "''")
This is changing your WHERE clause from something like
这正在改变您的 WHERE 子句，例如
WHERE DS01 = 'asdf' AND ...
To:
到：
WHERE DS01 = ''asdf'' AND ...
You need to do the replace on the individual values in the where clause, not on the whole select statement.
您需要对 where 子句中的各个值进行替换，而不是对整个 select 语句进行替换。
What you should really be doing is using a parameterized queryinstead.
您真正应该做的是使用参数化查询。
Update: added same link as aquinas because it's a good link
更新：添加与阿奎那相同的链接，因为它是一个很好的链接
回答by aquinas
Use parameterized queries, and only EVER use parameterized queries. See: How do I create a parameterized SQL query? Why Should I?
使用参数化查询，并且永远只使用参数化查询。请参阅：如何创建参数化 SQL 查询？我为什么要？
