insert multiple row in table and update multiple row

Question

hi all,

  Im trying to insert and update in datagridview using c# but what happening when i inserted one row into datagridview it inserted multiple same row in table when i try to update one row its updated all row in table actually im not getting why this happening pasting my code here (for refernce i attaching screenshot also)

 

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();

}