CODEWITHSUNDEEP
vb.netif-statement
LBPLC
LBPLC

Reputation: 1571

VB.Net Code exits routine on false if statement without executing 'Else' command

I'm using the following code to connect to an Access database and check if user's names are added to a table, if they are, then depending on which table they belong to directly affects their access levels to the software.

Public Sub Check_Database_For_Access_Level(Name As String)


    Dim myCon = New OleDbConnection(My.Settings.Database_Connection_String)
    myCon.Open()
    Dim dr As OleDbDataReader


    Dim Str

    Str = "SELECT * FROM [Admin] WHERE [Emp Name]=?"
    Dim cmd As OleDbCommand = New OleDbCommand(Str, myCon)
    cmd.Parameters.AddWithValue("Emp Name", Name)
    dr = cmd.ExecuteReader
    dr.Read()

    If dr("Emp Name").ToString = Name Then 'ERROR HERE
        My.Settings.Setting_AccessLevel = "Administrator"
    Else
        Str = "SELECT * FROM [ReadWrite] WHERE [Emp Name]=?"
        cmd.Parameters.AddWithValue("Emp Name", Name)
        dr = cmd.ExecuteReader
        dr.Read()

        If dr("Emp Name").ToString = Name Then
            My.Settings.Setting_AccessLevel = "Read And Write"
        Else

        End If

    End If



    myCon.Close()
End Sub

The line:

If dr("Emp Name").ToString = Name Then 'ERROR HERE

operates as it is supposed to if the result is true, but if it is false, the sub simply exits?

How come it isn't firing the Else part? Even stepping through it simply exits.

Upvotes: 0

Views: 53

Answers (2)

Andrew Morton
Andrew Morton

Reputation: 25013

It would be easier to SELECT COUNT(*) FROM... and use ExecuteScalar, as then a return value is pretty much guaranteed. Your intended check of the second table won't work because you haven't changed the .CommandText:

Using myCon As New OleDbConnection(My.Settings.Database_Connection_String)

    myCon.Open()

    Dim sql = "SELECT COUNT(*) FROM [Admin] WHERE [Emp Name]=?"

    Using cmd As New OleDbCommand(sql, myCon)
        cmd.Parameters.AddWithValue("Emp Name", name)
        Dim n = CInt(cmd.ExecuteScalar())

        If n = 1 Then
            My.Settings.Setting_AccessLevel = "Administrator"
        Else
            cmd.CommandText = "SELECT COUNT(*) FROM [ReadWrite] WHERE [Emp Name]=?"
            n = CInt(cmd.ExecuteScalar())

            If n = 1 Then
                My.Settings.Setting_AccessLevel = "Read And Write"
            Else
                ' no such user OR more than one user with the same name
            End If

        End If

    End Using

End Using

Upvotes: 2

Steve
Steve

Reputation: 216273

You SQL Statement contains a WHERE clause that already checks if the name passed as parameter is present or not in the database. So you don't really need to check again that condition. You could simply count how many records are present with that name and using a simpler ExecuteScalar to find out if you have at least one record with that name

Str = "SELECT COUNT(*) FROM [Admin] WHERE [Emp Name]=?"
Dim cmd As OleDbCommand = New OleDbCommand(Str, myCon)
cmd.Parameters.AddWithValue("Emp Name", Name)
dim result = Convert.ToInt32(cmd.ExecuteScalar)
if result > 0 Then
   ' You have the Admin with that name
else
   ' Check the other table
End If

Notice also that executing a COUNT is generally considered a bad method to accomplish this work but I am pretty sure that Access doesn't support more advanced techniques available in SQL Server and other database systems (IF EXISTS....)

Upvotes: 1

Related Questions