Click here to Skip to main content
14,303,069 members
Rate this:
Please Sign up or sign in to vote.
See more:
Actually im creating report in c#, having to Datetimepicker,Button and datagridview

when im selecting a date and click on show report its give me error
Data type mismatch in criteria expression
in
dataadapter.Fill(ds);

im using access database for this here im giving my table structure

AccountNumber--Number
Account-- Memo
Date--Date/Time
Description--Memo
Post_Ref--Memo
Debit--Number
Credit--Number
Balance--Number

What I have tried:

private void btnshowreport_Click(object sender, EventArgs e)
        {
 string connetionString = null;
            DateTime startT = new DateTime();
            DateTime endT = new DateTime();
            startT = dateTimePicker1.Value.Date;
            endT = dateTimePicker2.Value.Date;
            connetionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
            cnn.ConnectionString = connetionString;
string sql = "SELECT * FROM Billing where [Date] Between'"+startT+"'And '"+endT+"'";
            OleDbConnection connection = new OleDbConnection(connetionString);
            OleDbDataAdapter dataadapter = new OleDbDataAdapter(sql, connection);
            DataTable ds = new DataTable();
            connection.Open();
            dataadapter.Fill(ds);
            connection.Close();
            dataGridView1.DataSource = ds;
            show_data();
}
Posted
Updated 23-Mar-16 23:31pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Stop doing 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.
Pass your DateTimePicker.Value properties as DateTime values via parameters, and not as default strings, and your problem should go away as well.
   
Comments
Atul Rokade 24-Mar-16 5:57am
   
sir if you dont mind can you modify my code?? because i really not understand how should i do this
OriginalGriff 24-Mar-16 6:21am
   
You know how to use a parameterised query, don't you?
Atul Rokade 24-Mar-16 6:34am
   
i changes the code but now prblem is what when i search start_date suppose 17/02/2015 and end_date 24/03/2016 datagridview show me blank, but in table having value 17/02/2015 here is my modify code

string connetionString = null;
connetionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
cnn.ConnectionString = connetionString;
cmd = new OleDbCommand("SELECT * FROM Billing " + "WHERE Date between ? AND ?");
cmd.Parameters.AddWithValue("?", dateTimePicker1.Value);
cmd.Parameters.AddWithValue("?", dateTimePicker2.Value);
OleDbConnection connection = new OleDbConnection(connetionString);
OleDbDataAdapter dataadapter = new OleDbDataAdapter();
DataSet ds = new DataSet();
connection.Open();
dataadapter.SelectCommand = cmd;
connection.Close();
dataGridView1.DataSource = ds;


OriginalGriff 24-Mar-16 6:52am
   
Try changing the parameters to this:
cmd.Parameters.AddWithValue("@SD", dateTimePicker1.Value);
cmd.Parameters.AddWithValue("@ED", dateTimePicker2.Value);
Then check which DateTiemPicker is which, and make sure that 1 is the start and 2 is the end.
OriginalGriff 24-Mar-16 6:53am
   
And as a thought, why aren't you Filling the dataset at all?
That would probably help a lot because unless you do, all you have is an empty dataset...which is what you are seeing... :laugh:
FARONO 24-Mar-16 6:23am
   
http://stackoverflow.com/questions/6584817/direct-method-from-sql-command-text-to-dataset

-use DataSet as datasource
-use SqlCommand to add parameters (Google SqlCommand, CommandText, Parameters)

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