Search between two dates in database c#

Question

public void LoadAttendance()
{
    DataAdapter = new OleDbDataAdapter("SELECT * FROM AttendanceDatabase where EmpName = '" + txtEmpName.Text + "'", con);
    DataTable = new DataTable();
    DataAdapter.Fill(DataTable);
    dgvAttendance.DataSource = DataTable;
    dgvAttendance.Columns[1].DefaultCellStyle.Format = "h:mm tt";
    dgvAttendance.Columns[2].DefaultCellStyle.Format = "h:mm tt";
}

I want to search a record in between two dates and Employee Name. Can someone help me about the query? public void FillPresentDays() {

      Public void FillPresentDays
      {
        con.Open();
        cmd = new OleDbCommand("Select COUNT(*) from AttendanceDatabase  WHERE EmpName =@EmpName and Date between @d1 and @d2", con);
        cmd.Parameters.AddWithValue("@EmpName", txtEmpName.Text);
        cmd.Parameters.AddWithValue("@d1", dtDate1.Value.Date);
        cmd.Parameters.AddWithValue("@d2", dtDate2.Value.Date);
        cmd.ExecuteNonQuery();
        int count = (int)cmd.ExecuteScalar();
        txtPdays.Text = count.ToString();
        con.Close();
        LoadAttendance();

    }

this code is working the datetimepicker used here is the same that i will used in the LoadAttendance.

Can you show us your aspx coding? specially datetime picker format. — Litisqe Kumar, Oct 13 '15 at 05:20

score 2 · Answer 1 · 2015-10-13T05:10:17.730

2

public void LoadAttendance()
{
    DateTime fromdt = DateTime.ParseExact("1/1/2009", "M/d/yyyy", new CultureInfo("en-US"));
    DateTime todt = DateTime.ParseExact("1/1/2010", "M/d/yyyy", new CultureInfo("en-US"));
    DataAdapter = new OleDbDataAdapter("SELECT * FROM AttendanceDatabase where EmpName = '" + txtEmpName.Text + "' AND (DateField BETWEEN '" + fromdt +"' AND '" + todt + "') ", con);
    DataTable = new DataTable();
    DataAdapter.Fill(DataTable);
    dgvAttendance.DataSource = DataTable;
    dgvAttendance.Columns[1].DefaultCellStyle.Format = "h:mm tt";
    dgvAttendance.Columns[2].DefaultCellStyle.Format = "h:mm tt";
}

Use your datetime format instead 'm/d/yyyy'.

If its not work. try

DateField >= @fromdt AND DateField < @todt

edited Oct 13 '15 at 05:10

answered Oct 13 '15 at 04:58

culture info cannot be found. how to fix this sir? – nethken Oct 13 '15 at 05:02
i have two datetime picker and i want to search a records by those two datetimepicker and employee name. – nethken Oct 13 '15 at 05:07
see updated answer. convert datetimepicker value as dateTimePicker1.Value.ToString("MM/dd/yyyy")); – Oct 13 '15 at 05:10
String was not recognized as a valid DateTime. – nethken Oct 13 '15 at 05:11
http://stackoverflow.com/questions/13577898/sql-time-difference-between-two-dates-result-in-hhmmss – Oct 13 '15 at 05:42

Shweta Pathak · Answer 2 · 2015-10-13T04:28:56.353

0

Try this

SELECT * FROM AttendanceDatabase 
WHERE EmpName = '" + txtEmpName.Text + "' 
AND 
(DateField BETWEEN '" + DateTime.Parse(StartDate) + "' 
    AND '" + DateTime.Parse(EndDate) + "')"

edited Oct 13 '15 at 04:28

answered Oct 13 '15 at 04:22

Shweta Pathak

775
1
5
21

it gives me data type mismatch criteria exception – nethken Oct 13 '15 at 04:30

score 0 · Accepted Answer · answered Oct 13 '15 at 05:33

public void LoadAttendance()
    {
        con.Open();
        cmd = new OleDbCommand("Select * from AttendanceDatabase WHERE EmpName = @EmpName and Date between @d1 and @d2", con);
        cmd.Parameters.AddWithValue("@EmpName", txtEmpName.Text);
        cmd.Parameters.AddWithValue("@d1", dtDate1.Value.Date);
        cmd.Parameters.AddWithValue("@d2", dtDate2.Value.Date);
        DataAdapter = new OleDbDataAdapter(cmd);
        DataTable = new DataTable();
        DataAdapter.Fill(DataTable);
        dgvAttendance.DataSource = DataTable;
        con.Close();
    }

here how i solved lol.

Search between two dates in database c#

3 Answers3