Select statement in SQL Server

Question

I'm trying to display student individual attendance records between two dates. When teachers select two dates, the records will be displayed in GridView control. However, I have a problem which other students' attendance records are being displayed too. I can't show only a student attendance records.

This is my C# code, once teacher clicked on view buttuon:

protected void ButtonView_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString = "Data Source=DESKTOP-H7KQUT1;Initial Catalog=SAOS;Integrated Security=True";
    con.Open();

    string query1 = "SELECT * FROM attendance WHERE Date between '" + datepicker.Text + "'AND'" + datepicker1.Text + "'";
    SqlCommand sqlcomm = new SqlCommand(query1, con);

    SqlDataAdapter sda = new SqlDataAdapter(sqlcomm);
    DataTable dt = new DataTable();
    sda.Fill(dt);

    SqlDataReader sdr = sqlcomm.ExecuteReader();

    if (sdr.Read())
    {
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    else
    {
        Label7.Text = "No records found!!";
    }

    con.Close();
}

The problem is here:

 string query1 = "SELECT * FROM attendance WHERE Date between '" + datepicker.Text + "'AND'" + datepicker1.Text + "'";

I have a column called Student_ID as the foreign key in table attendance.

Answer 1

string query1 = "SELECT * FROM attendance WHERE Date between @startDate AND @endDate and Student_ID = @studentId";

SqlCommand sqlcomm = new SqlCommand(query1, con);
sqlcomm.Parameters.Add("@startDate", SqlDbType.Date).Value = datepicker.Text;
sqlcomm.Parameters.Add("@endDate", SqlDbType.Date).Value = datepicker1.Text;
sqlcomm.Parameters.Add("@studentId", SqlDbType.Int).Value = 100;