Click here to Skip to main content
15,888,283 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
, my data now inserted very well into database but now problem is when im trying to update particular cell(its not updated but create new one duplicate insert row) im not getting where is exactly problem, update code im written on dataGridView1_CellValueChanged event and insert code written on dataGridView1_RowLeave event here iam pasting Update code please help me to solve this issue (if i not write AccountNumber in cmd.Parameters.AddWithValue its giving me one or more reference missing) code is not working because im written on dataGridView1_CellValueChanged  
event?

What I have tried:

update query :

C#
  private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
                string cmd1 = "update Ledger set [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
Comments
Sascha Lefèvre 25-Mar-16 16:57pm    
It's not possible that an update statement creates a new record instead of updating one. Provided that your observation is correct, the only explanation would be that not the update statement but the insert statement gets executed somehow. I assume you didn't attempt to debug your code using the debugger. You should do that. For a programmer, a debugger is like a microscope for a biologist: It allows you to see what happens. Jump into it, learn how to use it and you will be able to solve a lot of problems yourself! Take a look here:
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide
Ultra short version: Place debug brakepoints at the start of your methods that are of interest, start debugging with F5, then step through the execution of your code with F10, observe the control flow and values of variables and compare that to what you think it should be. When it starts to differ from what you think it should be you've probably found the origin of the problem.
Atul Rokade 25-Mar-16 17:18pm    
thank you for replying sascha im check by using debugging also i updated account's one row when i was debugging its show me value is updated but when open the form i havent see updated value :(
Sascha Lefèvre 25-Mar-16 17:53pm    
Have you tested your update-code independently from the CellValueChanged-event? If not: Place a new Button on your Form, create an eventhandler-method for its Click-event and copy the code from dataGridView1_CellValueChanged into that method. Modify the parameter-creation-code so that it doesn't take the values from the DataGridView but instead write some hard-coded values there (just for testing). Make sure that you use an AccountNumber of an already existing record. Then run your program, click that test-button and see if 1) it works without throwing an exception and 2) if the record with that AccountNumber got updated. Then you'll at least know that that part works on its own.
Atul Rokade 25-Mar-16 18:01pm    
ok sascha i will do that and let me tell you .. but thanx man for such good helping .. :)
Atul Rokade 26-Mar-16 2:55am    
hello,

iam modify my update code but now its giving me Parameter @Description has no default value.

<pre lang="c#">

private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
int accountnumber=0;
string Account="";
DateTime ? Date=null;
string Description = "";
string Post_ref="";
int Debit=0;
int Credit=0;
int Balance=0;


if (!object.ReferenceEquals(dataGridView1.CurrentRow.Cells[0].Value, DBNull.Value))
{

accountnumber = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString());
}
else
if (!object.ReferenceEquals(dataGridView1.CurrentRow.Cells[1].Value, DBNull.Value))
{
Account = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
}
else if (!object.ReferenceEquals(dataGridView1.CurrentRow.Cells[2].Value, DBNull.Value))
{
Date = Convert.ToDateTime(dataGridView1.Rows[e.RowIndex].Cells[2].Value);
}
else if (!object.ReferenceEquals(dataGridView1.CurrentRow.Cells[3].Value,null))
{

Description = dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString();
}
else if (!object.ReferenceEquals(dataGridView1.CurrentRow.Cells[4].Value,null))
{
Post_ref = dataGridView1.Rows[e.RowIndex].Cells[4].Value.ToString();
}
else if (!object.ReferenceEquals(dataGridView1.CurrentRow.Cells[5].Value, DBNull.Value))
{
Debit = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[5].Value.ToString());
}
else if (!object.ReferenceEquals(dataGridView1.CurrentRow.Cells[6].Value, DBNull.Value))
{
Credit = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[6].Value.ToString());
}
else if (!object.ReferenceEquals(dataGridView1.CurrentRow.Cells[7].Value, DBNull.Value))
{
Balance = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[7].Value.ToString());
}

string cmd1 = ("update Ledger set [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;


cmd.Parameters.AddWithValue("@AccountNumber",accountnumber);
cmd.Parameters.AddWithValue("@Account", Account);
cmd.Parameters.AddWithValue("@Date", Date);
cmd.Parameters.AddWithValue("@Description", Description);
cmd.Parameters.AddWithValue("@Post_Ref", Post_ref);
cmd.Parameters.AddWithValue("@Debit", Debit);
cmd.Parameters.AddWithValue("@Credit", Credit);
cmd.Parameters.AddWithValue("@Balance", Balance);
cmd.ExecuteNonQuery();
con.Close();
Load_data();
dataGridView1.DataSource = null;
dataGridView1.Refresh();

}
</pre>

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