System Null Reference error when retrieving data from database

Question

I'm getting a system null reference error on this code. It searches for a random filename which may or may not exist. It works fine if a filename exists. If none exist then I get this error, it doesn't seem to want to do the 'ELSE' part where I assign filename="no results"

VB.Net...

Public Function GetFeaturedItem(ByVal accountID As Guid) As String

    Dim DBConnect55 As New DBConn
    Dim filename As String
    Using db As DbConnection = DBConnect55.Conn("DBConnectionString")
        Dim cmd As SqlCommand = DBConnect55.Command(db, "SelectFeaturedItem")
        cmd.Parameters.Add(New SqlParameter("accountID", SqlDbType.Uniqueidentifier, ParameterDirection.Input)).Value = accountID

        db.Open()
        Dim DR As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        While DR.Read
            If NOT IsDbNull(DR("filename")) Then
                filename = DR("filename")
            Else
                filename="no results"
            End If
        End While
        DR.Close()
        DR = Nothing
        cmd.Dispose()
        cmd = Nothing
        db.Dispose()
        db.Close()
    End Using
    Return filename
End Function

SQL...

SELECT TOP 1 filename FROM tblItems WHERE accountID=@accountID AND valid='1'
ORDER BY NEWID()

I CALL IT LIKE THIS..

featuredItem(noOfResults) = getFeaturedItem(DR("accountID")).ToString

1) If you need only one value from database, then use [ExecuteScalar](https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlcommand.executescalar) method. 2) There's no need to call `ToString` in `getFeaturedItem(DR("accountID")).ToString` since you *already* have a string. 3) Which line throws error? — JohnyL, May 22 '20 at 13:31
Thank you. I took the .ToString off when I call the function and it works perfectly now. I will mark this as the answer when it allows me to do so. — mark davies, May 22 '20 at 13:38
Have you tried stepping through this in the debugger? Have you considered what happens if `DR.Read` returns `False` on the first time through? — Craig, May 22 '20 at 14:00

score 0 · Accepted Answer · answered May 22 '20 at 13:39

0

You need to remove ToString in getFeaturedItem(DR("accountID")).ToString.

answered May 22 '20 at 13:39

JohnyL

6,894
3
22
41

System Null Reference error when retrieving data from database

1 Answers1