Retrieving ID from SQL Server 2008 table to asp.net site and then inserting the ID into another table

I have this codes:

part of .cs file for Upload page:

protected void Page_Load(object sender, EventArgs e)
{
        BtnImport1.Visible = false;
        Cancel.Visible = false;
}

private string ErrorMessage(string input)
{
     if (!string.IsNullOrEmpty(input))
        return input;

     BtnImport1.Visible = false; 
     return "No value entered!";
}

protected void btnUpload_Click(object sender, EventArgs e)
{开发者_如何学Python
    string strFileNameOnServer = fileUpload.PostedFile.FileName;
    string fileExt =
        System.IO.Path.GetExtension(fileUpload.FileName);

    if (fileUpload.PostedFile != null && fileExt == ".csv")
    {
        try
        {
           fileUpload.PostedFile.SaveAs(Server.MapPath("~/uploads"));
           Label1.Text = "File name: " +
                          fileUpload.PostedFile.FileName + "<br>" +
                          fileUpload.PostedFile.ContentLength + " kb<br>" +
                          "Content type: " +
                          fileUpload.PostedFile.ContentType;
        }
        catch (Exception ex)
        {
           Label1.Text = "Error saving <b>" + strFileNameOnServer + "</b><br>.  " + ex.Message;
        }
        BtnImport1.Visible = true;
        Cancel.Visible = true;
        fileUpload.Visible = false;
        btnUpload.Visible = false;
     }
     else
     {
        Label1.Text = "Error - a file name must be specified/only csv files are allowed";
        return;
     }        
}

protected void BtnImport1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(@"Data Source=localhost\SQLEXPRESS;Initial Catalog=PSeminar;Integrated Security=true;Trusted_Connection=Yes;MultipleActiveResultSets=true");
    StreamReader Sr = new StreamReader(Server.MapPath("~/uploads"));
    string line;
    while ((line = Sr.ReadLine()) != null)
    {
        String[] tokens = line.Split(',');
        const string SQL = "INSERT INTO Attendance ([GuestID], [IC_No], [Grouping], [Remarks], [GuestName], [Class_Group], [Staff], [Attendance_Parents_Only], [Registration], [Event_ID] ) VALUES (@GuestID, @IC_No, @Grouping, @Remarks, @GuestName, @Class_Group, @Staff, @Attendance_Parents_Only, @Registration, @Event_ID)";
        SqlCommand cmd = new SqlCommand(SQL, con);
        cmd.Parameters.AddWithValue("@GuestID", tokens[0]);
        cmd.Parameters.AddWithValue("@IC_No", tokens[1]);
        cmd.Parameters.AddWithValue("@Grouping", tokens[2]);
        cmd.Parameters.AddWithValue("@Remarks", tokens[3]);
        cmd.Parameters.AddWithValue("@GuestName", tokens[4]);
        cmd.Parameters.AddWithValue("@Class_Group", tokens[5]);
        cmd.Parameters.AddWithValue("@Staff", tokens[6]);
        cmd.Parameters.AddWithValue("@Attendance_Parents_Only", tokens[7]);
        cmd.Parameters.AddWithValue("@Registration", tokens[8]);
        cmd.Parameters.AddWithValue("@EventID", tokens[9]);

        con.Open();
        cmd.ExecuteNonQuery();  
        con.Close();
     }
     Response.Redirect("Main.aspx");
  }

So I want to get the EventID from SQL Server 2008 table called Event, and then inserting it into another table called Attendance as stated at BtnImport1_Click. There are the EventID and EventName columns in the table, called Event. I have queried string the EventName from the a ListBox in another .cs page. The query string stores the EventName:

protected void up_Click(object sender, EventArgs e)
{
    foreach (ListItem li in ListBox1.Items)
    {
        //if the item is selected in the listboxs
        if (li.Selected)
        {
           Response.Redirect("Upload.aspx?ename=" + this.ListBox1.SelectedItem);
        }
    }

Now, I do not know how to match the stored EventName with the EventID and also inserting the EventID into the Event table, together with the other information at BtnImport1_Click.

Please help, I've been cracking my head since a few days already =/

If you have a table Event with two columns EventID and EventName, and you do have the EventName, then you can use a query like

SELECT EventID
FROM dbo.Event
WHERE EventName = @EventName

in your code. Mind you: this will only reliably work if all event names are unique - if you happen to have two events by the same name, you cannot distinguish these two anymore.....

So really: you shouldn't be using / storing the event name - you should have the EventID at hand when you do work in your UI - the ID is the one thing that uniquely and clearly identifies an event.

So what you really need to do is change your application so that you have the EventID available when the user makes a choice in your ASP.NET UI. Most often, you can specify both a display value for the user (the event's name), as well as a key value that would be your EventID. When the user picks an event from your dropdown list, the user picks a name - but you'll be able to grab the EventID which is your key to your data.

Update: in order to get both EventID and EventName into your listbox, you need to use a class Event that contains both values. Fill that class from the database and bind it to the listbox, making EventName the DataTextField, and EventID the DataValueField. Then, the event name will be shown, but when the user selects an item, you can access the entire item - including it's EventID