VB.NET创建用户前如何检查MySQL中是否已存在该记录？

Hey there! Let's get your user existence check sorted out before inserting new records into your MySQL database. Right now your code skips that verification step, plus we should fix a couple of small issues (like SQL injection risks and type mismatches) to make your app more robust.

Step 1: Add a User Existence Check Method #

First, let's create a reusable method that checks if a username already exists in the people table. We'll use parameterized queries here to avoid SQL injection—this is super important, never concatenate user input directly into SQL statements!

Private Function IsUsernameExists(username As String) As Boolean
    Dim checkQuery As String = "SELECT COUNT(*) FROM people WHERE Username = @Username"
    Using cmd As New MySqlCommand(checkQuery, SQLConnection)
        cmd.Parameters.AddWithValue("@Username", username)
        Dim count As Integer = Convert.ToInt32(cmd.ExecuteScalar())
        Return count > 0
    End Using
End Function

Step 2: Update the Button Click Logic #

Now, modify your Button1_Click event to call this check before inserting the new user. We'll also fix the type mismatch with StudentClassReal (it's declared as an Integer, so don't compare it to a string "9") and clean up the input validation flow:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    FirstName = AFirstNameTextBox.Text.Trim()
    SecondName = ASecondNameTextBox.Text.Trim()
    FullName = $"{FirstName} {SecondName}"
    StudentClassValue = ASelectClassComboBox.SelectedItem?.ToString()
    Address = AAddressTextBox.Text.Trim()
    Username = AUsernameTextBox.Text.Trim()
    Password = APasswordTextBox.Text.Trim()

    ' Fix StudentClassReal assignment (type-safe)
    StudentClassReal = If(StudentClassValue = "Class IX", 9, 0)
    If StudentClassReal = 0 Then
        MessageBox.Show("You have selected a Wrong Class", "Wrong Class", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return ' Exit early if class is invalid
    End If

    ' Input validation (exit early if any field is empty)
    If String.IsNullOrWhiteSpace(FirstName) Then
        MessageBox.Show("You didn't enter your First Name", "First Name", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return
    End If
    If String.IsNullOrWhiteSpace(SecondName) Then
        MessageBox.Show("You didn't enter your Second Name", "Second Name", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return
    End If
    If String.IsNullOrWhiteSpace(Address) Then
        MessageBox.Show("You didn't enter your Address", "Address", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return
    End If
    If String.IsNullOrWhiteSpace(Username) Then
        MessageBox.Show("You didn't enter your Username", "Username", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return
    End If
    If String.IsNullOrWhiteSpace(Password) Then
        MessageBox.Show("You didn't enter your Password", "Password", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Return
    End If

    ' Verify user information
    Dim StudentInformationVerification As Integer = MessageBox.Show(
        $"Are you sure that these are your information?{vbCrLf}I am {FullName}, and I study at Class {StudentClassReal}. I live in {Address}. My Advanced Windows Study Username is {Username}, and my password is {Password}",
        "Information Verification", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

    If StudentInformationVerification = DialogResult.Yes Then
        ' Check if username already exists
        If IsUsernameExists(Username) Then
            MessageBox.Show("This username is already taken! Please choose another one.", "Username Exists", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Return
        End If

        ' Use parameterized query for insertion too (avoid SQL injection)
        Dim SQLStatement As String = "INSERT INTO people(FirstName, SecondName, Class, Address, Username, Password) VALUES(@FirstName, @SecondName, @Class, @Address, @Username, @Password)"
        Using cmd As New MySqlCommand(SQLStatement, SQLConnection)
            cmd.Parameters.AddWithValue("@FirstName", FirstName)
            cmd.Parameters.AddWithValue("@SecondName", SecondName)
            cmd.Parameters.AddWithValue("@Class", StudentClassReal)
            cmd.Parameters.AddWithValue("@Address", Address)
            cmd.Parameters.AddWithValue("@Username", Username)
            cmd.Parameters.AddWithValue("@Password", Password)
            cmd.ExecuteNonQuery()
        End Using

        My.Computer.Registry.LocalMachine.SetValue("Study", "1")
        SuccessfulMessage = 1
        MessageBox.Show("Welcome to Advanced Windows Studying", "Authentication Successful", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Me.Close()
    End If
End Sub

Step 3: Fix Other Minor Issues #

• In your ServerString, you had a typo: Passwordmy=Password should be Password=myPassword (adjust to your actual database password).
• In the WindowsAdvancedStudyStartingForms_Load event, the GoTo SqlConnection has a case mismatch with your label SQLConnection:—better to replace the GoTo with a loop for cleaner code, but if you keep it, make sure the label matches exactly.
• Critical Security Note: Storing passwords in plain text is a huge risk! Consider hashing passwords with a library like BCrypt or PBKDF2 before saving them to the database.

Why This Works #

• The IsUsernameExists method runs a count query on the people table for the given username. If the count is greater than 0, the username is already taken.
• Parameterized queries prevent SQL injection attacks by separating user input from the SQL command structure.
• Early returns in validation make the code flow cleaner, avoiding unnecessary nested Else blocks that do nothing.

内容的提问来源于stack exchange，提问作者Swastik Bhattacharyya