Inserting NULL values in a database from VBA code

Question

I have 3 radioButtons ("YES", "NO", "UNKNOWN") wich matches a column from my database with 3 possible values (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.

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 first pass the values that I want to be NULL as Integers containing -1.

Answer 1

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;

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

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

There's a sane way 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 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

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

myValue = Null

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

Answer 2

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

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

Do the following:

1. Write debug.print sqlQuery after 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.

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 injection and other problems. Instead use the method from the answer of Mat's Mug.