1

In my code I understand query getting null values and it throws this error. But since my query is little complex I don't understand how do I check for null values and avoid this error. Please help me to correct this query.

SELECT (SUM(charges) + SUM(behaviour) + SUM(admission) + SUM(properInformation) + SUM(hygine) + SUM(treatment))/(count(doctorID) * 6) AverageRating, COUNT(ID) RatingCount from ratings where doctorID = '" + doctorID + "'
  • 4
    Your error does not sound like it is the query itself. Sounds like you are not declaring something in your code as new. – jagler Mar 18 '16 at 19:22
  • 1
    The error is a .NET error, not an SQL error. Of course it may be caused by the result of your select, but you'll need to give us more details about the code to get help on that part. – Joachim Isaksson Mar 18 '16 at 19:23
  • @JoachimIsaksson Updated my code. Please have a look. –  Mar 18 '16 at 19:25
  • 1
    What line specifically are you getting this error? – Ageonix Mar 18 '16 at 19:29
  • 1
    Can't tell for sure what blows up, however you'll always get one row back for a sum, however if the doctor does not exist you'll get dbnull/null back as averagerating which may cause trouble. – Joachim Isaksson Mar 18 '16 at 19:31
  • 1
    you can find a good answer for your question in here http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it/26761773#26761773 – Farooq Alsaegh Mar 18 '16 at 19:32
  • @JoachimIsaksson Ok. I got the error. But I am changing my post please concern that what is actual problem –  Mar 18 '16 at 19:37
  • @JoachimIsaksson My actual problem is Conversion from type 'DBNull' to type 'Double' is not valid. Please solve this in my query. And sorry for wrong post earlier.. –  Mar 18 '16 at 19:41

2 Answers2

2

If you want the query to not return NULL, you can just surround the expression with IFNULL to convert a possible NULL to 0, something like;

SELECT IFNULL((SUM(charges) + SUM(behaviour) + SUM(admission) + 
               SUM(properInformation) + SUM(hygine) + SUM(treatment))
              /(count(doctorID) * 6), 0) AverageRating, 
       COUNT(ID) RatingCount 
FROM ratings 
WHERE doctorID = '" + doctorID + "'
Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
  • Will this only convert a null sum to 0, or convert any nulls in any of the summed columns to 0? – Aaron Dietz Mar 18 '16 at 19:48
  • @AaronD Just the null sum, if you want the individual sums turned to 0 you need an ifnull around each sum. SQL isn't very flexible when it comes to adding a null with any other value, sadly. – Joachim Isaksson Mar 18 '16 at 19:49
  • Okay thanks, that's what I expected. It would have been nice though, heh. – Aaron Dietz Mar 18 '16 at 19:50
0

If you definitely know your query returning null value correctly, then you can use try-catch block as below:

Try
Dim dt As DataTable = Me.GetData("SELECT (SUM(charges) + SUM(behaviour) + SUM(admission) + SUM(properInformation)
Catch ex As Exception
MsgBox("Error while fetching data" & vbCrLf & ex.Message)
End Try