ERROR in INSERT INTO Statement C#

Get help on programming - C++, Java, Delphi, etc.
Post Reply
geraldhum
Registered User
Posts: 199
Joined: 13 Feb 2009, 12:59

ERROR in INSERT INTO Statement C#

Post by geraldhum »

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
m_daDataAdapter.Update(m_dtContacts);
With error message, (Syntax error in INSERT INTO statement)

Can anyone see a fault here as i cant.
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: ERROR in INSERT INTO Statement C#

Post by RuadRauFlessa »

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.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
geraldhum
Registered User
Posts: 199
Joined: 13 Feb 2009, 12:59

Re: ERROR in INSERT INTO Statement C#

Post by geraldhum »

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
Registered User
Posts: 14338
Joined: 04 Sep 2004, 02:00
Processor: i386DX Sooper
Motherboard: A blue one
Graphics card: A red one
Memory: Hard drive
Location: On a Möbius strip
Contact:

Re: ERROR in INSERT INTO Statement C#

Post by Bladerunner »

Are your data types corresponding?
If I weren't insane: I couldn't be so brilliant! - The Joker
geraldhum
Registered User
Posts: 199
Joined: 13 Feb 2009, 12:59

Re: ERROR in INSERT INTO Statement C#

Post by geraldhum »

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
Registered User
Posts: 199
Joined: 13 Feb 2009, 12:59

Re: ERROR in INSERT INTO Statement C#

Post by geraldhum »

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:)
Post Reply