Reverse lookup for label ctrl.

timothy.farrell

I have a summary form used to display records in a database. I need to initiate some sort of reverse lookup for those columns that contain integer data so that the data is meaningful to the user. For instance: Say in the original form, that the user needed to choose a selection of users from a table. code-behind: if(!Page.IsPostBack) { SqlConnection Conn; SqlCommand Cmd; Conn = new SqlConnection("server=C099450d01;uid=sa;pwd=;database=tools"); Cmd = new SqlCommand("SELECT UID, LastName + ', ' + FirstName AS Name FROM Employee ORDER BY LastName", Conn); //Creator Conn.Open(); Creator.DataSource = Cmd.ExecuteReader(); Creator.DataTextField = "Name"; Creator.DataValueField = "UID"; Creator.DataBind(); Conn.Close(); } //The summary Label Ctrl. //Code-Behind to populate Ctrl: SqlConnection Conn; SqlCommand Cmd; Conn = new SqlConnection("server=C099450D01;uid=sa;pwd=;database=tools"); Cmd = new SqlCommand("SELECT LastName, FirstName from Employee WHERE id = @uid", Conn); Cmd.Parameters.Add("@uid", SqlDbType.Int).Value = Creator.SelectedItem.Value; Conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if(dr.Read()) Label2.Text = dr.GetString(0) + " " + dr.GetString(1); else Label2.Text = "Not found."; dr.Close(); The problem is that I get an error message saying that my connection is closed. Can someone help me out with this. Thanks alot!! Tim

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

timothy.farrell22y ago
Some code revision awarded some progress on this issue with one exception; for some reason I am only getting one value instead of multiple values if multiple creators are selected. I get multiple results for the selected text, just not the values. Any ideas??? Here is the code: SqlConnection conn = new SqlConnection("server=C099450d01;uid=**;pwd=**;database=tools"); SqlDataAdapter adapter = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand("Select UID, LastName +','+ FirstName From Employee Where UID = @UID ORDER BY LastName", conn); conn.Open(); foreach (ListItem myItem in Creator.Items) { if (myItem.Selected == true) { if (lblCreator.Text.Length > 0) { cmd.Parameters.Add("@UID", SqlDbType.Real).Value = Creator.SelectedItem.Value; lblCreator.Text += " - "; } lblCreator.Text += myItem.Text; } } conn.close();
timothy.farrell22y ago
I was able to get by the connection issue however; now I am presented with the following error: System.IndexOutOfRangeException: Index was outside the bounds of the array. Here is my modified code: SqlConnection conn = new SqlConnection("server=C099450d01;uid=sa;pwd=;database=tools"); SqlDataAdapter adapter = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand("Select id, LastName +','+ FirstName From Employee Where id = @id ORDER BY LastName", conn); cmd.Parameters.Add("@id", SqlDbType.Int).Value = Creator.SelectedItem.Value; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if(dr.Read()) lblCreator.Text = dr.GetString(1)+ "" + dr.GetString(2); else lblCreator.Text = "Not Found"; dr.Close(); conn.Close(); Thanks for any help you can provide. Sincerely, Tim