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

Inserting NULL values in a database from VBA code

提问by NattyRoots

I have 3 radioButtons ("YES", "NO", "UNKNOWN") wich matches a column from my database with 3 possible values (1, 0, NULL).

我有 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). But when I try to run my SQL query with my ADODB connection, it returns an error.

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

Is there a trick to pass NULL values in my database ? Here's a copy of my code :

有没有技巧可以在我的数据库中传递 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

I first pass the values that I want to be NULL as Integers containing -1.

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

回答by Mathieu Guindon

What you want is for your SQL statement to ultimately look like this:

你想要的是你的 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;

You want strings to be enclosed in single quotes, numerical values to notbe enclosed in such single quotes, and NULLvalues specified literally.

您希望字符串包含在单引号中，数值不包含在单引号中，以及按NULL字面指定的值。

You could keep your string-concatenation approach, and make the query contain NULLvalues by concatenating "NULL"string literals into your query:

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

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

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

Obviously this means dealing with when/whether to include the single quotes, and when notto.

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

And that might work... until it doesn't:

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

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

I know! I'll simply double-up any single quote in my string values! And that mightwork:

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

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

But go down that hill and you're in for a ride... you'll keep patching up and "sanitizing" the user's input until things work again, then they break down, and you patch it again...

但是走下那座山，你就开始兜风了……你会不断地修补和“消毒”用户的输入，直到一切恢复正常，然后他们崩溃了，你再修补它……

There's a saneway to do this.

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

Use parameters, and let the server deal with the parameters.

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

So instead of concatenating the parameter values into the command string, you send this to the server:

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

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 = ?;

..along with parameters.

..连同参数。

You don't work with a Recordset. You work with a Commandinstead:

你不使用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

To give the parameter a NULLvalue, simply use the Nullkeyword:

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

myValue = Null

This requires your nullable values to have a Variantdata type, because an Integeror Booleancan't be Nullin VBA.

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

回答by Vityata

Just an idea for the future - whenever you are having a problem with big SQL code like this:

只是对未来的一个想法 - 每当您遇到这样的大型 SQL 代码时：

sqlQuery = "UPDATE candidatures SET offre_ID = " & idO & ", candidat_Nom = " & more and more more SQL here";"

sqlQuery = "UPDATE candidatures SET offre_ID = " & idO & ", candidat_Nom = " & more and more more SQL here";"

Do the following:

请执行下列操作：

1. Write debug.print sqlQueryafter the code.
2. Take the result from the immediate window and inspect it.
3. Copy it and take it into Access query. It should work. If it does not - inspect and debug further.

1. 写 debug.print sqlQuery在代码之后。
2. 从直接窗口中获取结果并检查它。
3. 复制它并将其带入 Access 查询。它应该工作。如果没有 - 进一步检查和调试。

Edit: If you have the time& the knowledge& the will to write good code, do not write code like this. You may suffer from SQL injectionand other problems. Instead use the method from the answer of Mat's Mug.

编辑：如果你有time& 和knowledge& will to write good code，不要写这样的代码。您可能会遇到SQL injection和其他问题。而是使用 Mat's Mug 的答案中的方法。