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 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
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
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.
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.
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
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.
> 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
| |