CODEWITHSUNDEEP
c#asp.net
Karthik
Karthik

Reputation: 2399

inserting arraylist values into database

i have used a checkbox list in my project .am storing all the checked items values in arraylist using code below

 ArrayList  services= new ArrayList();
for (int i = 0; i < chkservices.Items.Count; i++)
        {
            if (chkservices.Items[i].Selected == true)
            {
                services.Add(chkservices.Items[i].Text+',');
            }
        }

now the problem is when i insert data in to database instead of data in the arraylist it gets inserted as 'System.Collections.ArrayList' how can i insert all values into database in a single insert statement?

EDIT

inserting into database 

con.Open();
        SqlCommand cmd = new SqlCommand("insert into XXX(First_Name,Last_Name,ServicesProvided) values ('" + txtfrstname.Text + "','" + txtlastname.Text + "','" + services + "')", con);
        cmd.ExecuteNonQuery();
        con.Close();

or could anyone provide me a alternative for arraylist..i need to save checked items from checkboxlist and save it in database

it should be saved in database as

  First_Name             Last_name                 ServicesProvided
user1firstname        user1lastname               selectedvalue1,
                                              selectedvalue2,selectedvalue3

Upvotes: 2

Views: 6828

Answers (4)

Alexander Galkin
Alexander Galkin

Reputation: 12554

Why not to concatenate your data using the following code:

 var mydata = String.Join(',', chkservices.Items
   .Where( a => a.Selected).Select( b => b.Text));

So you can add your data as a string.

EDIT: It is a very bad habit of concatenating strings to make a query! Apart from many side effects like in your case here it is a great security breach. Try the parameterized query instead:

     SqlCommand cmd = new SqlCommand(
         @"insert into XXX(First_Name,Last_Name,ServicesProvided) values 
         (@First_Name,@Last_Name,@ServicesProvided")", con); 
      cmd.Parameters.AddWithValue("@ServicesProvided", mydata);
      cmd.Parameters.AddWithValue("@First_Name", frstname.Text);
      cmd.Parameters.AddWithValue("@Last_Name", txtlastname.Text);
    cmd.ExecuteNonQuery();

mydata is the variable from my first example.

Upvotes: 2

Tu Tran
Tu Tran

Reputation: 1987

How many checkbox do you have? If you just have a little checkbox, so I suggest you transform each state of them into bit mask which represent a number, then store it to database.

long bitMask = 0; // All uncheck
for (int i = 0; i < chkServices.Items.Count; ++i) {
    if (chkServices.Items[i].Checked) {
        bitMask |= (1 << i);
    }
}

// Store bitMask to Database

In later, you can get state via bitMask again when needed.

Upvotes: 0

Shadow Wizzard
Shadow Wizzard

Reputation: 66398

Using INSERT INTO statement you can insert only one row at a time unless you're using sub query to select data from other table.

As you don't have the data in the database, your only option is iterate over the array and insert each value as new row.

Don't use ArrayList, you have generic list for what you need:

List<string> services = new List<string>();
for (int i = 0; i < chkservices.Items.Count; i++)
{
    if (chkservices.Items[i].Selected == true)
    {
        services.Add(chkservices.Items[i].Text);
    }
}

//...connection stuff....
strSQL = "INSERT INTO MyTable (MyField) VALUES (@val)"
using (SqlCommand command = new SqlCommand(strSQL, connection))
{
    command.Parameters.AddWithValue("@val", "");
    foreach (string service in services)
    {
        command.Parameters["@val"].Value = service;
        command.ExecuteNonQuery();
    }
}

Upvotes: 0

Ivo
Ivo

Reputation: 3436

You need to get the values of the array list and send them one by one

or create a stored procedure where you send all the values to using Alexanders Galkins example (or use the a Aggregate method). Then use the split function to split up the string and insert all the record

Upvotes: 0

Related Questions