Single quote problem

Anyone know how I can embed a names with embedded single quotes in an Access
Database, using VB script? The problem I have is illustrated in the
following error message I get when trying to save the data submitted by the
user:

=== > This is a dump of the SQL script
insert into tblLocate (weekending,
staffID,fullname,Telephone,dayofweek,Days, familyVisited, FamilyPhone,
FamilyRef, Activity, Location, timestart,timefinish, visitTime, StdHours,
fraction, remarks, Status) Values ('30/03/2007', '00123456', 'Mary
O'Loughlin', '1234 5678', 'Monday', 'Mon,Tue,Wed,Thu,Fri', 'Damian Murphy',
'', '24', 'Family visit', '25 Elsworth Crs', '8:00 am', '1:00 pm', 5, 7.6,
0.6, '', '');


=======> This is the error message I get
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression Mary O'Loughlin', '1234 5678', 'Monday', 'Mon'.

write2db.asp, line 168
=======> End error message

The name O'Loughlin is clearly the problem and I'm fresh out of ideas...

Thanks

--
Richard John (aka RJ)
rjbpond at bigpond.net.au

Anthony
Thu Mar 29 03:02:44 CDT 2007


"Richard John" <rjbpond@bigpond.net.au> wrote in message
news:%23K3jHGdcHHA.5044@TK2MSFTNGP05.phx.gbl...
> Anyone know how I can embed a names with embedded single quotes in an
Access
> Database, using VB script? The problem I have is illustrated in the
> following error message I get when trying to save the data submitted by
the
> user:
>
> === > This is a dump of the SQL script
> insert into tblLocate (weekending,
> staffID,fullname,Telephone,dayofweek,Days, familyVisited, FamilyPhone,
> FamilyRef, Activity, Location, timestart,timefinish, visitTime, StdHours,
> fraction, remarks, Status) Values ('30/03/2007', '00123456', 'Mary
> O'Loughlin', '1234 5678', 'Monday', 'Mon,Tue,Wed,Thu,Fri', 'Damian
Murphy',
> '', '24', 'Family visit', '25 Elsworth Crs', '8:00 am', '1:00 pm', 5, 7.6,
> 0.6, '', '');
>
>
> =======> This is the error message I get
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
in
> query expression Mary O'Loughlin', '1234 5678', 'Monday', 'Mon'.
>
> write2db.asp, line 168
> =======> End error message
>
> The name O'Loughlin is clearly the problem and I'm fresh out of ideas...
>
> Thanks
>

double up the ' :-


'Mary O''Loughlin'

s = Replace(s,"'", "''")

Richard
Thu Mar 29 11:50:27 CDT 2007

Thanks Anthony, your solution worked :-) Much obliged...

RJ

"Anthony Jones" <Ant@yadayadayada.com> wrote in message
news:%23CfTjidcHHA.4820@TK2MSFTNGP06.phx.gbl...
>
> "Richard John" <rjbpond@bigpond.net.au> wrote in message
> news:%23K3jHGdcHHA.5044@TK2MSFTNGP05.phx.gbl...
>> Anyone know how I can embed a names with embedded single quotes in an
> Access
>> Database, using VB script? The problem I have is illustrated in the
>> following error message I get when trying to save the data submitted by
> the
>> user:
>>
>> === > This is a dump of the SQL script
>> insert into tblLocate (weekending,
>> staffID,fullname,Telephone,dayofweek,Days, familyVisited, FamilyPhone,
>> FamilyRef, Activity, Location, timestart,timefinish, visitTime, StdHours,
>> fraction, remarks, Status) Values ('30/03/2007', '00123456', 'Mary
>> O'Loughlin', '1234 5678', 'Monday', 'Mon,Tue,Wed,Thu,Fri', 'Damian
> Murphy',
>> '', '24', 'Family visit', '25 Elsworth Crs', '8:00 am', '1:00 pm', 5,
>> 7.6,
>> 0.6, '', '');
>>
>>
>> =======> This is the error message I get
>> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>>
>> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
> in
>> query expression Mary O'Loughlin', '1234 5678', 'Monday', 'Mon'.
>>
>> write2db.asp, line 168
>> =======> End error message
>>
>> The name O'Loughlin is clearly the problem and I'm fresh out of ideas...
>>
>> Thanks
>>
>
> double up the ' :-
>
>
> 'Mary O''Loughlin'
>
> s = Replace(s,"'", "''")
>
>