Click here to Skip to main content
15,793,535 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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

Account-- Memo

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();
            dataGridView1.DataSource = ds;
Updated 24-Mar-16 0:31am

1 solution

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.
Share this answer
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();
dataadapter.SelectCommand = cmd;
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:

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900