SQL Statement with " ' " quote

[edisonl via AccessMonster.com]

Hi,

I had a string with "I'm a boy" with the single code... therefore system
prompt error thinking it was end of sql statement.. how do I eliminate this
error ?

Appreciate someone can help.. Thanks

Regards & God Bless, Edison

 

[Sylvain Lafontaine]

Double it: Replace ("I'm a boy", "'", "''")

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[John W. Vinson]

Hi,

I had a string with "I'm a boy" with the single code... therefore system
prompt error thinking it was end of sql statement.. how do I eliminate this
error ?

Appreciate someone can help.. Thanks

Regards & God Bless, Edison

Two ways:

1. Delimit the string constant with " rather than '. For instance,

INSERT INTO tablename (Textfield) Values('I'm a boy')

will fail, because the apostrophe is seen as the end of the string constant;
but

INSERT INTO tablename (Textfield) Values("I'm a boy")

will work.

2. Replace the ' in the string with two consecutive ' characters.

INSERT INTO tablename (Textfield) Values('I''m a boy')

that's with two consecutive apostrophes will also work.

Perhaps you could post the actual SQL and/or code causing the error... we
can't see it from here!

 

[edisonl via AccessMonster.com]

Hi John,

Thanks for replying..

Ok here it goes.. my application had a textbox that allowa user to input
comments...
So some comments like attend uncle's birthday, Personal Matter(Bring Cat to
vet)

The single code and comma not allowed by right... but the thing here is it
depending on what user input as their remarks you see..

Regards & God Bless, Edison

Hi,

[quoted text clipped - 5 lines]

Regards & God Bless, Edison

Two ways:

1. Delimit the string constant with " rather than '. For instance,

INSERT INTO tablename (Textfield) Values('I'm a boy')

will fail, because the apostrophe is seen as the end of the string constant;
but

INSERT INTO tablename (Textfield) Values("I'm a boy")

will work.

2. Replace the ' in the string with two consecutive ' characters.

INSERT INTO tablename (Textfield) Values('I''m a boy')

that's with two consecutive apostrophes will also work.

Perhaps you could post the actual SQL and/or code causing the error... we
can't see it from here!

 

[John W. Vinson]

Hi John,

Thanks for replying..

Ok here it goes.. my application had a textbox that allowa user to input
comments...
So some comments like attend uncle's birthday, Personal Matter(Bring Cat to
vet)

The single code and comma not allowed by right... but the thing here is it
depending on what user input as their remarks you see..

Correct the error in your code that's causing it to use ' as delimiters, then.

If you would like help doing so post the code for the textbox. I can't see it
from here.

A textbox bound to a Memo or Text field in your table, with no code involved,
should allow entry of commas, apostrophes, single or double quotes,
parentheses, etc. with no errors. It seems that you are doing something
different, but you don't say what that might be!!

 

[edisonl via AccessMonster.com]

Hi John,

Sorry for the late reply.. Here's the code as mention
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
strsql = "INSERT INTO PendingLeave_Table(UserID,LeaveType,AppliedTime,
AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTime],
[ApprovedDate],Remarks) VALUES('" & _
Forms!login_form!ApplicantUserID_Text & "','" & Forms!login_form!
LeaveType_ComboBox.Value & "','" & applytime & "','" & applydate & "','" &
Forms!login_form!UserName_Text & "','" & _
FromDate_Text & "','" & ToDate_Text & "','" & TotalDay_Text & "',0, '0:00',
'00/00/00',' " & Remarks_Text & "');"
DoCmd.SetWarnings False
CurrentDb.Execute strsql
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

If I insert into remarks_text as follows, error bound to occur:

as you can see from above the single quote and question mark might cause
problem in executing SQL statement.


Regards & God Bless, Edison

Hi John,

[quoted text clipped - 7 lines]

The single code and comma not allowed by right... but the thing here is it
depending on what user input as their remarks you see..

Correct the error in your code that's causing it to use ' as delimiters, then.

If you would like help doing so post the code for the textbox. I can't see it
from here.

A textbox bound to a Memo or Text field in your table, with no code involved,
should allow entry of commas, apostrophes, single or double quotes,
parentheses, etc. with no errors. It seems that you are doing something
different, but you don't say what that might be!!

 

[John W. Vinson]

Hi John,

