Using 'Else' in While data.Read() does not work?

Question

What I want to happen is if, textbox3.Text does not equal data(0) value then I want the MsgBox("test") to trigger. However, it does not. If the value of textbox3 does not exist with data(0) I want MsgBox("test") to trigger. I've tried every variation I could think of and I cannot get it to work.

Right now, if textbox.Text does not equal data(0) value nothing happens. However, if textbox3.Text equals data(0) then both Label3.Text = data(1) and MsgBox("Join code has been applied.") work.

Dim conn As New MySqlConnection
conn.ConnectionString = "server=;userid=;password=;database="
conn.Open()
Dim sqlquery As String = "SELECT * FROM joincodes WHERE code = '" & TextBox3.Text & "';"
Dim data As MySqlDataReader
Dim adapter As New MySqlDataAdapter
Dim command As New MySqlCommand
command.CommandText = sqlquery
command.Connection = conn
adapter.SelectCommand = command
data = command.ExecuteReader
While data.Read()
    If data.HasRows() = True Then
        If TextBox3.Text = data(0) Then
            Label3.Text = data(1)
            MsgBox("Join code has been applied.")
        Else
            MsgBox("test")
        End If
    End If
End While

Do you have any data? MsgBox the values of TextBox3 and data(0), are they really what you expect? — Hans Keﬆing, Jan 01 '20 at 07:07
@HansKeﬆing Yes they are what i expect. Just when clicking the button when textbox3 is empty the Else does not trigger. However if i enter the correct value in the textbox then the join code msgbox is triggered. — Chip, Jan 01 '20 at 07:29
Try this: `If TextBox3.Text.Length > 0 AndAlso TextBox3.Text = data(0)` — InteXX, Jan 01 '20 at 08:19
Also: you'll find that your code readability will improve significantly when you give your controls meaningful names. `TextBox3` is more confusing than helpful. — InteXX, Jan 01 '20 at 08:21
Also: beware of SQL Injection attacks. Your code is vulnerable. More info [here](https://stackoverflow.com/q/14376473). — InteXX, Jan 01 '20 at 08:23
Note that if you are relying on the order of the columns from a database query (e.g. `data(0)`) then you have to specify that order in the query (e.g. `SELECT col1, col2 FROM joincodes`) because if you use `*` then they *could* be returned in any order. — Andrew Morton, Jan 01 '20 at 10:09
Usually you would check for `.HasRows` *before* trying to `.Read()` the data. — Andrew Morton, Jan 01 '20 at 10:12

Andrew Morton · Accepted Answer · 2020-01-01T17:28:57.073

There are a few things that need to be changed in the code.

Database connections have "unmanaged resources" associated with them, which means that you have to .Dispose() of them when you have finished using them. To avoid some fiddly code, VB.NET conveniently provides the Using statement.
It is best to give controls meaningful names because it is much easier to see what is going on in the code. E.g. if you accidentally typed TextBox37 when you meant TextBox87 it would be hard to see, but you wouldn't mistype tbUserName for tbFavouriteColour.
In MySQL, CODE is a keyword, so you need to escape it with backticks to be safe: MySQL Keywords and Reserved Words
Putting variables directly into SQL statements is generally a mistake. SQL parameters are used for doing that; they are easy to use and prevent a lot of problems.
If you are relying on the order of the columns from a database query (e.g. data(0)) then you must specify that order in the query (e.g. SELECT `col1`, `col2` FROM joincodes) because if you use * then they could be returned in any order.
You are probably only interested in the first returned value from the database (if there is a returned value), so I added the ORDER BY `col1` LIMIT 1.
Always use Option Strict On. It will save you time.

With regard to the question as asked, all you need to do is have a flag, I used a boolean variable named success, to indicate if things went right.

I also added some points indicated with 'TODO: in the following code which you'll need to take care of to make sure it works properly:

Option Infer On
Option Strict On

Imports MySql.Data.MySqlClient
' ... (other code) ... '

'TODO: Any type conversion from the string TextBox3.Text.'
'TODO: Give TextBox3 a meaningful name.'
Dim userCode = TextBox3.Text

Dim connStr = "your connection string"
Using conn As New MySqlConnection(connStr)

    'TODO: Use the correct column names.'
    Dim sql = "SELECT `col1`, `col2` FROM `joincodes` WHERE `code` = @code ORDER BY `col1` LIMIT 1"

    Using sqlCmd As New MySqlCommand(sql, conn)
        'TODO: Use correct MySqlDbType and change .Size if applicable.'
        sqlCmd.Parameters.Add(New MySqlParameter With {.ParameterName = "@code", .MySqlDbType = MySqlDbType.String, .Size = 24, .Value = userCode})
        Dim success = False
        Dim rdr = sqlCmd.ExecuteReader()

        If rdr.HasRows Then
            rdr.Read()
            'TODO: Change GetString to the appropriate Get<whatever>.'
            If rdr.GetString(0) = userCode Then
                success = True
                'TODO: Check that `col2` is a string - change the GetString call as required and call .ToString() on the result if needed.'
                Label3.Text = rdr.GetString(1)
                MessageBox.Show("Join code has been applied.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        End If

        If Not success Then
            MsgBox("test")
        End If

    End Using

End Using

Re the columns not having predictable order when using Select * . I understand that schema change can break such code and several other reasons as per https://dzone.com/articles/why-you-should-not-use-select-in-sql-query-1 but barring a schema change is the return order of columns still unpredictable? — Mary, Jan 01 '20 at 18:48
Thank you, That seems to have worked, however, the `col1` & 2 does not work, ive removed them and just used * and that works, all im doing is grabbing information from the database if tb3 exists in the db. Thank you. — Chip, Jan 01 '20 at 18:52
I really like the `Limit 1` which will save time on a large table but then what is the point of an `Order By` if you are only returning one record? — Mary, Jan 01 '20 at 18:54
@Mary You need *more* reasons than that? ;) I suppose that the query optimiser might do something that changed the order of the columns. — Andrew Morton, Jan 01 '20 at 18:54
@Mary I think that the ORDER BY is a requirement to use LIMIT. — Andrew Morton, Jan 01 '20 at 18:55
@Chip Did you use the actual column names from the database instead of `col1` and `col2`? — Andrew Morton, Jan 01 '20 at 18:56
@AndrewMorton I just tried it again, with the proper col1 name, it is defaulting to not success even if tb3 is correct. Its fine though, i can just use * its no big deal. Thank you for the help. — Chip, Jan 01 '20 at 19:19

Using 'Else' in While data.Read() does not work?

1 Answers1