Click here to Skip to main content
14,329,392 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 22-Jun-11 19:57pm
v3
Rate this:
Please Sign up or sign in to vote.

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;
   
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.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Check the link given below:

A Fast CSV Reader[^]
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100