combobox, DTP, search button

Shovan Saha

visual studio 2015, C#, MS Access Database. I want to select from combobox as SuspenceAccount, datetimepicker1(dtpStartDate) and datetimepicker2(dtpEndDate) as PaymentDate. When I press Search button then data grid view will show the result.

Database:

ID	PaymentDate	SuspenceAccount	Name	FatherName	City
1	10 Feb 2017	A	asdf	wer	tgju
2	15 Mar 2017	B	wert	as	dfg
3	20 Mar 2017	B	asdf	hedr	we
4	07 Apr 2017	C	dfh	sdfg	rty

For result: I shall select B from combobox(SuspenceAccount), dtpStartDate(01 Mar 2017), dtpEndDate(31 Mar 2017). When I shall press Search Button then I want to get result in datagridview as:

ID	PaymentDate	SuspenceAccount	Name	FatherName	City
2	15 Mar 2017	B	wert	as	dfg
3	20 Mar 2017	B	asdf	hedr	we

I have tried as:

private void btnSearch_Click(object sender, EventArgs e)

{

con = new OleDbConnection(conString);

string query = "SELECT * FROM LalData WHERE PaymentDate " +

"BETWEEN @startDate AND @endDate" ;

con.Open();

OleDbCommand cmd = new OleDbCommand(query, con);

cmd.Parameters.AddWithValue("@startDate ", DbType.Date).Value =

dtpStartDate.Value.ToShortDateString();

cmd.Parameters.AddWithValue("@endDate ", DbType.Date).Value =

dtpEndDate.Value.ToShortDateString();

adapter = new OleDbDataAdapter(cmd);

adapter.Fill(dt);

dataGridView1.DataSource = dt;

dataGridView1.CurrentCell = dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[0];

con.Close();

}

I could not able to use combobox value for searching. Please help me with code or example that I can use combobox value, dtp(Start, End) value for searching database.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta9y ago
Accepted answer
Your query should be
var query = "Select * from LalData
Where
SuspenseAccount = @SuspenseAccount
And
PaymentDate between (@startDate and @endDate)";

// now pass respective parameters
+2
Amit Gupta9y ago
Did you get any error?

You have not corrected your code
1. Renove extra space in @startdate & @enddate .

2. Donot convert date to string, pass as it is
cmd.Parameters.AddWithValue("@startDate", dtpStartDate.Value);

3. What is the datatype of your payment date.

Rectifying all these stuffs will give you desired output
+1
Shovan Saha9y ago
Dear Amit Sir, I did not get result, why?

private void btnSearch_Click(object sender, EventArgs e)
{
con = new OleDbConnection(conString);
var query = "SELECT * FROM LalData " +
" WHERE " +
"SuspenceAccount = @suspenceAccount " +
"AND " +
"PaymentDate BETWEEN @startDate AND @endDate ";
con.Open();

OleDbCommand cmd = new OleDbCommand(query, con);
cmd.Parameters.AddWithValue("@startDate ", DbType.Date).Value =
dtpStartDate.Value.ToShortDateString();
cmd.Parameters.AddWithValue("@endDate ", DbType.Date).Value =
dtpEndDate.Value.ToShortDateString();
cmd.Parameters.AddWithValue("@suspenceAccount ", DbType.String).Value =
comboBox2.SelectedItem.ToString();
adapter = new OleDbDataAdapter(cmd);

adapter.Fill(dt);
dataGridView1.DataSource = dt;
dataGridView1.CurrentCell = dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[0];
con.Close();
}
+1
Amit Gupta9y ago
In your query, do might put a space while passing parameters
cmd.Parameters.AddWithValue("@startDate ", DbType.Date).Value =
dtpStartDate.Value.ToShortDateString();

If you are having a control of datetime, then convert it to string, pass the value as it is

cmd.Parameters.AddWithValue("@startDate", dtpStartDate.Value);
Note: your payment date in the database must be of date datatype else your query will not produce desired output.
+2