无法将签证号文件与机票的新链接保存至数据库求助

数据老陈

2026-7-3

无法将签证号文件与机票文件路径保存至数据库

执行代码无报错，但数据库中selectedVisanumberFile和selectedTicket字段始终为空，无法保存文件新路径。以下是相关代码：

UpdateData 方法

Public Sub UpdateData(recordID As Integer, name As String, contact As String, email As String, passport As String, nationality As String, position As String, company As String, basic As String, food As String, housing As String, transportation As String, offer As String, medical As String, remarks As String, visaPosition As String, visaNumber As String, visaNumberFile As String, airline As String, arrival As String, ticketFile As String, assignLocation As String, status As String, agency As String)
    Using connection As OleDbConnection = DatabaseManager.GetConnection()
        connection.Open()
        Using transaction As OleDbTransaction = connection.BeginTransaction()
            Try
                Dim commandString As String = "UPDATE recruitmentStatus SET [selectedName]=?, [selectedContact]=?, [selectedEmail]=?, [selectedPassport]=?, [selectedNationality]=?, [selectedPosition]=?, [selectedCompany]=?, [selectedBasic]=?, [selectedFood]=?, [SelectedHousing]=?, [selectedTransportation]=?, [SelectedOffer]=?, [selectedMedical]=?, [selectedRemarks]=?, [selectedVisaposition]=?, [selectedVisanumber]=?, [selectedVisanumberFile]=?, [selectedAirline]=?, [selectedArrival]=?, [selectedTicket]=?, [selectedAssignlocation]=?,[selectedStatus]=?, [selectedAgency]=?  WHERE [ID]=?"

                Using command As New OleDbCommand(commandString, connection, transaction)

                    command.Parameters.AddWithValue("selectedName", name)
                    command.Parameters.AddWithValue("selectedContact", contact)
                    command.Parameters.AddWithValue("selectedEmail", email)
                    command.Parameters.AddWithValue("selectedPassport", passport)
                    command.Parameters.AddWithValue("selectedNationality", nationality)
                    command.Parameters.AddWithValue("selectedPosition", position)
                    command.Parameters.AddWithValue("selectedCompany", company)
                    command.Parameters.AddWithValue("selectedBasic", basic)
                    command.Parameters.AddWithValue("selectedFood", food)
                    command.Parameters.AddWithValue("SelectedHousing", housing)
                    command.Parameters.AddWithValue("selectedTransportation", transportation)
                    command.Parameters.AddWithValue("SelectedOffer", offer)
                    command.Parameters.AddWithValue("selectedMedical", medical)
                    command.Parameters.AddWithValue("selectedRemarks", remarks)
                    command.Parameters.AddWithValue("selectedVisaposition", visaPosition)
                    command.Parameters.AddWithValue("selectedVisanumber", visaNumber)

                    ' 获取新文件路径
                    Dim newVisaNumberFile As String = SaveFileAndGetPath(name, visaNumberFile, "Visa")
                    Dim newTicketFile As String = SaveFileAndGetPath(name, ticketFile, "Ticket")

                    ' 更新文件路径参数
                    command.Parameters.AddWithValue("selectedVisanumberFile", newVisaNumberFile)
                    command.Parameters.AddWithValue("selectedAirline", airline)
                    command.Parameters.AddWithValue("selectedArrival", arrival)
                    command.Parameters.AddWithValue("selectedTicket", newTicketFile)

                    command.Parameters.AddWithValue("selectedAssignlocation", assignLocation)
                    command.Parameters.AddWithValue("selectedStatus", status)
                    command.Parameters.AddWithValue("selectedAgency", agency)
                    command.Parameters.AddWithValue("ID", recordID)

                    Dim result As Integer = command.ExecuteNonQuery()

                    If result > 0 Then
                        transaction.Commit()
                        MessageBox.Show("Data has been successfully updated.")
                        clearData()
                    Else
                        MessageBox.Show("No records were updated. Please check the ID and data.")
                    End If
                End Using
            Catch ex As OleDbException
                transaction.Rollback()
                MessageBox.Show("Database Error: " & ex.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Catch ex As Exception
                transaction.Rollback()
                MessageBox.Show("Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using
    End Using
End Sub

SaveFileAndGetPath 方法

Private Function SaveFileAndGetPath(candidateName As String, filePath As String, fileType As String) As String
    Try
        Dim baseFolder As String = "F:\HR Main Folder\Recruitment\Recruitment Selected Candidate files"
        Dim candidateFolder As String = Path.Combine(baseFolder, candidateName)

        If Not Directory.Exists(candidateFolder) Then
            Directory.CreateDirectory(candidateFolder)
        End If

        Dim subFolder As String = ""
        Select Case fileType
            Case "Visa"
                subFolder = "Visa"
            Case "Ticket"
                subFolder = "Ticket"
        End Select

        Dim subFolderPath As String = Path.Combine(candidateFolder, subFolder)

        If Not Directory.Exists(subFolderPath) Then
            Directory.CreateDirectory(subFolderPath)
        End If

        Dim fileName As String = $"{fileType}-{DateTime.Now.ToString("MMMM dd, yyyy")}{Path.GetExtension(filePath)}"
        Dim destinationPath As String = Path.Combine(subFolderPath, fileName)

        File.Copy(filePath, destinationPath)
        File.Delete(filePath)
        Return destinationPath
    Catch ex As Exception
        Return String.Empty
    End Try
End Function

排查与修复建议

1. 修复`SaveFileAndGetPath`的异常吞噬问题

当前方法捕获异常后直接返回空字符串，导致文件保存失败时无任何提示，数据库字段被设为空。修改方法如下：

Private Function SaveFileAndGetPath(candidateName As String, filePath As String, fileType As String) As String
    ' 先校验输入路径
    If String.IsNullOrWhiteSpace(filePath) Then
        Throw New ArgumentException($"请选择有效的{fileType}文件", "filePath")
    End If

    Try
        ' 原有文件处理逻辑
        Dim baseFolder As String = "F:\HR Main Folder\Recruitment\Recruitment Selected Candidate files"
        Dim candidateFolder As String = Path.Combine(baseFolder, candidateName)

        If Not Directory.Exists(candidateFolder) Then
            Directory.CreateDirectory(candidateFolder)
        End If

        Dim subFolder As String = fileType ' 简化分支逻辑
        Dim subFolderPath As String = Path.Combine(candidateFolder, subFolder)

        If Not Directory.Exists(subFolderPath) Then
            Directory.CreateDirectory(subFolderPath)
        End If

        Dim fileName As String = $"{fileType}-{DateTime.Now.ToString("MMMM dd, yyyy")}{Path.GetExtension(filePath)}"
        Dim destinationPath As String = Path.Combine(subFolderPath, fileName)

        File.Copy(filePath, destinationPath, True) ' 添加覆盖选项，避免文件重名报错
        ' 可选：不要删除原文件，避免用户误操作丢失文件
        ' File.Delete(filePath)
        Return destinationPath
    Catch ex As Exception
        MessageBox.Show($"保存{fileType}文件失败: {ex.Message}", "文件操作错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Throw ' 重新抛出异常，触发事务回滚
    End Try
End Function

2. 严格匹配OleDb参数顺序

OleDb不支持命名参数匹配，参数必须与SQL语句中的?占位符顺序完全一致。当前代码在添加到selectedVisanumber参数后，插入了文件路径获取逻辑，建议调整为先获取所有文件路径，再按SQL顺序添加所有参数，避免潜在的顺序混乱：

' 先获取文件路径，放在参数添加之前
Dim newVisaNumberFile As String = SaveFileAndGetPath(name, visaNumberFile, "Visa")
Dim newTicketFile As String = SaveFileAndGetPath(name, ticketFile, "Ticket")

Using command As New OleDbCommand(commandString, connection, transaction)
    ' 严格按照SQL语句的字段顺序添加参数
    command.Parameters.AddWithValue("selectedName", name)
    command.Parameters.AddWithValue("selectedContact", contact)
    command.Parameters.AddWithValue("selectedEmail", email)
    command.Parameters.AddWithValue("selectedPassport", passport)
    command.Parameters.AddWithValue("selectedNationality", nationality)
    command.Parameters.AddWithValue("selectedPosition", position)
    command.Parameters.AddWithValue("selectedCompany", company)
    command.Parameters.AddWithValue("selectedBasic", basic)
    command.Parameters.AddWithValue("selectedFood", food)
    command.Parameters.AddWithValue("SelectedHousing", housing)
    command.Parameters.AddWithValue("selectedTransportation", transportation)
    command.Parameters.AddWithValue("SelectedOffer", offer)
    command.Parameters.AddWithValue("selectedMedical", medical)
    command.Parameters.AddWithValue("selectedRemarks", remarks)
    command.Parameters.AddWithValue("selectedVisaposition", visaPosition)
    command.Parameters.AddWithValue("selectedVisanumber", visaNumber)
    ' 对应SQL中的selectedVisanumberFile
    command.Parameters.AddWithValue("selectedVisanumberFile", newVisaNumberFile)
    command.Parameters.AddWithValue("selectedAirline", airline)
    command.Parameters.AddWithValue("selectedArrival", arrival)
    ' 对应SQL中的selectedTicket
    command.Parameters.AddWithValue("selectedTicket", newTicketFile)
    command.Parameters.AddWithValue("selectedAssignlocation", assignLocation)
    command.Parameters.AddWithValue("selectedStatus", status)
    command.Parameters.AddWithValue("selectedAgency", agency)
    ' 最后是WHERE条件的ID
    command.Parameters.AddWithValue("ID", recordID)

    Dim result As Integer = command.ExecuteNonQuery()
    ' ... 后续逻辑
End Using

3. 检查数据库字段类型长度

如果使用Access数据库，Text字段默认仅支持255字符，长文件路径会被截断为空。需将selectedVisanumberFile和selectedTicket字段类型修改为Long Text（原Memo类型）；如果是SQL Server，修改为VARCHAR(MAX)或NVARCHAR(MAX)。

4. 校验输入参数有效性

在调用UpdateData前，确保传入的visaNumberFile和ticketFile不为空或无效路径，比如在UI层添加选择文件后的校验：

' 示例：UI层调用前的校验
If String.IsNullOrWhiteSpace(txtVisaFile.Text) Then
    MessageBox.Show("请选择签证号文件", "输入错误", MessageBoxButtons.OK, MessageBoxIcon.Warning)
    Return
End If
If String.IsNullOrWhiteSpace(txtTicketFile.Text) Then
    MessageBox.Show("请选择机票文件", "输入错误", MessageBoxButtons.OK, MessageBoxIcon.Warning)
    Return
End If

' 调用UpdateData
UpdateData(recordID, name, ..., txtVisaFile.Text, ..., txtTicketFile.Text, ...)

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

无法将签证号文件与机票的新链接保存至数据库求助

UpdateData 方法 #

SaveFileAndGetPath 方法 #

排查与修复建议 #

1. 修复SaveFileAndGetPath的异常吞噬问题