Solved

Display multiple selected Items from DDL in Label

Posted on 2004-08-04
15
245 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
  • 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now