Execute SQL Query in csv file using odbc

Question

My connection string

    <add connectionString="Driver={Microsoft Text Driver (*.txt; *.csv)}; Dbq=E:; Extensions=asc,csv,tab,txt;" 
     name="TestConnectionString"/>

My C# code:

        protected void ExecuteButton_Click(object sender, EventArgs e)
    {
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;
        using (OdbcConnection con = new OdbcConnection(connectionString))
        {
            con.Open();
            string sql = "SELECT date_id FROM date_conversion.csv WHERE [date_in_ad] = '4/13/1944'";
            using (OdbcCommand command =  new OdbcCommand(sql, con))
            {
                ResultTextBox.Text = Convert.ToString(command.ExecuteScalar());
            }
        }
    }

My CSV file:

date_id date_in_ad ad_month_id ad_date_id ad_year_id
  1      4/13/1944    4           13          1944
  2      4/14/1944    4           14          1944
  3      4/15/1944    4           15          1944
  4      4/16/1944    4           16          1944
  5      4/17/1944    4           17          1944

I am receiving following error: System.Data.Odbc.OdbcException: ERROR [22018] [Microsoft][ODBC Text Driver] Data type mismatch in criteria expression.

TomTom · Accepted Answer · 2014-02-11 09:50:58Z

1

So, lets rename the question to "I get a sql exception, please help"

It has nothing to do with csv. And from your code only one line is relevant:

string sql = "SELECT date_id FROM date_conversion.csv WHERE [date_in_ad] = '4/13/1944'";

Let's have a look again at the error:

Data type mismatch in criteria expression

THere is only one criteria - [date_in_ad] and a date literal.

http://msdn.microsoft.com/en-us/library/ms710282(v=vs.85).aspx

has nice exampls how dates should look for ODBC....

http://technet.microsoft.com/en-us/library/ms190234(v=sql.90).aspx

has more explanations. And no, they do absolutely not look like your string.

Date constants look like:

{ d '1990-10-02' }

To make things easy - use a parameter. Please.

answered Feb 11, 2014 at 9:50

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nightfire001 Over a year ago

Well if I change date_in_ad to date_id it gives me same problem.

TomTom Over a year ago

Yeah. Trying to compare a funny string to a int works not, similar to comparing it to a date. WHo would have expected that? Hint - everyone. Read my answer and fix the format of the date. THen grab a book about sql.

nightfire001 Over a year ago

@TomTom: If i change my query: SELECT date_in_ad FROM date_conversion.csv WHERE [date_id] = '1'. It give me same error

TomTom Over a year ago

Yes. Because a string is not an int.

Arsen Mkrtchyan · Accepted Answer · 2014-02-11 09:53:32Z

0

Check MS KB

Try this query

string sql = "SELECT date_id FROM date_conversion.csv WHERE [date_in_ad] = #4/13/1944#";

answered Feb 11, 2014 at 9:53

Arsen Mkrtchyan

50.9k33 gold badges155 silver badges187 bronze badges

Collectives™ on Stack Overflow

Execute SQL Query in csv file using odbc

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related