SQL syntax: How to insert srting with double quotes?

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.

re: SQL syntax: How to insert srting with double quotes?

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.

re: SQL syntax: How to insert srting with double quotes?

"deko" <deko@nospam.com> wrote in message
news:RYDMe.145$L03.94@newssvr27.news.prodigy.net.. .[color=blue]
> 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.[/color]




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

re: SQL syntax: How to insert srting with double quotes?

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

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');














[color=blue]
>
> 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
>[/color]

re: SQL syntax: How to insert srting with double quotes?

Ignore previous post... hit send by mistake..
[color=blue]
> 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:[/color]

Interesting, but having trouble getting it to work.

When an error is thrown, I put it into a sting like this:

strMsg = "Error Number " & Err.Number & ": " & Err.Description

Then I build an SQL statement to append strMsg into a table:

INSERT INTO... VALUES ( '" & strMsg & "' )'

So I tried:

msg = AddQuotes(msg, DoubleQuote)
msg = AddQuotes(msg, NoQuote)
etc.

The append query still fails because of the quotes. Am I doing something
wrong? The NoQuote option does not seem to do anything.

[color=blue]
> 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[/color]

re: SQL syntax: How to insert srting with double quotes?

Why are the strings 'modImex' and 'InsertData' in single quotes? Did you
want to surround the error message in single quotes too?
The function allows you to simply add any variable to the sql string without
enclosing it in quotes or worrying about whether the variable itself
contains quotes.
E.g. a single table with a single field is tblThing.ThingName

strThing="Pete ""The Punk"" Smith's Bicycle"

strSQL = "INSERT INTO tblThing ( ThingName ) "
strSQL = strSQL & "VALUES ( "
strSQL = strSQL & AddQuotes(strThing, DoubleQuote)
strSQL = strSQL & " );"




"deko" <deko@nospam.com> wrote in message
news:25HMe.158$zD3.63@newssvr29.news.prodigy.net.. .[color=blue][color=green]
>> 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:[/color]
>
> 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');
>[color=green]
>>
>> 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
>>[/color]
>
>[/color]

re: SQL syntax: How to insert srting with double quotes?

On Wed, 17 Aug 2005 13:38:06 GMT, "deko" <deko@nospam.com> wrote:
[color=blue][color=green]
>> 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:[/color]
>
>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');[/color]

OK, 3 things.

1. You can use either double-qoute characters or single-quote charcters as
string delimiters in SQL. If you use single-quotes, then the string can
contain double-quotes without doing anything special, and vice verse.

2. Whichever quote character you use, if your string value contains that same
kind of quote, you have to double it. When the text already contains pairs of
the same kind of quote mark, doble them all: ''' becomes ''''''. These are
all valid...

'O''Connor'
"O'Connor"
'3" long'
"3"" long"

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

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

3. When executing SQL from code, and inserting values that are not hard-coded
in your program (or even some that are), this whole technique is very error
prone, so don't do that. Use recordsets or querydef parameters instead. Note
that you can't insert more than 255 characters into a Memo field with a
querydef, so sometimes the recordset is simply the best way to go.

Here's an example using querydef parameters...

Dim dbs As DAO.Database
Dim qdf As DAO.Querydef

Set dbs = CurrentDb()
Set qdf = dbs.CreateQuerydef("")
qdf.SQL = "INSERT INTO ErrorLog ( ErrMsg, ErrFrm, ErrPrc ) " & _
"VALUES (prm_ErrMsg, prm_ErrFrm, prm_ErrPrc);

'Find out now if we have any bad field name typos, etc., not later
'when a cryptic error message comes up.
Debug.Assert qdf.Paramaters.Count = 3

qdf!prm_ErrMsg = ...
qdf!prm_ErrFrm = ...
qdf!prm_ErrPrc = ...
qdf.Execute dbFailOnError

Set qdf = nothing
Set dbs = Nothing

re: SQL syntax: How to insert srting with double quotes?

I did respond to that post which might help. But if it isn't clear already,
the AddQuotes function does two things: it doubles up any quotes inside the
string itself and then surrounds the whole thing in quotes itself.
Your mistake seems to be adding extra ones, you just need

strSQL = strSQL & "VALUES ( " & AddQuotes(strError,DoubleQuote) & " )"

Whether you use single or double quotes could be affected by a number of
things. With 'normal Access stuff', it is double.


"deko" <deko@nospam.com> wrote in message
news:nfHMe.176$L03.78@newssvr27.news.prodigy.net.. .[color=blue]
> Ignore previous post... hit send by mistake..
>[color=green]
>> 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:[/color]
>
> Interesting, but having trouble getting it to work.
>
> When an error is thrown, I put it into a sting like this:
>
> strMsg = "Error Number " & Err.Number & ": " & Err.Description
>
> Then I build an SQL statement to append strMsg into a table:
>
> INSERT INTO... VALUES ( '" & strMsg & "' )'
>
> So I tried:
>
> msg = AddQuotes(msg, DoubleQuote)
> msg = AddQuotes(msg, NoQuote)
> etc.
>
> The append query still fails because of the quotes. Am I doing something
> wrong? The NoQuote option does not seem to do anything.
>
>[color=green]
>> 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[/color]
>
>[/color]

re: SQL syntax: How to insert srting with double quotes?

>I did respond to that post which might help. But if it isn't clear[color=blue]
>already, the AddQuotes function does two things: it doubles up any quotes
>inside the string itself and then surrounds the whole thing in quotes
>itself.
> Your mistake seems to be adding extra ones, you just need
>
> strSQL = strSQL & "VALUES ( " & AddQuotes(strError,DoubleQuote) & " )"
>
> Whether you use single or double quotes could be affected by a number of
> things. With 'normal Access stuff', it is double.[/color]

Thanks for the reply.

I think I am beginning to understand... It seems the ENTIRE string needs to
be fixed up, not just one part.

So what I did here:

INSERT INTO ErrorLog ( [ErrMsg], [ErrFrm], [ErrPrc] ) VALUES ('"Error Number
3075: Syntax error (missing operator) in query expression '""this ""string""
has quotes""'."', 'modImex', 'InsertData');

was try to just fix the ErrMsg part.

I will play around with this and post back... got to attend to some other
things right now...

cheers

re: SQL syntax: How to insert srting with double quotes?

On Wed, 17 Aug 2005 07:12:35 -0700, Steve Jorgensen <nospam@nospam.nospam>
wrote:

....[color=blue]
>2. Whichever quote character you use, if your string value contains that same
>kind of quote, you have to double it. When the text already contains pairs of
>the same kind of quote mark, doble them all: ''' becomes ''''''. These are
>all valid...
>
>'O''Connor'
>"O'Connor"
>'3" long'
>"3"" long"
>
>"Error Number 3075: Syntax error (missing operator) in query expression
>'""credit card billed by ""SureFire""""'."
>
>'Error Number 3075: Syntax error (missing operator) in query expression
>''"credit card billed by "SureFire""''.'
>[/color]
....

