• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 964
  • Last Modified:

C# ASP.NET listbox value to SQL Table

This is the code that I use which retrieves the Last name and the username of each user from a group in AD:
 string connString = ConfigurationManager.ConnectionStrings["AppConnection"].ToString();
            SqlConnection conn = new SqlConnection(connString);
            
            int number = 0;


            try
            {
                PrincipalContext ctx = new PrincipalContext(ContextType.Domain, "Domain Name", "OU=Groups,OU=OU,DC=Domain Name,DC=local");
                GroupPrincipal grp = GroupPrincipal.FindByIdentity(ctx, IdentityType.SamAccountName, "Domain Users Group");
                string str;
                int increementer = 0;
                if (grp != null)
                {
                    foreach (Principal p in grp.GetMembers(false))
                    {
                        UserPrincipal theUser = p as UserPrincipal;

                        if (theUser != null)
                        {
                            if(increementer==0)
                            {
                       
                            //  if (theUser.IsAccountLockedOut())
                            str = (theUser.Name).ToString().Split(',')[0];
                            ListBox1.Items.Add(str);
                            ListBox1.Items.Add((theUser.SamAccountName).ToString());
                            SqlCommand comm = new SqlCommand("INSERT INTO [ITUsers] (LastName, Username) VALUES (@LastName, @Username)", conn);
                            conn.Open();
                            comm.Parameters.AddWithValue("@LastName", this.ListBox1.SelectedValue[increementer].ToString());
                            comm.Parameters.AddWithValue("@Username", this.ListBox1.SelectedValue[(increementer)+1].ToString());

                            comm.ExecuteNonQuery();
                            }
                       
                            else
                                if(increementer!=0)
                            {
                            increementer++;
                            //  if (theUser.IsAccountLockedOut())
                            str = (theUser.Name).ToString().Split(',')[0];
                            ListBox1.Items.Add(str);
                            ListBox1.Items.Add((theUser.SamAccountName).ToString());
                            SqlCommand comm = new SqlCommand("INSERT INTO [ITUsers] (LastName, Username) VALUES (@LastName, @Username)", conn);
                            conn.Open();
                            comm.Parameters.AddWithValue("@LastName", this.ListBox1.SelectedValue[increementer].ToString());
                            comm.Parameters.AddWithValue("@Username", this.ListBox1.SelectedValue[(increementer)+1].ToString());
                            }
                             
                        }

                    }
                }
                

            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);

            }
            finally
            {
               
                conn.Close();
                SqlConnection.ClearPool(conn);
         
            }

Open in new window


I am trying to use it so it imports the first 2 values of the list box into the SQL table. Because the first value is always Lastname then Username.....
But it does not do it. Please help!
0
pauledwardian
Asked:
pauledwardian
  • 4
  • 3
1 Solution
 
Ajay SharmaCommented:
You are trying to increment 1 to the value of the selected index's value, instead you should increment the selected index and then get the value from new index.

Replace the
comm.Parameters.AddWithValue("@Username", this.ListBox1.SelectedValue[(increementer)+1].ToString());

With

comm.Parameters.AddWithValue("@Username", this.ListBox1.Items[ListBox1.SelectedIndex + 1].Value.ToString());
0
 
pauledwardianAuthor Commented:
It still says "Index was outside the bounds of the array."

 string connString = ConfigurationManager.ConnectionStrings["AppConnection"].ToString();
            SqlConnection conn = new SqlConnection(connString);
            
            int number = 0;


            try
            {
                PrincipalContext ctx = new PrincipalContext(ContextType.Domain, "Domain Name", "OU=Groups,OU=OU,DC=Domain Name,DC=local");
                GroupPrincipal grp = GroupPrincipal.FindByIdentity(ctx, IdentityType.SamAccountName, "Domain Users Group");
                string str;
                int increementer = 0;
                if (grp != null)
                {
                    foreach (Principal p in grp.GetMembers(false))
                    {
                        UserPrincipal theUser = p as UserPrincipal;

                        if (theUser != null)
                        {
                            if(increementer==0)
                            {
                       
                            //  if (theUser.IsAccountLockedOut())
                            str = (theUser.Name).ToString().Split(',')[0];
                            ListBox1.Items.Add(str);
                            ListBox1.Items.Add((theUser.SamAccountName).ToString());
                            SqlCommand comm = new SqlCommand("INSERT INTO [ITUsers] (LastName, Username) VALUES (@LastName, @Username)", conn);
                            conn.Open();
                            comm.Parameters.AddWithValue("@LastName", this.ListBox1.SelectedValue[increementer].ToString());
                            comm.Parameters.AddWithValue("@Username", this.ListBox1.SelectedValue[(increementer)+1].ToString());

                            comm.ExecuteNonQuery();
                            }
                       
                            else
                                if(increementer!=0)
                            {
                            increementer++;
                            //  if (theUser.IsAccountLockedOut())
                            str = (theUser.Name).ToString().Split(',')[0];
                            ListBox1.Items.Add(str);
                            ListBox1.Items.Add((theUser.SamAccountName).ToString());
                            SqlCommand comm = new SqlCommand("INSERT INTO [ITUsers] (LastName, Username) VALUES (@LastName, @Username)", conn);
                            conn.Open();
                            comm.Parameters.AddWithValue("@LastName", this.ListBox1.SelectedValue[increementer].ToString());

comm.Parameters.AddWithValue("@Username", this.ListBox1.Items[ListBox1.SelectedIndex + 1].Value.ToString());
                            }
                             
                        }

                    }
                }
                

            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);

            }
            finally
            {
               
                conn.Close();
                SqlConnection.ClearPool(conn);
         
            }

Open in new window

0
 
Ajay SharmaCommented:
You will get this error when you select the last item in the listbox.
As you mentioned that the second value is always is the last name so there must be some item next to it.

Will you be able to send some sample data ??
0
Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

 
pauledwardianAuthor Commented:
Do you think there is any modifications that need to be done in the last post I made.
0
 
pauledwardianAuthor Commented:
this is how it looks like:

watts <-last name
jwatts  <- username
johnson <-last name
djohnson <- username
smith <-last name
jsmith <- username
.
.
.
Again, please look at my code to see if I did something wrong (which im sure I did but I cant figure it out. Maybe in the loop!!!)
0
 
Ajay SharmaCommented:
Hi, as you adding only the text values in the ListBox, so you should consider only the SelectedItem.Text property instead of SelectedValue

Instead of
comm.Parameters.AddWithValue("@LastName", this.ListBox1.SelectedItem[increementer].ToString());
comm.Parameters.AddWithValue("@Username", this.ListBox1.Items[ListBox1.SelectedIndex + 1].Value.ToString());
 
Use
comm.Parameters.AddWithValue("@LastName", this.ListBox1.SelectedItem.Text.ToString());
comm.Parameters.AddWithValue("@Username", this.ListBox1.Items[ListBox1.SelectedIndex + 1].Text.ToString());
0
 
informaniacCommented:
comm.Parameters.AddWithValue("@Username", ListBox1.Items[ListBox1.Items.Count.Equals(ListBox1.SelectedIndex + 1) ? ListBox1.SelectedIndex : ListBox1.SelectedIndex + 1].Value);
 

Open in new window

0
 
pauledwardianAuthor Commented:
Thanks anyways!!!
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now