如何在Visual Basic的SQL查询中使用数值变量避免类型转换错误

你触发的类型转换报错是因为VB语言中+运算符的特性：当+两侧同时存在字符串类型和Double数值类型时，VB会默认尝试将字符串转换为Double做数值加法，而不是执行你预期的字符串拼接操作，因此才会抛出将SQL开头的字符串转为Double失败的错误。

快速修复：替换字符串拼接符 #

将SQL拼接时使用的+替换为VB专门用于字符串拼接的&运算符即可，修改后的SQL语句如下：

sql = "SELECT GradeLetter FROM tblGrades WHERE " & _
      "CourseId = '1' AND (GradeBoundaryLow <= " & Percentage & ") " & _
      "AND (GradeBoundaryHigh >= " & Percentage & ")"

更安全的优化方案：使用参数化查询 #

直接拼接SQL字符串存在SQL注入风险，且容易出现类型匹配问题，更推荐使用参数化查询写法，代码示例如下：

Private Sub txtDenominator_TextChanged(sender As Object, e As EventArgs) Handles txtDenominator.TextChanged
    ' 先做输入合法性校验，避免非数字内容引发报错
    Dim Numerator As Integer
    Dim Denominator As Integer
    If Not Integer.TryParse(txtNumerator.Text, Numerator) OrElse Not Integer.TryParse(txtDenominator.Text, Denominator) Then
        Percentage = 0
        Exit Sub
    End If

    Dim Percentage As Double
    If (Numerator > 0) And (Denominator > 0) And (Numerator <= Denominator) Then
        Percentage = (Numerator / Denominator) * 100
    Else
        Percentage = 0
    End If

    Dim con As New OleDb.OleDbConnection
    con.ConnectionString = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source = G:/College Database.accdb"
    con.Open()
    ' 使用参数占位符
    Dim sql As String = "SELECT GradeLetter FROM tblGrades WHERE CourseId = '1' AND GradeBoundaryLow <= @Percentage AND GradeBoundaryHigh >= @Percentage"
    Dim cmd As New OleDb.OleDbCommand(sql, con)
    ' 传入参数，自动适配类型
    cmd.Parameters.AddWithValue("@Percentage", Percentage)
    da = New OleDb.OleDbDataAdapter(cmd)
    da.Fill(ds2, "Grades")
    con.Close()
End Sub

如果你的CourseId字段是数值类型，那么SQL里的CourseId = '1'需要去掉单引号，写为CourseId = 1，避免额外的类型转换问题。

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