Insert Multiple Rows from DropDown to SQL

Question

I have 6 DropDownList that have the following values;

ABC,DEF,GHI,JKL,MNO,PQR

I want to insert the values from ddl1, ddl2, ddl3, ddl4, ddl5 and ddl6 into my SQL table by taking the value from each dropdownlist.

I've currently got the following but need to adapt it to insert the values from each individual dropdownlist rather than the same value:

SqlConnection sqlCon = new SqlConnection("Data Source=***; User ID=***; MultipleActiveResultSets=True; Password=***; Initial Catalog=PMRDA;");

try
{
    sqlCon.Open();

    foreach (string str in AllOptions)
    {
        SqlCommand cmd = new SqlCommand("INSERT INTO lboard(eventType, date, college) values (@eventType, GETDATE(), @college)", sqlCon);

        SqlParameter eventType = cmd.Parameters.Add("@eventType", SqlDbType.VarChar);
        SqlParameter college = cmd.Parameters.Add("@college", SqlDbType.Char);

        eventType.Value = ddlEventType.SelectedItem.Value;
        college.Value = ddl5.SelectedItem.Value;

        cmd.ExecuteNonQuery();
    }

    LabelStatus.Text = "Submitted Succesfully";
}
catch(Exception ex)
{
    LabelStatus.Text = ex.Message;
}
finally
{
    sqlCon.Close();
}

By dropdown list you mean a combobox right?

Peter H
– Peter H

2014-02-05 11:52:08 +00:00
Commented Feb 5, 2014 at 11:52 — Peter H
– Peter H, Commented Feb 5, 2014 at 11:52

Schuere · Accepted Answer · 2014-02-05 15:50:24Z

2

in your foreach loop you have an allOptions string list (i'll assume this is the list with all the selectedValues from the ddl's)

change your

college.Value = ddl5.SelectedItem.Value;

To

college.Value = str;

else it would be quite astonishing you have a foreach loop with an object you never use ...

EDIT after comment;

Create a class that holds 2 string properties StringSome = your string to have, stringDrop = the dropdown selectedvalue

public class MyClass
{
 public string StringSome{get;set;}
 public stirng StringDrop{get;set;}
 public MyClass()
 {//default constructor}
 public MyClass(string a, string b)
 {
  StringSome = a; 
  StringDrop = b;}
 }

change

Alloptions = new List<string>();

to

Alloptions = new List<MyClass>();

Fill your class according to the data

i assume you want string1 to ddl1 and string2 to ddl2 etc...

example

AllOptions.Add(new MyClass(yourcontrol1.Text, ddl1.SelectedValue.Value));

keep the foreach loop on AllOptions

change

college.Value = str;

to

college.value = str.StringDrop;

use the str.StringSome for your other parameter

edited Feb 5, 2014 at 15:50

answered Feb 5, 2014 at 11:56

Schuere

1,64921 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nowYouSeeMe Over a year ago

Thanks @Schuere - you are correct STR does contain the selected values, how can I also enter another string array of selected values at the same time?

apomene · Accepted Answer · 2014-02-05 12:07:02Z

0

Iterate through your dropdown list, if list1 is that list:

  foreach (string str in list1)
{    
....
college.Value = str;
....
}

However by doing this it has no meaning having the values in dropdown listbox (thus selection looses its point of use) and you could simply store them in a simple List <string>

edited Feb 5, 2014 at 12:07

answered Feb 5, 2014 at 11:58

apomene

14.4k10 gold badges51 silver badges76 bronze badges

Comments

Peter H · Accepted Answer · 2014-02-05 12:09:48Z

0

Just add a for statement like this for each of you comboboxes.

string text_to_add;
for (int i=0; i< comboBox.Items.Count; i++)
{
    text_to_add=comboBox.Items[i].ToString();
    //Perform the db insert using the text_to_add string
}

edited Feb 5, 2014 at 12:09

answered Feb 5, 2014 at 12:00

Peter H

9011 gold badge10 silver badges33 bronze badges

Collectives™ on Stack Overflow

Insert Multiple Rows from DropDown to SQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related