VB.NET MSACCESS OLEDB Exception Unhandled Syntax error in INSERT INTO statement. but my statement is correct

Question

I get this error when I run my program

OLEDB Exception Unhandled Syntax error in INSERT INTO statement

but I can't spot the error

This is my code:

Dim pictureData As Byte()
    Using ms As New MemoryStream
        PICPictureBox.Image.Save(ms, Imaging.ImageFormat.Jpeg)
        pictureData = ms.ToArray()
    End Using
    strsql1 = "insert into criminallist (CC#, FIRSTNAME, MIDDLENAME, LASTNAME, QUALIFIERS, ALIAS, GENDER, LASTKNOWNADDRESS, CRIMINALACTIVITY, CRIMINALINVOLVEMENT, AREAOFOPERATION, ISSUINGCOURT, REMARKS, DATEOFJAIL, TRACKERTEAM, PIC)values(@a0,@a1,@a2,@a3,@a4,@a5,@a6,@a7,@a8,@a9,@a10,@a11,@a12,@a13,@a14,@a15)"
    acscmd1.CommandText = strsql1
    acscmd1.Connection = acsconn1
    acscmd1.Parameters.AddWithValue("@a0", CC_TextBox.Text)
    acscmd1.Parameters.AddWithValue("@a1", FIRSTNAMETextBox.Text)
    acscmd1.Parameters.AddWithValue("@a2", MIDDLENAMETextBox.Text)
    acscmd1.Parameters.AddWithValue("@a3", LASTNAMETextBox.Text)
    acscmd1.Parameters.AddWithValue("@a4", QUALIFIERSComboBox.SelectedItem)
    acscmd1.Parameters.AddWithValue("@a5", ALIASTextBox.Text)
    acscmd1.Parameters.AddWithValue("@a6", GENDERComboBox.SelectedItem)
    acscmd1.Parameters.AddWithValue("@a7", LAST_KNOWN_ADDRESSTextBox.Text)
    acscmd1.Parameters.AddWithValue("@a8", CRIMINAL_ACTIVITYTextBox.Text)
    acscmd1.Parameters.AddWithValue("@a9", CRIMINAL_INVOLVEMENTTextBox.Text)
    acscmd1.Parameters.AddWithValue("@a10", AREA_OF_OPERATIONTextBox.Text)
    acscmd1.Parameters.AddWithValue("@a11", ISSUING_COURTTextBox.Text)
    acscmd1.Parameters.AddWithValue("@a12", REMARKSTextBox.Text)
    acscmd1.Parameters.AddWithValue("@a13", DateTimePicker1.Value.Date)
    acscmd1.Parameters.AddWithValue("@a14", TRACKER_TEAMTextBox.Text)
    acscmd1.Parameters.AddWithValue("@a15", pictureData)
    acscmd1.ExecuteNonQuery()
    acscmd1.Dispose()
    MessageBox.Show("ADDED")

Can we stop using AddWithValue() already?

JohnyL
– JohnyL

2018-08-18 16:01:16 +00:00
Commented Aug 18, 2018 at 16:01 — JohnyL
– JohnyL, Commented Aug 18, 2018 at 16:01

jmcilhinney · Accepted Answer · 2018-08-18 15:16:17Z

2

Obviously your statement is not correct or you would not be getting that error message. The issue is with the name of the first column. You can't have spaces or other special characters in a column name in SQL code without escaping it. That's done for Access using brackets, i.e.

insert into criminallist ([CC#], FIRSTNAME

A better option is to not use spaces, special characters or reserved words in column names or any other identifiers.

answered Aug 18, 2018 at 15:16

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

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

7 Comments

HansUp Over a year ago

I wonder whether they will then encounter the same error with ALIAS as a field name.

jmcilhinney Over a year ago

@HansUp, certainly worth considering but "Alias" is not an Access reserved word.

Chris Dunaway Over a year ago

Is it also a problem that there are no spaces between the parentheses and the keyword 'values'? I'm not familiar enough with Access to know if that matters.

jmcilhinney Over a year ago

@ChrisDunaway, no, spaces aren't required except between keywords and/or identifiers. The lack of spaces within the second set of parentheses is no more a problem.

Chris Dunaway Over a year ago

I was referring to the closing parenthesis of the list of columns and the keyword 'values'. In other words, does there need to be spaces before and after the keyword values? i. e. does this )values( need to be ) values (?

|

Collectives™ on Stack Overflow

VB.NET MSACCESS OLEDB Exception Unhandled Syntax error in INSERT INTO statement. but my statement is correct

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related