Display multiple selected Items from DDL in Label

I have a webform that contains a dropdownlist with a list of users.  Later on in the form I have a confirmation panel in which users can confirm the accuracy of their data before submitting.  The problem I am having is that the label control is not listing all of the users selected from the dropdownlist control inteh webform.

Here is the code for the ddl:
<asp:listbox id="ActiveUsers" runat="server" SelectionMode="Multiple" Rows="1"></asp:listbox>

Here is the code to populate the control from code-behind:
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);
Conn.Open();
ActiveUsers.DataSource = Cmd.ExecuteReader();
ActiveUsers.DataTextField = "Name";
ActiveUsers.DataValueField = "Name";
ActiveUsers.DataBind();
Conn.Close();

Here is the code to populate the Label control in code-behind:
lblActUsr.Text = ActiveUsers.SelectedItem.Value

Can someone tell me why only one selection is appearing in the label control and how I can fix it.

Thank you.

Tim
TMF123Asked:
Who is Participating?
 
ibostConnect With a Mentor Commented:
I am assuming you want to submit each value to the db separately, like:
Peter
Paul
Mary

do you instead want to submit the same string that appears in the label?
Peter, Paul, Mary

If that is the case, then the code would be (note moved the cmd.execute out of the foreach loop and changed the assignment to +=):

cmd.Parameters.Add(new SqlParameter("@Active", ""));
foreach (ListItem myItem in ActiveUsers.Items)
{
   if (myItem.Selected == true)
      {
          cmd.Parameters["@Active"].Value += myItem.Text;
      }
}

cmd.ExecuteNonQuery();
0
 
ibostCommented:
Haven't done web stuff in a while, but try this:

foreach (ListItem myItem in ActiveUsers.Items)
{

   if (myItem.Selected == true)
   {

      if (lblActUsr.Text.Length > 0)
      {
         lblActUsr.Text += ", ";
      }

      lblActUsr.Text += myItem.Text;

   }

}


Basically, if the list box is a multi-select, then SelectedItem only gives you the lowest index selected.


-Ian
0
 
ibostCommented:
That is, do the above INSTEAD of this:
lblActUsr.Text = ActiveUsers.SelectedItem.Value
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
TMF123Author Commented:
Ian,

Great job!!!  I am now able to fill teh label controls with all selected.  However; it would appear that only the lowest id (as you mentioned) is being passed to the database.  Is there a way to implement your script within the cmd statement?

cmd.Parameters.Add(new SqlParameter("@Active", ActiveUsers.SelectedItem.Value));

Thank you for your help.

Sincerely,

Tim
0
 
ibostCommented:
you will have to implement a similar loop and pass each one to your cmd then execute it.

something like this, perhaps:



cmd.Parameters.Add(new SqlParameter("@Active", ""));

myConnection.Open();

foreach (ListItem myItem in ActiveUsers.Items)
{

   if (myItem.Selected == true)
   {
      cmd.Parameters["@Active"].Value = myItem.Text
      cmd.ExecuteNonQuery();
   }

}

myConnection.Close();




-Ian
0
 
TMF123Author Commented:
I made some modifications (dealing with the open and close since I already have those in place):

cmd.Parameters.Add(new SqlParameter("@Active", ""));
foreach (ListItem myItem in ActiveUsers.Items)
   {
      if (myItem.Selected == true)
         {
      cmd.Parameters["@Active"].Value = myItem.Text;
         }
    }

What I get is the last selected integer.  Any ideas why?

Thanks,

Tim
0
 
ibostCommented:
inside the if statement, just after you set the cmd.Parameters["@Active"].Value = myItem.Text,
you need to execute your query.  You need to execute the query for each item that is selected in the drop down list.

cmd.Parameters.Add(new SqlParameter("@Active", ""));
foreach (ListItem myItem in ActiveUsers.Items)
   {
      if (myItem.Selected == true)
         {
             cmd.Parameters["@Active"].Value = myItem.Text;
             cmd.ExecuteNonQuery();
         }
    }




-Ian
0
 
TMF123Author Commented:
In your opinion, if someone wanted to query against this column which method would you suggest?
0
 