Sorry for the late reply.. Here's the code as mention
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
strsql = "INSERT INTO PendingLeave_Table(UserID,LeaveType,AppliedTime,
AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTime],
[ApprovedDate],Remarks) VALUES('" & _
Forms!login_form!ApplicantUserID_Text & "','" & Forms!login_form!
LeaveType_ComboBox.Value & "','" & applytime & "','" & applydate & "','" &
Forms!login_form!UserName_Text & "','" & _
FromDate_Text & "','" & ToDate_Text & "','" & TotalDay_Text & "',0, '0:00',
'00/00/00',' " & Remarks_Text & "');"
DoCmd.SetWarnings False
CurrentDb.Execute strsql
~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

If I insert into remarks_text as follows, error bound to occur:

as you can see from above the single quote and question mark might cause
problem in executing SQL statement.

Why on Earth are you using an append query to insert one record, rather than
using a bound form with *no code at all*???? You're making your job much
harder than it needs to be.

That said... rather than using ' to delimit the Values, use two consecutive "
characters:

....FromDate_Text & "','" & ToDate_Text & "','" & TotalDay_Text & "',0, '0:00',
'00/00/00', """ & Remarks_Text & """);"

Also, if the ApprovedTime and ApprovedDate are Date/Time fields, why are you
storing a text string (for a meaningless date and midnight), rather than a
real date?

 

[edisonl via AccessMonster.com]

Hi John,

Thanks for fast response.
Not sure I understand what you mean, please elaborate.

Regards & God Bless, Edison

Hi John,

[quoted text clipped - 18 lines]

as you can see from above the single quote and question mark might cause
problem in executing SQL statement.

Why on Earth are you using an append query to insert one record, rather than
using a bound form with *no code at all*???? You're making your job much
harder than it needs to be.

That said... rather than using ' to delimit the Values, use two consecutive "
characters:

...FromDate_Text & "','" & ToDate_Text & "','" & TotalDay_Text & "',0, '0:00',
'00/00/00', """ & Remarks_Text & """);"

Also, if the ApprovedTime and ApprovedDate are Date/Time fields, why are you
storing a text string (for a meaningless date and midnight), rather than a
real date?

 

[John W. Vinson]

Hi John,

Thanks for fast response.
Not sure I understand what you mean, please elaborate.

Concatenating a literal ' character, a free form text string, and a literal '
character, as you are doing, will generate a string like

'Go to friend's house'

The apostrophe in friend's is seen as the closing apostrophe for the string.

Your alternative is to use a " character as the string delimiter, instead of
using a singlequote ' character: you want

"Go to friend's house"

delimited by doublequotes.

To get a string delimited by doublequotes, you must use TWO CONSECUTIVE
doublequotes in the literal string: rather than

strsql = "INSERT INTO PendingLeave_Table(UserID,LeaveType,AppliedTime,
AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTime],
[ApprovedDate],Remarks) VALUES('" & _
Forms!login_form!ApplicantUserID_Text & "','" & Forms!login_form!
LeaveType_ComboBox.Value & "','" & applytime & "','" & applydate & "','" &
Forms!login_form!UserName_Text & "','" & _
FromDate_Text & "','" & ToDate_Text & "','" & TotalDay_Text & "',0, '0:00',
'00/00/00',' " & Remarks_Text & "');"

try

strsql = "INSERT INTO PendingLeave_Table(UserID,LeaveType,AppliedTime,
AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTime],
[ApprovedDate],Remarks) VALUES('" & _
Forms!login_form!ApplicantUserID_Text & "','" & Forms!login_form!
LeaveType_ComboBox.Value & "','" & applytime & "','" & applydate & "','" &
Forms!login_form!UserName_Text & "','" & _
FromDate_Text & "','" & ToDate_Text & "','" & TotalDay_Text & "',0, '0:00',
'00/00/00',""" & Remarks_Text & """);"

