SQL语法：如何使用双引号插入srting？ [英] SQL syntax: How to insert srting with double quotes?

问题描述

我正在尝试记录错误消息，有时候（不知道何时何地）

消息包含带双引号的字符串。有没有办法获得

查询插入带双引号的字符串？我是否需要使用代码来支付
擦除每个字符串并在

a查询中使用之前删除或转义双引号？


我得到的错误是：


错误号码3075：查询表达式中的语法错误（缺少运算符）

''"信用卡收费; SureFire"'''。

SQL看起来像这样：


strSql =" INSERT INTO ErrorLog（[ErrMsg]， [ErrFrm]，"& _

" [ErrPrc]）VALUES（''"& msg&"''，''"& frm&" ''，''"& prc&"''）;"


提前致谢。

I''m trying to log error messages and sometimes (no telling when or where)
the message contains a string with double quotes. Is there a way get the
query to insert the string with the double quotes? Do I need to use code to
scrub each string and remove or escape the double quotes before using it in
a query?

The error I get is this:

Error Number 3075: Syntax error (missing operator) in query expression
''"credit card billed by "SureFire""''.

The SQL looks like this:

strSql = "INSERT INTO ErrorLog ( [ErrMsg], [ErrFrm], " & _
"[ErrPrc] ) VALUES (''" & msg & "'', ''" & frm & "'', ''" & prc & "'');"

Thanks in advance.

推荐答案

在我看来，你试图引用部分消息已经

，当你收到一条被引用的消息时，它会混淆你的陈述。

如果没有必要在日志文件中包含这些引号，我会删除

编码的qoutes。


如果你需要在日志文件中保留引号，那么最简单的就是检查qoutes中的qoutes在放入

SQL语句之前的Err.msg。


它应该只需要几行代码来检查和清理。

Looks to me like you''re trying to quote parts of the message already
and when you get a message that is quoted it messes with you statement.
If it''s not necessary to have these quotes in the log file, I''d remove
the coded qoutes.

If you need to keep the quotes in the log file, then it would be
easiest to check for the qoutes in the Err.msg prior to placing in the
SQL statement.

It should only take a couple of lines of code to check and clean.

" deko" <德** @ nospam.com>在消息中写道

新闻：RY ************** @ newssvr27.news.prodigy.net ..。

"deko" <de**@nospam.com> wrote in message
news:RY**************@newssvr27.news.prodigy.net.. .

我''我试图记录错误消息，有时（不知道何时何地）
消息包含带双引号的字符串。有没有办法让
查询插入带双引号的字符串？在查询中使用
之前，我是否需要使用代码来擦除每个字符串并删除或转义双引号？

我得到的错误是：
错误编号3075：查询表达式中的语法错误（缺少运算符）
''"信用卡由SureFire"''收费。

SQL看起来像这样：

strSql =" INSERT INTO ErrorLog（[ErrMsg]，[ErrFrm]，& _
" [ErrPrc]）VALUES（''"& msg&"''，''"& frm&"''，''"& prc&"''）;"

提前致谢。

I''m trying to log error messages and sometimes (no telling when or where)
the message contains a string with double quotes. Is there a way get the
query to insert the string with the double quotes? Do I need to use code
to scrub each string and remove or escape the double quotes before using
it in a query?

The error I get is this:

Error Number 3075: Syntax error (missing operator) in query expression
''"credit card billed by "SureFire""''.

The SQL looks like this:

strSql = "INSERT INTO ErrorLog ( [ErrMsg], [ErrFrm], " & _
"[ErrPrc] ) VALUES (''" & msg & "'', ''" & frm & "'', ''" & prc & "'');"

Thanks in advance.

是的，你需要代码 - 而且你总是需要检查。我通过

处理这个问题，将下面的代码放在一个模块中，然后你几乎可以忘记 -

除了确保你写的：


strSQL =" INSERT INTO ..."

strSQL = strSQL& .....

strSQL = strSQL& AddQuotes（strValue1，DoubleQuote）

strSQL = strSQL& AddQuotes（strValue2，DoubleQuote）

strSQL = strSQL& .....>
选项比较数据库

选项明确


Public Enum QuoteType

NoQuote

SingleQuote

DoubleQuote

结束枚举

公共函数AddQuotes（strValue As String，Q As QuoteType）As String


Dim strReturn As String


Select Case Q


案例QuoteType.SingleQuote

strReturn =替换（strValue，"''"，"''''"）

