0

I have write this code but it only insert one not multiple items...

for (int i = 0; i < listBox1.SelectedItems.Count; i++)   
{                  
   ad.InsertCommand = new OleDbCommand("insert into Map_Data (Material_Code,Product_Id) values(@Material_Code,@Product_Id)", con);    
   ad.InsertCommand.Parameters.Add("@Material_Code", OleDbType.Integer).Value =Convert.ToInt32(listBox1.SelectedValue);
   listBox1.Items.Remove(listBox1.SelectedItem);
   ad.InsertCommand.Parameters.Add("@Product_Id", OleDbType.Integer).Value = count;
}   
con.Open();
ad.InsertCommand.ExecuteNonQuery();  
con.Close();
MessageBox.Show("Record is successfully Save In The Database");

2 Answers 2

2

your

ad.InsertCommand.ExecuteNonQuery();

needs to be inside the for loop.

right now, it is outside the for loop and hence called only one. also because it is a multi row insert you're trying, you're better off wrapping it in a oledb transaction to avoid data loss/inconistencies..

basically,

  1. create an OleDbTransaction
  2. insert all your records. (by calling ExecuteNonQuery)
  3. Commit the OleDbTransaction
Sign up to request clarification or add additional context in comments.

Comments

0

It only inserts one row -the last one- because that's how many times you are calling ExecuteNonQuery. If you want to insert every singe row, then move your closing curly bracket right after con.Close();

for (int i = 0; i < listBox1.SelectedItems.Count; i++)
{

   ad.InsertCommand = new OleDbCommand("insert into Map_Data (Material_Code,Product_Id) values(@Material_Code,@Product_Id)", con);

   ad.InsertCommand.Parameters.Add("@Material_Code", OleDbType.Integer).Value =Convert.ToInt32(listBox1.SelectedValue);
   listBox1.Items.Remove(listBox1.SelectedItem);
   ad.InsertCommand.Parameters.Add("@Product_Id", OleDbType.Integer).Value = count;


   con.Open();
   ad.InsertCommand.ExecuteNonQuery();
   con.Close();
}
MessageBox.Show("Record is successfully Save In The Database");

You may want to add some sort of try/catch blocks in case you get problems opening the connection or executing the actual statement itself. Also, you don't need to open/close the connection inside the for loop. It should be enough with opening the connection outside the for and then closing it after the for block. But again, I suggest you add try/catch or use using statements instead.

2 Comments

i have use try catch in my code ..But my problem is that i want to insert multiple items of listbox in database by using your code it only insert one item not multiple items that i am selecting in list box..
i have use try catch in my code ..But my problem is that i want to insert multiple items of listbox in database by using your code it only insert one item not multiple items that i am selecting in list box..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.