Looping through SQLdatareader and comparing row values

Question

I have a function that calls a stored procedure. I need to compare the result to an input parameter. I keep getting a warning that the function does not return on all code paths, but I can't figure out where.

So, 2 questions: 1. Am I looping through the SqlDataReader correctly or should I fill a datatable with my reader results? 2. Where am I missing a return value?

Function code:

Function FZCheck(FZ As String) As Boolean

    Dim constr As String = My.Settings.devTOD.ToString
    Dim con As New SqlConnection(constr)

    Dim cmd As New SqlCommand("spSelectFloodZones", con)
    cmd.CommandType = CommandType.StoredProcedure

    If con.State = ConnectionState.Closed Then
        con.Open()
    End If

    Dim rdr As SqlDataReader
    rdr = cmd.ExecuteReader

    If rdr.HasRows Then
        Do While rdr.Read
            If String.Equals(rdr(0).ToString, FZ) = True Then
                Return True
            Else
                Return False
            End If
        Loop
    Else
        Return False
    End If

    If con.State = ConnectionState.Open Then
        con.Close()
    End If

    rdr.Close()

End Function

Stored proc code: (very simple)

ALTER PROCEDURE [dbo].[spSelectFloodZones]
AS
    SET NOCOUNT ON
    SET ROWCOUNT 0

-- ====================
-- Select flood zones
-- ====================

SELECT DISTINCT FloodZone
FROM TOD.dbo.FloodZones

Language is VB.NET, using SQL Server 2012.

Why don't you pass that parameter to the stored procedure and change its code with a WHERE clause? You will avoid a lot of code in the VB.NET part — Steve
– Steve, Commented Aug 11, 2015 at 15:32
@Steve what an excellent idea, can't believe I didn't think of that. I will try that now. — alybaba726
– alybaba726, Commented Aug 11, 2015 at 15:33
Not related to the question but it is always best to use Using for automatic resource handling. — zulqarnain
– zulqarnain, Commented Aug 11, 2015 at 15:39

zulqarnain · Accepted Answer · 2015-08-11 15:35:16Z

1

Here you go with some minor refactoring.

Function FZCheck(FZ As String) As Boolean
        Dim constr As String = My.Settings.devTOD.ToString
        Dim con As New SqlConnection(constr)
        Dim result As Boolean = False

        Dim cmd As New SqlCommand("spSelectFloodZones", con)
        cmd.CommandType = CommandType.StoredProcedure

        If con.State = ConnectionState.Closed Then
            con.Open()
        End If

        Dim rdr As SqlDataReader
        rdr = cmd.ExecuteReader

        If rdr.HasRows Then
            Do While rdr.Read
                If String.Equals(rdr(0).ToString, FZ) = True Then
                    result = True
                End If
            Loop
        End If

        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        rdr.Close()
        Return result
    End Function

answered Aug 11, 2015 at 15:35

zulqarnain

1,7452 gold badges16 silver badges35 bronze badges

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

1 Comment

alybaba726 Over a year ago

As soon as it will let me (one more minute!)

Collectives™ on Stack Overflow

Looping through SQLdatareader and comparing row values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related