Solved

Display multiple selected Items from DDL in Label

Posted on 2004-08-04
15
251 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

832 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