VB.NET搜索页面无法显示PDF二进制数据问题求助

问题说明 #

VB.NET新手，已完成数据插入操作，但搜索数据库中PDF二进制数据时，数据无法显示且出现报错，原代码如下：

Private Sub btnenter_Click(sender As Object, e As EventArgs) Handles btnenter.Click

        Dim con As SqlConnection = New SqlConnection("Data Source=AchevePC2;Initial Catalog=hrms;Integrated Security=True")
        Try
            con.Open()
            Dim query As String = "SELECT * FROM [application] WHERE 1 = 1"
            If txtappfrom.Text <> "" AndAlso txtappto.Text <> "" Then
                query += " AND applicationid BETWEEN '" + txtappfrom.Text + "' AND '" + txtappto.Text + "'"
            ElseIf txtappfrom.Text <> "" Then
                query += " AND applicationid = '" + txtappfrom.Text + "'"
            End If
            If dtpfrom.Text <> "" AndAlso dtpto.Text <> "" Then
                query += " AND interviewdtp BETWEEN '" + dtpfrom.Text + "' AND '" + dtpto.Text + "'"
            ElseIf dtpto.Text <> "" Then
                query += " AND interviewdtp = '" + dtpto.Text + "'"
            End If
            Dim cmd As New SqlCommand(query, con)
            Dim adp As New SqlDataAdapter(cmd)
            Dim dt As New DataTable
            adp.Fill(dt)
            If dt.Rows.Count > 0 Then
                ' Assuming you have a DataGridView named dgvApplications on ap06 form
                ap06.agv.DataSource = dt
                ap06.Show()
                apadd.Text = ""
            Else
                MessageBox.Show("No records found.", "No Records", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
            con.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

问题根源 #

1. SQL语句类型不匹配：applicationid应为数值类型，加单引号会触发数据库类型转换错误；日期字段直接用文本拼接，格式不兼容会导致解析失败。
2. SQL注入风险：直接拼接用户输入生成SQL，存在安全漏洞。
3. 二进制数据无法直接渲染：DataGridView默认不支持显示PDF二进制内容，直接绑定会出现乱码或空白。
4. 资源未妥善释放：未使用Using语句管理数据库连接，可能引发连接泄漏。

解决方案 #

1. 改用参数化查询修复SQL问题 #

替换字符串拼接为参数化查询，解决类型错误并规避注入风险：

Private Sub btnenter_Click(sender As Object, e As EventArgs) Handles btnenter.Click
    Dim query As String = "SELECT * FROM [application] WHERE 1 = 1"
    Using con As New SqlConnection("Data Source=AchevePC2;Initial Catalog=hrms;Integrated Security=True")
        Using cmd As New SqlCommand(query, con)
            ' 处理applicationid条件
            If Not String.IsNullOrWhiteSpace(txtappfrom.Text) AndAlso Not String.IsNullOrWhiteSpace(txtappto.Text) Then
                query += " AND applicationid BETWEEN @FromId AND @ToId"
                cmd.Parameters.AddWithValue("@FromId", Integer.Parse(txtappfrom.Text))
                cmd.Parameters.AddWithValue("@ToId", Integer.Parse(txtappto.Text))
            ElseIf Not String.IsNullOrWhiteSpace(txtappfrom.Text) Then
                query += " AND applicationid = @AppId"
                cmd.Parameters.AddWithValue("@AppId", Integer.Parse(txtappfrom.Text))
            End If

            ' 处理日期条件
            If dtpfrom.Value <> DateTime.MinValue AndAlso dtpto.Value <> DateTime.MinValue Then
                query += " AND interviewdtp BETWEEN @FromDate AND @ToDate"
                cmd.Parameters.AddWithValue("@FromDate", dtpfrom.Value)
                cmd.Parameters.AddWithValue("@ToDate", dtpto.Value)
            ElseIf dtpto.Value <> DateTime.MinValue Then
                query += " AND interviewdtp = @TargetDate"
                cmd.Parameters.AddWithValue("@TargetDate", dtpto.Value)
            End If

            cmd.CommandText = query
            Dim adp As New SqlDataAdapter(cmd)
            Dim dt As New DataTable
            adp.Fill(dt)

            If dt.Rows.Count > 0 Then
                ap06.agv.DataSource = dt
                ' 隐藏二进制列避免乱码显示
                ap06.agv.Columns("你的PDF二进制列名").Visible = False
                ' 添加PDF查看按钮列
                AddPdfViewButton()
                ap06.Show()
                apadd.Text = ""
            Else
                MessageBox.Show("未找到匹配记录。", "无记录", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        End Using
    End Using
End Sub

2. 添加PDF查看功能 #

DataGridView无法直接显示PDF，可通过按钮列实现点击查看：

' 添加PDF查看按钮列
Private Sub AddPdfViewButton()
    Dim btnColumn As New DataGridViewButtonColumn()
    btnColumn.Name = "ViewPdf"
    btnColumn.Text = "查看PDF"
    btnColumn.UseColumnTextForButtonValue = True
    ap06.agv.Columns.Add(btnColumn)

    ' 绑定按钮点击事件
    AddHandler ap06.agv.CellClick, AddressOf Agv_CellClick
End Sub

' 按钮点击事件处理
Private Sub Agv_CellClick(sender As Object, e As DataGridViewCellEventArgs)
    If e.ColumnIndex = ap06.agv.Columns("ViewPdf").Index AndAlso e.RowIndex >= 0 Then
        Dim row As DataRow = DirectCast(ap06.agv.Rows(e.RowIndex).DataBoundItem, DataRowView).Row
        Dim pdfData As Byte() = DirectCast(row("你的PDF二进制列名"), Byte())
        
        ' 保存为临时PDF并打开
        Dim tempPath As String = Path.Combine(Path.GetTempPath(), $"temp_{Guid.NewGuid()}.pdf")
        File.WriteAllBytes(tempPath, pdfData)
        Process.Start(tempPath)
    End If
End Sub

3. 注意事项 #

• 替换代码中的你的PDF二进制列名为数据库中实际存储PDF的字段名
• 对用户输入的txtappfrom.Text和txtappto.Text添加数值验证，避免解析失败
• 确保程序有写入临时目录的权限

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