数据库插入报错：将字符串"Insert into userinfo ('FirstName'"转换为Long类型无效的解决方法

Let's break down what's causing this error and fix it step by step — that "convert string to Long" message is a clue that your SQL statement has syntax and logic mixups confusing the database engine.

1. Root Causes of the Error #

• Wrong quotes around column names: You used single quotes ('FirstName') for column names, but single quotes are for string values in SQL. The database is trying to interpret these quoted column names as strings, leading to a type mismatch.
• Mismatched column-value order: Your INSERT column list (FirstName, MiddleName, LastName, Age, Gender, Birthdate) doesn't line up with your values. You're trying to put age data into the LastName column and gender data into the Age column — which is a huge type conflict (Age is numeric, Gender is text).
• Broken gender selection: radioValue1.Text Or radioValue2.Text uses VB's logical OR operator, which won't give you the selected radio button's text.
• Missing command execution: You created the SqlCommand but never ran it with ExecuteNonQuery() — so no data would insert even if everything else worked.
• SQL injection risk: String concatenation for queries is unsafe and prone to type errors.

Fixed Code #

Here's the corrected version with all issues addressed:

Dim bytFlag As Byte
Dim intI As Integer
Dim strC As String
Dim ans As Integer = MessageBox.Show("Are you sure on your inputs?", "ANADIET MEAL PLANNER", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

' Password validation logic stays the same
If Len(txtPW.Text) >= 8 Then
    For intI = 1 To Len(txtPW.Text)
        strC = Mid$(txtPW.Text, intI, 1)
        If strC >= "!" And strC <= "\" Then
            bytFlag = bytFlag Or &H8
        End If
        If strC >= "0" And strC <= "9" Then
            bytFlag = bytFlag Or &H4
        End If
        If strC >= ":" And strC <= "@" Then
            bytFlag = bytFlag Or &H8
        End If
        If strC >= "A" And strC <= "Z" Then
            bytFlag = bytFlag Or &H2
        End If
        If strC >= "a" And strC <= "z" Then
            bytFlag = bytFlag Or &H1
        End If
    Next intI
End If

' Simplified input validation (checks all empty fields at once)
If bytFlag <> &HF Then
    MessageBox.Show("Password must contain capital letters, small letters, numbers and special characters.", "ANADIET MEAL PLANNER", MessageBoxButtons.OK)
ElseIf txtAge.Text = "" Or txtFN.Text = "" Or txtLN.Text = "" Or txtPW.Text = "" Or txtRPW.Text = "" Or txtUN.Text = "" Or (Not radioValue1.Checked And Not radioValue2.Checked) Then
    MessageBox.Show("Please fill up all informations!", "ANADIET MEAL PLANNER", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
ElseIf txtPW.Text <> txtRPW.Text Then
    MessageBox.Show("Password do not match", "ANADIET MEAL PLANNER", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Else
    ' Validate age is a valid number first
    Dim age As Integer
    If Not Integer.TryParse(txtAge.Text, age) Then
        MessageBox.Show("Please enter a valid numeric age.", "ANADIET MEAL PLANNER", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Exit Sub
    End If

    ' Get selected gender correctly
    Dim gender As String = If(radioValue1.Checked, radioValue1.Text, radioValue2.Text)

    Try
        If ans = vbYes Then
            con.Open()
            ' Parameterized query to avoid injection and type errors
            Dim cmdText As String = "INSERT INTO userinfo (FirstName, MiddleName, LastName, Age, Gender, Birthdate) VALUES (@FirstName, @MiddleName, @LastName, @Age, @Gender, @Birthdate)"
            cmd = New SqlCommand(cmdText, con)
            
            ' Map parameters with correct data types
            cmd.Parameters.AddWithValue("@FirstName", txtFN.Text)
            cmd.Parameters.AddWithValue("@MiddleName", txtMN.Text)
            cmd.Parameters.AddWithValue("@LastName", txtLN.Text) ' You missed this in your original code!
            cmd.Parameters.AddWithValue("@Age", age)
            cmd.Parameters.AddWithValue("@Gender", gender)
            cmd.Parameters.AddWithValue("@Birthdate", DateTimePicker1.Value) ' Use .Value for proper DateTime type
            
            ' Execute the insert command
            cmd.ExecuteNonQuery()
            
            MessageBox.Show("Registration successful!", "ANADIET MEAL PLANNER", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    Catch ex As Exception
        MsgBox(ex.Message, vbCritical)
    Finally
        ' Ensure connection closes even if an error occurs
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
    End Try
End If

Key Improvements #

• Removed single quotes from column names (use square brackets [ColumnName] only if your column has special characters)
• Fixed column-value order and added the missing LastName value
• Properly retrieved the selected radio button's text for gender
• Added ExecuteNonQuery() to actually run the database insert
• Used parameterized queries to eliminate SQL injection and type mismatches
• Validated age is a numeric value before insertion
• Used DateTimePicker1.Value instead of .Text to pass a proper DateTime object to the database
• Added a Finally block to guarantee the connection closes safely

内容的提问来源于stack exchange，提问作者Janzen Huwee Nolido Baltores