Solved

Display multiple selected Items from DDL in Label

Posted on 2004-08-04
15
258 Views
Last Modified: 2010-04-15
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
0
Comment
Question by:TMF123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 10

Expert Comment

by:ibost
ID: 11722135
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
 
LVL 10

Expert Comment

by:ibost
ID: 11722157
That is, do the above INSTEAD of this:
lblActUsr.Text = ActiveUsers.SelectedItem.Value
0
 

Author Comment

by:TMF123
ID: 11728157
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 10

Expert Comment

by:ibost
ID: 11728266
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
 

Author Comment

by:TMF123
ID: 11728344
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
 
LVL 10

Expert Comment

by:ibost
ID: 11728440
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
 
LVL 10

Accepted Solution

by:
ibost earned 500 total points
ID: 11728627
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
 

Author Comment

by:TMF123
ID: 11729505
In your opinion, if someone wanted to query against this column which method would you suggest?
0
 

Author Comment

by:TMF123
ID: 11729864
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
 
LVL 10

Expert Comment

by:ibost
ID: 11729919
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
 

Author Comment

by:TMF123
ID: 11730195
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
 
LVL 10

Expert Comment

by:ibost
ID: 11730251
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
 
LVL 10

Expert Comment

by:ibost
ID: 11730293
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
 

Author Comment

by:TMF123
ID: 11730435
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
 
LVL 10

Expert Comment

by:ibost
ID: 11730705
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question