Parameter @description has no default value exception

Question

0.00/5 (No votes)

See more:

iam trying to update cell in datagridview but its giving me Parameter @Description has no default value exception and if i click enter into cell its giving me Index or primary key cannot contain a Null value in insert query AccountNumber is my primary key but right now all table is blank, insert query i written on dataGridView1_RowLeave event while update query written on dataGridView1_CellValueChanged event(

What I have tried:

Update code:

C#

private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
           if (dataGridView1.IsCurrentRowDirty)
            {
                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();
            }

}

Posted 25-Mar-16 21:25pm

Atul Rokade

Updated 26-Mar-16 9:36am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

Comments

RickZeeland 26-Mar-16 4:08am

I think it might be because you are not using DBNull.Value but null for Description.

Atul Rokade 26-Mar-16 4:19am

still same error Parameter @Description has no default value. i changed
if (!object.ReferenceEquals(dataGridView1.CurrentRow.Cells[3].Value, DBNull.Value))
{

Description = dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString();

RickZeeland 26-Mar-16 4:24am

Then maybe you have to look at the default value for the field in the database, don't know Access naming but in SQL Server it's called "default".

Atul Rokade 26-Mar-16 4:31am

i check Description column in table there Default value is nothing not even all fields

RickZeeland 26-Mar-16 4:33am

I also think you should check if the accountnumber already exists before executing an UPDATE command.

Atul Rokade 26-Mar-16 4:38am

actually what happening when im trying to do updating cell its giving me error but also i saw its creating duplicate insert row(s) also

Atul Rokade 26-Mar-16 4:42am

i written update code on dataGridView1_CellValueChanged event its any cause??

Sascha Lefèvre 26-Mar-16 7:29am

Please try this: Move this line:
cmd.Parameters.AddWithValue("@AccountNumber", accountnumber);
below the line for @Balance.

Atul Rokade 26-Mar-16 9:32am

not getting sascha?

Sascha Lefèvre 26-Mar-16 9:38am

Change this:

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);
to this:
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.Parameters.AddWithValue("@AccountNumber", accountnumber);

Atul Rokade 26-Mar-16 9:59am

now Parameter @Date has no default value exception is coming .. actually we are not finding what is exactly issue

Sascha Lefèvre 26-Mar-16 10:38am

Actually we're getting closer :)
But one thing I could have seen earlier: You're using else-if-statements for initializing the parameter-values from the datagridview. That makes no sense because it means that only one of those values will be set. You need to change all those to "independent" if-statements.

Atul Rokade 26-Mar-16 10:57am

sascha brother: im closely look now by using debugger, i saw when update one row , its updated but what happen debugger going into dataGridView1_RowLeave event which have insert statement so insert statement create duplicate entry

Sascha Lefèvre 26-Mar-16 12:14pm

Yep, when I saw your previous question I expected that: You missed one earlier suggestion from me. In dataGridView1_RowLeave you need to check if that row which was just left was a new row and only then execute the insert-code.

Atul Rokade 26-Mar-16 13:00pm

how could i do that??? in inert code?

Sascha Lefèvre 26-Mar-16 13:42pm

The DataGridViewCellEventArgs-argument "e" gives you the index of the row that was just left (e.RowIndex). With that index you need to figure out if that row is a new one. The trouble here is: When the RowLeave-event occurs, the row just isn't the new row any more. So you're "a bit late", so to say. You have two options:

Option 1: Add a boolean variable with a name like "CurrentRowIsNew" as a class-variable. Add an eventhandler-method for the event RowEnter. In that method set CurrentRowIsNew equal to e.IsNewRow. Now when the RowLeave-eventhandler-method gets called you can check if CurrentRowIsNew is true and then execute the insert-code (and set CurrentRowIsNew to false).

Sidenote: The CellValueChanged-event also occurs for new rows so your update-code gets called multiple times before the new row gets actually inserted into the database. If you check CurrentRowIsNew there too, you can execute the update-code only if CurrentRowIsNew is false.

Option 2: This would be the better option but require more changes. I wouldn't recommend using the DataGridView as the source for the values of your database-code. The DataGridView is intended as... a view. Not as a DataTable. Therefore a DataGridView's methods are designed for the visualization of data but not for handling the state of data. For that a DataTable is better. I assume your select-code fills a DataTable and sets that DataTable as the DataSource of your DataGridView. You could keep the reference to that DataTable as a class-variable. And the DataRow of the DataTable would tell you if it's a new row until you've saved it to the database, regardless whether the input cursor is still in that row or not.

1 solution

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)

Sascha Lefèvre · Answer 1 · 2016-03-26T08:02:00

Solution 1

You're using else-if-statements for initializing the parameter-values from the datagridview. That makes no sense because it means that only one of those values will be set. You need to change all those to "independent" if-statements.

Posted 26-Mar-16 8:02am

Sascha Lefèvre