CODEWITHSUNDEEP
mysqlvb.net
Chip
Chip

Reputation: 3

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

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

Upvotes: 0

Views: 74

Answers (1)

Andrew Morton
Andrew Morton

Reputation: 25013

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

Upvotes: 1

Related Questions