DBnull to integer conversion error

Question

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

A google search for Conversion from type 'DBNull' to type 'Integer' is not valid. has nearly 8000 results. — 000
– 000, Commented Apr 8, 2013 at 14:47
Can you run the query SELECT AVG(exam).. where Module_ID = nnn? I think, it is returning null because there aren't records for the module_ID you passed in. — shahkalpesh
– shahkalpesh, Commented Apr 8, 2013 at 14:49
There were no values selected from the list when the page loaded thus giving the null, thanks everyone for their help. — d99
– d99, Commented Apr 8, 2013 at 15:28

Amegon · Accepted Answer · 2013-04-08 15:09:23Z

2

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

edited Apr 8, 2013 at 15:09

Amegon

6306 silver badges15 bronze badges

answered Apr 8, 2013 at 14:48

eXecute

1767 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

C.Evenhuis · Accepted Answer · 2013-04-08 14:47:19Z

2

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.

answered Apr 8, 2013 at 14:47

C.Evenhuis

26.4k2 gold badges60 silver badges73 bronze badges

Comments

Amegon · Accepted Answer · 2013-04-08 14:56:06Z

0

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

edited Apr 8, 2013 at 14:56

answered Apr 8, 2013 at 14:47

Amegon

6306 silver badges15 bronze badges

Comments

Pandian · Accepted Answer · 2013-04-08 15:09:00Z

0

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))

answered Apr 8, 2013 at 15:09

Pandian

9,1562 gold badges26 silver badges34 bronze badges

Comments

DrCopyPaste · Accepted Answer · 2013-04-08 15:19:34Z

0

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

edited Apr 8, 2013 at 15:19

answered Apr 8, 2013 at 15:13

DrCopyPaste

4,1552 gold badges24 silver badges58 bronze badges

Collectives™ on Stack Overflow

DBnull to integer conversion error

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related