Re: INSERT Query problem with Quotes & Apostrophes

From: Pete (Pete_at_discussions.microsoft.com)
Date: 10/07/04 

Date: Thu, 7 Oct 2004 05:49:04 -0700

Thanks very much. Works a treat and I note your comment about the date (the
system will only ever be used in-hosue so this is not a problem).

Using punctuation characters in strings always seems to cause problems!
Shame there isn't a special data type "StringContainingPunctuation" or
something like that whereby Access does this for you, as the double quotes
solution is not very intuitive for novices and even trips up exerienced
programmers like myself occasionally!! :-)

"Douglas J. Steele" wrote:

> 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