I am usiing the code below to calculate the sum of rows in my datatable with the productcode in the textbox but if the code is not in the datatable I get the error "conversion from dbnull to sting is not valid". How can i trap this error in this scenario.

  RadTextBox8.Text = myTable.Compute("SUM(PercentageInMix)", "ProductCode = '" & txtProductCode.Text & "' ")
  Catch ex As Exception
  End Try

I even tried this its still giving me the error

  RadTextBox8.Text = myTable.Compute("SUM(PercentageInMix)", String.IsNullOrEmpty("ProductCode = '" & txtProductCode.Text & "'    "))

Edited by Reverend Jim: typo

4 Years
Discussion Span
Last Post by Reverend Jim

You don't say what database you are using. If it is SQL then you can use the COALESCE function in the query. This function is used to replace NULL values with default values. For example:

select LastName,MiddleName,FirstName from myTable

May return NULL values for MiddleName, however, the query

select LastName,COALESCE(MiddleName,"(none)"),FirstName from myTable

Would return the string "(none)" where no middle name was entered in the database. There is an equivalent function in Access named NZ.


this has nothing to do with nulls being returned from the database. It has to to do with the DataTable.Compute method returning a DBNull.Value when as the OP stated that the filter criteria does not match any records in the datatable.

Replace the textbox.text assigment statement with:

Dim o as Object = myTable.Compute("SUM(PercentageInMix)", "ProductCode = '" & txtProductCode.Text & "' ")
RadTextBox8.Text = If(IsDBNull(o), String.Empty, CType(o, String))

He didn't say "the filter criteria does not match any records in the database". He said, "the code is not in the datatable", and an error in conversion from dbnull to string sounds more to me like what I suggested, however, my experience with databases has been at the admin level, and interfacing with ADO which puts a lot fewer layers between my code and the DBMS. In either case one of our suggestions is bound to help.

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.