通过VBA代码在数据库中插入NULL值 [英] Inserting NULL values in a database from VBA code

问题描述

我有3个单选按钮（ YES， NO， UNKNOWN），它们匹配数据库中具有3个可能值（1、0，NULL）的列。

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

当选择的单选按钮为 UNKNOWN时，我想在此表中插入一个NULL值（使用Variant类型）。但是，当我尝试使用ADODB连接运行SQL查询时，它返回错误。

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.

是否有技巧在数据库中传递NULL值？这是我的代码的副本：

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

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首先将我想为NULL的值传递为包含-1的整数。

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

推荐答案

您想要的是SQL语句最终看起来像这样：

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

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 值。

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

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

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

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 not to.

这可能行得通...直到不行：

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 might work:

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...

有一种理智的方式来实现。

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

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

..以及参数。

您不使用记录集。您可以使用 Command 代替：

You don't work with a Recordset. You work with a Command instead:

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 值，只需使用空关键字：

To give the parameter a NULL value, simply use the Null keyword:

myValue = Null

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

This requires your nullable values to have a Variant data type, because an Integer or Boolean can't be Null in VBA.

这篇关于通过VBA代码在数据库中插入NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！