VB.Net - SQLDataReader handling Null values

Question

My SQL database has null values and I want my field in VB.Net to generate a text string if it finds a null value.

'SQL search emp name based on emp id input
        Dim cmd As New SqlCommand("select Lname from [ZLOGIN_MASTER] where Login_ID = '" & txtReport.Text & "'", Conn)
        Conn.Open()
        Dim rdr As SqlDataReader = cmd.ExecuteReader
        Dim index As Integer = rdr.GetOrdinal("Lname")

        If rdr.IsDBNull(index) Then

            RptNm.Text = "No name found."

        Else

            While rdr.Read

                RptNm.Text = rdr("Lname")

            End While

        End If

During testing the code stops at line

If rdr.IsDBNull(index) Then

showing the error invalid attempt to read when no data is present.

How can I tell the code to proceed down the if statement?

You need to call `rdr.Read()` first _before_ you try getting any values, including `IsDBNull(int)`. If your program expects a single row (and never zero rows) then you should throw an exception if the first call to `rdr.Read() == false`. — Dai, Aug 09 '23 at 01:42
`where Login_ID = '" & txtReport.Text & "'"` <-- [**DO NOT DO THIS**: Your program is vulnerable to both SQL injection](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) and breaking if `txtReport.Text` contains an apostrophe character... you **must** use parameters here (and don't use `AddWithValue` either). — Dai, Aug 09 '23 at 01:44
I suspect that what you actually want in that context is the `HasRows` property. What are you actually trying to do there? Are you trying to determine whether there are any records at all in the result set, i.e. whether any records matched the `WHERE` clause, or whether a particular record in the current row is `NULL`? If the former, `HasRows` is what you want. If the latter, it should be obvious that there needs to be a current row first, so you have to call `Read` first and then call `IsDBNull`. — jmcilhinney, Aug 09 '23 at 01:47

score 0 · Answer 1 · answered Aug 09 '23 at 06:18

In your example, the check for DbNull would be fine but you first need to check if any data came back first with rdr.Read (so execute the rdr.Read command and it will return false if no rows). I find often, it somewhat easier to return the data into a data table.

So, then say this code:

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim cmd As New SqlCommand("SELECT * FROM tblHotelsA 
                               WHERE ID = @id")
    cmd.Parameters.Add("@id", SqlDbType.Int).Value = txtHotelID.Text

    Dim rstData As DataTable = MyRstP(cmd)

    If rstData.Rows.Count > 0 Then
        With rstData.Rows(0)
            txtFirst.Text = .Item("FirstName")
            txtLast.Text = .Item("LastName")
            txtHotel.Text = .Item("HotelName")
        End With
    Else
        RptNm.Text = "Hotel not found."
    End If


End Sub

Public Function MyRstP(cmdSQL As SqlCommand) As DataTable

    Dim rstData As New DataTable
    Using conn As New SqlConnection(My.Settings.TEST4)
        Using (cmdSQL)
            cmdSQL.Connection = conn
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
        End Using
    End Using

    Return rstData

End Function

And MyRstP can be placed in a standard module, since then you can call that code from anywhere in your application.

Now, if say FirstName, and LastName, and HotelName in above could be null?

Then that will trigger an error. So, you can still of course have a row returned, but a column could still be empty with a null.

In that case, then this code will work if some of the columns are null.

            txtFirst.Text = .Item("FirstName").ToString
            txtLast.Text = .Item("LastName").ToString
            txtHotel.Text = .Item("HotelName").ToString

So, above can save quite a bit of messy code to check for null values, and if any of those columns are null, then an empty string ("") will be placed in those text boxes.

So, I suggest a data table. This works much like a "recordset" from say VBA, or VB5/VB6 days. And you can traverse the data table more then once, and you can even make changes to the row(s), and then send back all row changes in one operation.

So, for pulling of data, and that of writing out data, then data tables can save you quite a bit of code.

VB.Net - SQLDataReader handling Null values

1 Answers1