iiAaronXiX February 2016

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:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;

namespace RRAS
{

    public partial class formRRAS : Form
    {
        public OleDbConnection DataConnection = new OleDbConnection();


        public formRRAS()
        {
            InitializeComponent();

        }

        //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.
            this.tblReject_testTableAdapter.Fill(this.database1DataSet.tblReject_test);

            cmbRFR.SelectedItem = "";

        }

        //AddRFR method, called in the NewRFRPopup
        public void AddRFR(object item)
        {
            cmbRFR.Items.Add(item);
        }

        //The code for the button that closes the application
        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        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;
                //DateT        

Answers


SSK February 2016

You can try this

string Select = "SELECT * FROM tblReject_test WHERE department_id LIKE '" + Department + "'" + "AND body_part_examined LIKE'" + Anatomy + "'" + "AND study_date >=#" + Start_Date + "#" + "AND study_date <=#" + End_Date + "#" + "AND reject_category LIKE'" + RFR + "'" +  "AND reject_comment LIKE'" + Comment + "'";


Hambone February 2016

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:

  1. 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
  2. They make your code so much cleaner. All of those plus, single quote, double quotes make my head spin
  3. 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
  4. 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
  5. 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
    WHERE
        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);
cmd.Parameters.AddWithValue("@Department", txtDepartment.Text 

Post Status

Asked in February 2016
Viewed 2,186 times
Voted 13
Answered 2 times

Search




Leave an answer