TMF123Author Commented:
Option 1:
It would appear that the use of the cmdExecuteReader(); prevents subsequent values from being sent to the database.  I get an error indicating: 'Procedure 'InsertTool' expects parameter '@Version', which was not supplied.'

Option2:
I get the following error:
Procedure or function InsertTool has too many arguments specified.
InsertTool is my SPROC.

HTH,

Tim

0
 
ibostCommented:
I would definitely go with the first one (one name per insert).

As for the error, have you set the @Version parameter?

Please post your SPROC as well as the full code that calls it.

-Ian
0
 
TMF123Author Commented:
Here is the full code that calls the SPROC:
public void Insert_Click(object sender, System.EventArgs e)
{
Page.Validate();
SqlConnection conn = new SqlConnection("server=C099450d01;uid=sa;pwd=;database=tools");
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("InsertTool", conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.Parameters.Add(new SqlParameter("@ToolName", ToolName.Text));
cmd.Parameters.Add(new SqlParameter("@Creator", Creator.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Platform", Platform.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Vendor", Vendor.Text));
cmd.Parameters.Add(new SqlParameter("@SubPlatform", Subplatform.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Submitter", Submitter.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Finders", Finder.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@LTD", LTD.Text));
cmd.Parameters.Add(new SqlParameter("@JobArea", JobArea.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Func", Func.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Owners", Owner.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Active", ""));
cmd.Parameters.Add(new SqlParameter("@Active", ""));
foreach (ListItem myItem in ActiveUsers.Items)
{
if (myItem.Selected == true)
{
cmd.Parameters["@Active"].Value += myItem.Text;
}
}
cmd.Parameters.Add(new SqlParameter("@Version", SqlDbType.Decimal, 5, ParameterDirection.Input, false, 9, 2, "Version", DataRowVersion.Current, null));
cmd.Parameters["@Version"].Value = Decimal.Parse (Version.Text);
cmd.Parameters.Add(new SqlParameter("@Build", SqlDbType.Decimal, 5, ParameterDirection.Input, false, 9, 2, "Build", DataRowVersion.Current, null));
cmd.Parameters["@Build"].Value = Decimal.Parse (Build.Text);
cmd.Parameters.Add(new SqlParameter("@CWSTD", Cwstd.SelectedValue));
cmd.Parameters.Add(new SqlParameter("@Status", Status.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Cost", SqlDbType.Decimal, 5, ParameterDirection.Input, false, 9, 2, "Cost", DataRowVersion.Current, null));
cmd.Parameters["@Cost"].Value = Decimal.Parse (Cost.Text);
cmd.Parameters.Add(new SqlParameter("@Notes", Notes.Text));
cmd.Parameters.Add(new SqlParameter("@Keywords", Keywords.Text));
cmd.Parameters.Add(new SqlParameter("@Links", Links.Text));
cmd.Parameters.Add(new SqlParameter("@Paths", Paths.Text));
cmd.ExecuteNonQuery();
conn.Close();
}

Here is the SPROC:
ALTER PROCEDURE dbo.InsertTool
(
@ToolName [nvarchar] (250),
@Creator [nvarchar] (250),
@Platform [nvarchar] (250),
@Vendor [nvarchar] (250),
@Subplatform [nvarchar] (250),
@Submitter [nvarchar] (250),
@Finders [nvarchar] (250),
@LTD [nvarchar] (50),
@JobArea [nvarchar] (250),
@Func [nvarchar] (250),
@Owners [nvarchar] (250),
@Active [nvarchar] (250),
@Version [numeric] (9,2),
@Build [numeric] (9,2),
@CWSTD [nvarchar] (50),
@Status [nvarchar] (250),
@Cost [numeric] (9,2),
@Notes [nvarchar] (250),
@Keywords [nvarchar] (250),
@Links [nvarchar] (250),
@Paths [nvarchar] (250)
)
 AS
Insert into [ToolDB]
(
[ToolName],
[Creator],
[Platform],
[Vendor],
[Subplatform],
[Submitter],
[Finders],
[LTD],
[JobArea],
[Func],
[Owners],
[Active],
[Version],
[Build],
[CWSTD],
[Status],
[Cost],
[Notes],
[Keywords],
[Links],
[Paths]
)
Values
(
@ToolName, @Creator, @Platform, @Vendor, @Subplatform, @Submitter, @Finders, @LTD, @JobArea, @Func, @Owners, @Active, @Version, @Build, @CWSTD, @Status, @Cost, @Notes, @Keywords, @Links, @Paths)
0
 
ibostCommented:
1.  You added @Active parameter twice... don't know if that is affecting anything
Try this (moved loop to end of eventhandler and removed duplicate @Active):



public void Insert_Click(object sender, System.EventArgs e)
{
Page.Validate();
SqlConnection conn = new SqlConnection("server=C099450d01;uid=sa;pwd=;database=tools");
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand("InsertTool", conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.Parameters.Add(new SqlParameter("@ToolName", ToolName.Text));
cmd.Parameters.Add(new SqlParameter("@Creator", Creator.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Platform", Platform.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Vendor", Vendor.Text));
cmd.Parameters.Add(new SqlParameter("@SubPlatform", Subplatform.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Submitter", Submitter.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Finders", Finder.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@LTD", LTD.Text));
cmd.Parameters.Add(new SqlParameter("@JobArea", JobArea.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Func", Func.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Owners", Owner.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Active", ""));
cmd.Parameters.Add(new SqlParameter("@Version", SqlDbType.Decimal, 5, ParameterDirection.Input, false, 9, 2, "Version", DataRowVersion.Current, null));
cmd.Parameters["@Version"].Value = Decimal.Parse (Version.Text);
cmd.Parameters.Add(new SqlParameter("@Build", SqlDbType.Decimal, 5, ParameterDirection.Input, false, 9, 2, "Build", DataRowVersion.Current, null));
cmd.Parameters["@Build"].Value = Decimal.Parse (Build.Text);
cmd.Parameters.Add(new SqlParameter("@CWSTD", Cwstd.SelectedValue));
cmd.Parameters.Add(new SqlParameter("@Status", Status.SelectedItem.Value));
cmd.Parameters.Add(new SqlParameter("@Cost", SqlDbType.Decimal, 5, ParameterDirection.Input, false, 9, 2, "Cost", DataRowVersion.Current, null));
cmd.Parameters["@Cost"].Value = Decimal.Parse (Cost.Text);
cmd.Parameters.Add(new SqlParameter("@Notes", Notes.Text));
cmd.Parameters.Add(new SqlParameter("@Keywords", Keywords.Text));
cmd.Parameters.Add(new SqlParameter("@Links", Links.Text));
cmd.Parameters.Add(new SqlParameter("@Paths", Paths.Text));

foreach (ListItem myItem in ActiveUsers.Items)
{
   if (myItem.Selected == true)
   {
      cmd.Parameters["@Active"].Value = myItem.Text;
      cmd.ExecuteNonQuery();
   }
}

conn.Close();
}





-Ian
0
 
ibostCommented:
looking at your sp I think maybe it would be better to go w/ option 2.  I think if you just remove the duplicate line:

cmd.Parameters.Add(new SqlParameter("@Active", ""));



You'll be alright.
0
 
TMF123Author Commented:
Ian,

I hate it when I do stuipd stuff like that.  

Ok,

The data submitted just fine.  What I noticed about the data though is that the record is inserted as:
Kilroy, NancyLittle, MarkNichols, TomStevens, Mark

I take it that querying against a column with data like this won't return expected results will it?

Thank you very much for your help on this.

Sincerely,

Tim
0
 
ibostCommented:
No I don't think the results will be as expected....

I'm not sure what the purpose of "@Active" is...?  Is it the people who have checked out the tool?  Actively using it?

Assuming you have a table called ToolDB... I am guessing  you have a classic M:M relationship between tools and users, as in: many users can be using the same tool, and a user can be using many tools.

In this case, You have your ToolDB table, then you would want a UserDB table.

Both tables will need a primary key, and then you will have a 3rd table to link them, maybe called ToolUsersDB.  The primary key of ToolUsersDB is the combination of ToolDB_ID and UserDB_ID (both foreign keys in the ToolUsersDB table).

-Ian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.