strReturn ="''" &安培; strReturn& "''"


案例QuoteType.DoubleQuote

strReturn =替换（strValue，""""，""" ;"""）

strReturn ="""" &安培; strReturn& """"


Case Else

strReturn = strValue


结束选择

AddQuotes = strReturn


结束功能

Yes you do need code - and you always need to check. I handle this by
putting the code below in a module, then you can almost forget about -
except for making sure you write:

strSQL = "INSERT INTO ..."
strSQL = strSQL & .....
strSQL = strSQL & AddQuotes(strValue1, DoubleQuote)
strSQL = strSQL & AddQuotes(strValue2, DoubleQuote)
strSQL = strSQL & .....
Option Compare Database
Option Explicit

Public Enum QuoteType
NoQuote
SingleQuote
DoubleQuote
End Enum
Public Function AddQuotes(strValue As String, Q As QuoteType) As String

Dim strReturn As String

Select Case Q

Case QuoteType.SingleQuote
strReturn = Replace(strValue, "''", "''''")
strReturn = "''" & strReturn & "''"

Case QuoteType.DoubleQuote
strReturn = Replace(strValue, """", """""")
strReturn = """" & strReturn & """"

Case Else
strReturn = strValue

End Select

AddQuotes = strReturn

End Function

>是的，你确实需要代码 - 而且你总是需要检查。我通过

> Yes you do need code - and you always need to check. I handle this by

把这个代码放在一个模块中，然后你几乎可以忘记 -
除了确保你写的：


很有趣，但无法让它工作。


当抛出错误时，我将它放入如下字符串：

INSERT INTO ErrorLog（[ ErrMsg]，[ErrFrm]，[ErrPrc]）VALUES（'''错误号

3075：查询表达式中的语法错误（缺少运算符）''"" this""""" ;"

有引号""''。"''，''modImex''，''InsertData''）;


< strSQL =" INSERT INTO ..."
strSQL = strSQL& .....
strSQL = strSQL& AddQuotes（strValue1，DoubleQuote）
strSQL = strSQL& AddQuotes（strValue2，DoubleQuote）
strSQL = strSQL& ....

选项比较数据库
明确选项

Public Enum QuoteType
NoQuote
SingleQuote
DoubleQuote
公共函数AddQuotes（strValue As String，Q As QuoteType）As String

Dim strReturn As String

Select Case Q <案例QuoteType.SingleQuote
strReturn =替换（strValue，"''"，"''''"）
strReturn ="''" &安培; strReturn& "''"

案例QuoteType.DoubleQuote
strReturn =替换（strValue，""""，""""""）
strReturn ="""" &安培; strReturn& """"

Case Else
strReturn = strValue

结束选择

AddQuotes = strReturn

结束功能

putting the code below in a module, then you can almost forget about -
except for making sure you write:
Interesting, but having trouble getting it to work.

When an error is thrown, I put it into a string like this:
INSERT INTO ErrorLog ( [ErrMsg], [ErrFrm], [ErrPrc] ) VALUES (''"Error Number
3075: Syntax error (missing operator) in query expression ''""this ""string""
has quotes""''."'', ''modImex'', ''InsertData'');





strSQL = "INSERT INTO ..."
strSQL = strSQL & .....
strSQL = strSQL & AddQuotes(strValue1, DoubleQuote)
strSQL = strSQL & AddQuotes(strValue2, DoubleQuote)
strSQL = strSQL & .....
Option Compare Database
Option Explicit

Public Enum QuoteType
NoQuote
SingleQuote
DoubleQuote
End Enum
Public Function AddQuotes(strValue As String, Q As QuoteType) As String

Dim strReturn As String

Select Case Q

Case QuoteType.SingleQuote
strReturn = Replace(strValue, "''", "''''")
strReturn = "''" & strReturn & "''"

Case QuoteType.DoubleQuote
strReturn = Replace(strValue, """", """""")
strReturn = """" & strReturn & """"

Case Else
strReturn = strValue

End Select

AddQuotes = strReturn

End Function

这篇关于SQL语法：如何使用双引号插入srting？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！