How to search between two dates in C# with MS Access
So for a group project I need to be able to search between two dates "Start_Date" & "End_Date". Both of these fields use one Column from the database called "study_date". Currently I can only search for one date by entering the specified date into either field.
Here is my code:
public partial class formRRAS : Form
public OleDbConnection DataConnection = new OleDbConnection();
//When the form loads it sets the intial combo box RFR item to null
private void formRRAS_Load(object sender, EventArgs e)
// TODO: This line of code loads data into the 'database1DataSet.tblReject_test' table. You can move, or remove it, as needed.
cmbRFR.SelectedItem = "";
//AddRFR method, called in the NewRFRPopup
public void AddRFR(object item)
//The code for the button that closes the application
private void exitToolStripMenuItem_Click(object sender, EventArgs e)
private void btnSearch_Click(object sender, EventArgs e)
//This creates the String Publisher which grabs the information from the combo box on the form.
//Select and Dataconnection are also defined here.
string Department = String.IsNullOrEmpty(txtDepartment.Text)? "%" : txtDepartment.Text;
string Start_Date = String.IsNullOrEmpty(txtStart.Text)? "%" : txtStart.Text;
I think a couple of others mentioned it here, but don't ever do string concatenation in SQL for your "where" clause. It's just inviting all sorts of issues.
What you want is Parameters, aka bind variables. These serve as placeholders -- a way to say, "compile this SQL, and I'll provide you the values later."
Parameters have numerous advantages:
They prevent SQL Injection -- however rare and difficult to pull off, this is always a possibility; why invite the risk? Especially when you data comes from text boxes, you are almost begging for someone to try
They make your code so much cleaner. All of those plus, single quote, double quotes make my head spin
You can generally keep your SQL pristine -- copy and paste it right into your SQL editor, without having to prune the nasty characters involved in SQL concatenation. Okay, so this isn't true with Access, but with other RDBMSs, it makes a big difference to be able to cut and paste
They resolve datatypes -- you don't need to put #hash marks# around dates for Access or do anything special for strings (not even quotes) -- see my example below
They handle special characters. For example, if one of your text boxes contained I think I'll go to the park, that apostrophe would translate to a single quote which would wreck your query. With parameters, you don't have to worry about it
Here is an example of a parameterized version of your query:
string Select = @"
SELECT * FROM tblReject_test
department_id ALIKE @Department & '%' AND
body_part_examined ALIKE @Anatomy & '%' AND
reject_category ALIKE @RFR & '%' AND
reject_comment ALIKE @Comment & '%' and
study_date >= @Start_Date and
study_date <= @End_Date
OleDbCommand cmd = new OleDbCommand(Select, DataConnection);
Asked in February 2016Viewed 2,186 timesVoted 13Answered 2 times