C# SQL select date problems

Question

I`m New in c# I have problems with SQL and DATEs in this query I have error...

ERROR [22018] [Microsoft][ODBC dBase Driver] Data type mismatch in criteria expression.

    private void button2_Click(object sender, EventArgs e)
        {
            char split = '.';
            string[] s = dateTimePicker1.Text.Split(split);
            string sx = s[0] + "." + s[1] + ".";


            System.Data.Odbc.OdbcConnection oConn = new System.Data.Odbc.OdbcConnection();
            oConn.ConnectionString = @"Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;
SourceDB=C:\sales\;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
            oConn.Open();
            System.Data.Odbc.OdbcCommand oCmd = oConn.CreateCommand();
            oCmd.CommandText = @"SELECT COD,PRICE,SMAN_COD FROM C:\sales\sl.DBF WHERE DATE='"+dateTimePicker1.Text+"'";
            DataTable dt = new DataTable();
            dt.Load(oCmd.ExecuteReader());
            oConn.Close();

            dataGridView1.DataSource = dt;

        }

Please Help Me

Is your "Date" column of type date? DateTime? What text does dateTimePicker1.Text contain (just a date value, or a date + a time)? — Mark Carpenter
– Mark Carpenter, Commented Feb 28, 2011 at 11:28

Jon Skeet · Accepted Answer · 2011-02-28 11:29:22Z

7

Don't send the date/time value in the SQL at all. Use a parameterized query - that way you don't need to concern yourself with the format. Why complicate things and leave yourself open to subtle problems when you can communicate the data without parsing and formatting it?

Additionally, if you get in the habit of using parameters for all values, you'll avoid SQL injection attacks.

In this case you'd use DateTimePicker.Value instead of DateTimePicker.Text, to get at the value as a DateTime.

answered Feb 28, 2011 at 11:29

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

Comments

Kardel · Accepted Answer · 2011-02-28 12:09:39Z

2

Try to use Parametrized Queries to Avoid Sql Injection

ooops, OdbcCommand seems different with SqlCommand For Parametriezed Queries.

Change

System.Data.Odbc.OdbcCommand oCmd = oConn.CreateCommand();
oCmd.CommandText = @"SELECT COD,PRICE,SMAN_COD FROM C:\sales\sl.DBF WHERE DATE='"+dateTimePicker1.Text+"'";

To

System.Data.Odbc.OdbcCommand oCmd = oConn.CreateCommand();
oCmd.CommandText = @"SELECT COD,PRICE,SMAN_COD FROM C:\sales\sl.DBF WHERE DATE=?";
oCmd.Parameters.Add ("DATE", OdbcType.DateTime).Value = dateTimePicker1.Value;

Hopefully this will help you!

edited Feb 28, 2011 at 12:09

answered Feb 28, 2011 at 11:52

Kardel

812 bronze badges

1 Comment

Lamzaks Over a year ago

This worked... :) tnx.... But I need tur use dateTimePicker1.Text, becaues in this case I make only selects.. I must use base waht is

Øyvind Bråthen · Accepted Answer · 2011-02-28 11:28:29Z

2

If you want to make sure that the date sent into the database is formated in a proper way, I would recommend the following date format:

yyyy-MM-dd

This will, to my knowledge, work regardless of the culture of your machine, and the machine running the database server.

This format is called ISO 8601.

answered Feb 28, 2011 at 11:28

Øyvind Bråthen

60.9k28 gold badges128 silver badges154 bronze badges

Comments

Robbie Tapping · Accepted Answer · 2011-02-28 11:28:55Z

1

I think the problem is that your Output Date is in the Format of 12/01/2011

If it is. you need to convert it to 2010-01-12

the DateTimePicker i think has a property called SelectedDate instead of Text

If that is the case then

use:

String.Format("{0:yyyy-MM-dd}", DatePicker1.SelectedDate);

answered Feb 28, 2011 at 11:28

Robbie Tapping

2,5561 gold badge17 silver badges18 bronze badges

Comments

Ash Burlaczenko · Accepted Answer · 2011-02-28 11:29:49Z

0

This is happening because sql expects the date to be in a certain format. Try replacing

dateTimePicker1.Text

with

dateTimePicker1.Text.ToString("dd mmm yyyy");

Hope this helps.

answered Feb 28, 2011 at 11:29

Ash Burlaczenko

25.6k16 gold badges70 silver badges103 bronze badges

Collectives™ on Stack Overflow

C# SQL select date problems

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related