VB.net & Access query

Question

Im currently doing a vb.net project for college and want to create a new access record using textboxes, masked textboxes and richtextboxes using the vb gui. However I keep getting this exception:

"An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Syntax error in INSERT INTO statement."

Here is my code which is working on other forms

Private Sub btnSaveNew_Click(sender As Object, e As EventArgs) Handles btnSaveNew.Click
    Dim objrow As DataRow

    objrow = objDataSet.Tables("tblEngineersReport").NewRow

    objrow.Item("To") = txtTo.Text
    objrow.Item("Date_Carried_Out") = txtCompletedDate.Text
    objrow.Item("Description_Of_Work") = txtWorkDescription.Text
    objrow.Item("Comment") = txtComment.Text
    objrow.Item("Quantity1") = txtQuantity1.Text
    objrow.Item("Quantity2") = txtQuantity2.Text
    objrow.Item("Quantity3") = txtQuantity3.Text
    objrow.Item("Quantity4") = txtQuantity4.Text
    objrow.Item("Item_Description1") = txtDescription.Text
    objrow.Item("Item_Description2") = txtDescription2.Text
    objrow.Item("Item_Description3") = txtDescription3.Text
    objrow.Item("Item_Description4") = txtDescription4.Text
    objrow.Item("Unit_Price1") = txtUnitPrice1.Text
    objrow.Item("Unit_Price2") = txtUnitPrice2.Text
    objrow.Item("Unit_Price3") = txtUnitPrice3.Text
    objrow.Item("Unit_Price4") = txtUnitPrice4.Text
    objrow.Item("Rate1") = txtRate1.Text
    objrow.Item("Rate2") = txtRate2.Text
    objrow.Item("Rate3") = txtRate3.Text
    objrow.Item("Labour1") = txtDescription5.Text
    objrow.Item("Labour2") = txtDescription6.Text
    objrow.Item("Labour3") = txtDescription7.Text
    objrow.Item("Hours_Worked1") = txtHours1.Text
    objrow.Item("Hours_Worked2") = txtHours2.Text
    objrow.Item("Hours_Worked3") = txtHours3.Text

    objDataSet.Tables("tblEngineersReport").Rows.Add(objrow)
    objEngineerDA.Update(objDataSet, "tblEngineersReport")

    Retrieve()

    MessageBox.Show("new record added")
    cboJobID.Enabled = True

End Sub

the Quanity textboxes down to the hours worked are contained within a table layout panel and am just wondering would this have anything to do with the record not saving?

Is there any conversion that needs to be done? Can you maybe show the design of the table? — Icepickle
– Icepickle, Commented Apr 6, 2015 at 13:06
Are you sure all of the columns in the db table is of type Text? — Bjørn-Roger Kringsjå
– Bjørn-Roger Kringsjå, Commented Apr 6, 2015 at 13:15
no there of type number and date/time aswell, does that matter? — Hector122
– Hector122, Commented Apr 6, 2015 at 13:39

Steve · Accepted Answer · 2015-04-06 13:24:21Z

1

Looking at the names of your columns I could notice that you have a column named TO. This is a reserved keyword in MS-Access and thus the autogenerated queries for your adapter will have a syntax error if you don't tell to your OleDbCommandBuilder to encapsulate the column names with the appropriate QuotePrefix and QuoteSuffix string.

You need to add this code, just after the declaration and initialization of your OleDbCommandBuilder-

 Dim builder = new OleDbCommandBuilder(objEngineerDA)
 builder.QuotePrefix = "["
 builder.QuoteSuffix = "]"

answered Apr 6, 2015 at 13:24

Steve

217k22 gold badges242 silver badges296 bronze badges

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

3 Comments

Steve Over a year ago

It is the same Syntax Error as before? Did you add the QuotePrefix and QuoteSuffix to your OleDbCommandBuilder before generating the INSERT/UPDATE commands?

Hector122 Over a year ago

I didn't, where do i add this?

Steve Over a year ago

To execute the Update command of any dataadapter you need to have the InsertCommand/UpdateCommand/DeleteCommand properties of the adapter set to valid OleDbCommands. You could do this manually or using an OleDbCommandBuilder as shown in my answer. The declaration and initialization of the OleDbCommandBuilder should be done just after you have set your OleDbDataAdapter named objEngineerDA with the command that Select records from your table If you add the code that initializes the objEngineerDA I could be more precise in my answer.

Russ · Accepted Answer · 2015-04-06 13:08:37Z

0

You'll need to check the INSERT statement used in the definition of your DataAdapter (objEngineerDA). The syntax of that INSERT is apparently incorrect, according to the error. Without seeing what is currently there, I cannot advise as to what is wrong with it.

answered Apr 6, 2015 at 13:08

Russ

4,19325 silver badges33 bronze badges

Collectives™ on Stack Overflow

VB.net & Access query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related