Syntax error INSERT INTO statement - ACCESS DB

Question

I have come a long way since yesterday! lol I am now testing my database project by adding records. I have 58 fields so the INSERT INTO statement is long. No matter what I try (which is everything google has to offer as far as I know), I get the syntax error at runtime which is syntax error INSERT INTO statement. Here is the code I am using:

Private Sub AddRecord()
    'Add Parameters
    Access.AddParam("@v1", txtPrograms.Text)
    Access.AddParam("@v2", dtpQADate.Value.ToShortDateString)
    Access.AddParam("@v3", txtReviewer.Text)
    Access.AddParam("@v4", cmbAssessment.SelectedItem.ToString)
    Access.AddParam("@v5", dtpAssessment.Value.ToShortDateString)
    Access.AddParam("@v6", cmbScreening.SelectedItem.ToString)
    Access.AddParam("@v7", dtpScreening.Value.ToShortDateString)
    Access.AddParam("@v8", cmbStatusIndicators.SelectedItem.ToString)
    Access.AddParam("@v9", dtpStatusIndicators.Value.ToShortDateString)
    Access.AddParam("@v10", cmbHealthRecord.SelectedItem.ToString)
    Access.AddParam("@v11", dtpHealthRecord.Value.ToShortDateString)
    Access.AddParam("@v12", cmbSA.SelectedItem.ToString)
    Access.AddParam("@v13", dtpSA.Value.ToShortDateString)
    Access.AddParam("@v14", cmbFrail.SelectedItem.ToString)
    Access.AddParam("@v15", txtFrailExplain.Text)
    Access.AddParam("@v16", cmbDrAppt.SelectedItem.ToString)
    Access.AddParam("@v17", txtDrApptExplain.Text)
    Access.AddParam("@v18", cmbApptTracking.SelectedItem.ToString)
    Access.AddParam("@v19", cmbCurrent.SelectedItem.ToString)
    Access.AddParam("@v20", txtEFGuidelines.Text)
    Access.AddParam("@v21", cmbOrdersInPlace.SelectedItem.ToString)
    Access.AddParam("@v22", cmbOrdersCorrect.SelectedItem.ToString)
    Access.AddParam("@v23", txtAllergies.Text)
    Access.AddParam("@v24", cmbCertEntries.SelectedItem.ToString)
    Access.AddParam("@v25", cmbCertEntriesNo.SelectedItem.ToString)
    Access.AddParam("@v26", txtPC1.Text)
    Access.AddParam("@v27", dtpPC1.Value.ToShortDateString)
    Access.AddParam("@v28", txtPC2.Text)
    Access.AddParam("@v29", dtpPC2.Value.ToShortDateString)
    Access.AddParam("@v30", txtPC3.Text)
    Access.AddParam("@v31", dtpPC3.Value.ToShortDateString)
    Access.AddParam("@v32", txtPC4.Text)
    Access.AddParam("@v33", dtpPC4.Value.ToShortDateString)
    Access.AddParam("@v34", txtPC5.Text)
    Access.AddParam("@v35", dtpPC5.Value.ToShortDateString)
    Access.AddParam("@v36", txtPC6.Text)
    Access.AddParam("@v37", dtpPC6.Value.ToShortDateString)
    Access.AddParam("@v38", txtPC7.Text)
    Access.AddParam("@v39", dtpPC7.Value.ToShortDateString)
    Access.AddParam("@v40", txtPC8.Text)
    Access.AddParam("@v41", dtpPC8.Value.ToShortDateString)
    Access.AddParam("@v42", cmbLogComplete.SelectedItem.ToString)
    Access.AddParam("@v43", cmbLogCorrect.SelectedItem.ToString)
    Access.AddParam("@v44", cmbPRNComplete.SelectedItem.ToString)
    Access.AddParam("@v45", cmbPRNCorrect.SelectedItem.ToString)
    Access.AddParam("@v46", cmbNoProtocols.SelectedItem.ToString)
    Access.AddParam("@v47", txtControlled.Text)
    Access.AddParam("@v48", txtWasted.Text)
    Access.AddParam("@v49", cmbAccurate.SelectedItem.ToString)
    Access.AddParam("@v50", cmbCapable.SelectedItem.ToString)
    Access.AddParam("@v51", dtpSelfAdmin.Value.ToShortDateString)
    Access.AddParam("@v52", cmbLocked.SelectedItem.ToString)
    Access.AddParam("@v53", cmbOTC.SelectedItem.ToString)
    Access.AddParam("@v54", cmbOTCNo.SelectedItem.ToString)
    Access.AddParam("@v55", dtpPermit.Value.ToShortDateString)
    Access.AddParam("@v56", dtpWaiver.Value.ToShortDateString)
    Access.AddParam("@v57", cmbErrors.SelectedItem.ToString)
    Access.AddParam("@v58", txtNotesFinal.Text)

    'Execute Insert Command
    Access.ExecQuery("INSERT INTO tblQA ([Program],[assessdate],[Reviewer],[healthassess],[healthassessdate],[Screening],[screeningdate],[healthindicators],[healthindicatorsdate],[healthrecord],[healthrecorddate],[sareview],[sareviewdate],[Frail],[frailexplanation],[drappt],[drapptexplanation],[trackingform],[trackingformcorrect],[efguidelines],[ordersinplace],[orderscorrect],[Allergies],[certentries],[certentriesnum],[pc1],[pc1date],[pc2],[pc2date],[pc3],[pc3date],[pc4],[pc4date],[pc5],[pc5date],[pc6],[pc6date],[pc7],[pc7date],[pc8],[pc8date],[logdoccomplete],[logdoccorrect],[prnprotocolscomplete],[prnprotocolscorrect],[numprnprotocols],[cmedslist],[cmedslistwasted],[cmedslistaccurate],[selfadmincapable],[selfadminevaldate],[medslocked],[otcforms],[otcnum],[permitadmindate],[epwaiverdate],[numerrors],[Notes] ) _
                    VALUES (@v1,@v2,@v3,@v4,@v5,@v6,@v7,@v8,@v9,@v10,@v11,@v12,@v13,@v14,@v15,@v16,@v17,@v18,@v19,@v20,@v21,@v22,@v23,@v24, @v25,@v26,@v27,@v28,@v29,@v30,@v31,@v32,@v33,@v34,@v35,@v36,@v37,@v38,@v39,@v40,@v41,@v42,@v43,@v44,@v45,@v46,@v47,@v48,@v49,@v50,@v51,@v52,@v53,@v54,@v55,@v56,@v57,@v58);")

I have checked similar posts, but none seem to resolve my issue. Thank you for taking the time to guide me once again!

Did you try to remove square brackets [ and ] from column name — Adisak Anusornsrirung
– Adisak Anusornsrirung, Commented Apr 4, 2020 at 3:25

jmcilhinney · Accepted Answer · 2020-04-04 03:00:17Z

You have mistakenly put a VB line continuation character inside the literal String.

If you want to use a line continuation character, this:

Access.ExecQuery("INSERT INTO tblQA ([Program],[assessdate],[Reviewer],[healthassess],[healthassessdate],[Screening],[screeningdate],[healthindicators],[healthindicatorsdate],[healthrecord],[healthrecorddate],[sareview],[sareviewdate],[Frail],[frailexplanation],[drappt],[drapptexplanation],[trackingform],[trackingformcorrect],[efguidelines],[ordersinplace],[orderscorrect],[Allergies],[certentries],[certentriesnum],[pc1],[pc1date],[pc2],[pc2date],[pc3],[pc3date],[pc4],[pc4date],[pc5],[pc5date],[pc6],[pc6date],[pc7],[pc7date],[pc8],[pc8date],[logdoccomplete],[logdoccorrect],[prnprotocolscomplete],[prnprotocolscorrect],[numprnprotocols],[cmedslist],[cmedslistwasted],[cmedslistaccurate],[selfadmincapable],[selfadminevaldate],[medslocked],[otcforms],[otcnum],[permitadmindate],[epwaiverdate],[numerrors],[Notes] ) _
                VALUES (@v1,@v2,@v3,@v4,@v5,@v6,@v7,@v8,@v9,@v10,@v11,@v12,@v13,@v14,@v15,@v16,@v17,@v18,@v19,@v20,@v21,@v22,@v23,@v24, @v25,@v26,@v27,@v28,@v29,@v30,@v31,@v32,@v33,@v34,@v35,@v36,@v37,@v38,@v39,@v40,@v41,@v42,@v43,@v44,@v45,@v46,@v47,@v48,@v49,@v50,@v51,@v52,@v53,@v54,@v55,@v56,@v57,@v58);")

should be this:

Access.ExecQuery("INSERT INTO tblQA ([Program],[assessdate],[Reviewer],[healthassess],[healthassessdate],[Screening],[screeningdate],[healthindicators],[healthindicatorsdate],[healthrecord],[healthrecorddate],[sareview],[sareviewdate],[Frail],[frailexplanation],[drappt],[drapptexplanation],[trackingform],[trackingformcorrect],[efguidelines],[ordersinplace],[orderscorrect],[Allergies],[certentries],[certentriesnum],[pc1],[pc1date],[pc2],[pc2date],[pc3],[pc3date],[pc4],[pc4date],[pc5],[pc5date],[pc6],[pc6date],[pc7],[pc7date],[pc8],[pc8date],[logdoccomplete],[logdoccorrect],[prnprotocolscomplete],[prnprotocolscorrect],[numprnprotocols],[cmedslist],[cmedslistwasted],[cmedslistaccurate],[selfadmincapable],[selfadminevaldate],[medslocked],[otcforms],[otcnum],[permitadmindate],[epwaiverdate],[numerrors],[Notes])" & _
                 "VALUES (@v1,@v2,@v3,@v4,@v5,@v6,@v7,@v8,@v9,@v10,@v11,@v12,@v13,@v14,@v15,@v16,@v17,@v18,@v19,@v20,@v21,@v22,@v23,@v24,@v25,@v26,@v27,@v28,@v29,@v30,@v31,@v32,@v33,@v34,@v35,@v36,@v37,@v38,@v39,@v40,@v41,@v42,@v43,@v44,@v45,@v46,@v47,@v48,@v49,@v50,@v51,@v52,@v53,@v54,@v55,@v56,@v57,@v58);")

Since about 2008, VB hasn't required line continuation characters in most situations though (those where the line could not possibly be valid on its own) so you could do this:

Access.ExecQuery("INSERT INTO tblQA ([Program],[assessdate],[Reviewer],[healthassess],[healthassessdate],[Screening],[screeningdate],[healthindicators],[healthindicatorsdate],[healthrecord],[healthrecorddate],[sareview],[sareviewdate],[Frail],[frailexplanation],[drappt],[drapptexplanation],[trackingform],[trackingformcorrect],[efguidelines],[ordersinplace],[orderscorrect],[Allergies],[certentries],[certentriesnum],[pc1],[pc1date],[pc2],[pc2date],[pc3],[pc3date],[pc4],[pc4date],[pc5],[pc5date],[pc6],[pc6date],[pc7],[pc7date],[pc8],[pc8date],[logdoccomplete],[logdoccorrect],[prnprotocolscomplete],[prnprotocolscorrect],[numprnprotocols],[cmedslist],[cmedslistwasted],[cmedslistaccurate],[selfadmincapable],[selfadminevaldate],[medslocked],[otcforms],[otcnum],[permitadmindate],[epwaiverdate],[numerrors],[Notes])" &
                 "VALUES (@v1,@v2,@v3,@v4,@v5,@v6,@v7,@v8,@v9,@v10,@v11,@v12,@v13,@v14,@v15,@v16,@v17,@v18,@v19,@v20,@v21,@v22,@v23,@v24,@v25,@v26,@v27,@v28,@v29,@v30,@v31,@v32,@v33,@v34,@v35,@v36,@v37,@v38,@v39,@v40,@v41,@v42,@v43,@v44,@v45,@v46,@v47,@v48,@v49,@v50,@v51,@v52,@v53,@v54,@v55,@v56,@v57,@v58);")

In even more recent versions of VB (since 2017 I think, but maybe 2015), multiline String literals are also supported, so you could do this:

Access.ExecQuery("INSERT INTO tblQA ([Program],[assessdate],[Reviewer],[healthassess],[healthassessdate],[Screening],[screeningdate],[healthindicators],[healthindicatorsdate],[healthrecord],[healthrecorddate],[sareview],[sareviewdate],[Frail],[frailexplanation],[drappt],[drapptexplanation],[trackingform],[trackingformcorrect],[efguidelines],[ordersinplace],[orderscorrect],[Allergies],[certentries],[certentriesnum],[pc1],[pc1date],[pc2],[pc2date],[pc3],[pc3date],[pc4],[pc4date],[pc5],[pc5date],[pc6],[pc6date],[pc7],[pc7date],[pc8],[pc8date],[logdoccomplete],[logdoccorrect],[prnprotocolscomplete],[prnprotocolscorrect],[numprnprotocols],[cmedslist],[cmedslistwasted],[cmedslistaccurate],[selfadmincapable],[selfadminevaldate],[medslocked],[otcforms],[otcnum],[permitadmindate],[epwaiverdate],[numerrors],[Notes])
VALUES (@v1,@v2,@v3,@v4,@v5,@v6,@v7,@v8,@v9,@v10,@v11,@v12,@v13,@v14,@v15,@v16,@v17,@v18,@v19,@v20,@v21,@v22,@v23,@v24,@v25,@v26,@v27,@v28,@v29,@v30,@v31,@v32,@v33,@v34,@v35,@v36,@v37,@v38,@v39,@v40,@v41,@v42,@v43,@v44,@v45,@v46,@v47,@v48,@v49,@v50,@v51,@v52,@v53,@v54,@v55,@v56,@v57,@v58);")

SQL just ignores line breaks and whitespace so, if you like alignment, you can also do this:

Access.ExecQuery("INSERT INTO tblQA ([Program],[assessdate],[Reviewer],[healthassess],[healthassessdate],[Screening],[screeningdate],[healthindicators],[healthindicatorsdate],[healthrecord],[healthrecorddate],[sareview],[sareviewdate],[Frail],[frailexplanation],[drappt],[drapptexplanation],[trackingform],[trackingformcorrect],[efguidelines],[ordersinplace],[orderscorrect],[Allergies],[certentries],[certentriesnum],[pc1],[pc1date],[pc2],[pc2date],[pc3],[pc3date],[pc4],[pc4date],[pc5],[pc5date],[pc6],[pc6date],[pc7],[pc7date],[pc8],[pc8date],[logdoccomplete],[logdoccorrect],[prnprotocolscomplete],[prnprotocolscorrect],[numprnprotocols],[cmedslist],[cmedslistwasted],[cmedslistaccurate],[selfadmincapable],[selfadminevaldate],[medslocked],[otcforms],[otcnum],[permitadmindate],[epwaiverdate],[numerrors],[Notes])
                  VALUES (@v1,@v2,@v3,@v4,@v5,@v6,@v7,@v8,@v9,@v10,@v11,@v12,@v13,@v14,@v15,@v16,@v17,@v18,@v19,@v20,@v21,@v22,@v23,@v24,@v25,@v26,@v27,@v28,@v29,@v30,@v31,@v32,@v33,@v34,@v35,@v36,@v37,@v38,@v39,@v40,@v41,@v42,@v43,@v44,@v45,@v46,@v47,@v48,@v49,@v50,@v51,@v52,@v53,@v54,@v55,@v56,@v57,@v58);")

Collectives™ on Stack Overflow

Syntax error INSERT INTO statement - ACCESS DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related