I realized after I posted this that it fails to mention one more source of
confusion - VBA. All you need to know is shown above assuming you already
have a value in a string, and use some function to properly escape it. If
you're typing it in as a constant in VB or VBA code, though, you have to
remember that any double-quote characters must be doubled yet again.

Print "a""a"
a"a

So, that gives us the following set of examples...

a = "'O''Connor'"
a = """O'Connor"""
a = "'3"" long'"
a = """3"""""" long"""

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

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

As you can see, when you need to type these into VB code, it's best to assume
the SQL constant will be surrounded with single quote characters, so the
double-quotes don't get totally ludicrous.

re: SQL syntax: How to insert srting with double quotes?

> 1. You can use either double-quote characters or single-quote characters[color=blue]
> as
> string delimiters in SQL. If you use single-quotes, then the string can
> contain double-quotes without doing anything special, and vice verse.
>
> 2. Whichever quote character you use, if your string value contains that
> same
> kind of quote, you have to double it. When the text already contains
> pairs of
> the same kind of quote mark, double them all: ''' becomes ''''''. These
> are
> all valid...[/color]

Thanks for the detailed info.

But I never know what the string is going to contain.

Here are some examples (the brackets are to show start and end):

['this string has "quotes"']
[this 'string' has quotes]
[this '"string has quotes"']
[this "string' has quotes]
["this 'string' "has" quotes"]

How do I write a universal QuoteFix function?

One suggestion is to use the Replace function to double whatever quotes are
found in the string.

Borrowing from Mr. Hoffman, it might look like this:

Public Function QuoteFix(strMyString) As String
Dim strFix as String
'double single-quotes
strFix = Replace(strMyString, "'", "''")
'double double-quotes
strFix = Replace(strFix, """, """")
QuoteFix = strFix
End Function

This, I think, will resolve my problem of logging error messages that
contain quotes - but how to use it in my logging function?

Here is the function I use to log errors, which is called from the error
handler in other functions:

Public Sub LogErr(frm As String, prc As String, _
Optional logOnly As Boolean)
Dim msg As String
Dim frmPrc As String
Dim strSql As String
msg = "Error Number " & Err.Number & ": " & Err.Description
frmPrc = vbCrLf & vbCrLf & frm & "." & prc
If Not logOnly Then MsgBox msg & frmPrc, vbCritical, _
" Unexpected Error"
On Error GoTo Exit_Here 'can't use Resume Next here
strSql = "INSERT INTO ErrorLog " & _
"( [ErrMsg], [ErrFrm], [ErrPrc] " & _
") VALUES ('" & msg & "', '" & frm & "', '" & prc & "');"
'====>> call QuoteFix here??? strSql = QuoteFix(strSql)
'don't want to set any objects so use DoCmd.RunSql
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
Exit_Here:
End Sub

re: SQL syntax: How to insert srting with double quotes?

> Your mistake seems to be adding extra ones, you just need[color=blue]
>
> strSQL = strSQL & "VALUES ( " & AddQuotes(strError,DoubleQuote) & " )"[/color]

I tried this:

Public Sub LogErr(frm As String, prc As String, _
Optional logOnly As Boolean)
Dim msg As String
Dim frmPrc As String
Dim strSql As String
msg = "Error Number " & Err.Number & ": " & Err.Description
frmPrc = vbCrLf & vbCrLf & frm & "." & prc
If Not logOnly Then MsgBox msg & frmPrc, vbCritical, _
" Unexpected Error"
msg = msg & ", " & frm & ", " & prc"
On Error GoTo Exit_Here 'can't use Resume Next here
strSql = "INSERT INTO ErrorLog " & _
"( [ErrMsg], [ErrFrm], [ErrPrc] " & _
") VALUES ( " & AddQuotes(msg, DoubleQuote) & " );"
Debug.Print strSql
'don't want to set any objects so use DoCmd.RunSql
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
Exit_Here:
End Sub

The output of Debug.Print strSql is:

INSERT INTO ErrorLog ( [ErrMsg], [ErrFrm], [ErrPrc] ) VALUES ( "Error Number
3075: Syntax error (missing operator) in query expression '""Engineering
""Consultant""""'., modImex, InsertData" );

This SQL statement fails due to a syntax error.

The quoted value (that threw the original error) is:

Engineering "Consultant"

The error message (msg) looks like this:

Error Number 3075: Syntax error (missing operator) in query expression
'"Engineering "Consultant""'.

Why is AddQuotes not working? Am I still missing something?

Thanks again for the help!

re: SQL syntax: How to insert srting with double quotes?

On Wed, 17 Aug 2005 23:46:47 GMT, "deko" <deko@nospam.com> wrote:
[color=blue][color=green]
>> 1. You can use either double-quote characters or single-quote characters
>> as
>> string delimiters in SQL. If you use single-quotes, then the string can
>> contain double-quotes without doing anything special, and vice verse.
>>
>> 2. Whichever quote character you use, if your string value contains that
>> same
>> kind of quote, you have to double it. When the text already contains
>> pairs of
>> the same kind of quote mark, double them all: ''' becomes ''''''. These
>> are
>> all valid...[/color]
>
>Thanks for the detailed info.
>
>But I never know what the string is going to contain.[/color]

