DBNull to type Decimal

[weirdo71]

Hi and good day,

I have a piece of code im working on and is stuck on Select Sum statement which keeps returning a Conversion from type DBNull to type Decimal is invalid. Below is the code and Im working with a Access Database. Everything works good if the Batch Number exists and its when the Batch Number does not exist.

Thanks in Adavance
Ken

mycmd = New OleDb.OleDbCommand("SELECT SUM([Production This Session]) AS [Production This Session], SUM([Total Units]) AS [Total Units] FROM SBInput WHERE [Batch Number] = '" & TextBox6.Text & "'", mycon)
mycon.Open()

Dim mydr As OleDb.OleDbDataReader = mycmd.ExecuteReader

If mydr.Read = True Then
   TBP = mydr("Production This Session")
   TextBox14.Text = TBP.ToString("##,###.##0")
   TU = mydr("Total Units")
   TextBox15.Text = TU.ToString("##")
End If

If mycon.State <> ConnectionState.Closed Then
   mycon.Close()
End If

[CodeWord]

I haven't used Access in ages, but in SQL, you could do this:

Select SUM(ISNULL(<fldname>,0))

This will assign a value of 0 for <NULL> to be used in the SUM function.

[CodeWord]

What version of Access are you using?

[CodeWord]

On further review, I would guess that you need to validate the value in TextBox6.

My guess is that the user isn't typing in a value. I would not run the query if the the value is invalid.

Also, is it possible that there is a NULL in your data for batchNumber? If you are validating input, and it still happens, this could be the cause.

[weirdo71]

Hi again and thanks for responding so fast.

The first piece of code didnt work it told me that there were to many arguments.

I am working with access 2003. Hoping to change to SQL in the future.

Thanks again
Ken

[weirdo71]

I have a seperate piece of code for making sure the user does indeed put in values for textbox6. The problem is that it could be a new number that is not yet updated to the database. So yes it would give a null error. Im trying to figure out how to put in a 0 or noting in textbox14 and textbox15 if the answer is null.

Hope that makes sense

[CodeWord]

Ok, I get you. The error isn't coming from the SQL, but from trying to use the values returned by SUM, which in the case of a non-existent batch number is NULL.

If TBP and TU are doubles, then simply check the value of the results before trying to before assigning it to the variable.

Are you using vbscript? or vb.net? or VBA?

[CodeWord]

you could also try this using Nz(Value, [ValueIfNull]):

mycmd = New OleDb.OleDbCommand("SELECT Nz(SUM([Production This Session]),0) AS [Production This Session], Nz(SUM([Total Units]),0) AS [Total Units] FROM SBInput WHERE [Batch Number] = '" & TextBox6.Text & "'", mycon)
mycon.Open()

[weirdo71]

I am using VB.Net, 2008.

[CodeWord]

So, did the new sql statement work?

[weirdo71]

Hi again. No Im afraid it did not. Now its telling me theres to many arguments for the select statement.

Thanks
again