how to fix this error data type mismatch in crietera expression.

Question

1.00/5 (1 vote)

See more:

problem in select statament between two date. (dd/MM/yyyy)
the error is :

data type mismatch in crietera expression. as the photo

http://postimg.org/image/pldnrnp7r/[^]

and the table in the database ( access) as photo

http://postimg.org/image/au7r3qdr3/[^]

http://postimg.org/image/ag6f44tnj/[^]

select statament :

C#

JSS_sql_01 = "SELECT SUM(sivd_Qnt) AS sivd_Qnt FROM sales_invoices_details where sivd_Sale_DATE >=
'" + dateTimePicker1.Value + "' and sivd_Sale_DATE<='" + dateTimePicker2.Value + "' ";

Posted 18-Mar-14 22:56pm

Golden Basim

Add a Solution

2 solutions

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)

Bernhard Hiller · Answer 1 · 2014-03-18T23:31:00

Solution 1

Use a Parameterized Query instead of string concatenation - the magic behind them will take care for the correct date format, and - also very important - will help you prevent SQL Injection Attacks.

C#

JSS_sql_01 = "SELECT SUM(sivd_Qnt) AS sivd_Qnt FROM sales_invoices_details where sivd_Sale_DATE >= @startDate and sivd_Sale_DATE<= @endDate ";
...
command.Parameters.AddwithValue("@startDate", dateTimePicker1.Value);
...

Posted 18-Mar-14 23:31pm

Bernhard Hiller

Comments

Golden Basim 19-Mar-14 6:39am

thanks , the same error !!!

Bernhard Hiller 19-Mar-14 6:49am

What? Is sivd_Sale_DATE a varchar instead of a DateTime? Don't do such nonsense!

OriginalGriff · Answer 2 · 2014-03-18T23:32:00

Solution 2

First off, don't do it like that: Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead. It will probably also cure your problem.
There is also a BETWEEN clause in SQL:

SQL

SELECT * FROM MyTable WHERE myDate BETWEEN startDate AND endDate

So if you restructure your code to use a parameterised query and use BETWEEN is should solver you problem. I don't know how the rest of your code looks, but here's a simple version:

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT SUM(sivd_Qnt) AS sivd_Qnt FROM sales_invoices_details WHERE sivd_Sale_DATE BETWEEN @SD AND @ED", con))
        {
        cmd.Parameters.AddWithValue("@SD", dateTimePicker1.Value);
        cmd.Parameters.AddWithValue("@ED", dateTimePicker2.Value);
        using (SqlDataReader reader = cmd.ExecuteReader())
            {
            if (reader.Read())
                {
                int qnt = (int) reader["sivd_Qnt"];
                ...
                }
            }
        }
    }

Posted 18-Mar-14 23:32pm

OriginalGriff

Comments

Golden Basim 19-Mar-14 6:41am

thank you

i used ur code :

string JSS_connetionString01 = null;
JSS_connetionString01 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|/je_salestoredb.mdb;Persist Security Info=True;Jet OLEDB:Database Password=JE2014";

using (OleDbConnection JSS_cnn01 = new OleDbConnection(JSS_connetionString01))
{
JSS_cnn01.Open();
using (OleDbCommand JSS_cmd01 = new OleDbCommand("SELECT SUM(sivd_Qnt) AS sivd_Qnt FROM sales_invoices_details WHERE sivd_Sale_DATE BETWEEN @SD AND @ED", JSS_cnn01))
{
JSS_cmd01.Parameters.AddWithValue("@SD", dateTimePicker1.Value);
JSS_cmd01.Parameters.AddWithValue("@ED", dateTimePicker2.Value);
using (OleDbDataReader JSS_reader01 = JSS_cmd01.ExecuteReader())
{
if (JSS_reader01.Read())
{
int JSS_qnt01 = (int) JSS_reader01["sivd_Qnt"];

JSS_cmd01.Dispose();
JSS_cnn01.Close();

textBox5.Text = JSS_qnt01.ToString("000"); ;
}

}

}
}

but this error appeared here (int JSS_qnt01 = (int) JSS_reader01["sivd_Qnt"];)

Specified cast is not valid.
and at ( JSS_reader01) this error appeared
Cannot evaluate expression because the code of the current method is optimized.

Bernhard Hiller 19-Mar-14 6:51am

? perhaps you need a double instead of an int ?

Golden Basim 19-Mar-14 7:07am

same error

OriginalGriff 19-Mar-14 6:54am

And I'm supposed to know what is in your database? :laugh:

Golden Basim 19-Mar-14 7:06am

:] the data : http://postimg.org/image/ren4g24mt/

OriginalGriff 19-Mar-14 7:09am

And why do you think I want to look at pictures of your screenshots?
If you want to give me information, then give it to me, not link to a image. Copy and paste errors, field definitions etc into your question - that way if we need to test it, we can paste it into VS, or SSMS, or...
Think about it: and help us to help you!