VB.NET中SQL参数String转Int32失败及PDF生成问题求助

错误原因分析 #

错误Failed to convert parameter value from a String to a Int32的核心原因：

• 当通过SqlDataAdapter给TextBox1赋值时，可能出现dt(0)(0)返回DBNull或非整数字符串的情况，导致TextBox1.Text不是有效整数
• 直接将字符串类型的TextBox1.Text赋值给SqlDbType.Int类型的参数，未做类型转换验证

此外你的第二段代码存在逻辑冗余和顺序错误：

• 在adapter.Fill(dt)之前就循环dt，此时dt未填充数据，这段代码完全无效
• 同时使用SqlDataAdapter填充DataTable和SqlDataReader读取数据，属于重复操作
• 空Catch块会掩盖潜在异常，不利于调试

需求1：从SQL读取数据到TextBox1 #

读取单个ID到TextBox1 #

以下代码实现从数据库读取第一条记录的ID到TextBox1：

Dim connect As String = "Data Source=DESKTOP-D32ONKB;Initial Catalog=Attendance;Integrated Security=True"
Using conn As New SqlConnection(connect)
    Dim sql As String = "SELECT TOP 1 ID FROM stuattrecordAMPM ORDER BY ID"
    Using command As New SqlCommand(sql, conn)
        conn.Open()
        Dim result = command.ExecuteScalar()
        If result IsNot DBNull.Value Then
            TextBox1.Text = result.ToString()
        Else
            TextBox1.Text = "无数据"
        End If
    End Using
End Using

循环读取所有ID到TextBox1 #

如果需要像原For循环那样依次将每个ID赋值给TextBox1，可使用以下代码：

Dim connect As String = "Data Source=DESKTOP-D32ONKB;Initial Catalog=Attendance;Integrated Security=True"
Using conn As New SqlConnection(connect)
    Dim sql As String = "SELECT ID FROM stuattrecordAMPM ORDER BY ID"
    Using command As New SqlCommand(sql, conn)
        conn.Open()
        Using rdr As SqlDataReader = command.ExecuteReader()
            While rdr.Read()
                TextBox1.Text = rdr("ID").ToString()
                ' 可选：添加延迟查看每个ID
                System.Threading.Thread.Sleep(1000)
            End While
        End Using
    End Using
End Using

需求2：修正PDF生成代码并解决转换错误 #

以下是优化后的完整PDF生成代码，从根源避免类型转换错误：

' 先验证TextBox1内容是否为有效整数
Dim targetId As Integer
If Not Integer.TryParse(TextBox1.Text, targetId) Then
    MessageBox.Show("请输入有效的整数ID")
    Return
End If

Dim table As New PdfPTable(4)
table.TotalWidth = 416.0F
table.LockedWidth = False
Dim widths As Single() = New Single() {1.0F, 2.0F, 3.0F, 4.0F}
table.SetWidths(widths)
table.HorizontalAlignment = 0
table.SpacingBefore = 20.0F
table.SpacingAfter = 30.0F
Dim cell As New PdfPCell(New Phrase("Table Batch"))
cell.Colspan = 4
cell.Border = 0
cell.HorizontalAlignment = 1
table.AddCell(cell)

Dim connect As String = "Data Source=DESKTOP-D32ONKB;Initial Catalog=Attendance;Integrated Security=True"
Using conn As New SqlConnection(connect)
    Dim pdfDoc As New Document()
    ' 用验证后的整数生成安全的文件名
    Dim pdfPath As String = $"D:\pdf\{targetId}.pdf"
    Dim pdfWrite As PdfWriter = PdfWriter.GetInstance(pdfDoc, New FileStream(pdfPath, FileMode.Create))
    pdfDoc.Open()

    Dim sql As String = "SELECT ID,Name,Class,Date FROM stuattrecordAMPM where ID=@ID"
    Using command As New SqlCommand(sql, conn)
        ' 直接使用已验证的整数参数
        command.Parameters.Add("@ID", SqlDbType.Int).Value = targetId
        Try
            conn.Open()
            Using rdr As SqlDataReader = command.ExecuteReader()
                While rdr.Read()
                    table.AddCell(rdr("ID").ToString())
                    table.AddCell(rdr("Name").ToString())
                    table.AddCell(rdr("Class").ToString())
                    table.AddCell(rdr("Date").ToString())
                End While
            End Using
        Catch ex As Exception
            MessageBox.Show($"生成PDF失败：{ex.Message}")
            pdfDoc.Close()
            Return
        End Try
        pdfDoc.Add(table)
        pdfDoc.Close()
        MessageBox.Show("PDF生成成功")
    End Using
End Using

关键优化点说明 #

1. 类型安全验证：用Integer.TryParse确保输入能转换为有效整数，从根源避免类型转换错误
2. 逻辑简化：移除冗余的SqlDataAdapter和无效循环，直接用SqlDataReader读取数据
3. 异常可见性：添加异常提示，便于调试定位问题
4. 参数安全：直接使用验证后的整数赋值参数，避免字符串转整数的风险
5. 文件名安全：用验证后的整数生成文件名，避免无效字符导致的文件创建失败

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