Is something with my logic in this ADO.NET code? it is always return false?

Question

This is a simple function that returns TRUE if the user exists in the users table, false otherwise.

For some reason it is always return false, I even removed the WHERE clause and I still get false? (a manual check in query analyzer tells me I have lots of rows?)

Public Shared Function DoesUserExist(ByVal userID As Integer) As Boolean

    Dim retValue As Boolean

    retValue = False

    Using conn As New SqlConnection(GetConnectionString())

  'Dim cmd As SqlCommand = New SqlCommand("SELECT user_ID FROM users WHERE user_ID = @userID", conn)
        Dim cmd As SqlCommand = New SqlCommand("SELECT user_ID FROM users", conn)


        cmd.Parameters.Add("@userID", SqlDbType.NVarChar).Value = userID
        cmd.CommandType = CommandType.Text

        conn.Open()

        Dim reader As SqlDataReader = cmd.ExecuteReader()


     'If Not reader Is Nothing Then
        '    HttpContext.Current.Response.Write("<br>Null")
        'End If

        If reader.Read() Then

            retValue = True

        End If


        conn.Close()

        cmd.Dispose()

    End Using


    retValue = False



    Return retValue

End Function

Answer 1

You are setting retValue to false before exiting. That's the problem.

This is the proper code:

...

    If reader.Read() Then

        retValue = True

    Else

        retValue = False

    End If


    conn.Close()

    cmd.Dispose()

End Using

Return retValue

....

Answer 2

it could be the line at the end that sets "retValue" to false?

Answer 3

That's because you set retValue to false before you return.

It doesn't matter what you set it to before - it will always be false at the end of the function by these lines:

retValue = False
Return retValue

Answer 4

You set retValue to false right before you return it, and there's no other Return statement in your code, so it doesn't matter what happens before. Remove the last retValue = False.

You want something like this:

Public Shared Function DoesUserExist(ByVal userID As Integer) As Boolean

    Dim retValue As Boolean

    retValue = False

    Using conn As New SqlConnection(GetConnectionString())
         // conditionally set retValue = true, etc.
    End Using

    Return retValue

End Function

Answer 5

There are two problems in the code.

1: You are not using the @userID parameter in the query. Therefore it always returns all users, and the only case that the result would be empty is if there are no users at all.

Dim cmd As SqlCommand = New SqlCommand("SELECT user_ID FROM users where user_ID = @UserID", conn)

2: You are setting the retValue variable to false again just before the return statement. Therefore the method always returns false regardless of result from checking the data reader.

Also, you should call Dispose on the data reader.