how to get data from my database using dates between two dates

Question

My work is working it outputs my table column names in the DataGridView but my data doesn't show up is their someting im missing here?

        private void button1_Click(object sender, EventArgs e)
        {
            DateTime dt = this.dateTimePicker1.Value.Date;

            DateTime dt2 = this.dateTimePicker1.Value.Date;

            command.CommandType = CommandType.Text; 
           command.CommandText = ("SELECT * FROM BillingSystem WHERE DateOfTransaction BETWEEN #" + dt.ToShortDateString() + "# AND #" + dt2.ToShortDateString() + "#");

           OleDbDataAdapter adapter = new OleDbDataAdapter(command.CommandText, connectionBilling);
           DataSet ds = new DataSet();

           adapter.Fill(ds);
           dataGridView1.DataSource = ds.Tables[0];
           connectionBilling.Close();

            }

Answer 1

Well, I see that the two dates are the same (probably you want to enclose a full day in the BETWEEN statement). So I would try to change your query to use a parameterized query instead of string concatenation and fix the date inclusion part.

   private void button1_Click(object sender, EventArgs e)
   {
        string cmdText = "SELECT * FROM BillingSystem " + 
                         "WHERE DateOfTransaction BETWEEN ? AND ?";

        DateTime dt = this.dateTimePicker1.Value.Date;
        DateTime dt2 = this.dateTimePicker1.Value.Date.AddMinutes(1440);
        command.CommandText = cmdText;
        command.Parameters.AddWithValue("@p1",dt1);
        command.Parameters.AddWithValue("@p2",dt2);
        OleDbDataAdapter adapter = new OleDbDataAdapter(command, connectionBilling);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        dataGridView1.DataSource = ds.Tables[0];
        connectionBilling.Close();
    }

Notice that I add 1440 minutes (24 hours) to your second date.
This will allow a full day to be enclosed in your BETWEEN syntax because Access stores also the TIME part in the DateOfTransaction field and without this fix no date will match the query (unless some trasactions happened exactly at midnight)

Also a parameterized query remove the need to specify any formatting around your dates (and no possibilities of Sql Injection)