CODEWITHSUNDEEP
databasevb.net
d99
d99

Reputation: 37

DBnull to integer conversion error

I get the Conversion from type 'DBNull' to type 'Integer' is not valid." error on the line "Dim avgObject As string = Cstr(avgCom.ExecuteScalar())

The command works when the where module_ID='" & moduleSelect & "' statement is removed and I do not know how to fix this, can anyone help?

    Dim moduleSelect As String = moduleRadio.SelectedValue
    Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")
        Using avgCom As New SqlCommand("SELECT AVG(exam) FROM completed_module where module_ID='" & moduleSelect & "' ", _
                                        connection)
            connection.Open()
            Dim avgObject As Integer = CInt(avgCom.ExecuteScalar())
            Dim averageVar As String
            averageVar = avgObject.ToString
            avgLabel.Text = averageVar
        End Using

Upvotes: 1

Views: 8432

Answers (5)

DrCopyPaste
DrCopyPaste

Reputation: 4117

Probably this fails because there is a value missing. (i.e. NULL) But it might work if you default to 0 if a row with NULL was encountered:

SELECT AVG(ISNULL(exam,0)) FROM completed_module where module_ID=

Otherwise make sure your table does not include NULL-values for that column:

UPDATE completed_module SET exam = 0 WHERE exam IS NULL

(maybe constraint it so it may not have future NULL-Values also ;))

EDIT: this assumes that you can actually have an average value for every row, even those where the column you access is NULL, in that case i would assume NULL does not add anything to your average value (which the other rows that share that ID might) so I default it to 0

Upvotes: 0

eXecute
eXecute

Reputation: 176

I believe you are looking for something like this, first checking if it is dbnull:

Dim moduleSelect As String = moduleRadio.SelectedValue

Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")
    Using avgCom As New SqlCommand("SELECT AVG(exam) FROM completed_module where module_ID='" & moduleSelect & "' ", _
                                    connection)
        connection.Open()
        Dim result = avgCom.ExecuteScalar()
        If IsDBNull(result) then return
        Dim avgObject As Integer = CInt(result)
        Dim averageVar As String
        averageVar = avgObject.ToString
        avgLabel.Text = averageVar
    End Using

Upvotes: 2

Pandian
Pandian

Reputation: 9126

Error :Conversion from type 'DBNull' to type 'Integer' is not valid.

This error Occurs because your query return a NULL value.. you can manage the NULL value by using the Below code..

Try like below it will help you...

connection.Open()
Dim result As String = avgCom.ExecuteScalar().ToString()
Dim avgObject As Integer = If(result = "", 0, CInt(result))

Upvotes: 0

Amegon
Amegon

Reputation: 630

Use Convert.ToString instead. Directcast as string does not work for Null/Nothing

UPDATE Problem happens whenever you do not receive any results.

I tested, so CStr to Convert.ToString works for DBNUll, but CInt and Convert.ToIntXX still throws an eception. You can use 

Dim scalarResult = avgCom.ExecuteScalar()
If Convert.IsDBNull(scalarResult) then
  avgObject = 0
Else
  avgObject = Convert.toInt32(scalarResult)
End If

Upvotes: 0

C.Evenhuis
C.Evenhuis

Reputation: 26436

DBNull means that the record in the database does not contain a value for the column. So basically you are trying to convert "nothing" into a number.

What do you want your program to do? Skip the row? Use a default value instead?

If the command really "works" if you remove a statement from the command, I suggest you simply remove it.

Upvotes: 2

Related Questions