Quote and apostrophe problem in MS Access

[Kanad Maitra]

I am running this query from ASP into an acces database.

insert into Campaign_m values( 21, '1', "Kanad's
Test", 'N', 'N' );

It gives me this error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1.


However if I run this within MS Access SQL Query it runs
perfectly.

I need to save the apostrophe in the database so I can't
use single quote - 'Kanad's Test'. If I do I get this
error.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query expression ''Kanad's
Test', 'N', 'N' );'.


Please can anybody help me?

 

[John Spencer (MVP)]

Use Double quotes instead of the apostrophe or use routine that doubles the
apostrophe (two apostrophes) in Kanad's Test

Values(21, "1","Kanad's Test","N","N")

or
values( 21, '1', 'Kanad''s Test', 'N', 'N')

 

[James Chin]

Kanad,

Double-quotes are typically used to specify schema, table or column
names that were created with special characters. For example, if the
table has a column named 'column-name', you would not be able to query
the table like:

select column-name from ....

The hyphen would be assumed to mean a subtraction operation. This
would be correct:

select "column-name" from ...

A single quote is the only character that identifies a value as being
a text value. Doubling up the apostrophe, as shown in John's second
example, should work.

Regards,

James Chin
OpenLink Software Inc.
http://www.openlinksw.com
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology

Use Double quotes instead of the apostrophe or use routine that doubles the
apostrophe (two apostrophes) in Kanad's Test

Values(21, "1","Kanad's Test","N","N")

or
values( 21, '1', 'Kanad''s Test', 'N', 'N')

I am running this query from ASP into an acces database.

insert into Campaign_m values( 21, '1', "Kanad's
Test", 'N', 'N' );

It gives me this error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1.

However if I run this within MS Access SQL Query it runs
perfectly.

I need to save the apostrophe in the database so I can't
use single quote - 'Kanad's Test'. If I do I get this
error.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query expression ''Kanad's
Test', 'N', 'N' );'.

Please can anybody help me?