Hi everyone, pls help me with my problem. I got error (OleDbException was unhandled) (sysntax error in INSERT INTO statement) specifically, the error points out in "int temp = oleDbCmd.ExecuteNonQuery();"

And, here's my code:

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

namespace sad
{
    public partial class SignIn : Form
    {
        private OleDbConnection personalConn;
        private OleDbCommand oleDbCmd = new OleDbCommand();
        private String connParam = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\EtaYuy\Documents\Collection22ady1.mdb;Persist Security Info=False";

        public SignIn()
        {
            personalConn = new OleDbConnection(connParam);
            InitializeComponent();

        }



       private void button1_Click(object sender, EventArgs e)
       {
           Close();

           T frm = new T();
           frm.Show();


       }

       private void button2_Click(object sender, EventArgs e)
       {
           personalConn.Open();
           oleDbCmd.Connection = personalConn;
           oleDbCmd.CommandText = "insert into Personal Data (Block No, Lot No,Number of Occupants,First Name,Family Name,Date of Birth,Age, Blood Type, Place of Birth, Religion) values ('" + this.textBox1.Text + "','" + this.textBox2.Text + "','" + this.textBox3.Text + "','" + this.textBox4.Text + "','" + this.textBox5.Text + "','" + this.textBox6.Text + "','" + this.textBox7.Text + "','" + this.textBox8.Text + "','" + this.textBox9.Text + "','" + this.textBox10.Text + "');";
           oleDbCmd.CommandText = "insert into Spouse (First Name, Family Name,Date of Birth,Age,Blood Type,Place of Birth, Religion, Start of occupancy, contact number, Name of Children, Number of Occupants, Number of Dogs, Vaccinated with Anti-rabies) values ('" + this.textBox11.Text + "','" + this.textBox12.Text + "','" + this.textBox21.Text + "','" + this.textBox13.Text + "','" + this.textBox14.Text + "','" + this.textBox15.Text + "','" + this.textBox16.Text + "','" + this.textBox17.Text + "','" + this.textBox18.Text + "','" + this.richTextBox1.Text + "','" + this.textBox20.Text + "','" + this.textBox25.Text + "','" + this.textBox19.Text + "');";
        **   int temp = oleDbCmd.ExecuteNonQuery();** //error here
           if (temp > 0)
           {
               textBox1.Text = null;
               textBox2.Text = null;
               textBox3.Text = null;
               textBox4.Text = null;
               textBox5.Text = null;
               textBox6.Text = null;
               textBox7.Text = null;
               textBox8.Text = null;
               textBox9.Text = null;
               textBox10.Text = null;
               textBox11.Text = null;
               textBox12.Text = null;
               textBox21.Text = null;
               textBox13.Text = null;
               textBox14.Text = null;
               textBox15.Text = null;
               textBox16.Text = null;
               textBox17.Text = null;
               textBox18.Text = null;
               richTextBox1.Text = null;
               textBox25.Text = null;
               textBox19.Text = null;

               MessageBox.Show("Record Successfuly Added");
               textBox1.Clear();
               textBox2.Clear();
               textBox3.Clear();
               textBox4.Clear();
               textBox5.Clear();
               textBox6.Clear();
               textBox7.Clear();
               textBox8.Clear();
               textBox9.Clear();
               textBox10.Clear();
               textBox11.Clear();
               textBox12.Clear();
               textBox21.Clear();
               textBox13.Clear();
               textBox14.Clear();
               textBox15.Clear();
               textBox16.Clear();
               textBox17.Clear();
               textBox18.Clear();
               richTextBox1.Clear();
               textBox20.Clear();
               textBox25.Clear();
               textBox19.Clear();

           }
           else
           {
               MessageBox.Show("Record Fail to Added");
           }
           personalConn.Close();

       }

       private void SignIn_Load(object sender, EventArgs e)
       {

       }

I hope you can help me. thank you

    }
}

I hope

Edited 2 Years Ago by SkyCross: wrong inputs

For field names with spaces, encapsulate them in square brackets. If you still encounter syntax errors in your queries, try displaying them into message boxes before executing them, and post the queries here.

Thank you scudzilla,
I already tried your suggestions. however I got error again, and it states (OleDbException was unhandled) Data type mismatch in criteria.It points out again in ExecuteQuery();

by the way I have here my Access for the database references

Edited 2 Years Ago by SkyCross: wrong inputs

If your field needs numbers, you won't need apostrophes surrounding your value. I suspect the Block No., Lot No., ages, Number of xxx etc. needs integers? Even the date of birth fields, perhaps you've set them up as date or datetime?

Anyway, as our resident Reverend Jim always says regarding database applications, use parameterized queries. Using such prevents injections in your queries, and may save you headaches caused by data type mismatch problems.

Thank you again scudzilla, it works. however, another problem occured. And it states OleDbException was unhandled(syntax error in INSERT INTO statement) The INSERT INTO statement contains the following unknown field name:"First Name"."Make sure you have typed the name correctly,and try the operation againAnd still it points out in ExecuteQuery();

I tried putting single quotation in strings. Ex:['First Name']

