Click here to Skip to main content
15,883,783 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
im creating one application where user can be enter there details in datagridview data inserted successfully but when i check again it show me multiple entry has done by one data i pasted here my inserted code and one more query i have iam trying to do update also but its give me No value given for one or more required parameters exception im giving that code also


What I have tried:

inserted code :
C#
private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
 account = dataGridView1.Rows[e.RowIndex].Cells["AccountNumber"].Value.ToString();
            if (account == "")
            {
                account1 = 0;
            }
            else
            {
                account1 = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells["AccountNumber"].Value.ToString());
            }
            if (account1 == 0)
            {



            string connectionString = null;
            connectionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
            con.ConnectionString = connectionString;

            string cmd1 = "insert into Ledger([AccountNumber],[Account],[Date],[Description],[Post_Ref],[Debit],[Credit],[Balance])values(?,?,?,?,?,?,?,?)";
            OleDbCommand cmd = new OleDbCommand(cmd1, con);
            con.Open();
            cmd.CommandType = CommandType.Text;



            int accountNumber;

            bool accountHasValue = int.TryParse(dataGridView1.Rows[e.RowIndex].Cells["AccountNumber"].Value.ToString(), out accountNumber);

            if (accountHasValue)
            {
                cmd.Parameters.AddWithValue("@AccountNumber", accountNumber);
            }
            else
            {
                cmd.Parameters.AddWithValue("@AccountNumber", DBNull.Value);
            }

            string accounts = dataGridView1.Rows[e.RowIndex].Cells["Account"].Value.ToString();
            cmd.Parameters.AddWithValue("@Account", accounts);

            DateTime datetime;
            bool dateTimeHasValue = DateTime.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Date"].Value.ToString(), out datetime);

            if (dateTimeHasValue)
            {
                cmd.Parameters.AddWithValue("@Date", datetime);
            }
            else
            {
                cmd.Parameters.AddWithValue("@Date", DBNull.Value);
            }


            string Description = dataGridView1.Rows[e.RowIndex].Cells["Description"].Value.ToString();
            cmd.Parameters.AddWithValue("@Description", Description);


            string Post_Ref = dataGridView1.Rows[e.RowIndex].Cells["Post_Ref"].Value.ToString();
            cmd.Parameters.AddWithValue("@Post_Ref", Post_Ref);


            int debit;
            bool debitHasValue = Int32.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Debit"].Value.ToString(), out debit);

            if (debitHasValue)
            {
                cmd.Parameters.AddWithValue("@Debit", debit);
            }
            else
            {
                cmd.Parameters.AddWithValue("@Debit", DBNull.Value);
            }


            int Credits;
            bool CreditsHasValue = Int32.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Credit"].Value.ToString(), out Credits);

            if (CreditsHasValue)
            {
                cmd.Parameters.AddWithValue("@Credit", Credits);
            }
            else
            {
                cmd.Parameters.AddWithValue("@Credit", DBNull.Value);
            }

            int Balances;
            bool BalancesHasValue = Int32.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Balance"].Value.ToString(), out Balances);

            if (BalancesHasValue)
            {
                cmd.Parameters.AddWithValue("@Balance", Balances);
            }
            else
            {
                cmd.Parameters.AddWithValue("@Balance", DBNull.Value);
            }


            cmd.ExecuteNonQuery();


            con.Close();


        }




update code :
C#
else
         {
          string cmd1 = "update Ledger set [AccountNumber]=?,[Account]=?,[Date]=?,[Description]=?,[Post_Ref]=?,[Debit]=?,[Credit]=?,[Balance]=? where AccountNumber=?";
             OleDbCommand cmd = new OleDbCommand(cmd1, con);
             con.Open();
             cmd.CommandType = CommandType.Text;
             int accountNumber;

             bool accountHasValue = int.TryParse(dataGridView1.Rows[e.RowIndex].Cells["AccountNumber"].Value.ToString(), out accountNumber);

             if (accountHasValue)
             {
                 cmd.Parameters.AddWithValue("@AccountNumber", accountNumber);
             }
             else
             {
                 cmd.Parameters.AddWithValue("@AccountNumber", DBNull.Value);
             }

             string accounts = dataGridView1.Rows[e.RowIndex].Cells["Account"].Value.ToString();
             cmd.Parameters.AddWithValue("@Account", accounts);

             DateTime datetime;
             bool dateTimeHasValue = DateTime.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Date"].Value.ToString(), out datetime);

             if (dateTimeHasValue)
             {
                 cmd.Parameters.AddWithValue("@Date", datetime);
             }
             else
             {
                 cmd.Parameters.AddWithValue("@Date", DBNull.Value);
             }


             string Description = dataGridView1.Rows[e.RowIndex].Cells["Description"].Value.ToString();
             cmd.Parameters.AddWithValue("@Description", Description);


             string Post_Ref = dataGridView1.Rows[e.RowIndex].Cells["Post_Ref"].Value.ToString();
             cmd.Parameters.AddWithValue("@Post_Ref", Post_Ref);


             int debit;
             bool debitHasValue = Int32.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Debit"].Value.ToString(), out debit);

             if (debitHasValue)
             {
                 cmd.Parameters.AddWithValue("@Debit", debit);
             }
             else
             {
                 cmd.Parameters.AddWithValue("@Debit", DBNull.Value);
             }


             int Credits;
             bool CreditsHasValue = Int32.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Credit"].Value.ToString(), out Credits);

             if (CreditsHasValue)
             {
                 cmd.Parameters.AddWithValue("@Credit", Credits);
             }
             else
             {
                 cmd.Parameters.AddWithValue("@Credit", DBNull.Value);
             }

             int Balances;
             bool BalancesHasValue = Int32.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Balance"].Value.ToString(), out Balances);

             if (BalancesHasValue)
             {
                 cmd.Parameters.AddWithValue("@Balance", Balances);
             }
             else
             {
                 cmd.Parameters.AddWithValue("@Balance", DBNull.Value);
             }


             cmd.ExecuteNonQuery();


             con.Close();


        }
}
Posted
Updated 24-Mar-16 10:55am

1 solution

The cause for the multiple inserts is that you're doing it in dataGridView1_CellValueChanged. That eventhandler-method is called every time a cell value has changed. You should use the RowLeave-event instead (and make sure it was a new row).

The cause for the "No value given for one or more required parameters" exception is that you need to add the OleDbParameter for the AccountNumber a second time at the very end because of the where-clause and because Access doesn't have named parameters - so it doesn't know that it could use the first occurence of the AccountNumber-parameter again.
 
Share this answer
 
v2
Comments
Atul Rokade 25-Mar-16 1:57am    
@sascha : i written code on RowLeave event (insert code) but still multiple value coming
Atul Rokade 25-Mar-16 2:04am    
now what happening if i insert(multiple rows are inserted) if i updated one row multiple rows updated why this?

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