date query problem

Question

Dear All, please check my code: Why there is no result?   private void btnAdjusted_Click(object sender, EventArgs e) { con = new OleDbConnection(conString); if (comboBoxSelectSuspenseAccount.Text != "") { var query = "SELECT * FROM LalData " + " WHERE " + " (PaymentDate between '" + dtpFirstDate.Text.ToString() + "' AND '" + dtpLastDate.Text.ToString() + "')" + "AND " + "SuspenseAccount like '" + comboBoxEnterSuspenseAccount.SelectedItem.ToString() + "'"; con.Open(); if (dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[10].ToString() != "") { adapter = new OleDbDataAdapter(query, con); dt = new DataTable(); adapter.Fill(dt); dataGridView1.DataSource = dt; dataGridView1.CurrentCell = dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[0]; btnSubmit.Enabled = false; con.Close(); } }

Amit Gupta · Accepted Answer

Hey   Couple of things are required to change in your code prior to get the result 1. Avoid using string concatination based query, prefer using Parameterized query. It will always create confusion   2. Split your query followed by your AND keyword and try to execute seperately to assure that both query have value or it doesnot match your criteria   3. Why using this line if (dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[10].ToString() != "")

Shovan Saha · Answer

Dear Amit Sir, You are only the person that can understand my problem. Your 3rd question is my problem. With out that line my code (below) works fine. But I need to search only those data that 10th column cells are filled-in (not empty). Please help me.   private void btnAdjusted_Click(object sender, EventArgs e) { dt.Clear(); if (comboBoxEnterSuspenseAccount.Text.ToString() != "") { con = new OleDbConnection(conString); var query = "SELECT * FROM LalData " + " WHERE " + "SuspenseAccount = @suspenceAccount1 " + "AND " + "(PaymentDate BETWEEN @firstDate AND @lastDate)"; con.Open(); OleDbCommand cmd = new OleDbCommand(query, con); cmd.Parameters.AddWithValue("@suspenceAccount1 ", DbType.String).Value = comboBoxEnterSuspenseAccount.Text.ToString(); cmd.Parameters.AddWithValue("@firstDate ", DbType.Date).Value = dtpFirstDate.Value.ToShortDateString(); cmd.Parameters.AddWithValue("@lastDate ", DbType.Date).Value = dtpLastDate.Value.ToShortDateString(); adapter = new OleDbDataAdapter(cmd); adapter.Fill(dt); dataGridView1.DataSource = dt; dataGridView1.CurrentCell = dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells[0]; con.Close(); } }