Dealing with a null result from a SQL qry

Friends,

I have the following code, but when I run it, and the query that its data is derived returns a row, and that row has a value of Null

If dr.HasRows Then
                While dr.Read()
                    Attempt = dr.Item("attempt")
                    If Attempt = Nothing Then
                        txtAttemptNumber.Text = 1
                    Else
                        'Debug.WriteLine(dr.Item("LastName"))
                        txtAttemptNumber.Text = Attempt
                    End If
                End While
End If

I get the following error:

Cast from type 'DBNull' to type 'Integer' is not valid.

Can anybody see how to fix this, please?

Regards,
Eric
indy500fanAsked:
Who is Participating?
 
NetworkArchitekCommented:
Technically this is not good flow control but (the line the error is on would be helpful):

If dr.HasRows Then
                While dr.Read()
                    Try
                            Attempt = dr.Item("attempt")
                           'Debug.WriteLine(dr.Item("LastName"))
                            txtAttemptNumber.Text = Attempt
                    Catch ex As Exception
                            txtAttemptNumber.Text = 1
                   
                    End Try
                End While
End If


An alternative is to say something like ...

If  dr.Item("attemp") Is DBNull.Value
0
 
william007Commented:
try this..
  While dr.Read()
                   
                    If dr.Item("attempt")= Nothing Then
                        txtAttemptNumber.Text = 1
                    Else
                        'Debug.WriteLine(dr.Item("LastName"))
                        Attempt = dr.Item("attempt")
                        txtAttemptNumber.Text = Attempt
                    End If
                End While
0
 
william007Commented:
also try this.
If dr.HasRows Then
                While dr.Read()
                    Attempt = dr.Item("attempt")
                    If Attempt is dbnull.value Then
                        txtAttemptNumber.Text = 1
                    Else
                        'Debug.WriteLine(dr.Item("LastName"))
                        txtAttemptNumber.Text = Attempt
                    End If
                End While
End If
0
 
indy500fanAuthor Commented:
william007, your solutions didn't work unfortunately, but that was mainly due to the fact that I led you down the wrong path, and for that I am sorry.  I am so new at this, I forgot to mention as a work around I was trying to declare a variable Attempt as integer, reading the record from dr and if the result was something do something.  And for all that and wasting your valuable time and resources, I'm sorry.

 NetworkArchitek, you saw through what I was doing and the last line: If  dr.Item("attempt") Is DBNull.Value worked perfectly.

Thank you to you both.  I learn so much from everyone here!
0
 
william007Commented:
Nevermind, we learn together:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.