Avatar of RecipeDan
RecipeDan
 asked on

ListBox Selected Value

Hello:

I have a listbox that when I select multiple values I want the values to go into a database. I do not get any error from the script blow but the values are not being inserted.

<asp:ListBox ID="MenuAccess" SelectionMode="Multiple" runat="server" />

Open in new window

if (MenuAccess.Items.Count > 0)
        {
            for (int i = 0; i < MenuAccess.Items.Count; i++)
            {
                if (MenuAccess.Items[i].Selected)
                {
                    SqlConnection conn;
                    SqlCommand comm;
                    string connectionString1 = ConfigurationManager.ConnectionStrings["DataString"].ConnectionString;
                    conn = new SqlConnection(connectionString1);
                    comm = new SqlCommand("INSERT INTO Menus (LinkID) VALUES (@LinkID)", conn);
                    comm.Parameters.Add("@LinkID", System.Data.SqlDbType.Int);
                    comm.Parameters["@LinkID"].Value = MenuAccess.Items[i].Text;
                }
                try
                {
                    conn.Open();
                    comm.ExecuteNonQuery();
                }
                finally
                {
                    conn.Close();
                }
            }
        }

Open in new window

ASP.NETC#

Avatar of undefined
Last Comment
RecipeDan

8/22/2022 - Mon
guru_sami

1: Set the breakpoints and debug to see if right value is assigned to the LinkID parameter
2: move try/finally inside if
3: Add catch block
4: If you have access to SqlProfiler then try to setup a trace and see if the command is executed and the value passed.
kaufmed

Which method is the code you posted above contained within?
RecipeDan

ASKER
It seems to be a null value. However, the listbox does have values when the Page is loaded

      <option value="13">Change Password</option>
      <option value="14">New User</option>
      <option value="15">Delete Users</option>
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
guru_sami

Make sure your ListBox is not re-binded during postback...
i.e. place your listbox binding code in this block...
if(!Page.IsPostBack)
{ //Bind listbox
}

Open in new window

Or share your code-behind on how you are binding the data to listbox.
RecipeDan

ASKER
I made a few changes and it still does not work. Here is the current way I am binding the ListBox and trying to enter the values in a table.  

if (Page.IsPostBack == true)
        {
            string IsCurrent = Request.Form["PAns"];
            if (IsCurrent == "Y")
            {
                PAns.Visible = true;
                PYes.Visible = true;
                PNo.Visible = false;
                BindMenuAccessList();
            }
            else
            {
                PAns.Visible = true;
                PYes.Visible = false;
                PNo.Visible = true;
                BindMenuAccessList2();
            }
        }

Open in new window

protected void UserAdd_Click(object sender, EventArgs e)
    {
        if (MenuAccess.Items.Count > 0)
        {
            for (int i = 0; i < MenuAccess.Items.Count; i++)
            {
                if (MenuAccess.Items[i].Selected)
                {
                    SqlConnection conn1;
                    SqlCommand comm1;
                    string connectionString = ConfigurationManager.ConnectionStrings["DataString"].ConnectionString;
                    conn = new SqlConnection(connectionString);
                    comm = new SqlCommand("INSERT INTO ACC_Menus (LinkID) VALUES (@LinkID)", conn);
                    comm.Parameters.Add("@LinkID", System.Data.SqlDbType.Int);
                    comm.Parameters["@LinkID"].Value = MenuAccess.Items[i].Text;
                    try
                    {
                        conn.Open();
                        comm.ExecuteNonQuery();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }
    }

Open in new window

guru_sami

Why are you binding menulist on postback?
if (Page.IsPostBack == true)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
RecipeDan

ASKER
When I first started I was binding it on page load and it did not work.
RecipeDan

ASKER
It binds fine. When I load the page, the names and values are shows in the Listbox.
guru_sami

yes but when you rebind it on postback the selected values are lost...
so you mush put your code in
if(!Page.IsPostBack){ }
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
RecipeDan

ASKER
Put what code in this: if(!Page.IsPostBack){ } ? BindMenuAccessList()?
guru_sami

Yes... the code to bind your MenuAccess ListBox control
RecipeDan

ASKER
That worked...however I am getting a different error Invalid Input string. It has to do with this line
comm.Parameters["@LinkID"].Value = MenuAccess.Items[i].Text;

Open in new window


When I hard code a value like this:
comm.Parameters["@LinkID"].Value = "3";

It works fine.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
guru_sami

Are you getting any empty string for the Text?
Try to cast the value to Int32 like:

comm.Parameters["@LinkID"].Value = Convert.ToInt32(MenuAccess.Items[i].Text);

Open in new window

RecipeDan

ASKER
it is reading the first value and inserting it as many times I selected the values. So I select 5 items and the first value is 12. It is insertring the 12 five times into the table instead of 12, 7, 3, 6, 2
guru_sami

When you debug and check the value on each loop do you see right value being assigned to the LinkID parameter or not?
if not..
Try using the ListItem while looping instead of an Items collection...like shown here: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.listbox.aspx

foreach (ListItem li in ListBox1.Items)
          {
              if (li.Selected == true)
              {
                  msg += "<BR>" + li.Text + " is selected.";
              }
          }

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
RecipeDan

ASKER
When I do that the text shows (LinkName) instead of the actual value (Link ID).

Change Password is selected.
List Users is selected.
New User is selected.
Update User is selected.
Delete User is selected.

It should be

12 is selected.
7 is selected.
5 is selected.
4 is selected.
2 is selected.
RecipeDan

ASKER
Its binding right

            MenuAccess.DataValueField = "LinkID";
            MenuAccess.DataTextField = "LinkName";
            MenuAccess.DataBind();
            Menureader.Close();
ASKER CERTIFIED SOLUTION
guru_sami

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
RecipeDan

ASKER
It works great. Thank you for your help.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.