VB代码运行报Conversion from string "" to Double is not valid错误如何解决

问题根因 #

报错是VB的隐式类型转换导致的：你数据库中tipp1/tipp2为varchar类型，存在空字符串""的记录，而goals1/goals2是int类型，直接比较DataRow返回的Object类型字段时，VB会自动尝试把空字符串转为数值类型做匹配，就触发了空字符串转Double失败的异常。另外代码中_row1("iCount") = "1"的写法也有问题，int类型的iCount和字符串"1"比较也会触发隐式转换，增加报错概率。

修复方案 #

把原来的行内比较逻辑替换为显式类型转换+合法性校验的逻辑，修改后的代码如下：

Private Sub mdlCalculate()
    Try
        Dim _sql As String = "SELECT DISTINCT username FROM fly_tippspiel_tipps"
        Dim _dt As DataTable = cData.getDataTable(_sql, 5, GetCurrentMethod.Name, ControlName)

        If _dt.Rows.Count < 1 Then Exit Sub

        _sql = "DELETE FROM fly_tippspiel_ranking"
        cData.exeNonQuery(_sql, 5, GetCurrentMethod.Name, ControlName)

        For Each _row As DataRow In _dt.Rows
            _sql = "SELECT Table1.goals1, Table1.goals2, Table1.result1, Table1.tipp1, Table1.tipp2, Table1.result2," & _
                " ISNULL(Table2.iCount,0) AS iCount" & _
                " FROM (" & _
                    " SELECT fly_tippspiel_games.id, fly_tippspiel_games.goals1, fly_tippspiel_games.goals2, fly_tippspiel_games.result AS result1," & _
                    " fly_tippspiel_tipps.tipp1, fly_tippspiel_tipps.tipp2, fly_tippspiel_tipps.result AS result2" & _
                    " FROM fly_tippspiel_games" & _
                    " INNER JOIN fly_tippspiel_tipps ON fly_tippspiel_games.id = fly_tippspiel_tipps.games_id" & _
                    " WHERE (fly_tippspiel_tipps.username = '" & _row("username") & "')" & _
                    " AND (NOT (fly_tippspiel_games.goals1 IS NULL))) AS Table1" & _
                " FULL JOIN" & _
                    " (SELECT fly_tippspiel_games.id, COUNT(fly_tippspiel_games.id) AS iCount" & _
                    " FROM fly_tippspiel_games" & _
                    " INNER JOIN fly_tippspiel_tipps ON fly_tippspiel_games.id = fly_tippspiel_tipps.games_id" & _
                    " WHERE goals1 = tipp1 AND goals2 = tipp2" & _
                    " GROUP BY fly_tippspiel_games.id) AS Table2" & _
                " ON Table1.id = Table2.id" & _
                " WHERE NOT Table1.goals1 IS NULL"
            Dim _dt1 As DataTable = cData.getDataTable(_sql, 5, GetCurrentMethod.Name, ControlName)

            Dim _sum As Integer = 0
            For Each _row1 As DataRow In _dt1.Rows
                ' 显式转换所有字段，处理空值
                Dim goals1 As Integer = CInt(_row1("goals1"))
                Dim goals2 As Integer = CInt(_row1("goals2"))
                Dim tipp1Str As String = If(_row1("tipp1") Is DBNull.Value, "", _row1("tipp1").ToString().Trim())
                Dim tipp2Str As String = If(_row1("tipp2") Is DBNull.Value, "", _row1("tipp2").ToString().Trim())
                Dim iCount As Integer = CInt(_row1("iCount"))
                Dim result1 As Integer = CInt(_row1("result1"))
                Dim result2 As Integer = CInt(_row1("result2"))

                ' 校验tipp是否为合法数值，不合法直接跳过不计分
                Dim tipp1 As Integer, tipp2 As Integer
                If Not Integer.TryParse(tipp1Str, tipp1) OrElse Not Integer.TryParse(tipp2Str, tipp2) Then
                    Continue For
                End If

                ' 同类型比较，无隐式转换风险
                If goals1 = tipp1 AndAlso goals2 = tipp2 AndAlso iCount = 1 Then
                    _sum = _sum + 5     ' 唯一全中得分
                ElseIf goals1 = tipp1 AndAlso goals2 = tipp2 Then
                    _sum = _sum + 3     ' 全中得分
                ElseIf result1 = result2 Then
                    _sum = _sum + 1     ' 趋势正确得分
                End If
            Next

            _sql = "INSERT INTO fly_tippspiel_ranking (username, points) " & _
                   "VALUES ('" & _row("username") & "', " & _sum & ")"
            cData.exeNonQuery(_sql, 5, GetCurrentMethod.Name, ControlName)
        Next
    Catch ex As Exception
        cData.exMessage(ex.ToString, GetCurrentMethod.Name, ControlName)
    End Try
End Sub

额外优化建议 #

• 建议在代码文件顶部添加Option Strict On，强制开启显式类型转换检查，编译阶段就能发现这类隐式转换的问题，不需要等到运行时报错。
• 目前SQL拼接的写法存在SQL注入风险，建议改用参数化查询替代字符串拼接。

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