Note: before and after Remarks_Text I am suggesting that you put """ - three
consectuive doublequote characters. Two consecutive doublequotes within a
string will be converted to one doublequote.

My question from the previous post still remains:

You're using complicated VBA code and an append query to do something that
Access does for you FOR FREE, with *no* code, *no* queries, *no* complicated
double-doublequotes. WHY are you doing so? You're apparently wasting a whole
lot of time and effort; maybe you have a good reason to do so but I do not
understand what it might be!

 

[edisonl via AccessMonster.com]

Hi John,

Anything to share so that I can use that as a guide for my next applications
regards to the SQL alternatives provided free by Access?

Regards & God Bless, Edison

Hi John,

Thanks for fast response.
Not sure I understand what you mean, please elaborate.

Concatenating a literal ' character, a free form text string, and a literal '
character, as you are doing, will generate a string like

'Go to friend's house'

The apostrophe in friend's is seen as the closing apostrophe for the string.

Your alternative is to use a " character as the string delimiter, instead of
using a singlequote ' character: you want

"Go to friend's house"

delimited by doublequotes.

To get a string delimited by doublequotes, you must use TWO CONSECUTIVE
doublequotes in the literal string: rather than

strsql = "INSERT INTO PendingLeave_Table(UserID,LeaveType,AppliedTime,
AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTime],
[ApprovedDate],Remarks) VALUES('" & _
Forms!login_form!ApplicantUserID_Text & "','" & Forms!login_form!
LeaveType_ComboBox.Value & "','" & applytime & "','" & applydate & "','" &
Forms!login_form!UserName_Text & "','" & _
FromDate_Text & "','" & ToDate_Text & "','" & TotalDay_Text & "',0, '0:00',
'00/00/00',' " & Remarks_Text & "');"

try

strsql = "INSERT INTO PendingLeave_Table(UserID,LeaveType,AppliedTime,
AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTime],
[ApprovedDate],Remarks) VALUES('" & _
Forms!login_form!ApplicantUserID_Text & "','" & Forms!login_form!
LeaveType_ComboBox.Value & "','" & applytime & "','" & applydate & "','" &
Forms!login_form!UserName_Text & "','" & _
FromDate_Text & "','" & ToDate_Text & "','" & TotalDay_Text & "',0, '0:00',
'00/00/00',""" & Remarks_Text & """);"

Note: before and after Remarks_Text I am suggesting that you put """ - three
consectuive doublequote characters. Two consecutive doublequotes within a
string will be converted to one doublequote.

My question from the previous post still remains:

You're using complicated VBA code and an append query to do something that
Access does for you FOR FREE, with *no* code, *no* queries, *no* complicated
double-doublequotes. WHY are you doing so? You're apparently wasting a whole
lot of time and effort; maybe you have a good reason to do so but I do not
understand what it might be!

 

[John W. Vinson]

Hi John,

Anything to share so that I can use that as a guide for my next applications
regards to the SQL alternatives provided free by Access?

Try Crystal's tutorial or video.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Bob Barrows]

Two ways:

Actually 3 ... use a parameter! No more problems with delimiters!

 

[edisonl via AccessMonster.com]

Hi Bob,

Erm.. not sure if I understand what you mean 3 ... care to englighten me ?

Regards & God Bless, Edison

[quoted text clipped - 7 lines]

Two ways:

Actually 3 ... use a parameter! No more problems with delimiters!

 

[Bob Barrows]

John listed two ways to handle delimiters (and literal quotes). I mentioned
a third: using parameters.
Make sure you set a reference to the DAO library
(Tools > References > Microsoft DAO 3.6 Object Library)
Using your code:

