By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Difficulty with INSERT INTO sql

BHo15
100+
P: 135
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...
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO tblErrors (ErrDate,ErrNumb,ErrDescrip,ErrLocation) VALUES (#" & Now & "#, '" & Err.Number & "', '" & Err.Description & "', '" & _
  2.                     "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.
Mar 22 '18 #1
Share this Question
Share on Google+
29 Replies


NeoPa
Expert Mod 15k+
P: 31,271
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.
Mar 22 '18 #2

twinnyfo
Expert Mod 2.5K+
P: 3,127
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). ;-)
Mar 22 '18 #3

BHo15
100+
P: 135
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!
Mar 22 '18 #4

BHo15
100+
P: 135
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.
Mar 22 '18 #5

NeoPa
Expert Mod 15k+
P: 31,271
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.
Mar 22 '18 #6

BHo15
100+
P: 135
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.
Mar 22 '18 #7

BHo15
100+
P: 135
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...
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLogin_Click()
  2.     On Error GoTo Ooops
  3.     Dim intSecLevel As Integer
  4.         intSecLevel = DLookup("UserSecurity", "tblUsers", "Username = '" & Me.txtUser & "')")
  5.     Select Case intSecLevel
  6.         Case Is = 0 'Admin
  7.         Case Is = 1 'User
  8.             DoCmd.OpenForm "frmEntries", , , "CoderID = " & Me.CoderID
  9.             Me.Visible = False
  10.         Case Is = 2 'SuperUser
  11.             DoCmd.OpenForm "Switchboard"
  12.             Me.Visible = False
  13.     End Select
  14.     Exit Sub
  15.  
  16. Ooops:
  17.     MsgBox ("There has been an error. " & Chr(10) & "If it continues, let your manager know.")
  18.     DoCmd.SetWarnings False
  19.     DoCmd.RunSQL "INSERT INTO tblErrors (ErrDate, ErrNumb, ErrDescrip, ErrLocation) VALUES (#" & Now & "#, '" & Err.Number & "', '" & Err.Description & _
  20.                         "', '" & "cmdLogin_Click - frmLogin" & "')"
  21.     DoCmd.SetWarnings True
  22.     Exit Sub
  23. 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.
Mar 23 '18 #8

BHo15
100+
P: 135
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?
Mar 23 '18 #9

NeoPa
Expert Mod 15k+
P: 31,271
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.
Mar 23 '18 #10

Rabbit
Expert Mod 10K+
P: 12,324
You need to escape the single quotes on those variables you're plugging into the RunSQL. Either that or use parameterized queries.
Mar 23 '18 #11

BHo15
100+
P: 135
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.
Mar 23 '18 #12

BHo15
100+
P: 135
Update... I created a sub to just test my SQL. I used variables instead of Err.Number and Err.Description. Here it is.
Expand|Select|Wrap|Line Numbers
  1. Public Sub Test()
  2.     Dim ErrNumber As String
  3.         ErrNumber = "12345"
  4.     Dim ErrDescrip As String
  5.         ErrDescrip = "Gonna be bad"
  6.     DoCmd.SetWarnings False
  7.     DoCmd.RunSQL "INSERT INTO tblErrors (ErrDate, ErrNumb, ErrDescrip, ErrLocation) VALUES (#" & Now & "#, '" & ErrNumber & "', '" & ErrDescrip & _
  8.                         "', '" & "cmdLogin_Click - frmLogin" & "')"
  9.     DoCmd.SetWarnings True
  10. End Sub
It worked like a champ with no errors.
Mar 23 '18 #13

NeoPa
Expert Mod 15k+
P: 31,271
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.
Mar 23 '18 #14

BHo15
100+
P: 135
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?
Mar 23 '18 #15

