Click here to Skip to main content
15,917,473 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Everyone,

I want to insert multiple rows in a table on clicking a button.
The process I used is going through each row of gridview and inserting the data into table. But if there is some problem in between the process then half rows get inserted and half rows don't. But it should be either all rows get inserted or none.
I cannot think of a way to achieve this. Can anyone help with any idea? I am using access 2013 with

What I have tried:

For now, this is my code.

foreach (GridViewRow row in grid_score.Rows)
                string revw_date = DateTime.Today.ToString();
                string emp_fname = row.Cells[0].Text;
                string emp_lname = row.Cells[1].Text;
                string revwr_name = row.Cells[6].Text;
                string intscore = row.Cells[8].Text;

                //insert into score table
                sql = "INSERT INTO TABLENAME VALUES('" + revw_date + "',(SELECT EMP_ID FROM USER_DETAIL WHERE FIRST_NAME='" + emp_fname + "' AND LAST_NAME='" + emp_lname + "'),(SELECT REVIEWER_ID FROM USER_DETAIL WHERE REVIEWER_NAME='" + revwr_name + "')," + intscore + ")";

                OleDbCommand cmd = new OleDbCommand(sql, conn);
                int count = cmd.ExecuteNonQuery();

                if (count == 1)
                    System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "alert('Data inserted successfully');", true);
                    System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(), "displayalertmessage", "alert('Sorry! Data cannot be submitted. Try again.');", true);

As anyone can see from the code that success message will be displayed everytime a row gets inserted. This should not be the case.
Updated 27-Jun-16 23:33pm
OriginalGriff 28-Jun-16 5:34am    
There is a solution built in to all DB systems: Transactions.

1 solution

Use a Transaction: OleDbTransaction Class (System.Data.OleDb)[^] - You create it at the start, do your updates, and if anything fails, you use Rollback to cancel them all. Otherwise you Commit the transaction and close the connection.

But...don't do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900