vba - 从 VBA 代码在数据库中插入 NULL 值

Question

我有 3 个单选按钮(“YES”、“NO”、“UNKNOWN”)，它们与我的数据库中的一列匹配，其中包含 3 个可能的值(1、0、NULL)。

When the radioButton selected is "UNKNOWN"I want to insert a NULL value in this table (using Variant type).但是当我尝试使用我的 ADODB 连接运行我的 SQL 查询时，它返回一个错误。

有没有技巧可以在我的数据库中传递 NULL 值？这是我的代码的副本:

Public Function setCandidature(idC As Integer, idO As Integer, nomC As String, prenomC As String, nomM As String, prenomM As String, idRegion As Integer, idUM As Integer, idDUM As Integer, nni As String, emploiC As String, repM As Integer, repA As Integer, accDisp As Integer, precAcc1 As Integer, precAcc2 As Integer)
Dim sqlQuery As String
Dim rs As ADODB.Recordset
Dim repAVar As Variant, repMVar As Variant

Set connect = New ADODB.Connection
connect.Open connString

If repA = -1 Then
    repAVar = Null
Else
    repAVar = repA
End If

If repM = -1 Then
    repMVar = Null
Else
    repMVar = repM
End If

sqlQuery = "UPDATE candidatures SET offre_ID = " & idO & ", candidat_Nom = " & nomC & ", candidat_Prenom = " & prenomC & ", manager_Nom = " & nomM & ", manager_Prenom = " & prenomM & ", reponse_Manager = " & repMVar & ", reponse_Agent = " & repAVar & ", region_Candidat = " & idRegion & _
           ", um_Candidat = " & idUM & ", dum_Candidat = " & idDUM & ", nni_Candidat = " & nni & ", emploi_Candidat = " & emploiC & ", accompagnement_Dispense = " & accDisp & ", accompagnement_Precision_ID = " & precAcc1 & ", accompagnement2_Precision_ID = " & precAcc2 & _
           " WHERE candidature_ID = " & idC & ";"

rs.Open sqlQuery, connect
End Function

我首先将我想要为 NULL 的值作为包含 -1 的整数传递。

Answer 1

你想要的是你的 SQL 语句最终看起来像这样:

UPDATE candidatures 
SET offre_ID = 42
   ,candidat_Nom = 'Doe'
   ,candidat_Prenom = 'John'
   ,manager_Nom = 'Nuts'
   ,manager_Prenom = 'Doug'
   ,reponse_Manager = NULL
   ,reponse_Agent = NULL
   ,region_Candidat = 'WEST'
   ,um_Candidat = 72
   ,dum_Candidat = 72
   ,nni_Candidat = 24
   ,emploi_Candidat = 'something'
   ,accompagnement_Dispense = 'whatever'
   ,accompagnement_Precision_ID = 10
   ,accompagnement2_Precision_ID = 11
WHERE candidature_ID = 2345;

您希望将字符串括在单引号中，不将数值括在此类单引号中，以及字面指定的 NULL 值。

您可以保留字符串连接方法，并通过将 NULL 字符串文字连接到查询中来使查询包含 "NULL" 值:

If repA = -1 Then
    repAVar = "NULL"
Else
    repAVar = repA
End If

If repM = -1 Then
    repMVar = "NULL"
Else
    repMVar = repM
End If

显然，这意味着处理何时/是否包含单引号，以及何时不包含。

这可能会起作用……直到它不起作用:

UPDATE candidatures 
SET offre_ID = 42
   ,candidat_Nom = 'O'Connor'
   ,candidat_Prenom = 'David'
...

我知道!我将简单地将字符串值中的任何单引号加倍!这可能有效:

UPDATE candidatures 
SET offre_ID = 42
   ,candidat_Nom = 'O''Connor'
   ,candidat_Prenom = 'David'
...

但是走下那座山，你就开始兜风了……你会继续修补和“ sanitizer ”用户的输入，直到事情再次工作，然后他们崩溃了，你再修补它……

有一种理智的方法可以做到这一点。

使用参数，让服务器处理参数。

因此，不是将参数值连接到命令字符串中，而是将其发送到服务器:

UPDATE candidatures 
SET offre_ID = ?
   ,candidat_Nom = ?
   ,candidat_Prenom = ?
   ,manager_Nom = ?
   ,manager_Prenom = ?
   ,reponse_Manager = ?
   ,reponse_Agent = ?
   ,region_Candidat = ?
   ,um_Candidat = ?
   ,dum_Candidat = ?
   ,nni_Candidat = ?
   ,emploi_Candidat = ?
   ,accompagnement_Dispense = ?
   ,accompagnement_Precision_ID = ?
   ,accompagnement2_Precision_ID = ?
WHERE candidature_ID = ?;

..连同参数。

您不使用 Recordset 。您可以使用 Command 代替:

Dim connect As ADODB.Connection
Set connect = New ADODB.Connection
connect.ConnectionString = connString
connect.Open

With New ADODB.Command
    .ActiveConnection = connect
    .CommandType = adCmdText
    .CommandText = sqlQuery

    'append parameters in the same order they show up in the query
    .Parameters.Append .CreateParameter(Type:=adInteger, Direction:=adParamInput, Value:=myValue)

    '...
    .Execute
End With
connect.Close

要给参数一个 NULL 值，只需使用 Null 关键字:

myValue = Null

这要求您的可空值具有 Variant 数据类型，因为 Integer 或 Boolean 在 VBA 中不能是 Null。