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

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)

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
End If

Edited by __avd: Added [code] tag. Do wrap your programming code blocks within [code] ... [/code] tags

7 Years
Discussion Span
Last Post by weirdo71

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.


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.

Edited by CodeWord: n/a


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


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


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?

Edited by CodeWord: n/a


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)

Edited by __avd: Added [code] tags. Do wrap your programming code blocks within [code] ... [/code] tags


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


This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.