SQL String syntax error in VBA

Question

I have this SQL string

theSQL = "INSERT INTO tbl_PROJECTS (co_id, contact_id, prop_id, worktype_id, incharge_id, project_name, project_ref," _
    & "project_status, project_awardref, project_awarddate, project_startdate, project_targetdate," _
    & "project_completedate, project_finalreportdate, project_location, project_notes)"

theSQL = theSQL & " VALUES (" & theCoID & "," & theContactID & "," & thePropID & "," & theWorkTypeID & "," & theInCharge & "," _
      & "'" & theProjectName & "'" & "," & theProjectRef & "," & theProjectStatus & "," & theAwardRef _
      & theAwardDate & "," & theStartDate & "," & theTargetDate & "," & theCompleteDate & "," & theFinalReportDate & "," _
      & theLocation & "," & theNotes & ")"

When I do DoCmd.RunSQL (theSQL) I get syntax error (runtime error 3134).

I sent the output to Debug.Print. Can't find what is wrong with the syntax.

Anyone who can tell what is wrong with this sql command from VBA?

Some variables are null like thetargetdate and thecompletedate and I did not include the projectID in this query because it is autonumber. I want the number generated automatically.

Is it not allowed to pass null value to SQL?

Thanks

DEBUG PRINT RESULT :

 INSERT INTO tbl_PROJECTS (co_id, contact_id, prop_id, worktype_id,
 incharge_id, project_name, project_ref, project_status,
 project_awardref, project_awarddate, project_startdate,
 project_targetdate, project_completedate, project_finalreportdate,
 project_location, project_notes) VALUES (61,66,134,1,1,'STRUCTURAL
 DESIGN',,AWARDED,Test LPO,2/11/2016,,,,,Dnata 4 storey warehouse,)

Please read this blog post before continuing down your current path as what you're doing is a security risk. troyhunt.com/2013/07/… — Paul Hunt
– Paul Hunt, Commented Feb 11, 2016 at 13:54
You're also missing a comma at the end of the second row of your SQL (after theAwardRef. Please STRONGLY consider changing to parameters - you avoid these types of mistakes and reduce the risk of SQL injection. — D Stanley
– D Stanley, Commented Feb 11, 2016 at 13:59
@Wayne It was useful to show us what theSQL contains --- doing that got quick responses pointing out the flaws in the INSERT statement. You could spend your time fixing those, but I think you would be better off to switch to a parameter query as the others advised or use DAO.Recordset.Add With either of those you would not be bothered by issues such as quote needed here but not there ... and so forth. — HansUp
– HansUp, Commented Feb 11, 2016 at 14:11

Gustav · Accepted Answer · 2016-02-11 13:58:50Z

3

Is it not allowed to pass null value to SQL?

It is, but it can't be done by stating a space. You must write Null:

VALUES (61,66,134,1,1,'STRUCTURAL DESIGN',Null,'AWARDED','Test LPO',#2/11/2016#,Null,Null,Null,Null,'Dnata 4 storey warehouse Alquoz',Null)

and quotes and date delimiters are missing.

answered Feb 11, 2016 at 13:58

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

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

1 Comment

Wayne Over a year ago

Though I will not continue to this path as others have suggested. Good to know the proper syntax for it. Thanks a lot!

Johnny Bones · Accepted Answer · 2016-02-11 14:01:20Z

You wrote this:

theSQL = "INSERT INTO tbl_PROJECTS (co_id, contact_id, prop_id, worktype_id, incharge_id, project_name, project_ref," _
    & "project_status, project_awardref, project_awarddate, project_startdate, project_targetdate," _
    & "project_completedate, project_finalreportdate, project_location, project_notes)"

theSQL = theSQL & " VALUES (" & theCoID & "," & theContactID & "," & thePropID & "," & theWorkTypeID & "," & theInCharge & "," _
      & "'" & theProjectName & "'" & "," & theProjectRef & "," & theProjectStatus & "," & theAwardRef _
      & theAwardDate & "," & theStartDate & "," & theTargetDate & "," & theCompleteDate & "," & theFinalReportDate & "," _
      & theLocation & "," & theNotes & ")"

In the second line in the second block, there's no ending quotes.

Collectives™ on Stack Overflow

SQL String syntax error in VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related