Difficulty with INSERT INTO sql

[BHo15]

I decided to put a statement in my error handlers that would send any errors to a table that I could look at later. But I keep getting random errors when the code gets to one of these statements.

Here is one of the statements in the error handler...

DoCmd.RunSQL "INSERT INTO tblErrors (ErrDate,ErrNumb,ErrDescrip,ErrLocation) VALUES (#" & Now & "#, '" & Err.Number & "', '" & Err.Description & "', '" & _
                    "cmdSelected_Click - Switchboard" & "')"

One of the errors I got was Syntax error (missing operator) in query expression Syntax error in date in query expression 'EntryDate Between #3/18/2018# AND #To Date'.".

I threw this error on purpose to test the INSERT INTO statement (hence I had the ending date set to 'To Date'). But why is this error telling me the error? It is supposed to just send it to the errors table, and then move on.

Thanks for looking.

[NeoPa]

Two ideas spring to mind :

1. If you don't have an Exit Sub line before the code drops into the error handler then the code can drop through without actually hitting any error.
2. In the VBIDE there is a setting under Tools / Options / General / Error Trapping.
   If you have this set to Break on All Errors then your error handling will never be invoked. Either of the other two should suit you. I tend to use Break in Class Module as that handles ordinary code as well as Class code, but Break on Unhandled Errors should work just as well if you aren't doing any work on Classes.

[twinnyfo]

BHo15,

What is confusing to me is your error Statement: Syntax error (missing operator) in query expression Syntax error in date in query expression 'EntryDate Between #3/18/2018# AND #To Date'.

Based upon your intended SQL statement, I do not see the phrase EntryDate Between #3/18/2018# AND #To Date. So, something tells me that your error is being thrown elsewhere, and not during your Error Handling (which I am proud of you for trying to put Error Handling into all your code). ;-)

[BHo15]

First off NeoPa... I do have Exit Sub in place. And I checked the setting for Error Trapping, and it was set to Break on Unhandled Errors.

Twinnyfo... I totally agree. That is what is baffling me. It seems to be that the error that is popping up is the initial error that engaged the Error Handler, even though it is stopping on the INSERT INTO statement of the Error Handler. Makes no sense.

Thank so to you both!

[BHo15]

I think I just figured it out. I had Resume Next at the bottom of each of my error handlers. I think it was just getting stuck in a loop, and hitting the Error again. I changed them all to be Exit Sub or Exit Function.

I’ll have to use it for a bit to see if it really did fix the issue. I’ll let you know.

[NeoPa]

Resume Next is there only for when you want to say :
"OK. So that didn't work. Well I don't much care, I'll let it run on anyway after the line where it failed."

Certainly not standard procedure for errors but can be useful sometimes.

[BHo15]

Indeed. I guess my use of Resume Next was just me being hopeful that the remaining of the code would continue without issue. I guess I need to have more of a glass half empty mind set.

[BHo15]

Spoke too soon. Just had another one of those weird errors, even with Exit Sub at the bottom of the Error Handler. Here is all of the code from the Sub...

Private Sub cmdLogin_Click()
    On Error GoTo Ooops
    Dim intSecLevel As Integer
        intSecLevel = DLookup("UserSecurity", "tblUsers", "Username = '" & Me.txtUser & "')")
    Select Case intSecLevel
        Case Is = 0 'Admin
        Case Is = 1 'User
            DoCmd.OpenForm "frmEntries", , , "CoderID = " & Me.CoderID
            Me.Visible = False
        Case Is = 2 'SuperUser
            DoCmd.OpenForm "Switchboard"
            Me.Visible = False
    End Select
    Exit Sub
 