strsql = "INSERT INTO PendingLeave_Table(UserID,LeaveType,AppliedTime,
AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTime],
[ApprovedDate],Remarks) VALUES(" & _
"[p1],[p2],[p3],[p4],[p5],[p6],[p7],[p8],0,'0:00','00/00/00',[p9])"

dim db as DAO.Database
Set db=CurrentDB
dim qdf as DAO.Querydef
Set qdf=db.CreateQuerydef("",strsql)
qdf(0) = Forms!login_form!ApplicantUserID_Text
qdf(1) = Forms!login_form!LeaveType_ComboBox.Value
qdf(2) = applytime
qdf(3) = applydate
qdf(4) = Forms!login_form!UserName_Text
qdf(5) = FromDate_Text
qdf(6) = ToDate_Text
qdf(7) = TotalDay_Text
qdf(8) = Remarks_Text
qdf.execute

See? No worries about delimiters. I am assuming those date and time fields
(ApprovedTime, ApprovedDate, etc.) are actually Text fields ... if so, why
haven't you configured them as Date/Time fields?

 

[edisonl via AccessMonster.com]

Hi Bob,

Thanks for replying .. I am using ADO as my default setting... not sure if I
use DAO setting here will have any impact on other part of the application.

Also I am not really sure on John solutions as he pose quite a number of
links that when I visit there
it is a general overview...as such I have problem looking for what he point
at.

Sorry for the trouble that I caused.

Regards & God Bless, Edison

John listed two ways to handle delimiters (and literal quotes). I mentioned
a third: using parameters.
Make sure you set a reference to the DAO library
(Tools > References > Microsoft DAO 3.6 Object Library)
Using your code:

strsql = "INSERT INTO PendingLeave_Table(UserID,LeaveType,AppliedTime,
AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTime],
[ApprovedDate],Remarks) VALUES(" & _
"[p1],[p2],[p3],[p4],[p5],[p6],[p7],[p8],0,'0:00','00/00/00',[p9])"

dim db as DAO.Database
Set db=CurrentDB
dim qdf as DAO.Querydef
Set qdf=db.CreateQuerydef("",strsql)
qdf(0) = Forms!login_form!ApplicantUserID_Text
qdf(1) = Forms!login_form!LeaveType_ComboBox.Value
qdf(2) = applytime
qdf(3) = applydate
qdf(4) = Forms!login_form!UserName_Text
qdf(5) = FromDate_Text
qdf(6) = ToDate_Text
qdf(7) = TotalDay_Text
qdf(8) = Remarks_Text
qdf.execute

See? No worries about delimiters. I am assuming those date and time fields
(ApprovedTime, ApprovedDate, etc.) are actually Text fields ... if so, why
haven't you configured them as Date/Time fields?

Hi Bob,

[quoted text clipped - 4 lines]

 

[Bob Barrows]

Using both DAO and ADO will not cause a problem as long as you
explicitly qualify the similarly named objects: DAO.Recordset and
ADODB.Recordset.
If you'd rather not do that, you can do the same thing using an ADO
Command object with a string containing parameter markers:

strsql = "INSERT INTO PendingLeave_Table(UserID,LeaveType,AppliedTime,
AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTim
e],
[ApprovedDate],Remarks) VALUES(" & _
"?,?,?,?,?,?,?,?,0,'0:00','00/00/00',?)"

dim arParms as Variant
arParms = Array(Forms!login_form!ApplicantUserID_Text, _
Forms!login_form!LeaveType_ComboBox.Value, _
applytime, _
applydate, _
Forms!login_form!UserName_Text, _
FromDate_Text, _
ToDate_Text, _
TotalDay_Text, _
Remarks_Text)


dim cn as adodb.connection,cmd as adodb.command
set cn = Application.CurrentProject.AccessConnection
set cmd=new adodb.command
with cmd
.commandtext=strsql
.commandtype=1 'adCmdType
set .activeconnection = cn
.execute ,arParms,128 '128=adExecuteNoRecords
end with
cn.close:set cn = nothing

Again, no delimiter/literal quote issues. The only qualm I would have is
the issue of what is the actual datatype of those dae and time columns?

And I haven't seen any "trouble" ...

Hi Bob,

Thanks for replying .. I am using ADO as my default setting... not
sure if I use DAO setting here will have any impact on other part of
the application.

Also I am not really sure on John solutions as he pose quite a number
of links that when I visit there
it is a general overview...as such I have problem looking for what he
point at.

Sorry for the trouble that I caused.

Regards & God Bless, Edison

John listed two ways to handle delimiters (and literal quotes). I
mentioned a third: using parameters.
Make sure you set a reference to the DAO library
(Tools > References > Microsoft DAO 3.6 Object Library)
Using your code:

strsql = "INSERT INTO
PendingLeave_Table(UserID,LeaveType,AppliedTime,

AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTim
e],

[ApprovedDate],Remarks) VALUES(" & _
"[p1],[p2],[p3],[p4],[p5],[p6],[p7],[p8],0,'0:00','00/00/00',[p9])"

dim db as DAO.Database
Set db=CurrentDB
dim qdf as DAO.Querydef
Set qdf=db.CreateQuerydef("",strsql)
qdf(0) = Forms!login_form!ApplicantUserID_Text
qdf(1) = Forms!login_form!LeaveType_ComboBox.Value
qdf(2) = applytime
qdf(3) = applydate
qdf(4) = Forms!login_form!UserName_Text
qdf(5) = FromDate_Text
qdf(6) = ToDate_Text
qdf(7) = TotalDay_Text
qdf(8) = Remarks_Text
qdf.execute

See? No worries about delimiters. I am assuming those date and time
fields (ApprovedTime, ApprovedDate, etc.) are actually Text fields
... if so, why haven't you configured them as Date/Time fields?

Hi Bob,

[quoted text clipped - 4 lines]

Actually 3 ... use a parameter! No more problems with delimiters!

 

[edisonl via AccessMonster.com]

Hi Bob,

Thanks once again.. will try it out..

By the way, Date format I had it as date so that user will have no problem
remebering
what are the date format to input.. Eg: 26-08-09, 26/08/09, 26-aug-2009....
etc etc...

And The date are auto input from calendar objects all that is required is
click on the date
that they want and wa la... all is done for them

Regards & God Bless, Edison

Using both DAO and ADO will not cause a problem as long as you
explicitly qualify the similarly named objects: DAO.Recordset and
ADODB.Recordset.
If you'd rather not do that, you can do the same thing using an ADO
Command object with a string containing parameter markers:

strsql = "INSERT INTO PendingLeave_Table(UserID,LeaveType,AppliedTime,
AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTim
e],
[ApprovedDate],Remarks) VALUES(" & _
"?,?,?,?,?,?,?,?,0,'0:00','00/00/00',?)"

dim arParms as Variant
arParms = Array(Forms!login_form!ApplicantUserID_Text, _
Forms!login_form!LeaveType_ComboBox.Value, _
applytime, _
applydate, _
Forms!login_form!UserName_Text, _
FromDate_Text, _
ToDate_Text, _
TotalDay_Text, _
Remarks_Text)

dim cn as adodb.connection,cmd as adodb.command
set cn = Application.CurrentProject.AccessConnection
set cmd=new adodb.command
with cmd
.commandtext=strsql
.commandtype=1 'adCmdType
set .activeconnection = cn
.execute ,arParms,128 '128=adExecuteNoRecords
end with
cn.close:set cn = nothing

Again, no delimiter/literal quote issues. The only qualm I would have is
the issue of what is the actual datatype of those dae and time columns?

And I haven't seen any "trouble" ...

Hi Bob,

[quoted text clipped - 19 lines]

strsql = "INSERT INTO
PendingLeave_Table(UserID,LeaveType,AppliedTime,

AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTim
e],

[ApprovedDate],Remarks) VALUES(" & _
"[p1],[p2],[p3],[p4],[p5],[p6],[p7],[p8],0,'0:00','00/00/00',[p9])"

[quoted text clipped - 26 lines]

 

[Bob Barrows]

I was not asking about the Format. I was asking about the Data Type
(number, text,date/time, etc.). That is the only relevant thing to
consider when deciding how to delimit literal values in sql statements.
Format only controls how the data is displayed, not how it is stored.

Hi Bob,

Thanks once again.. will try it out..

By the way, Date format I had it as date so that user will have no
problem remebering
what are the date format to input.. Eg: 26-08-09, 26/08/09,
26-aug-2009.... etc etc...

And The date are auto input from calendar objects all that is
required is click on the date
that they want and wa la... all is done for them

Regards & God Bless, Edison

Using both DAO and ADO will not cause a problem as long as you
explicitly qualify the similarly named objects: DAO.Recordset and
ADODB.Recordset.
If you'd rather not do that, you can do the same thing using an ADO
Command object with a string containing parameter markers:

strsql = "INSERT INTO
PendingLeave_Table(UserID,LeaveType,AppliedTime,
AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTim
e], [ApprovedDate],Remarks) VALUES(" & _
"?,?,?,?,?,?,?,?,0,'0:00','00/00/00',?)"

dim arParms as Variant
arParms = Array(Forms!login_form!ApplicantUserID_Text, _
Forms!login_form!LeaveType_ComboBox.Value, _
applytime, _
applydate, _
Forms!login_form!UserName_Text, _
FromDate_Text, _
ToDate_Text, _
TotalDay_Text, _
Remarks_Text)

dim cn as adodb.connection,cmd as adodb.command
set cn = Application.CurrentProject.AccessConnection
set cmd=new adodb.command
with cmd
.commandtext=strsql
.commandtype=1 'adCmdType
set .activeconnection = cn
.execute ,arParms,128 '128=adExecuteNoRecords
end with
cn.close:set cn = nothing

Again, no delimiter/literal quote issues. The only qualm I would
have is the issue of what is the actual datatype of those dae and
time columns?

And I haven't seen any "trouble" ...

Hi Bob,

[quoted text clipped - 19 lines]

strsql = "INSERT INTO
PendingLeave_Table(UserID,LeaveType,AppliedTime,

AppliedDate,AppliedBy,[FromDate],[ToDate],TotalDays,Approve,[ApprovedTim
e],

[ApprovedDate],Remarks) VALUES(" & _
"[p1],[p2],[p3],[p4],[p5],[p6],[p7],[p8],0,'0:00','00/00/00',[p9])"

[quoted text clipped - 26 lines]