Inserting data to MS Access; INSERT INTO ERROR

Question

Dear fellow programmers,

I am using VB.NET 2008 and currently trying to add textbox/combobox/datetimepicker values to my Access database which is now connected in my application.

The name of my table is Encoding. This is my code so far:

Imports System.Data.OleDb

Public Class frmEncode
Dim strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &  System.Environment.CurrentDirectory & "\encoding.accdb"

Dim cnnOLEDB As New OleDbConnection(strConnectionString)
Dim cmdOLEDB As New OleDbCommand
Dim cmd As OleDbCommand

'------------
Private Sub ButtonX7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX7.Click

    Dim InsertQuery As String

    InsertQuery = "INSERT INTO Encoding (Client, Address, Date_Bill, Vessel, TIN, Port, Voyage No, Vessel Type, InterPleader1, InterPleader2, Waste Details, Payment, BI#, CSR#, Amount)VALUES(@client, @address, @date, @vess, @tin, @port, @voyno, @vesstype, @intplead1, @intplead2, @waste, @pay, @bi, @csr, @amnt)"

    Dim cmd As OleDbCommand = New OleDbCommand(InsertQuery, cnnOLEDB)

    cmd.Parameters.AddWithValue("@client", ComboBoxEx1.Text)
    cmd.Parameters.AddWithValue("@address", TextBoxX1.Text)
    cmd.Parameters.AddWithValue("@date", DateTimePicker1.Text)
    cmd.Parameters.AddWithValue("@vess", TextBoxX2.Text)
    cmd.Parameters.AddWithValue("@tin", TextBoxX3.Text)
    cmd.Parameters.AddWithValue("@port", ComboBoxEx2.Text)
    cmd.Parameters.AddWithValue("@voyno", TextBoxX4.Text)
    cmd.Parameters.AddWithValue("@vesstype", ComboBoxEx3.Text)
    cmd.Parameters.AddWithValue("@intplead1", ComboBoxEx4.Text)
    cmd.Parameters.AddWithValue("@intplead2", ComboBoxEx5.Text)
    cmd.Parameters.AddWithValue("@waste", ComboBoxEx6.Text)
    cmd.Parameters.AddWithValue("@pay", ComboBoxEx7.Text)
    cmd.Parameters.AddWithValue("@bi", TextBoxX7.Text)
    cmd.Parameters.AddWithValue("@csr", TextBoxX5.Text)
    cmd.Parameters.AddWithValue("@amnt", TextBoxX8.Text)

    cnnOLEDB.Open()
    cmdOLEDB = New OleDbCommand(InsertQuery, cnnOLEDB)
    cmd.ExecuteNonQuery()
    cnnOLEDB.Close()
    frmEncodeDatabase.Show()


End Sub

The error is in the 'cmd.ExecuteNonQuery()', which says 'Syntax error in INSERT INTO statement'.

I don't know what is the problem with System Into statement. Should I add Try/Catch statement? Add quotation marks around the values? Or add brackets??

It's not necessary to SHOUT HELP in your title; it's clear that you need help by the fact you posted here in the first place. VB.NET and Access are both available from the tags you provided, so they're not necessary in the title either. And finally, please RESIST THE URGE TO TYPE IN ALL CAPS. It's rude, and it makes your question harder to read; it also will not get you help any faster. — Ken White
Thank you Sir Ken White and sorry for being rude. Because coming from another forum we usually use the word HELP in our title. Apologies Mr. White. — CodingSource
These are not your other forums. :-) You should take the tour and spend some time in the help center learning how this site works. We're a lot different, especially with regard to quality standards and voting (on both questions and answers). — Ken White

LarsTech LarsTech · Accepted Answer · 2014-12-23T02:38:17

You need to bracket your field names that have spaces, special characters, or are keywords:

[Voyage No],... [BI#], etc...

Are all of your fields in the database a string type? All of your parameters are going to pass strings, so you should convert them, values such as Dates and Numbers, etc.

Inserting data to MS Access; INSERT INTO ERROR

3 Answers