Assigning Values from a dropdown list.

[evanguiton]

I am currently doing a college project and I can not get a certain area to work. I want to call values from my SQL DB to my asp web screen. I want the values to correspond with a Date and a Staff Number Dropdown List.

I have tried several different combinations of data sources but none work.

I have attached an image below to help.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Web.Configuration;
using System.Data.Common;
using System.Data.SqlClient;

public partial class Default2 : System.Web.UI.Page
{
    string Staff_Number;
    string WeekEnd;
    string Date1;
    string Date2;
    string Date3;
    string Date4;
    string Date5;
    string Date6;
    string Date7;
    string Start1;
    string Start2;
    string Start3;
    string Start4;
    string Start5;
    string Start6;
    string Start7;
    string Finish1;
    string Finish2;
    string Finish3;
    string Finish4;
    string Finish5;
    string Finish6;
    string Finish7;
    string Lunch1;
    string Lunch2;
    string Lunch3;
    string Lunch4;
    string Lunch5;
    string Lunch6;
    string Lunch7;
    string Normal1;
    string Normal2;
    string Normal3;
    string Normal4;
    string Normal5;
    string Normal6;
    string Normal7;
    string OT1;
    string OT2;
    string OT3;
    string OT4;
    string OT5;
    string OT6;
    string OT7;
    string OT8;
    string OT9;
    string OT10;
    string OT11;
    string OT12;
    string OT13;
    string OT14;
    string Comment;
    string Name;
    string Total_NH;
    string TotalOT1;
    string TotalOT2;



    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        WeekEnd = (DropDownList1.Text);
        Staff_Number = (DropDownList2.Text);
        AssignValues();

        txtStaffName.Text = Name;
        txtDate1.Text = Date1;
        txtDate2.Text = Date2;
        txtDate3.Text = Date3;
        txtDate4.Text = Date4;
        txtDate5.Text = Date5;
        txtDate6.Text = Date6;
        txtDate7.Text = Date7;

        txtStart1.Text = Start1;
        txtStart2.Text = Start2;
        txtStart3.Text = Start3;
        txtStart4.Text = Start4;
        txtStart5.Text = Start5;
        txtStart6.Text = Start6;
        txtStart7.Text = Start7;

        txtFinish1.Text = Finish1;
        txtFinish2.Text = Finish2;
        txtFinish3.Text = Finish3;
        txtFinish4.Text = Finish4;
        txtFinish5.Text = Finish5;
        txtFinish6.Text = Finish6;
        txtFinish7.Text = Finish7;

        txtLunch1.Text = Lunch1;
        txtLunch2.Text = Lunch2;
        txtLunch3.Text = Lunch3;
        txtLunch4.Text = Lunch4;
        txtLunch5.Text = Lunch5;
        txtLunch6.Text = Lunch6;
        txtLunch7.Text = Lunch7;

        txtNormal1.Text = Normal1;
        txtNormal2.Text = Normal2;
        txtNormal3.Text = Normal3;
        txtNormal4.Text = Normal4;
        txtNormal5.Text = Normal5;
        txtNormal6.Text = Normal6;
        txtNormal7.Text = Normal7;

        txtOT1.Text = OT1;
        txtOT2.Text = OT2;
        txtOT3.Text = OT3;
        txtOT4.Text = OT4;
        txtOT5.Text = OT5;
        txtOT6.Text = OT6;
        txtOT7.Text = OT7;
        txtOT8.Text = OT8;
        txtOT9.Text = OT9;
        txtOT10.Text = OT10;
        txtOT11.Text = OT11;
        txtOT12.Text = OT12;
        txtOT13.Text = OT13;
        txtOT14.Text = OT14;

        txtTotalNH.Text = Total_NH;
        txtTotalOT1.Text = TotalOT1;
        txtTotalOT2.Text = TotalOT2;

    }

    private void AssignValues()
    {

        SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

        //Execute SQL Statement
        SqlCommand comm = new SqlCommand("SELECT * FROM Hours WHERE [WeekEnd] = @WeekEnd", conn);

        //Set Parameters



        comm.Parameters.Add("@WeekEnd", SqlDbType.VarChar);
       // comm.Parameters.Add("@Staff_Number", SqlDbType.VarChar);


        comm.Parameters["@WeekEnd"].Value = WeekEnd;
       // comm.Parameters["@Staff_Number"].Value = Staff_Number;



        //Open Connection
        conn.Open();

        //Start SQL Reader
        SqlDataReader reader = comm.ExecuteReader();

        while (reader.Read())
        {
            Name = (string)reader["Name"];
            Date1 = (string)reader["Date1"];
            Date2 = (string)reader["Date2"];
            Date3 = (string)reader["Date3"];
            Date4 = (string)reader["Date4"];
            Date5 = (string)reader["Date5"];
            Date6 = (string)reader["Date6"];
            Date7 = (string)reader["Date7"];

            Start1 = (string)reader["Start1"];
            Start2 = (string)reader["Start2"];
            Start3 = (string)reader["Start3"];
            Start4 = (string)reader["Start4"];
            Start5 = (string)reader["Start5"];
            Start6 = (string)reader["Start6"];
            Start7 = (string)reader["Start7"];

            Finish1 = (string)reader["Finish1"];
            Finish2 = (string)reader["Finish2"];
            Finish3 = (string)reader["Finish3"];
            Finish4 = (string)reader["Finish4"];
            Finish5 = (string)reader["Finish5"];
            Finish6 = (string)reader["Finish6"];
            Finish7 = (string)reader["Finish7"];

            Lunch1 = (string)reader["Lunch1"];
            Lunch2 = (string)reader["Lunch2"];
            Lunch3 = (string)reader["Lunch3"];
            Lunch4 = (string)reader["Lunch4"];
            Lunch5 = (string)reader["Lunch5"];
            Lunch6 = (string)reader["Lunch6"];
            Lunch7 = (string)reader["Lunch7"];

            Normal1 = (string)reader["Normal1"];
            Normal2 = (string)reader["Normal2"];
            Normal3 = (string)reader["Normal3"];
            Normal4 = (string)reader["Normal4"];
            Normal5 = (string)reader["Normal5"];
            Normal6 = (string)reader["Normal6"];
            Normal7 = (string)reader["Normal7"];

            OT1 = (string)reader["OT1"];
            OT2 = (string)reader["OT2"];
            OT3 = (string)reader["OT3"];
            OT4 = (string)reader["OT4"];
            OT5 = (string)reader["OT5"];
            OT6 = (string)reader["OT6"];
            OT7 = (string)reader["OT7"];
            OT8 = (string)reader["OT8"];
            OT9 = (string)reader["OT9"];
            OT10 = (string)reader["OT10"];
            OT11 = (string)reader["OT11"];
            OT12 = (string)reader["OT12"];
            OT13 = (string)reader["OT13"];
            OT14 = (string)reader["OT14"];

            Comment = (string)reader["Comment"];
            Total_NH = (string)reader["Total_NH"];
            TotalOT1 = (string)reader["TotalOT1"];
            TotalOT2 = (string)reader["TotalOT2"];

        }
        //Close Reader
        reader.Close();

        //Close Connection
        conn.Close();
    }
   }