personalConn.Open();
           oleDbCmd.Connection = personalConn;
           oleDbCmd.CommandText = "insert into  [Personal Data] ([Block No], [Lot No],[Number of Occupants],['First Name'],['Family Name'],[Date of Birth],Age, ['Blood Type'], ['Place of Birth'], 'Religion') values ('" + this.textBox1.Text + "','" + this.textBox2.Text + "','" + this.textBox3.Text + "','" + this.textBox4.Text + "','" + this.textBox5.Text + "','" + this.textBox6.Text + "','" + this.textBox7.Text + "','" + this.textBox8.Text + "','" + this.textBox9.Text + "','" + this.textBox10.Text + "');";
           oleDbCmd.CommandText = "insert into [Spouse] (['First Name'], ['Family Name'],[Date of Birth],Age,['Blood Type'],['Place of Birth'], 'Religion', [Start of ocupancy], [contact number], ['Name of Children'], [Number of Occupants], [Number of Dogs], ['Vaccinated with Anti-rabies']) values ('" + this.textBox11.Text + "','" + this.textBox12.Text + "','" + this.textBox21.Text + "','" + this.textBox13.Text + "','" + this.textBox14.Text + "','" + this.textBox15.Text + "','" + this.textBox16.Text + "','" + this.textBox17.Text + "','" + this.textBox18.Text + "','" + this.richTextBox1.Text + "','" + this.textBox20.Text + "','" + this.textBox25.Text + "','" + this.textBox19.Text + "');";
           int temp = oleDbCmd.ExecuteNonQuery();

For field names, you don't need apostrophes, only square brackets.

For example, assuming Block No, Lot No, Number of Occupants and Age need integers:

oleDbCmd.CommandText = "insert into  [Personal Data] ([Block No], [Lot No],[Number of Occupants],[First Name],[Family Name],[Date of Birth],Age, [Blood Type], [Place of Birth], Religion) values (" + this.textBox1.Text + "," + this.textBox2.Text + "," + this.textBox3.Text + ",'" + this.textBox4.Text + "','" + this.textBox5.Text + "','" + this.textBox6.Text + "'," + this.textBox7.Text + ",'" + this.textBox8.Text + "','" + this.textBox9.Text + "','" + this.textBox10.Text + "');";

Notice the text of textboxes 1, 2, 3 and 7 are not surrounded by apostrophes (because their fields are integer types).

if the field names are string does it need an apostrophe?

my Block No, Lot No, Number of Occupants and Date of Birth are in the form of numbers. and its a long integer. Does it require only an integer?
It returns again in Data Type mismatch criteria..:(

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

namespace sad
{
    public partial class SignIn : Form
    {
        private OleDbConnection personalConn;
        private OleDbCommand oleDbCmd = new OleDbCommand();
        private String connParam = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\EtaYuy\Documents\Collection22ady1.mdb;Persist Security Info=False";

        public SignIn()
        {
            personalConn = new OleDbConnection(connParam);
            InitializeComponent();

        }



       private void button1_Click(object sender, EventArgs e)
       {
           Close();

           T frm = new T();
           frm.Show();


       }

       private void button2_Click(object sender, EventArgs e)
       {
           personalConn.Open();
           oleDbCmd.Connection = personalConn;
           oleDbCmd.CommandText = "insert into Personal Data ([Block No], [Lot No],[Number of Occupants],[First Name],[Family Name],[Date of Birth],Age, [Blood Type], [Place of Birth], Religion) values ('" + this.textBox1.Text + "','" + this.textBox2.Text + "','" + this.textBox3.Text + "','" + this.textBox4.Text + "','" + this.textBox5.Text + "','" + this.textBox6.Text + "','" + this.textBox7.Text + "','" + this.textBox8.Text + "','" + this.textBox9.Text + "','" + this.textBox10.Text + "');";
           oleDbCmd.CommandText = "insert into Spouse ([First Name], [Family Name],[Date of Birth],Age,[Blood Type],[Place of Birth], Religion, [Start of ocupancy], [contact number], [Name of Children], [Number of Occupants], [Number of Dogs], [Vaccinated with Anti-rabies]) values ('" + this.textBox11.Text + "','" + this.textBox12.Text + "','" + this.textBox21.Text + "','" + this.textBox13.Text + "','" + this.textBox14.Text + "','" + this.textBox15.Text + "','" + this.textBox16.Text + "','" + this.textBox17.Text + "','" + this.textBox18.Text + "','" + this.richTextBox1.Text + "','" + this.textBox20.Text + "','" + this.textBox25.Text + "','" + this.textBox19.Text + "');";
           int temp = oleDbCmd.ExecuteNonQuery();
           if (temp > 0)
           {
               textBox1.Text = null;
               textBox2.Text = null;
               textBox3.Text = null;
               textBox4.Text = null;
               textBox5.Text = null;
               textBox6.Text = null;
               textBox7.Text = null;
               textBox8.Text = null;
               textBox9.Text = null;
               textBox10.Text = null;
               textBox11.Text = null;
               textBox12.Text = null;
               textBox21.Text = null;
               textBox13.Text = null;
               textBox14.Text = null;
               textBox15.Text = null;
               textBox16.Text = null;
               textBox17.Text = null;
               textBox18.Text = null;
               richTextBox1.Text = null;
               textBox25.Text = null;
               textBox19.Text = null;

               MessageBox.Show("Record Successfuly Added");
               textBox1.Clear();
               textBox2.Clear();
               textBox3.Clear();
               textBox4.Clear();
               textBox5.Clear();
               textBox6.Clear();
               textBox7.Clear();
               textBox8.Clear();
               textBox9.Clear();
               textBox10.Clear();
               textBox11.Clear();
               textBox12.Clear();
               textBox21.Clear();
               textBox13.Clear();
               textBox14.Clear();
               textBox15.Clear();
               textBox16.Clear();
               textBox17.Clear();
               textBox18.Clear();
               richTextBox1.Clear();
               textBox20.Clear();
               textBox25.Clear();
               textBox19.Clear();

           }
           else
           {
               MessageBox.Show("Record Fail to Added");
           }
           personalConn.Close();

       }

       private void SignIn_Load(object sender, EventArgs e)
       {

       }







    }
}
This article has been dead for over six months. Start a new discussion instead.