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.

Can you try vbNull ?

Vityata
– Vityata

2017-04-20 14:08:38 +00:00
Commented Apr 20, 2017 at 14:08 — Vityata
– Vityata, Commented Apr 20, 2017 at 14:08
@Vityata Like this ? : repAVar = vbNull

Natty
– Natty

2017-04-20 14:14:48 +00:00
Commented Apr 20, 2017 at 14:14 — Natty
– Natty, Commented Apr 20, 2017 at 14:14
Cause it does not work

Natty
– Natty

2017-04-20 14:15:14 +00:00
Commented Apr 20, 2017 at 14:15 — Natty
– Natty, Commented Apr 20, 2017 at 14:15
Yup, thats what I thought. Try then like repAVar = "Null"

Vityata
– Vityata

2017-04-20 14:41:43 +00:00
Commented Apr 20, 2017 at 14:41 — Vityata
– Vityata, Commented Apr 20, 2017 at 14:41
Related: stackoverflow.com/q/1747641/1188513

Mathieu Guindon
– Mathieu Guindon

2017-04-20 14:50:15 +00:00
Commented Apr 20, 2017 at 14:50 — Mathieu Guindon
– Mathieu Guindon, Commented Apr 20, 2017 at 14:50

Mathieu Guindon · Accepted Answer · 2017-04-20 14:44:47Z

12

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, 2017 at 14:44

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Vityata Over a year ago

I know the feeling when you write a giant answer, post it and see that someone has commented the essence of it! :)

Mathieu Guindon Over a year ago

@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 ;-)

Vityata Over a year ago

Good Command example. :) Perhaps you can write a VBA Wiki?

Vityata Over a year ago

Well, leave some place for SQL injections, some people make a living out of it ;)

Natty Over a year ago

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 !

|

Vityata · Accepted Answer · 2017-04-20 15:18:42Z

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, 2017 at 15:18

answered Apr 20, 2017 at 14:56

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

2 Comments

Mathieu Guindon Over a year ago

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.

Vityata Over a year ago

@Mat'sMug - Getting rid of concatenations is exactly the way to do it, cannot agree more.

Collectives™ on Stack Overflow

Inserting NULL values in a database from VBA code

2 Answers 2

12 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related