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.

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

I even tried this its still giving me the error

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

Edited 4 Years Ago by Reverend Jim: typo

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.

Guys,
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 article has been dead for over six months. Start a new discussion instead.