Check if there is a duplicate username in MySQL database

[HitNSplit]

Good day! I am new in vb.net and I want to detect if there is a duplicate of the record that a user will try to add. If the user adds an existing username from the database then it will show an error that the username has already been used.
Here is my code:
conn.Open()

        Dim qadd As String = "INSERT INTO tbl_user (uname, pword, ulvl) VALUES ('" & txt_uname.Text & "' , '" & txt_pword1.Text & "' , '" & txt_pword2.Text & "') ON DUPLICATE KEY UPDATE uname = ' " & txt_uname.Text & " '"
        Dim cmd As New MySqlCommand(qadd, conn)
        Dim data As MySqlDataReader = cmd.ExecuteReader
        MsgBox("User has been added!", MsgBoxStyle.Information)
        txt_uname.Clear()
        txt_pword1.Clear()
        txt_pword2.Clear()
        txt_uname.Focus()
        conn.Close()

[Papa_Don]

Here's what I've done. Keep in mind that, in my search I was looking to see if an existing account number exists. But you can do the same thing with names, addresses, etc. Just change the data field you're searching.

Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim query As String = "Select Count (*) As CustAccntNo FROM CustRec WHERE CustAccntNo = @AcctNo"
        Try
            con.ConnectionString = "Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=C:\USERS\DON\DOCUMENTS\SALES.MDF;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
            con.Open()
            cmd.Connection = con
            cmd = New SqlCommand((query), con)
            cmd.Parameters.AddWithValue("@AcctNo", SqlDbType.BigInt).Value = Convert.ToInt64(tbxAccntNo.Text)
            execScarlar = Convert.ToInt64(cmd.ExecuteScalar())

        Catch ex As Exception
            MessageBox.Show("Invalid" & ex.Message)
        Finally
            con.Close()

        End Try
        If execScarlar <> 0 Then
                MessageBox.Show("Account No. " & tbxAccntNo.Text & " has already been used. Please use another account number.", "Customer Account Number")
                tbxAccntNo.Text = ""
                tbxAccntNo.Select()
                Exit Sub
        End If

[Dani]

HitNSplit, may I ask what the PRIMARY and UNIQUE indexes of the MySQL table look like? The reason I ask is because you have ON DUPLICATE KEY specified. If the primary key for the table is username, then basically if it tries to insert a new record with a specific username, and the username is already taken, it will overwrite that record with the new username while leaving the other columns of the original record. I highly doubt this is what you're trying to do.

What you can do instead is just INSERT IGNORE the new record into the table without overwriting anything if the username/id/whatever your primary key is already exists. In other words, completely remove the ON DUPLICATE KEY UPDATE part of the query. The IGNORE keyword will allow you to insert a new record into the table without giving an error if a duplicate key exists. Then, right after, you can SELECT ROW_COUNT(); which will return how many rows were inserted/updated as a result of the immediately previous SQL query. If 0 rows were inserted/updated, you know the insert failed because there was a duplicate record, and you can give the user an error message. If 1 row is returned, you know the new record was inserted successfully.

Good luck.

[Dani]

I would just like to add, I would not recommend doing it the way Papa_Don suggets. In his code, he is first doing a SELECT query to see if a duplicate row exists, and if it does, throw an error message. Otherwise, he assumes you can INSERT the record successfully. There are two reasons not to do it this way.

The first is because it's an extra SELECT query for no reason. It's more efficient to do an INSERT and then check the resulting status of that INSERT, than to do a SELECT followed by an INSERT.

The second reason is simply because it can easily introduce a race condition when the app is utilized by multiple users simultaneously. It doesn't account for an INSERT query introducing a duplicate record at the very instant or milliseconds after the SELECT query is performed by a different client, two similar INSERTS happening simultaneously, etc.

For example, an end-user can accidentally double-click the submit button and the script is executed twice. Both SELECTs might run on the server before either INSERT is run, resulting in duplicate rows being added near-simultaneously.

[Dani]

This is very weird.

Papa Don, I just got an email saying you commented on my post writing:

Dani, I see your point. You are correct that I am doing both a SELECT and a INSERT statement. I would be more efficient to do the single INSERT.

However, I don't see your comment :-/ Did you delete it?

[Papa_Don]

@Dani,

I just tried to use the comment button inside your post. However I can't see this either. But I'm not deleting them I gather you saw my comment in your email. Like you I don't see it here either.

For the record, I don't know how to delete anything here. I've not found an edit/delete button.

[Dani]

Post comments show up when you vote on a post and provide a comment while doing so. If you revoke/change your vote, your comment gets deleted as well. I received an email that you left me a post comment and upvoted the post, but then when I viewed the post, it was gone.

As for editing/deleting posts, you can only do so for the first handful of minutes after posting, to correct typos and that sort of thing. We had way too many students/people who would post a question, get the help they needed, and then go back and edit their original question saying, "Problem solved. No help needed." completely breaking the thread and devaluing all the replies.

If you hover over a recent post, there should be a little pencil icon to edit it.