Check username

[PF2G]

Hi,
i'm doing a registration form and i have problems checking if the username exists in the database.

Imports MySql.Data.MySqlClient

Public Class Admin_Reg
    Dim server As String = "Server=localhost;Database=escola_musica;Uid=root;Pwd=;"
    Dim con As New MySqlConnection

    Private Sub Admin_Reg_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = server
        Try
            If con.State = ConnectionState.Closed Then
                con.Open()
            Else
                con.Close()
                MsgBox("Ligação falhada!!")
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Public Sub admin_reg(ByRef sqlstate As String)
        Dim sql_ver As String = "SELECT username FROM admin WHERE username = '" & user_box.Text & "'"
        Dim cmd As New MySqlCommand(sql_ver, con)
        Dim dr As MySqlDataReader

        With cmd
            .CommandText = sqlstate
            .CommandType = CommandType.Text
            .Connection = con
            .ExecuteNonQuery()
        End With

        cmd = New MySqlCommand(sql_ver, con)

        dr = cmd.ExecuteReader

        con.Close()

        Dim rowCount As Integer

        If rowCount <> 0 Then
            MsgBox("Sorry, this user already exists!")
        Else
            MsgBox("This username is not taken!")
        End If

        If pass_box.Text <> cpass_box.Text Then
            MsgBox("Passwords não correspondem.")
        Else
            MsgBox("Adicionado com sucesso!!")
            con.Dispose()
        End If

    End Sub

    Private Sub reg_button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles reg_button.Click
        Dim sql As String = "INSERT INTO admin (username, pass) VALUES('" & user_box.Text & "', '" & pass_box.Text & "')"
        admin_reg(sql)
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Login.Show()
        Me.Close()
    End Sub
End Class

Can someone help me?

Thank you,
PF2G

[adam_k]

I'm not sure what you are planning to do with this code, as I'm not making any sense out of it, but definately this block of code needs to go :

With cmd
            .CommandText = sqlstate
            .CommandType = CommandType.Text
            .Connection = con
            .ExecuteNonQuery()
        End With

        cmd = New MySqlCommand(sql_ver, con)

I can't see where you declare and assign anything to sqlstate and then you try to execute a non query, before going down to re-initialize an already initialized and ready to use command.

Over to this part now:

dr = cmd.ExecuteReader

        con.Close()

        Dim rowCount As Integer

        If rowCount <> 0 Then
            MsgBox("Sorry, this user already exists!")
        Else
            MsgBox("This username is not taken!")
        End If

I don't see a connection between rowCount and anything, but there is a check if it's value is different than 0. Why do you use dr? Why not code it like:

Dim sql_ver As String = "SELECT count(*) FROM admin WHERE username = '" & user_box.Text & "'"
      Dim cmd As New MySqlCommand(sql_ver, con)

dim rowCount as Integer 
rowCount = cmd.ExecuteScalar

and then go ahead and check the value of rowCount

[anuj6928]

just fill that values in datatable and check the row count weather ... it is greater than 0 or not .. if it is greater than 0 that means username exists ... else
not exist ..

[Begginnerdev]

Have you tried:

If cmd.ExecuteNonQuery() = False Then
'Your code here
End If

[Reverend Jim]

I'm not sure what your intention is in Sub Admin_Reg_Load but the logic is

if the connection is closed then
    open it
else
    close it
end if

I can't imagine why you would need a Sub to toggle the state of the connection. Usually you would have code that says

if the connection is closed then
    open it
end if

or

if the connection is open then
    close it
end if

[adam_k]

@Reverend Jim: This would be the case, if only OP hadn't just set the connection string!!!

[Reverend Jim]

My point is that the Else clause is not necessary. The only purpose of that code is to ensure that the connection is open. To be picky, it should be in a Try block.

[adam_k]

I understand and agree, but in this particular code the whole if is not necessary.
Why check the connection status if you've just declared the connection/set a connection string.

My guess is that this code has been written without a clear flow in mind.