Inserting NULL values in a database from VBA code

Question

I have 3 radioButtons ("YES", "NO", "UNKNOWN") which 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.

@Vityata Like this ? : repAVar = vbNull – Natty Apr 20 '17 at 14:14 — Natty, Apr 20 '17 at 14:14
Yup, thats what I thought. Try then like `repAVar = "Null"` – Vityata Apr 20 '17 at 14:41 — Vityata, Apr 20 '17 at 14:41
Related: http://stackoverflow.com/q/1747641/1188513 – Mathieu Guindon Apr 20 '17 at 14:50 — Mathieu Guindon, Apr 20 '17 at 14:50

score 11 · Accepted Answer · answered Apr 20 '17 at 14:44

11

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.

answered Apr 20 '17 at 14:44

Mathieu Guindon

69,817
8
107
235

I know the feeling when you write a giant answer, post it and see that someone has commented the essence of it! :) – Vityata Apr 20 '17 at 14:45
1

@Vityata meh.. I wasn't going to make an answer that recommends concatenating `"Null"` into the command string - but I knew it was going to come up one way or another. The only sane way to do this is to use a parameterized command... and that's nowhere in the comments ;-) – Mathieu Guindon Apr 20 '17 at 14:47
1

Good `Command` example. :) Perhaps you can write a VBA Wiki? – Vityata Apr 20 '17 at 14:47
Well, leave some place for SQL injections, some people make a living out of it ;) – Vityata Apr 20 '17 at 14:48
Hello and thank you for your answers ! I haven't tried to make it work yet but would you recommend to use the ADODB.Command for every non-SELECT queries ? Also thanks for the tip on how to display my queries : it surely makes my code easier to read ! – Natty Apr 21 '17 at 09:00
@Mat'sMug I am getting an error on the `.Execute` : "This parameter type is not handled" (poor translation but you get the idea), do I need an extra line to end the Parameters.Append ? If so, which method allows this ? – Natty Apr 21 '17 at 11:52
@NattyRoots I use `ADODB.Command` for everything *including* `SELECT` queries, parameterized or not. That way securely adding a parameter afterwards is a trivial change. Not sure what error you're getting, but make sure you use the appropriate types and that the parameters are appended in the same order as their respective placeholder `?` in the command text. `Append` is a method of `Command.Parameters`, the `.CreateParameter` function returns a `Parameter` object that `Append` uses; not sure what you mean with "to end the Parameters.Append". – Mathieu Guindon Apr 21 '17 at 13:55
I'd suggest you google up the *exact error message*, and then ask a separate question with it if you don't already find an answer on this site. – Mathieu Guindon Apr 21 '17 at 14:00
@MathieuGuindon is it possible to do this with `QueryTable` instead of `ADODB` : https://stackoverflow.com/questions/52675562 ? – sam-6174 Oct 06 '18 at 04:07
@NattyRoots Apropos SQL injection, see the section on ActiveX Data Objects at [bobby-tables.com](https://bobby-tables.com/com_automation). – Zev Spitz Aug 07 '19 at 21:47
According to https://www.w3schools.com/asp/met_comm_createparameter.asp adVariant is not supported by ADODB. But the following code worked for me to insert NULL into MariaDB: `.Parameters.Append .CreateParameter(Type:=adVarChar, Direction:=adParamInput, Size:=1, Value:=Null)` It is critical to set the Size parameter to larger than 0. – sdittmar Jul 25 '20 at 20:54
@sdittmar not sure where you read anything involving adVariant in this answer. The last part is about the VBA data types to hold the data on the VBA side of things; a VBA string or integer can't be Null, you need a Variant to hold a Null value in VBA *on the client side*. Server side couldn't care less what data type the client variables are. – Mathieu Guindon Jul 25 '20 at 21:12

Vityata · Answer 2 · 2017-04-20T15:18:42.843

1

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:

Write debug.print sqlQuery after the code.
Take the result from the immediate window and inspect it.
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.

edited Apr 20 '17 at 15:18

answered Apr 20 '17 at 14:56

Vityata

42,633
8
55
100

Arguably "whenever you are having a problem with big SQL code like this `sqlQuery = "SOME huge SQL statement WITH = " & concatenations & " AND " & moreConcatenations & ";"` ...solution is to get rid of the concatenations. But yeah +1 for showing how to use the debugger & immediate pane to fix things. – Mathieu Guindon Apr 20 '17 at 15:12
@Mat'sMug - Getting rid of concatenations is exactly the way to do it, cannot agree more. – Vityata Apr 20 '17 at 15:16

Inserting NULL values in a database from VBA code

2 Answers2

Linked

Related