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, Apr 10 '15 at 19:54

Steve · Accepted Answer · 2015-04-10T20:03:33.103

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 '15 at 20:03

answered Apr 10 '15 at 19:58

Steve

213,761
22
232
286

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 – Ordiz Imbano Apr 10 '15 at 20:07
1

@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... – Trevor Apr 10 '15 at 20:10

score -1 · Answer 2 · edited Apr 15 '15 at 14:50

-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 '15 at 14:50

Michele La Ferla

6,775
11
53
79

answered Apr 10 '15 at 20:07

Ordiz Imbano

31
1
8

The answer from @Steve has some advantages. What would happen if somebody entered the following user name: "';DROP TABLE userscanner;"??? – S. Adam Nissley Apr 10 '15 at 20:12
1

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](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work)) 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. – Steve Apr 10 '15 at 20:24

MySQL inserting a column into a variable

2 Answers2