473,320 Members | 1,965 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

escape the ' and " chars to insert them onto an Access database

Greetings

I'm having some problems on my C# application. I'm using an access
database and I'm not able to do select queries with the ' character.

My code is this:

// some previous code like open connection

string sqlStatement="SELECT oid FROM formatos WHERE formato='cd\\'s'";

myComm.CommandText=sqlStatement;
OleDbDataReader myDR=myComm.ExecuteReader();

//... and so on

I get the exception message

AccessException: Syntax error (missing operator) in query expression
'formato='cd\'s''.
DeclaringType.FullName:: System.Data.OleDb.OleDbCommand
Method - Void ExecuteCommandTextErrorHandling(Int32)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32 hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior
behavior, Object& executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(Comma ndBehavior
behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()
at
....
AccessError: Syntax error (missing operator) in query expression
'formato='cd\'s''.
Source: Microsoft JET Database Engine
Native Error: -524553244
State: 3075

How can I escape the ' and " chars so I can insert them onto an Access
database?

TIA

Lum
Nov 16 '05 #1
7 3935
With access use the following string:

string sqlStatement = "SELECT oid FROM formatos WHERE formato = \"cd's\"";

Try that :)

Mythran
"Luminal" <luminal_NOSPAM@hotpop_NOSPAM.com> wrote in message
news:eK*************@TK2MSFTNGP11.phx.gbl...
Greetings

I'm having some problems on my C# application. I'm using an access
database and I'm not able to do select queries with the ' character.

My code is this:

// some previous code like open connection

string sqlStatement="SELECT oid FROM formatos WHERE formato='cd\\'s'";

myComm.CommandText=sqlStatement;
OleDbDataReader myDR=myComm.ExecuteReader();

//... and so on

I get the exception message

