to insert data in database

Question

0.00/5 (No votes)

See more:

C#

cmd.Parameters.Add(new SqlParameter("@itemno", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@itemname", SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter("@price", SqlDbType.Decimal));
cmd.Parameters.Add(new SqlParameter("@quantity", SqlDbType.Decimal));
cmd.Parameters.Add(new SqlParameter("@weight", SqlDbType.Decimal));
cmd.Parameters.Add(new SqlParameter("@itemtotal", SqlDbType.Decimal));

C#

con.Open();
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    if (!row.IsNewRow)
                    {
                        cmd.Parameters["@itemno"].Value = row.Cells[0].Value;
                        cmd.Parameters["@itemname"].Value = row.Cells[1].Value;
                        cmd.Parameters["@price"].Value = row.Cells[2].Value;
                        cmd.Parameters["@quantity"].Value = row.Cells[3].Value;
                        cmd.Parameters["@weight"].Value = row.Cells[4].Value;
                        cmd.Parameters["@itemtotal"].Value = row.Cells[5].Value;
                        
                    }


                }
                cmd.ExecuteNonQuery();
                MessageBox.Show("Procedure Executed");
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
            finally
            { con.Close(); }

        }

My last row data is inserting twice in database ....
how can i solve this problem

I have tried t use cmd.ExecuteNonQuery(); outside of loop , but still my last row data in sql database is inserting twice (repeatedly ) in database..........

plz suggest write code to correct error........in this code

Posted 5-Dec-13 2:53am

safal.786

Updated 7-Dec-13 9:12am

v2

Add a Solution

Comments

Karthik_Mahalingam 5-Dec-13 8:58am

try this DataGridViewRow .AllowUserToAddRows = false

joginder-banger 7-Dec-13 15:22pm

try this. DataGridViewRow1.Rows.Count-1

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Pheonyx · Answer 1 · 2013-12-05T02:56:00

Solution 1

Your problem is because you are calling

cmd.ExecuteNonQuery();

inside the loop and then once outside.

If you remove that line from just before the MessageBox.Show line then it should solve your issue.

Posted 5-Dec-13 2:56am

Pheonyx

HobbyProggy · Answer 2 · 2013-12-05T04:29:00

using a Transaction instead of single commits might also solve your Problem and is less Performance killing

just write it like this:

C#

con.Open();
//initializing transaction
private SqlTransaction trans = con.BeginTransaction();

                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    if (!row.IsNewRow)
                    {
                        cmd.Parameters["@itemno"].Value = row.Cells[0].Value;
                        cmd.Parameters["@itemname"].Value = row.Cells[1].Value;
                        cmd.Parameters["@price"].Value = row.Cells[2].Value;
                        cmd.Parameters["@quantity"].Value = row.Cells[3].Value;
                        cmd.Parameters["@weight"].Value = row.Cells[4].Value;
                        cmd.Parameters["@itemtotal"].Value = row.Cells[5].Value;
                        //adding command to the Transaction and "executing" it
                        cmd.Transaction = trans;
                        cmd.ExecuteNonQuery();
                    }
 

                }
                MessageBox.Show("Procedure Executed");
                try
                {
                    //commit the Transaction ( all commands)
                    this.trans.Commit();
                    this.trans.Dispose();
                    this.trans = null;

                }
                catch ()
                {
                }
                finally
                {
                    if (trans != null)
                    {
                        trans.Dispose();
                        trans = null;
                    }
                }
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
            finally
            { con.Close(); }
 
        }

If i made no mistakes "i hope so", the cmd is stored into the Transaction and is commited after trans.Commit(), so your commands get done in correct order but in "one step".

No double inserts and if something goes wrong, just Transaction.Rollback();
and no changes will be done, ist like a buffer i'd say.

:)