Re: INSERT Query problem with Quotes & Apostrophes

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 10/07/04 

Date: Thu, 7 Oct 2004 08:06:00 -0400

What you have to do is decide whether you want to use single quotes or
double quotes as your delimiter, and then double each occurrence of that
delimiter symbol in your string.

Assuming you're using Access 2000 or newer, the Replace function will help
here.

In your example, you're using double quotes as the delimiter, therefore you
need to change each occurrence of " in your text to "":

         .CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
 cChange) VALUES ('" & CurrentUser & "', #" & Date() & "#, " & Chr(34) &
 Replace(RecordChanged, Chr(34), Chr(34) & Chr(34)) & Chr(34) & ")"

Don't worry that this will impact what you're inserting. It won't: even
though you're putting two double quotes into the string, it will be
recognized as only being one for insertion purposes.

Had you wanted to use a single quote as the delimiter, you'd use:

         .CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
 cChange) VALUES ('" & CurrentUser & "', #" & Date() & "#, '" &
Replace(RecordChanged, "'", "''") & "')"

Exagerated for clarity, that's:

         .CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
 cChange) VALUES ('" & CurrentUser & "', #" & Date() & "#, ' " &
Replace(RecordChanged, " ' ", " ' ' ") & " ' )"

BTW, if there's a chance that people in other countries will be using your
application, you should be aware that putting Date() into the SQL like that
may not always work. If your user has the Short Date format set to
dd/mm/yyyy (as it is in much of the world), the SQL statement will not work
for the first 12 days of each month. My advice is to format the date
explicitly to avoid the possibility of problems:

         .CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
 cChange) VALUES ('" & CurrentUser & "', " & Format(Date(),
"\#mm\/dd\/yyyy\#") & ", '" & Replace(RecordChanged, "'", "''") & "')" 

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
"Pete" <Pete@discussions.microsoft.com> wrote in message
news:1A6EBE46-B2B0-4BDA-A144-D67899F75E48@microsoft.com...
> We have an application with an audit table to keep track of who changed
what
> and when. This is updated using the following code:
>     Dim cmd1 As ADODB.Command
>     Set cmd1 = New ADODB.Command
>     With cmd1
>         .ActiveConnection = CurrentProject.Connection
>         .CommandText = "INSERT INTO tblAuditTrail(cUserCode, dDateTime,
> cChange) VALUES ('" & CurrentUser & "', #" & Date() & "#, " & Chr(34) &
> RecordChanged & Chr(34) & ")"
>         .CommandType = adCmdText
>         .Execute
>     End With
>     Set cmd1 = Nothing
>
> The Chr(34)s were added as I ran into problem if the value of the
> RecordChanged string contained an apostrophe (').
>
> I now have a problem as a user wants to write a record containg quotes (")
> and an apostrophe ('). I have written a function that strips these
characters
> out of the string, but ideally I would like to audit exactly what the user
> has entered. I have tried playing around with adding Chr(39) into the
above
> CommandText but not had any luck. Help please!
>
> --
> Peter Schmidt
> Ross-on-Wye, UK

Relevant Pages