OleDbException was unhandled(syntax error in INSERT INTO statement)

Question

SkyCross 0 Newbie Poster

10 Years Ago

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 10 Years Ago by SkyCross because: wrong inputs

2 Contributors
6 Replies
351 Views
2 Hours Discussion Span
Latest Post 10 Years Ago Latest Post by SkyCross

All 6 Replies

scudzilla 32 Junior Poster

10 Years Ago

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.

scudzilla 32 Junior Poster

10 Years Ago

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.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

SkyCross 0 Newbie Poster · Answer 1 · 2014-03-07T14:18:37+00:00

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

SkyCross 0 Newbie Poster · Answer 2 · 2014-03-07T15:20:49+00:00

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();

scudzilla 32 Junior Poster · Answer 3 · 2014-03-07T15:49:17+00:00

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).

SkyCross 0 Newbie Poster · Answer 4 · 2014-03-07T16:13:01+00:00

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)
       {

       }







    }
}

OleDbException was unhandled(syntax error in INSERT INTO statement)

Recommended Answers Collapse Answers

All 6 Replies

Recommended Answers