Handling Single Quote Issue in ODBC

Question

4.00/5 (1 vote)

See more:

, +

Hi Guys,

I am trying to query a CSV file using where statement. Following is the code:-

Line 1: string sDataValue = "Icom 20' Extension Cable f/COMMANDMIC";
Line 2: DataTable dtCSV = new DataTable();
Line 3: string strConnString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=data.csv;Extensions=asc,csv,tab,txt"
Line 4: OdbcConnection CsvConn = new OdbcConnection(strConnString);
Line 5: CsvConn.Open();
Line 6: string strSQL;
Line 7: strSQL = "SELECT * FROM data.csv WHERE column1 = '" + sDataValue + "'";
Line 8: OdbcCommand cmdSelect = new OdbcCommand(strSQL, CsvConn);
Line 9: cmdSelect.Connection = CsvConn;
Line 10: OdbcDataAdapter daCSV = new OdbcDataAdapter(cmdSelect);
Line 11: daCSV.Fill(dtCSV);

In Line 11 the following exception is thrown:

ERROR [42000] [Microsoft][ODBC Text Driver] Syntax error (missing operator) in query expression '[ProductXName] = 'Icom 20' Extension Cable f/COMMANDMIC'.

When I remove the single quote (') from sDataValue, the query executes without any exception. But the sDataValue should have the single quote. How could this issue be handled?

Your solution to his issue will be highly appreciated.

Mohandas

Posted 22-Jun-11 19:53pm

Mohandas_Kulasekaran

Updated 22-Jun-11 19:57pm

v3

Add a Solution

2 solutions

Solution 1

Check the link given below:

A Fast CSV Reader[^]

Posted 22-Jun-11 20:00pm

2irfanshaikh

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prerak Patel · Accepted Answer · 2011-06-22T20:07:00

Solution 2

Easiest way,

Line 7: strSQL = "SELECT * FROM data.csv WHERE column1 = '" + sDataValue.Replace("'", "''") + "'";

Proper way,
Use parameterised query.

Line 7: strSQL = "SELECT * FROM data.csv WHERE column1 = ?";
Line 8: OdbcCommand cmdSelect = new OdbcCommand(strSQL, CsvConn);
        cmdSelect.Parameters.AddWithValue("@column1", sDataValue);
Line 9: cmdSelect.Connection = CsvConn;

Posted 22-Jun-11 20:07pm

Prerak Patel

Comments

Mohandas_Kulasekaran 23-Jun-11 2:33am

Hi,

Thank for your response. I have already tried Replace() as said by you. The query throws no exception, but it neither returns rows because the parameter values changes to "Icom 20'' Extension Cable f/COMMANDMIC" whereas the value in CSV is "Icom 20' Extension Cable f/COMMANDMIC".

I was not sure about using parameterized query for ODBC hence i didn't thought of that way. Thanks for throwing light on that. I will try that as well.

Thanks again.

thatraja 23-Jun-11 2:48am

Right suggestion, 5!

Mohandas_Kulasekaran 23-Jun-11 3:56am

Your suggestion to use parameterized query worked. Thanks Patel,

Prerak Patel 23-Jun-11 4:12am

You are welcome.