向SQL数据库插入GridView数据时遇字符串转Integer错误求助

错误原因 #

你遇到的Conversion from string "VisitorID" to type 'Integer' is not valid错误，是因为GridView的Cells集合不支持直接用列名作为索引。当你写row.Cells("VisitorID")时，实际获取到的是表头行中显示"VisitorID"的单元格文本，而非数据行对应的VisitorID数值，因此执行CInt()转换时会把字符串"VisitorID"转为整数，触发类型转换失败。

解决方法 #

方法1：直接从绑定的数据源（DataTable）取值（推荐） #

相比从GridView单元格文本取值，直接从原始DataTable中读取数据更可靠，避免因单元格格式化、列顺序变化导致的问题：

Dim url As String
url = bcintegration.GetInfoForIntegration(DropDownList1.SelectedValue).Rows(0)(1)

ServicePointManager.Expect100Continue = True
ServicePointManager.SecurityProtocol = CType(3072, SecurityProtocolType)
Dim json As String = (New WebClient).DownloadString(url)

' 将DataTable存入变量，后续直接使用
Dim dt As DataTable = JsonConvert.DeserializeObject(Of DataTable)(json)
IntegrationGridView.DataSource = dt
IntegrationGridView.DataBind()

' 循环DataTable的行，而非GridView的行
For Each dr As DataRow In dt.Rows
    If DropDownList1.SelectedValue = "SignInSystemEntityIntegration" Then
        Dim visitorID As Integer
        ' 先验证转换有效性，避免异常
        If Integer.TryParse(dr("VisitorID").ToString(), visitorID) Then
            bcintegration.SubmitDataForSignInSystemEntity(visitorID, Nothing, Nothing, Nothing, Nothing, Nothing, True, False, Nothing, Nothing, Nothing, 1)
        Else
            ' 处理转换失败的情况，比如跳过该行或记录日志
            ' Console.WriteLine($"无效的VisitorID值：{dr("VisitorID")}")
        End If
    End If
Next

方法2：通过控件ID获取单元格值（适用于GridView绑定了控件的场景） #

如果你的GridView中VisitorID列是用Label等控件显示的，可以通过FindControl定位控件后取值：

For Each row As GridViewRow In IntegrationGridView.Rows
    ' 只处理数据行，跳过表头/页脚行
    If row.RowType = DataControlRowType.DataRow Then
        If DropDownList1.SelectedValue = "SignInSystemEntityIntegration" Then
            Dim visitorID As Integer
            Dim lblVisitorID As Label = DirectCast(row.FindControl("lblVisitorID"), Label)
            If lblVisitorID IsNot Nothing AndAlso Integer.TryParse(lblVisitorID.Text, visitorID) Then
                bcintegration.SubmitDataForSignInSystemEntity(visitorID, Nothing, Nothing, Nothing, Nothing, Nothing, True, False, Nothing, Nothing, Nothing, 1)
            End If
        End If
    End If
Next

方法3：通过列索引获取单元格值 #

如果你确定VisitorID列在GridView中的固定索引（比如第0列），可以直接用索引访问：

For Each row As GridViewRow In IntegrationGridView.Rows
    If row.RowType = DataControlRowType.DataRow Then
        If DropDownList1.SelectedValue = "SignInSystemEntityIntegration" Then
            Dim visitorID As Integer
            If Integer.TryParse(row.Cells(0).Text, visitorID) Then
                bcintegration.SubmitDataForSignInSystemEntity(visitorID, Nothing, Nothing, Nothing, Nothing, Nothing, True, False, Nothing, Nothing, Nothing, 1)
            End If
        End If
    End If
Next

注意：方法3依赖列的固定顺序，如果后续GridView列顺序调整，代码会出错，因此优先级低于前两种方法。

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