Well, then most of what I said will not apply except when you are manually
hard-coding test data.
[color=blue]
>Here are some examples (the brackets are to show start and end):
>
>['this string has "quotes"']
>[this 'string' has quotes]
>[this '"string has quotes"']
>[this "string' has quotes]
>["this 'string' "has" quotes"]
>
>How do I write a universal QuoteFix function?
>
>One suggestion is to use the Replace function to double whatever quotes are
>found in the string.[/color]

That's pretty much all you have to do. I pretty much just say something like
"'" & Replace(strValue,"'","''") & "'". Of course, I still recommend using
parameters instead.
[color=blue]
>Borrowing from Mr. Hoffman, it might look like this:
>
>Public Function QuoteFix(strMyString) As String
> Dim strFix as String
> 'double single-quotes
> strFix = Replace(strMyString, "'", "''")
> 'double double-quotes
> strFix = Replace(strFix, """, """")
> QuoteFix = strFix
>End Function[/color]

That function is wrong. You only want to double the kind of quote mark that
you use to surround the string. The other quotes should not be doubled.
there is no case where you should double both double-quotes and single-quotes
in a single expression. Of course, that function is also wrong because a VB
expression for one double-qoute is """", and the expression for a pair of
double-quotes is """""". The first and last quotes surround the string, and
each quote mark to be contained in the string must be represented by a pair of
double-quotes. 3 double-quote characters in a row is an unclosed string
expression.
[color=blue]
>This, I think, will resolve my problem of logging error messages that
>contain quotes - but how to use it in my logging function?
>
>Here is the function I use to log errors, which is called from the error
>handler in other functions:
>
>Public Sub LogErr(frm As String, prc As String, _
> Optional logOnly As Boolean)
> Dim msg As String
> Dim frmPrc As String
> Dim strSql As String
> msg = "Error Number " & Err.Number & ": " & Err.Description
> frmPrc = vbCrLf & vbCrLf & frm & "." & prc
> If Not logOnly Then MsgBox msg & frmPrc, vbCritical, _
> " Unexpected Error"
> On Error GoTo Exit_Here 'can't use Resume Next here
> strSql = "INSERT INTO ErrorLog " & _
> "( [ErrMsg], [ErrFrm], [ErrPrc] " & _
> ") VALUES ('" & msg & "', '" & frm & "', '" & prc & "');"
> '====>> call QuoteFix here??? strSql = QuoteFix(strSql)
> 'don't want to set any objects so use DoCmd.RunSql
> DoCmd.SetWarnings False
> DoCmd.RunSQL strSql
> DoCmd.SetWarnings True
>Exit_Here:
>End Sub
>[/color]

Ah - I see the problem. Once you've built the SQL string, it's too late to
use a quote fix function because you've already lost any informatino about
what quotes are delimiters, and what qutoes are content. You need to fix up
each argument before you put it into the SQL string.

First, I don't know why you don't want to use DAO objects, but given that you
don't, just do something like the following (noting that I'm following your
variable naming, though it's not what I'd recommend):

Public Sub LogErr( _
frm As String, _
prc As String, _
Optional logOnly As Boolean = False _
)
Dim msg As String
Dim frmPrc As String
Dim strSql As String

msg = "Error Number " & Err.Number & ": " & _
Err.Description

frmPrc = vbCrLf & vbCrLf & frm & "." & prc

If Not logOnly Then
MsgBox msg & frmPrc, vbCritical, _
" Unexpected Error"

On Error GoTo Exit_Here 'can't use Resume Next here
'and should never do so for
'more than a single statement
'anyway.

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

DoCmd.SetWarnings False
DoCmd.RunSQL strSql

Exit_Here:
'Turn warnings back on always, not just if no
'error writing to the log.
DoCmd.SetWarnings True

End Sub


Public Function SQLStringExpr(StringValue As Variant) As String

If IsNull(StringValue) then

'Handle Null values in case we ever want to
'generate Null value parameters
SQLStringExpr = "Null"

Else
SQLStringExpr = "'" & _
Replace(StringValue, "'", "''") & _
"'"
Endif

End Function

re: SQL syntax: How to insert srting with double quotes?

> Ah - I see the problem. Once you've built the SQL string, it's too late[color=blue]
> to
> use a quote fix function because you've already lost any information about
> what quotes are delimiters, and what quotes are content. You need to fix
> up
> each argument before you put it into the SQL string.[/color]

So, when I have 3 substrings that I concatenate to create a new string, I
need to first fix each of the 3 substrings before concatenation? And
"fixing" the substring involves doubling *only* the outer quotes -
regardless if they are single or double? Is this correct?
[color=blue]
> Of course, I still recommend using parameters instead.[/color]

I will explore this. But even with parameters, I will still need to fix the
substrings, correct?
[color=blue]
> First, I don't know why you don't want to use DAO objects, but given that
> you
> don't, just do something like the following (noting that I'm following
> your
> variable naming, though it's not what I'd recommend):[/color]

That function is part of an error handler, so I want to keep it as simple as
possible. But I suppose I could stick with Hungarian naming convention.

re: SQL syntax: How to insert srting with double quotes?

On Thu, 18 Aug 2005 03:49:03 GMT, "deko" <deko@nospam.com> wrote:
[color=blue][color=green]
>> Ah - I see the problem. Once you've built the SQL string, it's too late
>> to
>> use a quote fix function because you've already lost any information about
>> what quotes are delimiters, and what quotes are content. You need to fix
>> up
>> each argument before you put it into the SQL string.[/color]
>
>So, when I have 3 substrings that I concatenate to create a new string, I
>need to first fix each of the 3 substrings before concatenation? And
>"fixing" the substring involves doubling *only* the outer quotes -
>regardless if they are single or double? Is this correct?[/color]

I think you've got it, but just to be precise - you double only the quotes in
the expression that are of the same type as the quotes that surround the
expression.
[color=blue][color=green]
>> Of course, I still recommend using parameters instead.[/color]
>
>I will explore this. But even with parameters, I will still need to fix the
>substrings, correct?[/color]

No - that is not correct. The point of the database APIs is to abstract away
how things are done at a lower level. How strings are encoded to pass them to
an SQL parser is one of those low-level issues that query parameters are
designed to abstract away and handle for you.
[color=blue][color=green]
>> First, I don't know why you don't want to use DAO objects, but given that
>> you
>> don't, just do something like the following (noting that I'm following
>> your
>> variable naming, though it's not what I'd recommend):[/color]
>
>That function is part of an error handler, so I want to keep it as simple as
>possible. But I suppose I could stick with Hungarian naming convention.[/color]

I'm not saying one should always go with hungarian notation, in fact, I've
decided to never use it for parameter names anymore. I would say, though,
that in VB code, if you don't use hungarian, you should capitalize the first
letters of names so it's clear that any abbreviations are -not- Hungarian
prefixes. I also wouldn't abbreviate the terms in the names as much as you
are doing. After all, it's more clear with full words than abbreviations,
since clarity is our goal.

re: SQL syntax: How to insert srting with double quotes?

>>So, when I have 3 substrings that I concatenate to create a new string, I[color=blue][color=green]
>>need to first fix each of the 3 substrings before concatenation? And
>>"fixing" the substring involves doubling *only* the outer quotes -
>>regardless if they are single or double? Is this correct?[/color]
>
> I think you've got it, but just to be precise - you double only the quotes
> in
> the expression that are of the same type as the quotes that surround the
> expression.[/color]

So I need to double *all* quotes *in* the expression that are the same as
quotes that *surround* the expression - not just the outer quotes. Is
this correct?
[color=blue][color=green]
>>I will explore this. But even with parameters, I will still need to fix
>>the
>>substrings, correct?[/color]
>
> No - that is not correct. The point of the database APIs is to abstract
> away
> how things are done at a lower level. How strings are encoded to pass
> them to
> an SQL parser is one of those low-level issues that query parameters are
> designed to abstract away and handle for you.[/color]

Well then, as you said, it makes sense to use a parameterized query here
since all quoting issues are avoided.

Okay, so my compiled query - qryLogErr - looks like this:

INSERT INTO tblErrorLog ( ErrMsg, ErrFrm, ErrPrc )
VALUES ( [ErrMsg], [ErrFrm], [ErrPrc] );

My revised function looks like this:

Public Sub LogErr(strErrFrm As String, strErrPrc As String, _
Optional blnLogOnly As Boolean)
Dim db As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.QueryDef
Dim strErrMsg As String
Dim strInfo As String
Dim strSql As String
strErrMsg = "Error Number " & Err.Number & ": " & Err.Description
strInfo = vbCrLf & vbCrLf & strErrFrm & "." & strErrPrc
If Not blnLogOnly Then MsgBox strErrMsg & strInfo, vbCritical, _
" Unexpected Error"
On Error GoTo Exit_Here
Set db = CurrentDb
Set qdfs = db.QueryDefs
Set qdf = qdfs("qryLogErr")
qdf.Parameters(0) = strErrMsg
Debug.Print strErrMsg
qdf.Parameters(1) = strErrFrm
Debug.Print strErrFrm
qdf.Parameters(2) = strErrPrc
Debug.Print strErrPrc
qdf.Execute
Exit_Here:
End Sub

qryLogErr appears to be executing successfully (but I've only tried it
once).

Here is the output from Debug.Print:

Error Number 3075: Syntax error (missing operator) in query expression
'"Engineering "Consultant""'.
modImex
InsertData

Bottom line - we don't care at all about the quotes in strErrMsg when using
a parameterized query like this.

re: SQL syntax: How to insert srting with double quotes?

One situation where I'm still having problems with quotes is the query that
generated the error I was trying to log:

strSql = "INSERT INTO " & strInternalTable & " SELECT " & _
strUserFields & " FROM [" & strImportedTable & "];"

strUserFields is a dynamically constructed, comma-separated string of field
names. The purpose of this query is to allow users to insert data from an
imported table.

The error I was trying to log:

Error Number 3075: Syntax error (missing operator) in query expression
'"Engineering "Consultant""'.

was a result of running the above query when one of the fields in the
imported table - strImportedTable - contained the following:

Engineering "Consultant"

Is it possible to prevent quoting problems in this situation? I don't see
how I could use parameters. I suppose I could loop through each row and
field in the imported table and run some kind of QuoteFix function, but that
seems like a lot of overhead.

re: SQL syntax: How to insert srting with double quotes?

On Thu, 18 Aug 2005 05:41:08 GMT, "deko" <deko@nospam.com> wrote:
[color=blue][color=green][color=darkred]
>>>So, when I have 3 substrings that I concatenate to create a new string, I
>>>need to first fix each of the 3 substrings before concatenation? And
>>>"fixing" the substring involves doubling *only* the outer quotes -
>>>regardless if they are single or double? Is this correct?[/color]
>>
>> I think you've got it, but just to be precise - you double only the quotes
>> in
>> the expression that are of the same type as the quotes that surround the
>> expression.[/color]
>
>So I need to double *all* quotes *in* the expression that are the same as
>quotes that *surround* the expression - not just the outer quotes. Is
>this correct?[/color]

Almost. Not "not just the outer quotes", but "not the outer quotes".
[color=blue][color=green][color=darkred]
>>>I will explore this. But even with parameters, I will still need to fix
>>>the
>>>substrings, correct?[/color]
>>
>> No - that is not correct. The point of the database APIs is to abstract
>> away
>> how things are done at a lower level. How strings are encoded to pass
>> them to
>> an SQL parser is one of those low-level issues that query parameters are
>> designed to abstract away and handle for you.[/color]
>
>Well then, as you said, it makes sense to use a parameterized query here
>since all quoting issues are avoided.
>
>Okay, so my compiled query - qryLogErr - looks like this:
>
>INSERT INTO tblErrorLog ( ErrMsg, ErrFrm, ErrPrc )
>VALUES ( [ErrMsg], [ErrFrm], [ErrPrc] );
>
>My revised function looks like this:
>
>Public Sub LogErr(strErrFrm As String, strErrPrc As String, _
> Optional blnLogOnly As Boolean)
> Dim db As DAO.Database
> Dim qdfs As DAO.QueryDefs
> Dim qdf As DAO.QueryDef
> Dim strErrMsg As String
> Dim strInfo As String
> Dim strSql As String
> strErrMsg = "Error Number " & Err.Number & ": " & Err.Description
> strInfo = vbCrLf & vbCrLf & strErrFrm & "." & strErrPrc
> If Not blnLogOnly Then MsgBox strErrMsg & strInfo, vbCritical, _
> " Unexpected Error"
> On Error GoTo Exit_Here
> Set db = CurrentDb
> Set qdfs = db.QueryDefs
> Set qdf = qdfs("qryLogErr")
> qdf.Parameters(0) = strErrMsg
> Debug.Print strErrMsg
> qdf.Parameters(1) = strErrFrm
> Debug.Print strErrFrm
> qdf.Parameters(2) = strErrPrc
> Debug.Print strErrPrc
> qdf.Execute
>Exit_Here:
>End Sub[/color]

It's probably overkill to make a variable for qdfs, and it's more explicit and
reliable to reference the query parameters by name rather than by index
number, but other than that, you're almost there <g>. If you ever want to
trap database errors, you'll have to specify the dbFailOnError option for
qdf.Execute. Also, you'll want to explicitly set qdf = Nothing and db =
Nothing after Exit_Here:
[color=blue]
>
>qryLogErr appears to be executing successfully (but I've only tried it
>once).
>
>Here is the output from Debug.Print:
>
>Error Number 3075: Syntax error (missing operator) in query expression
>'"Engineering "Consultant""'.
>modImex
>InsertData
>
>Bottom line - we don't care at all about the quotes in strErrMsg when using
>a parameterized query like this.
>[/color]

Correct.

re: SQL syntax: How to insert srting with double quotes?

On Thu, 18 Aug 2005 06:08:47 GMT, "deko" <deko@nospam.com> wrote:
[color=blue]
>One situation where I'm still having problems with quotes is the query that
>generated the error I was trying to log:
>
>strSql = "INSERT INTO " & strInternalTable & " SELECT " & _
> strUserFields & " FROM [" & strImportedTable & "];"
>
>strUserFields is a dynamically constructed, comma-separated string of field
>names. The purpose of this query is to allow users to insert data from an
>imported table.
>
>The error I was trying to log:
>
>Error Number 3075: Syntax error (missing operator) in query expression
>'"Engineering "Consultant""'.
>
>was a result of running the above query when one of the fields in the
>imported table - strImportedTable - contained the following:
>
>Engineering "Consultant"
>
>Is it possible to prevent quoting problems in this situation? I don't see
>how I could use parameters. I suppose I could loop through each row and
>field in the imported table and run some kind of QuoteFix function, but that
>seems like a lot of overhead.[/color]

This is actually a different case entirely since we're talking about field
names instead of constant value expressions. What will work for this case is
to arrange for the code that supplies the field name list to surround each
field name with square brackets [], so your field expression would be
[Engineering "Consultant"].

If you happen to be starting with an array, you can use this shortcut to wrap
the names...

varFieldNames = Array("Field1", "Field '2'")
strFieldNameList = "[" & Join(varFieldNames, "],[") & "]"

That only works if there's always at least one field name in the array,
otherwise, it needs a few more lines.

re: SQL syntax: How to insert srting with double quotes?

> strSql = "INSERT INTO " & strInternalTable & " SELECT " & _[color=blue]
> strUserFields & " FROM [" & strImportedTable & "];"[/color]

Correction - the above query does *not* have problems with quotes.

This query does:

strSql = "INSERT INTO tblOrders ( " & strFields & " ) VALUES ( " & strValues
& " );"

I never know how may fields/values I'm going to have.

I played around a bit and got this working:

strSql = "Select " & strFields & ", TempID FROM [" & _
strImportedTable & "] ORDER BY TempID;"
Set qdf = db.CreateQueryDef("")
strQdfSql = "INSERT INTO tblOrders ( " & strFields & " ) " & _
"VALUES (" & strFields & ");"
qdf.SQL = strQdfSql
Set rstSource = db.OpenRecordset(strSql)
astrFld = Split(strFields, ",")
'loop through each row in source table
'and insert into target table
Do While Not rstSource.EOF
i = 0
For Each varFld In astrFld
strFld = Trim(varFld)
qdf.Parameters(i) = rstSource(strFld)
i = i + 1
Next varFld
qdf.Execute dbFailOnError
[code omitted]
Loop

Instead of building a monster, comma-separated strValues, I assign each
value from the recordset to a parameter. This avoids the quote-related
error I had trouble logging (still, I use that LogErr function in the error
handler of every procedure in the project so I'm sure I'll come across an
error with quotes sooner or later - now I will not miss these in the
ErrorLog).

Parameterized queries rock!

Thanks for the help!

re: SQL syntax: How to insert srting with double quotes?

"deko" <deko@nospam.com> wrote in message
news:62RMe.2460$Z%6.2164@newssvr17.news.prodigy.co m...[color=blue][color=green]
>> Your mistake seems to be adding extra ones, you just need
>>
>> strSQL = strSQL & "VALUES ( " & AddQuotes(strError,DoubleQuote) & " )"[/color]
>
> I tried this:
>
> Public Sub LogErr(frm As String, prc As String, _
> Optional logOnly As Boolean)
> Dim msg As String
> Dim frmPrc As String
> Dim strSql As String
> msg = "Error Number " & Err.Number & ": " & Err.Description
> frmPrc = vbCrLf & vbCrLf & frm & "." & prc
> If Not logOnly Then MsgBox msg & frmPrc, vbCritical, _
> " Unexpected Error"
> msg = msg & ", " & frm & ", " & prc"
> On Error GoTo Exit_Here 'can't use Resume Next here
> strSql = "INSERT INTO ErrorLog " & _
> "( [ErrMsg], [ErrFrm], [ErrPrc] " & _
> ") VALUES ( " & AddQuotes(msg, DoubleQuote) & " );"
> Debug.Print strSql
> 'don't want to set any objects so use DoCmd.RunSql
> DoCmd.SetWarnings False
> DoCmd.RunSQL strSql
> DoCmd.SetWarnings True
> Exit_Here:
> End Sub
>
> The output of Debug.Print strSql is:
>
> INSERT INTO ErrorLog ( [ErrMsg], [ErrFrm], [ErrPrc] ) VALUES ( "Error
> Number 3075: Syntax error (missing operator) in query expression
> '""Engineering ""Consultant""""'., modImex, InsertData" );
>
> This SQL statement fails due to a syntax error.
>
> The quoted value (that threw the original error) is:
>
> Engineering "Consultant"
>
> The error message (msg) looks like this:
>
> Error Number 3075: Syntax error (missing operator) in query expression
> '"Engineering "Consultant""'.
>
> Why is AddQuotes not working? Am I still missing something?
>
> Thanks again for the help![/color]



Hi
By the looks of things, you are going down the querydef parameters route
with Steve Jorgensen - which is fine and will work, but just to re-assure
you, the AddQuotes approach is another possibility which really does work.
I looked at your code, but don't understand how you could get the function
to run with the following line
msg = msg & ", " & frm & ", " & prc"
Surely you've got an extra double-quote at the end?
Anyway, if you simply needed a demo of this working, you could let me know
an e-mail address to send it to.

re: SQL syntax: How to insert srting with double quotes?

> Hi[color=blue]
> By the looks of things, you are going down the querydef parameters route
> with Steve Jorgensen - which is fine and will work, but just to re-assure
> you, the AddQuotes approach is another possibility which really does work.
> I looked at your code, but don't understand how you could get the function
> to run with the following line
> msg = msg & ", " & frm & ", " & prc"
> Surely you've got an extra double-quote at the end?
> Anyway, if you simply needed a demo of this working, you could let me know
> an e-mail address to send it to.[/color]

I'm sure it's just my lack of understanding that's the problem. I will no
doubt need that function soon and will try it again.

Actually, I just ran into a problem using query parameters. I am unable to
log this error:

Error Number 3075: Syntax error (missing operator) in query expression
'((tblUtility.Modified BETWEEN #8/15/2004# AND #8/18/2005#) Or
(tblUtility.Created BETWEEN #8/15/2004# AND #8/18/2005#)) WHERE
(qry1000.Cat_ID In (SELECT Cat_ID FROM qrySearchCat) Or qry1000.Cat_ID Is
Null)'.

My guess is the problem is caused by the pound signs (#). I'm wondering if
AddQuotes might help.

The error I get is:

3271 Invalid property value.

Here's the complete logging function:

Public Sub LogErr(strErrFrm As String, strErrPrc As String, _
Optional blnLogOnly As Boolean)
Dim db As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.QueryDef
Dim strErrMsg As String
Dim strInfo As String
Dim strSql As String
strErrMsg = "Error Number " & Err.Number & ": " & Err.Description
strInfo = vbCrLf & vbCrLf & strErrFrm & "." & strErrPrc
If Not blnLogOnly Then MsgBox strErrMsg & strInfo, vbCritical, _
" Unexpected Error"
On Error GoTo Exit_Here
Set db = CurrentDb
Set qdfs = db.QueryDefs
Set qdf = qdfs("qryLogErr")
Debug.Print strErrMsg
qdf.Parameters(0) = strErrMsg
qdf.Parameters(1) = strErrFrm
qdf.Parameters(2) = strErrPrc
qdf.Execute dbFailOnError
Exit_Here:
Set qdf = Nothing
Set qdfs = Nothing
Set db = Nothing
Debug.Print Err.Number & " " & Err.Description
End Sub

Here's the text of qryLogErr:

INSERT INTO tblErrorLog ( ErrMsg, ErrFrm, ErrPrc )
VALUES (prmErrMsg, prmErrFrm, prmErrPrc);

re: SQL syntax: How to insert srting with double quotes?

"deko" <deko@nospam.com> wrote in message
news:WFYMe.3548$hF1.39@newssvr30.news.prodigy.com. ..[color=blue][color=green]
>> Hi
>> By the looks of things, you are going down the querydef parameters route
>> with Steve Jorgensen - which is fine and will work, but just to re-assure
>> you, the AddQuotes approach is another possibility which really does
>> work.
>> I looked at your code, but don't understand how you could get the
>> function to run with the following line
>> msg = msg & ", " & frm & ", " & prc"
>> Surely you've got an extra double-quote at the end?
>> Anyway, if you simply needed a demo of this working, you could let me
>> know an e-mail address to send it to.[/color]
>
> I'm sure it's just my lack of understanding that's the problem. I will no
> doubt need that function soon and will try it again.
>
> Actually, I just ran into a problem using query parameters. I am unable
> to log this error:
>
> Error Number 3075: Syntax error (missing operator) in query expression
> '((tblUtility.Modified BETWEEN #8/15/2004# AND #8/18/2005#) Or
> (tblUtility.Created BETWEEN #8/15/2004# AND #8/18/2005#)) WHERE
> (qry1000.Cat_ID In (SELECT Cat_ID FROM qrySearchCat) Or qry1000.Cat_ID Is
> Null)'.
>
> My guess is the problem is caused by the pound signs (#). I'm wondering
> if AddQuotes might help.
>
> The error I get is:
>
> 3271 Invalid property value.
>
> Here's the complete logging function:
>
> Public Sub LogErr(strErrFrm As String, strErrPrc As String, _
> Optional blnLogOnly As Boolean)
> Dim db As DAO.Database
> Dim qdfs As DAO.QueryDefs
> Dim qdf As DAO.QueryDef
> Dim strErrMsg As String
> Dim strInfo As String
> Dim strSql As String
> strErrMsg = "Error Number " & Err.Number & ": " & Err.Description
> strInfo = vbCrLf & vbCrLf & strErrFrm & "." & strErrPrc
> If Not blnLogOnly Then MsgBox strErrMsg & strInfo, vbCritical, _
> " Unexpected Error"
> On Error GoTo Exit_Here
> Set db = CurrentDb
> Set qdfs = db.QueryDefs
> Set qdf = qdfs("qryLogErr")
> Debug.Print strErrMsg
> qdf.Parameters(0) = strErrMsg
> qdf.Parameters(1) = strErrFrm
> qdf.Parameters(2) = strErrPrc
> qdf.Execute dbFailOnError
> Exit_Here:
> Set qdf = Nothing
> Set qdfs = Nothing
> Set db = Nothing
> Debug.Print Err.Number & " " & Err.Description
> End Sub
>
> Here's the text of qryLogErr:
>
> INSERT INTO tblErrorLog ( ErrMsg, ErrFrm, ErrPrc )
> VALUES (prmErrMsg, prmErrFrm, prmErrPrc);[/color]


I believe the problem here is the length of the error message. I assume you
realize that some messages will be longer than 255 characters and have the
field in the table as a memo field. However, I'm not sure what will happen
if the parameter length is over 255, but you can show this is the problem by
using
strErrMsg = "Error Number " & Err.Number & ": " & Err.Description
strErrMsg = Left(strErrMsg , 255)
and this truncated description should work OK.

My AddQuotes approach will not have this limitation.

re: SQL syntax: How to insert srting with double quotes?

> I believe the problem here is the length of the error message. I assume[color=blue]
> you realize that some messages will be longer than 255 characters and have
> the field in the table as a memo field. However, I'm not sure what will
> happen if the parameter length is over 255, but you can show this is the
> problem by using
> strErrMsg = "Error Number " & Err.Number & ": " & Err.Description
> strErrMsg = Left(strErrMsg , 255)
> and this truncated description should work OK.[/color]

You're absolutely correct. I just tried it and that was the problem.

Thanks.

re: SQL syntax: How to insert srting with double quotes?

"deko" <deko@nospam.com> wrote in message
news:xuZMe.890$L03.662@newssvr27.news.prodigy.net. ..[color=blue][color=green]
>> I believe the problem here is the length of the error message. I assume
>> you realize that some messages will be longer than 255 characters and
>> have the field in the table as a memo field. However, I'm not sure what
>> will happen if the parameter length is over 255, but you can show this is
>> the problem by using
>> strErrMsg = "Error Number " & Err.Number & ": " & Err.Description
>> strErrMsg = Left(strErrMsg , 255)
>> and this truncated description should work OK.[/color]
>
> You're absolutely correct. I just tried it and that was the problem.
>
> Thanks.[/color]

If you're happy with that, then great, but if you want a working demo where
the error message can be more than 255 characters, I could e-mail you one.
Just let me know.