ERROR in INSERT INTO Statement C#

geraldhum · **Joined:** 13 Feb 2009, 12:59 **Posts:** 156

Hi Guys

I am busy learn from SAMS teach yourlsef c# in 24 hours and been struggling for the pass few hours on this Database lesson. My Code is as follows.

class Level Vairables

Code: Select all

   OleDbConnection m_cnADONetconnection = new OleDbConnection();
        OleDbDataAdapter m_daDataAdapter = new OleDbDataAdapter();
        DataTable m_dtContacts = new DataTable(); 
        int m_rowPosition = 0;

Form Load Event

Code: Select all

   m_cnADONetconnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\JosamRepairs.accdb;Persist Security Info=False";
            m_cnADONetconnection.Open();
            m_daDataAdapter = new OleDbDataAdapter ("SELECT * FROM Repairs ",m_cnADONetconnection);
            OleDbCommandBuilder m_cbCommandBuilder = new OleDbCommandBuilder(m_daDataAdapter);
            m_daDataAdapter.Fill(m_dtContacts);

btnSave_click

Code: Select all

   DataRow drNewRow = m_dtContacts.NewRow();
            drNewRow["Client"] = cboClient.Text;
            drNewRow["Date"] = dateTimePicker1.Text;
            drNewRow["Serial"] = txtSerial.Text;
            drNewRow["Fault"] = txtWorkDone.Text;
            m_dtContacts.Rows.Add(drNewRow);
            m_daDataAdapter.Update(m_dtContacts);
            m_rowPosition = m_dtContacts.Rows.Count - 1;

I have gone over everything and still cant seem to get this Right. when i click on save this gets High Lighted

Quote:

m_daDataAdapter.Update(m_dtContacts);

With error message, (Syntax error in INSERT INTO statement)

Can anyone see a fault here as i cant.

RuadRauFlessa · Posted: 29 Jul 2010, 14:13

Check what is in the values you are passing as parameters:

Code: Select all

drNewRow["Client"] = cboClient.Text;
drNewRow["Date"] = dateTimePicker1.Text;
drNewRow["Serial"] = txtSerial.Text;
drNewRow["Fault"] = txtWorkDone.Text;

one of them might contain an ' or something which should be escaped before you pass it in.

geraldhum · **Joined:** 13 Feb 2009, 12:59 **Posts:** 156

I double checked and re-checked, everything seems perfect. checked my connection string and good. Even re-checked my access table, using access 2007.

Bladerunner · Posted: 29 Jul 2010, 15:58

Are your data types corresponding?

geraldhum · **Joined:** 13 Feb 2009, 12:59 **Posts:** 156

Yes they all are. I have even tried commenting out "Date,Serial,Fault" and still the same error, Then i commeted out "Client Date Fault" and still the same error. Here is the code for my fclsNewclient class.

Code: Select all

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;
using System.IO;
using System.Data.OleDb;

namespace josam_Repairs
{
    public partial class clsfNewRepair : Form
    {
        OleDbConnection m_cnADONetconnection = new OleDbConnection();
        OleDbDataAdapter m_daDataAdapter = new OleDbDataAdapter();
        DataTable m_dtContacts = new DataTable(); 
        int m_rowPosition = 0;


        public clsfNewRepair()
        {
            InitializeComponent();
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            //Closes the form(clsfNewRepair)
            this.Dispose();
            this.Close();
        }

        private void clsfNewRepair_Load(object sender, EventArgs e)
        {
            try
            {
                clsDirectories.RetrieveDirectories(cboClient);

                m_cnADONetconnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\JosamRepairs.accdb;Persist Security Info=False";
                m_cnADONetconnection.Open();
                m_daDataAdapter = new OleDbDataAdapter("SELECT * FROM Repairs", m_cnADONetconnection);
                OleDbCommandBuilder m_cbCommandBuilder = new OleDbCommandBuilder(m_daDataAdapter);
                m_daDataAdapter.Fill(m_dtContacts);
            }
            catch (Exception test)
            {
                MessageBox.Show(test.Message);
            }

          
        }

        private void clsfNewRepair_FormClosed(object sender, FormClosedEventArgs e)
        {
            m_cnADONetconnection.Dispose();
            m_cnADONetconnection.Close();

        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
            

                DataRow drNewRow = m_dtContacts.NewRow();
                drNewRow["Client"] = cboClient.Text;
               drNewRow["Date"] = dateTimePicker1.Text;
                drNewRow["Serial"] = txtSerial.Text;
                drNewRow["Fault"] = txtWorkDone.Text;
                m_dtContacts.Rows.Add(drNewRow);
                m_daDataAdapter.Update(m_dtContacts);
                m_rowPosition = m_dtContacts.Rows.Count - 1;
            }
            catch (System.Data.OleDb.OleDbException test)
            {
                MessageBox.Show(test.Message);
            }
            
           
        }
    }
}

And then the code for my clsDirectory class which i use to scann up the client folders and insert them into the cboClient.

Code: Select all

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace josam_Repairs
{
    public class clsDirectories
    {

        public clsDirectories()
        {
        }

        public static void RetrieveDirectories(System.Windows.Forms.ComboBox cboBox)
        {
            //Vairable to store the JosamClientProfilePath
            String ClientProfilePath = @"\\SERVER\\Share Josam\\Josam Client Profile";

            DirectoryInfo di = new DirectoryInfo(ClientProfilePath);

            DirectoryInfo[] diArr = di.GetDirectories();

            foreach (DirectoryInfo dri in diArr)
                cboBox.Items.Add(dri.Name);
        }
    }

}



               

geraldhum · **Joined:** 13 Feb 2009, 12:59 **Posts:** 156

The problem was with the DateTime in Access, i assume that it is a reserved word but changed it to DateCalibrated and works perfectly. thanks for the help:)

Forum rules

ERROR in INSERT INTO Statement C#

Who is online