将PDF导入MS SQL遇varchar转varbinary(MAX)隐式转换错误求助

问题概述 #

尝试将PDF文件导入SQL Server数据库时，触发错误：不允许从数据类型varchar隐式转换为varbinary(MAX)。请使用CONVERT函数运行此查询。

操作流程：通过AxAcroPDF1控件加载PDF并预填文本框，用户输入Broker Load Number后点击保存，通过OpenFileDialog选择文件时导入失败。

数据库表结构 #

ID, int, NOT NULL IDENTITY(1,1) PRIMARY KEY,
BROKER_LOAD_NUMBER nvarchar(15) NOT NULL,
PDF_FILENAME,nvarchar(50) NOT NULL,
PETS_LOAD_NUMBER nvarchar(10) NOT NULL,
PAPERWORK varbinary(MAX) NOT NULL

错误原因分析 #

1. SQL参数占位符错误：Insert语句中参数被单引号包裹（如'@bl'），导致SQL将其识别为字符串字面量而非参数占位符，进而尝试把字符串值传入varbinary(MAX)类型的PAPERWORK字段，引发类型转换错误。
2. 参数名拼写错误：代码中把对应PETS_LOAD_NUMBER的参数@pl写成了@p1，导致参数不匹配。
3. 冗余参数与重复插入：添加了表结构中不存在的@fp参数，且重复调用upLoadImageOrFile方法，会触发多次无效插入。
4. 未实现文件读取函数：ReadFile函数未实现，导致upLoadImageOrFile方法无法正确读取文件字节流。

修复后的代码 #

Imports System.Data.SqlClient
Imports System.IO

Public Class LoadDocs
    Private DV As DataView
    Private currentRow As String

    Private Sub LoadDocs_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Documents_TableTableAdapter.Fill(DocDataset.Documents_Table)
    End Sub

    Private Sub btnOpenPDF_Click(sender As Object, e As EventArgs) Handles btnOpenPDF.Click
        Dim CurYear As String = Now.Year.ToString

        OpenFileDialog1.Filter = "PDF Files(*.pdf)|*.pdf"
        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            AxAcroPDF1.src = OpenFileDialog1.FileName
            tbFilePath.Text = OpenFileDialog1.FileName

            Dim filename As String = tbFilePath.Text
            tbFileName.Text = filename.Substring(Math.Max(0, filename.Length - 18))

            Dim loadnumber As String = tbFileName.Text
            If loadnumber.Length >= 14 Then ' 确保字符串长度足够截取
                tbPetsLoadNumber.Text = loadnumber.Substring(7, 7)
            End If
        End If
    End Sub

    ' 原搜索功能代码保留，此处省略
    Private Sub SearchResult()
        ' 原搜索逻辑不变
    End Sub

    Private Sub LbllSearchResults_SelectedIndexChanged(sender As Object, e As EventArgs) Handles lblSearchResults.SelectedIndexChanged
        Dim ix As Integer = DocumentsTableBindingSource.Find("PETS_LOAD_NUMBER", CInt(lblSearchResults.SelectedItem.ToString))
        DocumentsTableBindingSource.Position = ix
        lblSearchResults.Visible = False
    End Sub

    Private Sub DocumentsTableBindingSource_PositionChanged(sender As Object, e As EventArgs) Handles DocumentsTableBindingSource.PositionChanged
        Try
            currentRow = DocDataset.Documents_Table.Item(DocumentsTableBindingSource.Position).ToString
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub BtnSavePDF_Click(sender As Object, e As EventArgs) Handles btnSavePDF.Click
        If String.IsNullOrEmpty(tbPetsLoadNumber.Text) Then
            MessageBox.Show("Please enter a PETS Load Number", "Missing Load Number", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        ElseIf String.IsNullOrEmpty(tbBrokerLoadNumber.Text) Then
            MessageBox.Show("Please enter a Broker Load Number", "Missing Load Number", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        ElseIf String.IsNullOrEmpty(tbFileName.Text) Then
            MessageBox.Show("Please enter a Filename", "Missing Filename", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Exit Sub
        End If

        Try
            Using OpenFileDialog As New OpenFileDialog()
                OpenFileDialog.Filter = "PDF Files(*.pdf)|*.pdf"
                If OpenFileDialog.ShowDialog(Me) <> DialogResult.OK Then
                    Exit Sub
                End If
                tbFilePath.Text = OpenFileDialog.FileName
            End Using

            ' 读取PDF文件为字节数组
            Dim data As Byte() = ReadFile(tbFilePath.Text)
            If data Is Nothing Then
                MessageBox.Show("Failed to read the PDF file")
                Exit Sub
            End If

            ' 插入数据库
            Using conn As New SqlConnection(My.MySettings.Default.PETS_DatabaseConnectionString)
                conn.Open()
                Dim cmdText As String = "INSERT INTO Documents_Table (BROKER_LOAD_NUMBER, PDF_FILENAME, PETS_LOAD_NUMBER, PAPERWORK)
                                        VALUES (@bl, @fn, @pl, @pdf);"
                Using cmd As New SqlCommand(cmdText, conn)
                    cmd.Parameters.AddWithValue("@bl", tbBrokerLoadNumber.Text)
                    cmd.Parameters.AddWithValue("@fn", tbFileName.Text)
                    cmd.Parameters.AddWithValue("@pl", tbPetsLoadNumber.Text)
                    cmd.Parameters.AddWithValue("@pdf", data)

                    cmd.ExecuteNonQuery()
                End Using
            End Using

            MessageBox.Show("File successfully Imported to Database")

        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub

    ' 实现文件读取函数
    Private Function ReadFile(sFilePath As String) As Byte()
        Try
            Using fs As New FileStream(sFilePath, FileMode.Open, FileAccess.Read)
                Using br As New BinaryReader(fs)
                    Return br.ReadBytes(CInt(fs.Length))
                End Using
            End Using
        Catch ex As Exception
            MessageBox.Show("Error reading file: " & ex.Message)
            Return Nothing
        End Try
    End Function
End Class

关键修复点说明 #

• 修正SQL参数占位符：移除参数周围的单引号，确保SQL识别为参数而非字符串字面量。
• 修正参数名：将@p1改为@pl，与SQL语句中的占位符匹配。
• 实现ReadFile函数：使用Using语句确保文件流自动释放，正确读取文件字节数组。
• 避免重复插入：移除冗余的upLoadImageOrFile方法，将文件读取和插入逻辑合并到BtnSavePDF中。
• 资源释放：使用Using语句管理SqlConnection、SqlCommand、FileStream等资源，避免内存泄漏。

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