AccessException: Syntax error (missing operator) in query expression
'formato='cd\'s''.
DeclaringType.FullName:: System.Data.OleDb.OleDbCommand
Method - Void ExecuteCommandTextErrorHandling(Int32)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32 hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior
behavior, Object& executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(Comma ndBehavior
behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()
at
...
AccessError: Syntax error (missing operator) in query expression
'formato='cd\'s''.
Source: Microsoft JET Database Engine
Native Error: -524553244
State: 3075

How can I escape the ' and " chars so I can insert them onto an Access
database?

TIA

Lum

Nov 16 '05 #2
Luminal,

Instead of trying to figure out the quotes yourself, I would recommend
using a parameterized query, and then just set the value of the parameter to
the value you have. The provider will take care of formatting the values
correctly.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Luminal" <luminal_NOSPAM@hotpop_NOSPAM.com> wrote in message
news:eK*************@TK2MSFTNGP11.phx.gbl...
Greetings

I'm having some problems on my C# application. I'm using an access
database and I'm not able to do select queries with the ' character.

My code is this:

// some previous code like open connection

string sqlStatement="SELECT oid FROM formatos WHERE formato='cd\\'s'";

myComm.CommandText=sqlStatement;
OleDbDataReader myDR=myComm.ExecuteReader();

//... and so on

I get the exception message

AccessException: Syntax error (missing operator) in query expression
'formato='cd\'s''.
DeclaringType.FullName:: System.Data.OleDb.OleDbCommand
Method - Void ExecuteCommandTextErrorHandling(Int32)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32
hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS
dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior
behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(Comma ndBehavior
behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()
at
...
AccessError: Syntax error (missing operator) in query expression
'formato='cd\'s''.
Source: Microsoft JET Database Engine
Native Error: -524553244
State: 3075

How can I escape the ' and " chars so I can insert them onto an Access
database?

TIA

Lum

Nov 16 '05 #3
Thanks Nicholas, I know that, but the way the application is done,
changing all the data layer statements/commands would take a long time.

Nicholas Paldino [.NET/C# MVP] wrote:
Luminal,

Instead of trying to figure out the quotes yourself, I would recommend
using a parameterized query, and then just set the value of the parameter to
the value you have. The provider will take care of formatting the values
correctly.

Hope this helps.

Nov 16 '05 #4
True, but at the same time, you are going to have to address the ones
that don't work anyways, so why not just change those?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Luminal" <luminal_NOSPAM@hotpop_NOSPAM.com> wrote in message
news:u9**************@TK2MSFTNGP15.phx.gbl...
Thanks Nicholas, I know that, but the way the application is done,
changing all the data layer statements/commands would take a long time.

Nicholas Paldino [.NET/C# MVP] wrote:
Luminal,

Instead of trying to figure out the quotes yourself, I would
recommend using a parameterized query, and then just set the value of the
parameter to the value you have. The provider will take care of
formatting the values correctly.

Hope this helps.


Nov 16 '05 #5
Luminal <luminal_NOSPAM@hotpop_NOSPAM.com> wrote:
Thanks Nicholas, I know that, but the way the application is done,
changing all the data layer statements/commands would take a long time.


It would be worth it. There are all kinds of reasons to use parameters
instead of embedding the values in SQL statements.

Better to do the work now than to get a SQL injection attack due to
some faulty quoting or formatting in your code.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 16 '05 #6
Sure, I will take those consideration in future development :)

Thanks

Jon Skeet [C# MVP] wrote:
Luminal <luminal_NOSPAM@hotpop_NOSPAM.com> wrote:
Thanks Nicholas, I know that, but the way the application is done,
changing all the data layer statements/commands would take a long time.

It would be worth it. There are all kinds of reasons to use parameters
instead of embedding the values in SQL statements.

Better to do the work now than to get a SQL injection attack due to
some faulty quoting or formatting in your code.

Nov 16 '05 #7
> string sqlStatement="SELECT oid FROM formatos WHERE formato='cd\\'s'";

You're escaping it the wrong way. The C# compiler is fine with the
unescaped string. You need to escape it for the SQL interpreter:
string sqlStatement="SELECT oid FROM formatos WHERE formato='cd''s'";
// that's single-quote, c, d, single-quote, single-quote, s, single-quote
(Then a double-quote to end the single)
--
Truth,
James Curran
Home: www.noveltheory.com Work: www.njtheater.com
Blog: www.honestillusion.com Day Job: www.partsearch.com
(note new day job!)
"Luminal" <luminal_NOSPAM@hotpop_NOSPAM.com> wrote in message
news:eK*************@TK2MSFTNGP11.phx.gbl...
Greetings

I'm having some problems on my C# application. I'm using an access
database and I'm not able to do select queries with the ' character.

My code is this:

// some previous code like open connection

string sqlStatement="SELECT oid FROM formatos WHERE formato='cd\\'s'";

myComm.CommandText=sqlStatement;
OleDbDataReader myDR=myComm.ExecuteReader();

//... and so on

I get the exception message

AccessException: Syntax error (missing operator) in query expression
'formato='cd\'s''.
DeclaringType.FullName:: System.Data.OleDb.OleDbCommand
Method - Void ExecuteCommandTextErrorHandling(Int32)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32 hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object&
executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior
behavior, Object& executeResult)
at
System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior
behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(Comma ndBehavior
behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()
at
...
AccessError: Syntax error (missing operator) in query expression
'formato='cd\'s''.
Source: Microsoft JET Database Engine
Native Error: -524553244
State: 3075

How can I escape the ' and " chars so I can insert them onto an Access
database?

TIA

Lum

Nov 16 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Luis | last post by:
I'm using a SQL Server 2000 stored procedure similar to the one below to upload data to a database. This data is collected from the user on a number of asp pages and stored in session variables...
3
by: Radu | last post by:
Hi. I have lots of processing to do on the server - from the client (Access) I call a sproc which returns a recordset (the sproc is essentially a big "select"). With the obtained data , I need to...
6
by: peter pilsl | last post by:
postgres 7.3.2 I store unicode-data in postgresql. The data is retrieved via webinterfaces, processed with perl and then stored in postgresql (and viceversa). All is going nice with one...
3
by: Paul | last post by:
I have an Access 2000 database with a form that is giving me some major headaches. When you open the form, it displays all records and allows editing, but has AllowAdditions set to False so that...
3
by: Luminal | last post by:
greetings i'm developing an C# application using Access as database and I'm having problems inserting data containing the ' char and the " char. What is the best practice to insert this chars on...
6
by: Stijn Vanpoucke | last post by:
Hi, I've made a program with an access database. In my sql insert command I need to use escape characters to insert text strings but te problem is that I want to use escape chars in my text...
0
by: rokuingh | last post by:
ok, so i've been working on this one for quite a while, and the code is very big so i'm just going to give the relevant parts. this is a program that builds polymers (chemical structures of repeated...
5
by: djsdaddy | last post by:
Good Day All, I have some EEO data in an old dBase4 database that I have converted to an Access table. Since dBase was not a relational database, I didn't create any key fields. I linked all of the...
1
by: Fresno Bob | last post by:
Hi I'm creating a data access layer in which each object maps onto a database table. I will also be creating basic methods that return the object by primaryid and perform insert and update...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.