SQL Reader Problems

Hey guys, I'm pretty new to SQL and I'm having the following problem.

I am creating a program in VB that looks through Multiple SQL tables for information. I have all the looping done to access all the tables, however, the SQL reader fails whenever I hit a table that doesn't have any information in the column I'm looking up. (These tables are constantly being modified so it may go from having data in the column one day, to the column being cleared the next)

Is there a way to use the reader but instead of throwing exceptions (crashing the program), make the reader skip over tables that don't have anything in the column I'm searching under???

Thanks in advance!!

You can do it one of two ways. Handle it in your SELECT statement (Use isnull(field, 'default value')) or you can handle it in your app; like so:

VB Code:

Dim read As SqlClient.SqlDataReader
            If Not read.IsDBNull(0) Then
                Myval = read.GetValue(0)
            End If

well if you insert proper try/catch exception handling then your program won't crash when it hits an exception..

or you may not need exception handling, it may be that you just need to check the column to see if its null, and skip it if so.. but I am not sure if that is what you meant by "doesn't have any information in the column i'm looking up"

Quote:

---

Originally Posted by kleinma

well if you insert proper try/catch exception handling then your program won't crash when it hits an exception..

or you may not need exception handling, it may be that you just need to check the column to see if its null, and skip it if so.. but I am not sure if that is what you meant by "doesn't have any information in the column i'm looking up"

---

that is exactly what I need to do... the VB example above is still throwing the "Invalid attempt to read when no data is present" exception. I'll see what I can do with the SQL example...

it sounds like there's nothing in your reader. Check .HasRows to see if anything was returned.

Actually, you know what? Just post your reader code. :p

VB Code:

sqlConn.Open()
                DatabaseCommand.Connection = sqlConn
                DatabaseCommand.CommandText = "SELECT Counter FROM " & tableList2(table).ToString & " WHERE Counter = (SELECT MAX(Counter) FROM " & tableList2(table).ToString & ") ;"
                Dim rdrMyReader As SqlDataReader
                rdrMyReader = DatabaseCommand.ExecuteReader
                rdrMyReader.Read()
                If Not rdrMyReader.IsDBNull(0) Then
                    UpperBound = rdrMyReader.GetValue(0)
                End If
                sqlConn.Close()

like seven said.. check for rows before reading..

VB Code:

sqlConn.Open()
                DatabaseCommand.Connection = sqlConn
                DatabaseCommand.CommandText = "SELECT Counter FROM " & tableList2(table).ToString & " WHERE Counter = (SELECT MAX(Counter) FROM " & tableList2(table).ToString & ") ;"
                Dim rdrMyReader As SqlDataReader
                rdrMyReader = DatabaseCommand.ExecuteReader
                if rdrMyReader.HasRows then
                    rdrMyReader.Read()
                    If Not rdrMyReader.IsDBNull(0) Then
                        UpperBound = rdrMyReader.GetValue(0)
                    End If
                    sqlConn.Close()
                else
                    'anything you may do when there are no rows
                end if

VB Code:

Try
            sqlConn.Open()
            DatabaseCommand.Connection = sqlConn
            DatabaseCommand.CommandText = "SELECT TOP 1 Counter FROM " & tableList2(table).ToString & " ORDER BY counter DESC;"
            Dim rdrMyReader As SqlClient.SqlDataReader
            rdrMyReader = DatabaseCommand.ExecuteReader(CommandBehavior.SingleRow)
            While rdrMyReader.Read
                If Not rdrMyReader.IsDBNull(0) Then
                    UpperBound = rdrMyReader.GetValue(0)
                End If
            End While
        Catch ex As Exception
            'Handle
        Finally
            If sqlConn.State = ConnectionState.Open Then
                sqlConn.Close()
            End If
        End Try

Or if I'm reading your code right, you can totally do away with the reader:

VB Code:

Try
            sqlConn.Open()
            DatabaseCommand.Connection = sqlConn
            DatabaseCommand.CommandText = "SELECT TOP 1 Counter FROM " & tableList2(table).ToString & " ORDER BY counter DESC;"
            UpperBound = DatabaseCommand.ExecuteScalar
        Catch ex As Exception
            'Handle
        Finally
            If sqlConn.State = ConnectionState.Open Then
                sqlConn.Close()
            End If
        End Try

Quote:

---

Originally Posted by sevenhalo

Or if I'm reading your code right, you can totally do away with the reader:

VB Code:

Try
            sqlConn.Open()
            DatabaseCommand.Connection = sqlConn
            DatabaseCommand.CommandText = "SELECT TOP 1 Counter FROM " & tableList2(table).ToString & " ORDER BY counter DESC;"
            UpperBound = DatabaseCommand.ExecuteScalar
        Catch ex As Exception
            'Handle
        Finally
            If sqlConn.State = ConnectionState.Open Then
                sqlConn.Close()
            End If
        End Try

---

Thanks man! That worked out perfectly. :thumb: