作者：处女同乡会同乡情 | 来源：互联网 | 2024-11-16 12:51
前言：本文由编程笔记#小编整理，主要介绍如何在Access VBA中处理参数查询时将整数正确地传递给查询，避免数据类型不匹配的问题。
我遇到了一个看似简单但令人困惑的问题。我正在尝试对Access表执行UPDATE查询。在我的表单上有一个隐藏的文本框，其中包含要更新记录的ID。问题是，查询在将整数存储到参数中时会自动将其转换为字符串。即使我将值显式转换为整数，它仍然会这样处理。参数名称为p6。以下是相关代码示例：
Private Sub SubmitButton_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSql As String
Dim frm As Object
If IsRequiredFilled(Me) = False Then
MsgBox "请填写所有必填字段。", vbCritical
Exit Sub
End If
Set db = CurrentDb
strSql = "UPDATE [Batches_T] " & _
"SET [BatchName] = [BatchName] + [p1], " & _
"[StatusID] = [StatusID] + [p2], " & _
"[InternalStatusID] = [InternalStatusID] + [p2], " & _
"[ReviewerID] = [ReviewerID] + [p3], " & _
"[StartDate] = [StartDate] + [p4], " & _
"[PowerPointFilePath] = [PowerPointFilePath] + [p5] " & _
"WHERE [ID] = [p6]"
Set qdf = db.CreateQueryDef(vbNullString, strSql)
With qdf
.Parameters("p1").Value = Me.BatchName
.Parameters("p2").Value = Me.StatusID
.Parameters("p3").Value = Me.InternalStatusID
.Parameters("p4").Value = Me.StartDate
.Parameters("p5").Value = Me.PowerPointFilePath
.Parameters("p6").Value = CInt(Me.ID)
.Execute dbFailOnError
End With
Set qdf = Nothing
Set db = Nothing
Forms![Dashboard_F]![Batches_DS_F].Requery
If Me.keepOpenCheckBox = False Then
DoCmd.Close acForm, "AddBatch_F", acSaveYes
End If
End Sub
解决方案
为了确保参数p6被正确识别为整数类型，可以在SQL语句中显式声明参数类型：
strSql = "PARAMETERS [p6] INTEGER; " & _
"UPDATE [Batches_T] " & _
"SET [BatchName] = [BatchName] + [p1], " & _
"[StatusID] = [StatusID] + [p2], " & _
"[InternalStatusID] = [InternalStatusID] + [p2], " & _
"[ReviewerID] = [ReviewerID] + [p3], " & _
"[StartDate] = [StartDate] + [p4], " & _
"[PowerPointFilePath] = [PowerPointFilePath] + [p5] " & _
"WHERE [ID] = [p6]"
通过这种方式，可以确保参数p6在查询执行时被正确识别为整数类型，从而避免数据类型不匹配的错误。
京公网安备 11010802041100号