mdb access to names containing "'"

G

G.F.Schl

In an ASP I access an Access DB containing names. I tried
everthing I can imagine to use """ instead of '" including
defining a constant (X37) but it either fails with an exec
error or fails when names containing ' are processed.
MY current code is:
sqltemp="select * from logindata where lastname='"
sqltemp=sqltemp & myname & "'"
(It doesn't work when I try to make it with one statement)
I am using FP2002.
Can anyone tell me how to code this for handling names
that contain '.
Thanks 10to6
 
K

Kevin Spencer

Double your single quotes in the string.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
 
G

G.F.Schl

The following statement works unless "myname" contains
a '.
sqltemp="select * from logindata where lastname='" &
myname & "' "

I tried using your suggestion but get only exec errors.
How do you suggest to code?
 
K

Kevin Spencer

Replace(myname, "'", "''") ' Replaces single quotes with doubled single
quotes in the value

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.
 
G

G.F.Schl

Maybe I misunderstand but I can't make this work:
1. (using three ")
sqltemp="select * from logindata where lastname=""" &
myname & """"
yields the following error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1.

2. (using two ' and one ")
sqltemp="select * from logindata where lastname=''" &
myname & "''"
yields the following error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query
expression 'lastname=''Testing'''.

3. (using two ')
sqltemp="select * from logindata where lastname='' &
myname & ''"
yields the following error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1.

4. (using one ' and one ")
sqltemp="select * from logindata where lastname='" &
myname & "' "
works fine unless myname contains ' than error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query
expression 'lastname='O'Test''.
 
G

Guest

I tried all combinations I can imagine but I like to
learn. Do you know what could work?
1. (using thre ")
sqltemp="select * from logindata where lastname=""" &
myname & """"
yields the following error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1.

2. (using two ' and one ")
sqltemp="select * from logindata where lastname=''" &
myname & "''"
yields the following error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query
expression 'lastname=''Testing'''.

3. (using two ')
sqltemp="select * from logindata where lastname='' &
myname & ''"
yields the following error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1.

4. (using one ' and one ")
sqltemp="select * from logindata where lastname='" &
myname & "' "
works fine unless myname contains ' than error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query
expression 'lastname='O'Test''.
 
K

Kevin Spencer

sqltemp = "select * from logindata where lastname = '" & Replace(myname,
"'", "''") & "'"

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.


G.F.Schl said:
Maybe I misunderstand but I can't make this work:
1. (using three ")
sqltemp="select * from logindata where lastname=""" &
myname & """"
yields the following error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1.

2. (using two ' and one ")
sqltemp="select * from logindata where lastname=''" &
myname & "''"
yields the following error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query
expression 'lastname=''Testing'''.

3. (using two ')
sqltemp="select * from logindata where lastname='' &
myname & ''"
yields the following error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1.

4. (using one ' and one ")
sqltemp="select * from logindata where lastname='" &
myname & "' "
works fine unless myname contains ' than error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query
expression 'lastname='O'Test''.

-----Original Message-----
Replace(myname, "'", "''") ' Replaces single quotes with doubled single
quotes in the value

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.




.
Click to expand...
Click to expand...
 
M

Mike Mueller

EZ & cheasy answer...

Do a search and replace to change the ' (single quote) to a
` (left of #1 key)

Mike



message : In an ASP I access an Access DB containing names. I tried
: everthing I can imagine to use """ instead of '" including
: defining a constant (X37) but it either fails with an exec
: error or fails when names containing ' are processed.
: MY current code is:
: sqltemp="select * from logindata where lastname='"
: sqltemp=sqltemp & myname & "'"
: (It doesn't work when I try to make it with one statement)
: I am using FP2002.
: Can anyone tell me how to code this for handling names
: that contain '.
: Thanks 10to6
 
G

G.F.Schl

Thanks, I misunderstood. I used a "replace" prior any
references to myname since there are subsequent queries.
-----Original Message-----
I tried all combinations I can imagine but I like to
learn. Do you know what could work?
1. (using thre ")
sqltemp="select * from logindata where lastname=""" &
myname & """"
yields the following error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1.

2. (using two ' and one ")
sqltemp="select * from logindata where lastname=''" &
myname & "''"
yields the following error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query
expression 'lastname=''Testing'''.

3. (using two ')
sqltemp="select * from logindata where lastname='' &
myname & ''"
yields the following error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1.

4. (using one ' and one ")
sqltemp="select * from logindata where lastname='" &
myname & "' "
works fine unless myname contains ' than error:
Microsoft OLE DB Provider for ODBC Drivers
error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query
expression 'lastname='O'Test''.
-----Original Message-----
Try
sqltemp="select * from logindata where lastname=""" & myname & """"

Ron
--
Reply only to group - emails will be deleted unread.


message
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DB access to names containing ' 1
Unable to open registry-ODBC Driver error 1
Adding a folder results in error in currently working database 4
Update table using data in a different .mdb 0
DAO - apostrophe in primary key 4
Problem with - sign in sheet name?? 1
Macro to create Name Ranges! 1
Problem creating named ranges in a Macro! 2

Top