Atul Atul - 1 month ago 17
C# Question

Value doesnot show in datagridview when date selected in datetimepicker

When i choose From Date and Todate from datetimepicker to show data in datagridview at that time datagridview show me empty data , code is written button click evet, here im using access database

DateTime startT = new DateTime();
DateTime endT = new DateTime();
startT = dateTimePicker1.Value.Date;
endT = dateTimePicker2.Value.Date;
if (startT.Date > endT.Date)
{
MessageBox.Show("To Date Cannot be greater than Start Date");
}
else
{
string connetionString = null;
connetionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
con.ConnectionString = connetionString;

DataSet ds = new DataSet();
string sql = "SELECT Medicine_name,sum(Medicine_count) as Medicine_count,To_Date from Medicine_count_info where [To_Date] Between #" + startT.ToString("dd'/'MM'/'yyyy") + "#And #" + endT.ToString("dd'/'MM'/'yyyy") + "#group by Medicine_name,Medicine_count,To_Date order by Medicine_count desc ";
OleDbConnection connection = new OleDbConnection(connetionString);
OleDbDataAdapter dataadapter = new OleDbDataAdapter(sql, connection);
ds = new DataSet();
connection.Open();
dataadapter.Fill(ds);
connection.Close();
dataGridView1.DataSource = ds;
dataGridView1.DataMember = ds.Tables[0].TableName;
dataGridView1.Refresh();
}


enter image description here
enter image description here

Answer

First of all, in your select you are missing a space in "#And #"

Also,assuming [To_Date] is a datetime field, the problem is probably how you are building your select clause passing the data as string. Use DateTime fields instead and, of course ALWAYS USE PARAMETRIZED QUERIES. Something like this:

string sql = "SELECT Medicine_name,sum(Medicine_count) as Medicine_count,To_Date " +
"FROM Medicine_count_info " +
"WHERE [To_Date] Between @startDate And @endDate " +
"GROUP BY Medicine_name,Medicine_count,To_Date " +
"ORDER BY Medicine_count desc ";
OleDbDataAdapter dataadapter = new OleDbDataAdapter(sql, connection);
dataadapter.SelectCommand.Parameters.AddWithValue("@startDate", startT);
dataadapter.SelectCommand.Parameters.AddWithValue("@startDate", endT);
ds = new DataSet();
dataadapter.Fill(ds);
...
Comments