MySQL inserting a column into a variable

Question

I'm working on a project that needs to prevent double username inserted in a database my code looks like this:

    Command = New MySqlCommand("Select * From userscanner", Connection)
            Command.ExecuteNonQuery()
            Dim dr As MySqlDataReader
            dr = Command.ExecuteReader
            With dr
                .Read()
                Dim check As String = .Item(1)
                .Close()
                If check = txtbox_username.Text Then
                    MsgBox("Username Already Taken")
                    Exit Sub

My problem is it only gets 1 column or is there any other way to prevent double username in my database?

I need all column in my username column. I'm using VB and MySQL.

Why don't directly query for the entered username in the database? — Josh Part
– Josh Part, Commented Apr 10, 2015 at 19:54

Steve · Accepted Answer · 2015-04-10 20:03:33Z

3

You should ask your database if a particular user name exists or not. This could be done with a WHERE clause

 Dim sqlText = "SELECT 1 FROM userscanner WHERE username = @name"
 Using Command = New MySqlCommand(sqlText, Connection)
    Command.Parameters.Add("@name", MySqlDbType.VarChar).Value = txtbox_username.Text 
    Dim result = Command.ExecuteScalar()
    if result IsNot Nothing Then
        MsgBox("Username Already Taken")
    End If
End Using

Here I assume that your database table named userscanner has a field named username (the field retrieved by your code with Item(1)) where you store the user names. Adding a WHERE condition and a simple return of 1 if there is a record allows to use the simple ExecuteScalar that returns the value 1 if there is a matching record to your textbox value or Nothing if there is no record

edited Apr 10, 2015 at 20:03

answered Apr 10, 2015 at 19:58

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ordiz Imbano Over a year ago

Thanks Your answer is really helpful but I make my own way based on your answer and I guess mine is a much simpler way. I'll post it and please let me know if it has disadvanteges

Trevor Over a year ago

@OrdizImbano your's isn't simpler at all, use parameter's as Steve has shown to do. Also wrap your command in using to ensure it is getting disposed and such. Just use his answer...

Michele La Ferla · Accepted Answer · 2015-04-15 14:50:40Z

-1

I found an answer to the question on my own.

 Command = New MySqlCommand("Select * From userscanner WHERE Username = '" & txtbox_username.Text & "'", Connection)
 Command.ExecuteNonQuery()
 Dim dr As MySqlDataReader
 dr = Command.ExecuteReader
 dr.Read()
 dr.Close()

 If dr.HasRows Then
    MsgBox("Username Already Taken")

Thanks for the help guys

edited Apr 15, 2015 at 14:50

Michele La Ferla

6,88211 gold badges57 silver badges84 bronze badges

answered Apr 10, 2015 at 20:07

Ordiz Imbano

311 silver badge8 bronze badges

2 Comments

S. Adam Nissley Over a year ago

The answer from @Steve has some advantages. What would happen if somebody entered the following user name: "';DROP TABLE userscanner;"???

Steve Over a year ago

NO, this is the wrong way to make a query. Never use string concatenations. Parameterized queries solve the problem of Sql Injection (Here a funny but instructive example) and what if your user name textbox contains a single quote? (for example O'Hare causes a syntax error here) . Then a disposable object like the command and the connection should be disposed after usage (the using statement does this) finally the ExecuteNonQuery has no meaning for a SELECT query.

Collectives™ on Stack Overflow

MySQL inserting a column into a variable

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related