Ooops:
    MsgBox ("There has been an error. " & Chr(10) & "If it continues, let your manager know.")
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblErrors (ErrDate, ErrNumb, ErrDescrip, ErrLocation) VALUES (#" & Now & "#, '" & Err.Number & "', '" & Err.Description & _
                        "', '" & "cmdLogin_Click - frmLogin" & "')"
    DoCmd.SetWarnings True
    Exit Sub
End Sub

The error this time was...
Syntax error (missing operator) in query expression "Extra ) in query expression 'Username = 'Teresa"., and it happens on the INSERT INTO line.

[BHo15]

Well... I found why the error in the first place. My parenthesis and double/single quotes are all jacked up on that DLookup. Therfore, it should not error anymore.

BUT... Why the error messages related to the original error on the SQL statement?

[NeoPa]

BHo15:
BUT... Why the error messages related to the original error on the SQL statement?

Because when the code is already handling an error it is not able to handle another one. It could if you wanted to end up in a sorry mess, but the designers of VBA showed some wisdom when they ensured that couldn't happen.

Error handling code is special. It has a higher level of responsibility (so requires more care) than ordinary code. It should be designed to ensure it can never error. From the triggering of the error until an explicit (Resume, Resume Next) or implicit (Exit Sub, Exit Function, etc, or just dropping out of the procedure) Resume your code is in that special state where it cannot afford to fail.

How to Debug SQL String may help with getting the SQL right.

BTW I was referring earlier to an Exit Sub in the ordinary code above (before) the Error Handler label. Before the end of the procedure (End Sub or End Function it makes no discernable difference.

[Rabbit]

You need to escape the single quotes on those variables you're plugging into the RunSQL. Either that or use parameterized queries.

[BHo15]

Thanks to both of you. NeoPa... That is very interesting that VBA does not deal with back to back errors inside of an error handler. AND... That makes sense in my situation, in that I am hitting an error, going to Error Handler, and then hitting another error. Since it can't store another error's details, it just shows me the first.

Rabbit... I don't understand what you mean by escaping the single quotes on the variables. Can you provide an example?

Thanks.

[BHo15]

Update... I created a sub to just test my SQL. I used variables instead of Err.Number and Err.Description. Here it is.

Public Sub Test()
    Dim ErrNumber As String
        ErrNumber = "12345"
    Dim ErrDescrip As String
        ErrDescrip = "Gonna be bad"
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblErrors (ErrDate, ErrNumb, ErrDescrip, ErrLocation) VALUES (#" & Now & "#, '" & ErrNumber & "', '" & ErrDescrip & _
                        "', '" & "cmdLogin_Click - frmLogin" & "')"
    DoCmd.SetWarnings True
End Sub

It worked like a champ with no errors.

[NeoPa]

I suspect Rabbit's comment was related to the earlier version that you've reported as fixed anyway BHo15.

That is the other possibility for handling errors within your handler - to make it call a procedure to do the work for you.

[BHo15]

Gotcha. So since my SQL worked on its own, do you all see any issues with it or know why it seems to be occasionally erroring within my Error Handler?

[NeoPa]

Is it still erroring (You indicated in post #9 it was ok.)?

Have you checked out the link in post #10?

[Rabbit]

Bho fixed the dlookup error. But that original error description had unescaped quotes in it which caused the insert SQL to error out.

[BHo15]

Yep... Still erroring. It of course only happens if I force an error up top.

I did indeed look at your article on SQL NeoPa. Thank you for that. But it really does appear that my SQL is solid (this time).

However... I fixed the problem with a workaround. I ended up creating an Append Query, and then just calling that query from my code. It works without error.

Thanks everyone for the help.

[NeoPa]

BHo15:
I did indeed look at your article on SQL NeoPa. Thank you for that. But it really does appear that my SQL is solid (this time).

It appears that you didn't carefully enough, and that it isn't.

That is to say - in reverse order - the resultant SQL string will have single quotes (') in it that should be treated as string literal data and not (as they would be in your SQL) as commands to the SQL interpreter to delimit a literal string (As alluded to by Rabbit twice now).

This leads onto the first. Had you followed the instructions carefully and fully then you would have seen the resultant string had the extra quotes in it that weren't intended to be SQL quotes, but merely part of the string literal data. To get around this you could look at the ideas found in SQL Injection Attack, in particular my two comments in posts #2 & #3.

You should then find that your SQL works correctly even within an error handler.

[BHo15]

Sorry NeoPa. You're experience and knowledge are too much for me.

I played around with the SQL I had prior to going to query. I liked your double single quotes in the SQL Injection Attack post, even though I didn't quite understand it.

I tried it and several other methods. Here are my results...

1)

DoCmd.RunSQL "INSERT INTO tblErrors (ErrDate,ErrNumb,ErrDescrip,ErrLocation) VALUES (#" & Now & "#, 'Err.Number', 'Err.Description', 'txtUser_AfterUpdate - frmLogin')"

Worked without error, but sent "Err.Number" and "Err.Description" to the table instead of their actual values

2)

DoCmd.RunSQL "INSERT INTO tblErrors (ErrDate,ErrNumb,ErrDescrip,ErrLocation) VALUES (#" & Now & "#, ''Err.Number'', ''Err.Description'', ''txtUser_AfterUpdate - frmLogin'')"

Got Syntax error (missing operator) in query expression '''Err.Number'''.

3)

DoCmd.RunSQL "INSERT INTO tblErrors (ErrDate,ErrNumb,ErrDescrip,ErrLocation) VALUES (#" & Now & "#, '" & 'Err.Number' & "', '" & 'Err.Description' & "', '" & 'txtUser_AfterUpdate - frmLogin' & "')"

Got Expected Expression on 'Err.Number'

4)

DoCmd.RunSQL "INSERT INTO tblErrors (ErrDate,ErrNumb,ErrDescrip,ErrLocation) VALUES (#" & Now & "#, " & Chr(34) & Err.Number & Chr(34) & ", " & Chr(34) & Err.Description & Chr(34) & ", " & Chr(34) & "txtUser_AfterUpdate - frmLogin" & Chr(34) & ")"

Worked.

If you have patience with me, I would love to have further instructions in the double singles. I work in Information Security, and am quite familiar with SQL Injection. I just don't know how to program for it.

[NeoPa]

OK. Let's start with the first paragraph after Solutions in the linked thread (How to Debug SQL String). For each of the examples you showed, post the exact SQL used in here and we'll identify why each behaved as it did.

NB. For the same behaviour to occur as occurred before, you'll need the same first error to trigger the routine.

NB. Using Chr(34) (==>") in place of a single-quote (') is not good practice when dealing with SQL. Using Chr(39) (==') makes better sense, but is still exactly the same as using the single-quote unless you do it wrong. There's no real benefit to it. It simply has the effect of confusing anyone trying to maintain your code.

[NeoPa]

BHo15:
I work in Information Security, and am quite familiar with SQL Injection. I just don't know how to program for it.

As an example, consider the following SQL where [Please enter UserID] & [Please Enter your password] are prompts for the user to enter values to be used into variables strUID & strPW respectively :

strSQL = "SELECT [SecurityToken]" & vbNewLine _
       & "FROM   [UserTable]" & vbNewLine _
       & "WHERE  ([UserID]='" & strUID & "')" & vbNewLine _
       & "  AND  ([Password]='" & strPW & "')"

The user enters :
Admin') OR ('X'=
) OR ('X'='Y
The resultant SQL is :

SELECT [SecurityToken]
FROM   [UserTable]
WHERE  ([UserID]='Admin') OR ('X'=')
  AND  ([Password]=') OR ('X'='Y')

That's the danger.

However, if when you create your SQL string you were to use the function suggested, you'd say :

strSQL = "SELECT [SecurityToken]" & vbNewLine _
       & "FROM   [UserTable]" & vbNewLine _
       & "WHERE  ([UserID]='" & SafeSQL(strUID) & "')" & vbNewLine _
       & "  AND  ([Password]='" & SafeSQL(strPW) & "')"

That way you'd end up instead with :

SELECT [SecurityToken]
FROM   [UserTable]
WHERE  ([UserID]='Admin'') OR (''X''=')
  AND  ([Password]=') OR (''X''=''Y')

This would simply fail to find a record and not cause your SQL any problems whatsoever.

[BHo15]

Thank you for attempting to walk through this with me.

I'll admit, I did not use your SafeSQL function, because I wanted to build it myself, and understand how it worked. I also was not using Debug.Print (and SHOULD have).

But this time, I used your SafeSQL function and Debug.Print results. I got an error.

Here is my code...

Dim strSQL As String
        strSQL = SafeSQL("INSERT INTO tblErrors (ErrDate,ErrNumb,ErrDescrip,ErrLocation) VALUES (#" & Now & "#, '" & Err.Number & "', '" & Err.Description & "', '" & "txtUser_AfterUpdate - frmLogin" & "')")
    Debug.Print strSQL
    DoCmd.RunSQL strSQL

Here was what was printed...

INSERT INTO tblErrors (ErrDate,ErrNumb,ErrDescrip,ErrLocation) VALUES (#3/26/2018 8:59:40 AM#, ''94'', ''Invalid use of Null'', ''txtUser_AfterUpdate - frmLogin'')

And here was my error...
Syntax error (missing operator) in query expression '''94'''.

Thoughts?

[BHo15]

And BTW... Feel free to say "Problem exists between keyboard and chair."

[NeoPa]

BHo15:
Thoughts?

Many.

Firstly, when posting anything with quotes it helps to look at it in a non-proportional font, such as that used when we post within the [CODE] tags.

This helps you see the difference between a single double-quote (") and two single-quotes ('').

I expect, now you can see the result, you'll see how far off it is from your requirements.

Secondly, SafeSQL() is specifically for handling data. It's supposed to be called for the data provided by a user (or any other source that may include single-quotes within the data). It should never be used to process the SQL you provide in the code. Your post is a perfect example of why not. It tries to treat your SQL as a simple literal string - which is absolutely not what you want.

I imagine the [ErrNumb] field is actually numeric so doesn't need or want quotes.

Date fields should always be formatted correctly for SQL. Being American and relying on the standard US format matching one of the SQL standard formats is not good enough. It works at home.

Try instead :

Dim strSQL As String
strSQL = "INSERT INTO tblErrors (ErrDate,ErrNumb,ErrDescrip,ErrLocation) " _
       & "VALUES (#" & Format(Now(),"yyyy\-m\-d hh:nn:ss") & "#, " _
       & Err.Number _
       & ", '" & SafeSQL(Err.Description) & "', " _
       & "'txtUser_AfterUpdate - frmLogin')")
Debug.Print strSQL
DoCmd.RunSQL strSQL

[BHo15]

I'm sure no surprise on your end, but... It WORKED!

Why did the ErrLocation value not go through SafeSQL. Is SafeSQL only used for variables? Would it be used if the value was coming from a form? i.e... SafeSQL(Me.txtUserName)

Also, can you explain why SafeSQL replaces ' with '', but then it produces the value with no quotes? I did Debug.Print after the SafeSQL, and my err.description came out as Invalid use of Null (with no quotes)

[NeoPa]

BHo15:
Why did the ErrLocation value not go through SafeSQL.

As the value you used for [ErrLocation] was entirely under your control there was no requirement for it to be pre-processed by SafeSQL().

BHo15:
Is SafeSQL only used for variables?

Yes - as a general rule. Technically, it can also be used for references to anywhere that a user or some other source can provide data that may not be conformant with SQL string literal standards. The Error Description can contain single quotes so that needs to be handled whether it's stored in a variable or not. Most developers will use a variable for that though.

BHo15:
Would it be used if the value was coming from a form? i.e... SafeSQL(Me.txtUserName)

Yes. For the same reason as explained previously.

BHo15:
Also, can you explain why SafeSQL replaces ' with '', but then it produces the value with no quotes?

Simply, because that's what we need it to do and it's been designed to do.

BHo15:
I did Debug.Print after the SafeSQL, and my Err.Description came out as Invalid use of Null (with no quotes)

As you don't explain explicitly it's hard for me to know what you're asking here, but if I understand you correctly, that seems to me to be exactly what you should have expected (The correct output).

I hope that clarifies your understanding a little.

[BHo15]

It does, and I think I have a bit better understanding. I have been doing some more reading on the topic as well.

I appreciate your patience in helping me through this. You are a scholar and a gentleman (and there are not too many of us left out there). 😋

[NeoPa]

BHo15:
You are a scholar and a gentleman (and there are not too many of us left out there).

Another Brit I see :-) I remember the advert, but I've been a Real Ale drinker all my adult life. The adverts were way better than the product (Skol) IMHO ;-)

[BHo15]

Not a Brit as it turns out. A physician once said that to me. I’m glad now to know where he got it.