Joe Gambler

Joe Gambler

  • NA
  • 9
  • 0

OleDbException was unhandled - Syntax error in INSERT INTO

Oct 19 2009 9:51 AM
I am attempting to use an Access .mdb file as my database on a stand alone PC that has neither SQL or Access installed.
The program will read the records, but when I attempt to add a record I receive the syntax error.
As I have only been learning C# for 8 lessons, this is a little beyond my level of knowledge.


This is being coded & debugged on Visual Studio 2008 Pro. In the school.mdb file. the ID & Phone _Number columns are "numbers" & all other columns are "text".
This code works and "loads" the form with the 1st record in the school.mdb file

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

 

namespace UsingAccess

{

    public partial class namesForm : Form

    {

        System.Data.OleDb.OleDbConnection con;

        DataSet ds1;

        System.Data.OleDb.OleDbDataAdapter da;

 

        public namesForm()

        {

            InitializeComponent();

        }

 

        private void Form1_Load(object sender, EventArgs e) /* loads the 1st record in school.mdb to the form */

        {

            con = new System.Data.OleDb.OleDbConnection();

            ds1 = new DataSet();

 

            con.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/school.mdb";

 

            string sql = "SELECT * From [Names]";

            da = new System.Data.OleDb.OleDbDataAdapter(sql, con);

 

            con.Open();

 

            da.Fill(ds1, "Names");

            NavigateRecords();

 

            con.Close();

            con.Dispose();

 

        }

 

        private void NavigateRecords()

        {

            DataRow dRow = ds1.Tables["Names"].Rows[0];

 

            firstNameTextBox.Text = dRow.ItemArray.GetValue(1).ToString();

            lastNameTextBox.Text = dRow.ItemArray.GetValue(2).ToString();

            address1TextBox.Text = dRow.ItemArray.GetValue(3).ToString();

            address2TextBox.Text = dRow.ItemArray.GetValue(4).ToString();

            phoneNoTextBox.Text = dRow.ItemArray.GetValue(5).ToString();

 

        }

    }

}



However when I attempt to add a record from the form, I receive the error at the code line
da.Update(ds1, "Names");


        private void btnSave_Click(object sender, EventArgs e)

        {

 

            con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/school.mdb";

            con.Open();

 

            System.Data.OleDb.OleDbCommandBuilder cb;

            cb = new System.Data.OleDb.OleDbCommandBuilder(da);

 

            DataRow dRow = ds1.Tables["Names"].NewRow();

 

            dRow[0] = IDNoTextBox.Text;

            dRow[1] = firstNameTextBox.Text;

            dRow[2] = lastNameTextBox.Text;

            dRow[3] = address1TextBox.Text;

            dRow[4] = address2TextBox.Text;

            dRow[5] = phoneNoTextBox.Text;

            dRow[6] = yearNoTextBox.Text;

 

            ds1.Tables["Names"].Rows.Add(dRow);

 

            MaxRows = MaxRows + 1;

            inc = MaxRows - 1;

 

            da.Update(ds1, "Names");

            MessageBox.Show("Entry Added");

 

        }


Thanks for taking the time to read this message and any suggestions or help would be appreciated.

Answers (10)