operator '-' is not defined for type 'decimal' and type 'dbnull'

Question

0.00/5 (No votes)

See more:

Hello All,

i am using a datagrid to insert data in sql.

Then to update and entry, owing to logical reasons i delete the related records from database and insert the new ones again.

The first time adding is done fine.

But while insert query runs while updating, if a cell is left blank(dbnull) then the above error is shown. Updation is fine if i enter 0 in the cell.

My question: When the insert command works fine the 1st time, then what happens in 2nd time?

I have also tried inserting following code to deal with dbnull cells but not working:

VB

For Each Row As DataGridViewRow In DataGridView1.Rows
            If (Not Row.IsNewRow) Then
                Try
                    If Row.Cells(favcadr.Index) Is DBNull.Value Then
                        Row.Cells(favcadr.Index).Value = (0.0).ToString
                    End If

                    If Row.Cells(favcacr.Index) Is DBNull.Value Then
                        Row.Cells(favcacr.Index).Value = (0.0).ToString
                    End If

                    Insert.Parameters(0).Value = Row.Cells(ddlcode.Index).Value
                    Insert.Parameters(1).Value = (Row.Cells(favcadr.Index).Value - Row.Cells(favcacr.Index).Value)

                    Insert.ExecuteNonQuery()
                    vartemp1 = vartemp1 + 1
                Catch ex As Exception
                    MsgBox("Error in Adding:" & vbCrLf & ex.Message)
                End Try
            End If

        Next

Posted 15-Feb-14 20:53pm

atul sharma 5126

Updated 15-Feb-14 21:31pm

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

v3

Add a Solution

4 solutions

Solution 2

Two things:
If Row.Cells(favcadr.Index) Is DBNull.Value Then
should be changed to
If Row.Cells(favcadr.Index).Value Is Nothing Then

Also, DataGridViewRow.Cells[index].Value is an Object. Consequently, you do not need to use ToString on the value you set, i.e. do
Row.Cells(favcadr.Index).Value = 0.0

Posted 16-Feb-14 21:06pm

Bernhard Hiller

Comments

atul sharma 5126 18-Feb-14 5:41am

Thanks for helping. But same error

atul sharma 5126 18-Feb-14 6:18am

Dear Bernhard,
You are right. The value of blank column of grid is Nothing, as i have found out in immediate window at first time insertion.
But at the time of second insertion as soon as we refer to the blank cell the said error appears.

Solution 1

I guess the following line causes the issue.
That is because, for one instance, one value comes decimal and other comes as dbnull.

VB

Insert.Parameters(1).Value = (Row.Cells(favcadr.Index).Value - Row.Cells(favcacr.Index).Value)

So, debug your code and find out what is the issue. Then correct your code.

Posted 15-Feb-14 21:36pm

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

Comments

atul sharma 5126 16-Feb-14 10:54am

you are correct dear. This is the line where one value is dbnull due to logical reasons as 1 of the columns in grid is supposed to be left blank and moved on to next column by tab. But the same procedure is followed at first time insert also. So why this problem aries only on second insert.
Also, as you can see in my code, i have tried to change the value of cell to 0 in case it is dbnull, but it is not working.
Please suggest some solution of changing value if dbnull.

Thanks for support

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 16-Feb-14 13:23pm

Okay for first time, also debug and see how the values are coming, so that you will get the idea.
While debugging check what is the value of Row.Cells(favcadr.Index) and Row.Cells(favcacr.Index) in immediate window. If you check the values, you will come to know that which value you should compare these with instead of dbnull. Most probably you should compare with blank. In that case you can check by string.IsNullOrEmpty(stringVariable).

Do you get my point?

atul sharma 5126 18-Feb-14 6:15am

Hello Tadit,
I added the following variables to get exact value i am getting:
vartempdr = Row.Cells(favcadr.Index).Value
vartempcr = Row.Cells(favcacr.Index).Value

And in debugging i found:
On first insertion the blank cells value is Nothing and records are inserted
Whereas in second insertion (at updation) as soon as the command to give value to variable is run, the following error is shown in immediate window..

Step into: Stepping over non-user code 'System.Windows.Forms.DataGridViewRow.Cells.get'
A first chance exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 18-Feb-14 13:00pm

I guess you are pressing F11 for while debugging. Please press F10.

atul sharma 5126 18-Feb-14 6:37am

I tried checking string.isNullOrEmpty but that changed the error to : "Conversion from type DBNull to type string is not valid

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 18-Feb-14 13:01pm

First you need to check if it is null or not. Then only you can convert to String and check by IsNullOrEmpty.

atul sharma 5126 18-Feb-14 9:03am

First time: Blank cell = Nothing
Second time: Blank cell = DBNull

atul sharma 5126 18-Feb-14 9:16am

Strange. the case of being dbnull is not activated. i.e. the Then command of If is jumped over. what may be wrong in this code:
If Row.Cells(favcadr.Index) Is DBNull.Value Then
Row.Cells(favcadr.Index).Value = ""
End If

Solution 3

keep this in else statement

VB

If Row.Cells(favcadr.Index) Is DBNull.Value Then
                        Row.Cells(favcadr.Index).Value = (0.0).ToString
                    End If

                   else If Row.Cells(favcacr.Index) Is DBNull.Value Then
                        Row.Cells(favcacr.Index).Value = (0.0).ToString
else

Insert.Parameters(1).Value = (Row.Cells(favcadr.Index).Value - Row.Cells(favcacr.Index).Value)
                    End If

Posted 16-Feb-14 21:10pm

Member 10476757

Comments

atul sharma 5126 18-Feb-14 5:41am

Thanks for helping. But same error

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)

atul sharma 5126 · Accepted Answer · 2014-02-18T07:32:00

hello guys.

I have to find other way round:

VB

If IsNumeric(Row.Cells(favcadr.Index).Value) Then
                        vartempdr = Row.Cells(favcadr.Index).Value
                    Else
                        vartempdr = 0
                    End If

checking numeric instead of dbnull.

Thanks all for support specially tadit.