Click here to Skip to main content
14,733,912 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
im insert and update data in datagridview but it inserted multiple duplicate rows in table at same time when i updated one row it updated all row actually im not understanding where i done mistake im providing you my insert code and update code if it is possible give me modify code 
insert code i written : dataGridView1_RowLeave event and Updated Code i written : dataGridView1_CellValueChanged 


What I have tried:

insert code:

private void dataGridView1_RowLeave(object sender, DataGridViewCellEventArgs e)
      {
          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 :

private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            string connectionString = null;
            connectionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;
            con.ConnectionString = connectionString;



            string cmd1 = "update Ledger set [AccountNumber]=@AccountNumber,[Account]=@Account,[Date]=@Date,[Description]=@Description,[Post_Ref]=@Post_Ref,[Debit]=@Debit,[Credit]=@Credit,[Balance]=@Balance where AccountNumber=@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

1 solution

Maybe you can use:
if (dataGridView1.IsCurrentRowDirty)
{
   // Save ...
}

in your RowLeave method.
   
Comments
Atul Rokade 25-Mar-16 6:06am
   
no still double duplicate entry done!
RickZeeland 25-Mar-16 6:59am
   
That's strange, in my test program (using an xml dataset) it works.
Are you sure you have no external code in your Access database that can cause the duplicates ?
Atul Rokade 25-Mar-16 16:29pm
   
you have any idea about update?
RickZeeland 25-Mar-16 16:37pm
   
No, I can not figure out what is wrong with your code, maybe you should test this on another machine and see if it works there.
Or, but you will not want to hear this: try another database or use XML.
Atul Rokade 25-Mar-16 14:55pm
   
now insert query running fine but prblem is when im trying to do update again multiple insert (duplicate) row inserted i written update code on dataGridView1_CellValueChanged event for that's why this is happen?

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