Solved

Bug in ListBoxControl ???

Posted on 2004-09-10
14
237 Views
Last Modified: 2010-04-15
I have a webform that uses a listboxcontrol to display a list of users from a table.  Once the form is submitted I would like to have the values passed to the database.  The problem is that only the first int value of the selected users is passing through to the database.  

Can someone evaluate my code and pin point the problem for me.

Listboxcontrol:
<asp:listbox id="Creator" runat="server" SelectionMode="Multiple" Rows="1"></asp:listbox>

CodeBehind:
if(!Page.IsPostBack)
{
SqlConnection Conn;
SqlCommand Cmd;
Conn = new SqlConnection("server=C099450d01;uid=sa;pwd=;database=tools");
Cmd = new SqlCommand("SELECT ID, LastName + ', ' + FirstName AS Name FROM Employee ORDER BY LastName", Conn);
//Creator
Conn.Open();
Creator.DataSource = Cmd.ExecuteReader();
Creator.DataTextField = "Name";
Creator.DataValueField = "ID";
Creator.DataBind();
Conn.Close();

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

I can't seem to get all of the values of those selected.

Please Help.

Sincerely.

Tim
0
Comment
Question by:TMF123
  • 6
  • 5
14 Comments
 
LVL 19

Expert Comment

by:drichards
ID: 12026987
I assume then that you have a multi-select listbox?  If so, you need to iterate through the selections.  

    for ( int ii = Creator.SelectedIndex; ii < Creator.Items.Count; ii++)
    {
        if ( Creator.Items[ii].Selected )
        {
                // Set the SQL parameter value and insert into database
        }
    }
0
 
LVL 19

Expert Comment

by:drichards
ID: 12026999
You could also iterate from 0, but the SelectedIndex property gives you the lowest selected index, so nothing less than that will be selected.
0
 

Author Comment

by:TMF123
ID: 12028676
Unfortunately, I am still only getting one id for the value.

for ( int ii = Creator.SelectedIndex; ii < Creator.Items.Count; ii++)
  {
       if ( Creator.Items[ii].Selected )
           {
      cmd.Parameters.Add(new SqlParameter("@Creator", Creator.SelectedItem.Value));
           }
   }

I am figuring that I there is a problem with the Creator.SelectedItem.Value part of the above script.

Thank you for your help.
0
Industry Leaders: 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!

 
LVL 19

Expert Comment

by:drichards
ID: 12031281
What are the parameters for?  Is the command expecting a single parameter and just using the first or last one in the list?  As a test, set a breakpiont inside the for loop and see how many times you hit it.  My guess is that you are getting all the selected values but you are not processing them correctly.
0
 

Author Comment

by:TMF123
ID: 12043397
I put a break on the cmd.Paramaters line.

What I am finding is that if I mouse over the following line:
for ( int ii = Creator.SelectedIndex; ii < Creator.Items.Count; ii++)
the Creator.SelectedIndex = 13

If I mouse the this line:
cmd.Parameters.Add(new SqlParameter("@Creator", Creator.SelectedItem.Value));
the Creator.SelectedItem.Value = 15

So something isn't right.

Do you know how I can fix this issue?

Thank you for your comments!!

Sincerely,

Tim
0
 

Author Comment

by:TMF123
ID: 12076570
drichards,

Oh no.  I've lost you or you've given up on me.  Please come back!

From your last post I have found that the count = 43 which is the number of records in the table.  What I would determine to be happening is that I am retreiveing all of the records from the creator table, the problem is that I am unable to create a second list with just the values of those selected from the listbox control.

I certainly can't believe that what I am attempting to do is foreign to web programming.  I thought that was the sole purpose of this type of control, to introduce one to many relationships between tables etc..

If you get a chance to reply I would greatly appreciate your comments.

Sincerely,

Tim
0
 
LVL 19

Expert Comment

by:drichards
ID: 12080622
Sorry, I've been out of town for a few days.

I think your problem is here:
-----------------------------
for ( int ii = Creator.SelectedIndex; ii < Creator.Items.Count; ii++)
  {
       if ( Creator.Items[ii].Selected )
       {
           cmd.Parameters.Add(new SqlParameter("@Creator", Creator.SelectedItem.Value));  // <-- you create many parameters.
       }
   }
-------------------------------

What is the command?  I doubt it takes 43 parameters.  You should probably be creating a single parameter before going into the loop and then on each loop iteration you set the parameter and execute the command.  I'll put together some code for you if you need it.

Do this test to confirm:  Check the first and last parameter values added.  You said the first one was value=15 in a previous comment.  Also look at the value the last time in the loop.  I'm betting that the command is executed with one of these two values.
0
 

Author Comment

by:TMF123
ID: 12172572
drichards,

Thank you for your patience.

I discovered that I was in fact getting the values.  Once I put a break in teh code and added the control to the watchlist, I could see the selected values in the Creator.SelectedItem.Value.

The problem I am faced with now is how do I get those values to insert into a seperate table so that the values are on a per row basis?

Like this:
ToolUsr_tbl
id             empid           toolid
1                 15               25
2                 17               25
3                 19               25
4                 25               32

That way I can query against the field.

Thank you for your time.

Regards,

Tim
0
 
LVL 19

Expert Comment

by:drichards
ID: 12176054
I'm not sure aht you're trying yo do here.  Where do 'id', 'empid', and 'toolid' come from?  Is 'id' just an autonumber column in the table?  How are any of these related to the selections in the listbox?  I assume it has something to do with the command you're trying to execute.
0
 

Author Comment

by:TMF123
ID: 12179157
I am sorry for the confusion.  Let me start again.

I have a webform that contains a listbox control set for multiple select.  Once the form is complete, then the data is inserted into the Tools database.  However; because the listbox control is allowing for multiple selection, the data submitted from the form for this control must be inserted into a seperate table and a foreign key relationship established.  The reason for this is because if you insert a string of data into a single column within a table, you loose the ability to search against that column.  

In other words, say the user has selected the following names in the control:
Kirk,Spock,Kerry,Bush.
If you query against this column select * where @name = spock you will get nothing in return.  

Therefore I am in search of a method in which I can get each of the values entered into a seperate table at the time the form is submitted with a foreign key relationship to the id of the primary table Tools.

Does this make sense?  I guess I'm just looking for someone to tell me how they handle this scenario.  How do you script a listbox control with multiple select and save the data in a table that is in searchable format?

Thank you so much for not giving up on me.  Although I am begining to wonder if I'm going out of my mind in what I am trying to accomplish here. ;)

Sincerely,

Tim
0
 
LVL 19

Accepted Solution

by:
drichards earned 500 total points
ID: 12181333
You have two options here:

1) In the loop that reads all the selections, generate a call to the database on each iteration to insert the current value.

2) Create a dataset and create rows based on the values - one row for each iteration of the selection loop.  Then do one database update at the end using the dataset.

The first option is simpler to code, so you might want to start there.You would make an insert command and either format the text each time through the loop or create a command parameter and set the parameter value each time through the loop.  Either way, you want to also execute the command each iteration of the loop.  And don't create new parameters each time - just set the value of the existing one.  Before entering the loop, create the parameter, and then just set its value on each iteration.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

Suggested Solutions

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 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