NeoPa
Expert Mod 15k+
P: 31,271
Is it still erroring (You indicated in post #9 it was ok.)?

Have you checked out the link in post #10?
Mar 24 '18 #16

Rabbit
Expert Mod 10K+
P: 12,324
Bho fixed the dlookup error. But that original error description had unescaped quotes in it which caused the insert SQL to error out.
Mar 24 '18 #17

BHo15
100+
P: 135
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.
Mar 25 '18 #18

NeoPa
Expert Mod 15k+
P: 31,271
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.
Mar 25 '18 #19

BHo15
100+
P: 135
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)
Expand|Select|Wrap|Line Numbers
  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)
Expand|Select|Wrap|Line Numbers
  1. 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)
Expand|Select|Wrap|Line Numbers
  1. 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)
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 25 '18 #20

NeoPa
Expert Mod 15k+
P: 31,271
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.
Mar 26 '18 #21

NeoPa
Expert Mod 15k+
P: 31,271
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 :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT [SecurityToken]" & vbNewLine _
  2.        & "FROM   [UserTable]" & vbNewLine _
  3.        & "WHERE  ([UserID]='" & strUID & "')" & vbNewLine _
  4.        & "  AND  ([Password]='" & strPW & "')"
The user enters :
Admin') OR ('X'=
) OR ('X'='Y
The resultant SQL is :
Expand|Select|Wrap|Line Numbers
  1. SELECT [SecurityToken]
  2. FROM   [UserTable]
  3. WHERE  ([UserID]='Admin') OR ('X'=')
  4.   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 :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT [SecurityToken]" & vbNewLine _
  2.        & "FROM   [UserTable]" & vbNewLine _
  3.        & "WHERE  ([UserID]='" & SafeSQL(strUID) & "')" & vbNewLine _
  4.        & "  AND  ([Password]='" & SafeSQL(strPW) & "')"
That way you'd end up instead with :
Expand|Select|Wrap|Line Numbers
  1. SELECT [SecurityToken]
  2. FROM   [UserTable]
  3. WHERE  ([UserID]='Admin'') OR (''X''=')
  4.   AND  ([Password]=') OR (''X''=''Y')
This would simply fail to find a record and not cause your SQL any problems whatsoever.
Mar 26 '18 #22

BHo15
100+
P: 135
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...
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.         strSQL = SafeSQL("INSERT INTO tblErrors (ErrDate,ErrNumb,ErrDescrip,ErrLocation) VALUES (#" & Now & "#, '" & Err.Number & "', '" & Err.Description & "', '" & "txtUser_AfterUpdate - frmLogin" & "')")
  3.     Debug.Print strSQL
  4.     DoCmd.RunSQL strSQL
Here was what was printed...
Expand|Select|Wrap|Line Numbers
  1. 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?
Mar 26 '18 #23

BHo15
100+
P: 135
And BTW... Feel free to say "Problem exists between keyboard and chair."
Mar 26 '18 #24

NeoPa
Expert Mod 15k+
P: 31,271
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.
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "INSERT INTO tblErrors (ErrDate,ErrNumb,ErrDescrip,ErrLocation) " _
  3.        & "VALUES (#" & Format(Now(),"yyyy\-m\-d hh:nn:ss") & "#, " _
  4.        & Err.Number _
  5.        & ", '" & SafeSQL(Err.Description) & "', " _
  6.        & "'txtUser_AfterUpdate - frmLogin')")
  7. Debug.Print strSQL
  8. DoCmd.RunSQL strSQL
Mar 26 '18 #25

BHo15
100+
P: 135
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)
Mar 26 '18 #26

NeoPa
Expert Mod 15k+
P: 31,271
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.
Mar 26 '18 #27

BHo15
100+
P: 135
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). 😋
Mar 26 '18 #28

NeoPa
Expert Mod 15k+
P: 31,271
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 ;-)
Mar 27 '18 #29

BHo15
100+
P: 135
Not a Brit as it turns out. A physician once said that to me. I’m glad now to know where he got it.
Mar 27 '18 #30

Post your reply

Sign in to post your reply or Sign up for a free account.