none
Syntax error in INSERT INTO statement. RRS feed

  • Question

  • I'm having a problem with my code below it's giving me the syntax error in insert statement please help

    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 Access
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            private void Form1_Load(object sender, EventArgs e)
            {
                OleDbConnection con = new OleDbConnection();
                con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Victor\Documents\Eddie.accdb;
    Jet OLEDB:Database Password=MyDbPassword";
                OleDbDataAdapter da = new OleDbDataAdapter();
                da.InsertCommand = new OleDbCommand("INSERT INTO ed VALUES (@First, @Second", con);
                da.InsertCommand.Parameters.Add("@First", OleDbType.Char).Value = textBox1.Text;
                da.InsertCommand.Parameters.Add("@Second", OleDbType.Char).Value = textBox2.Text;
                con.Open();
                da.InsertCommand.ExecuteNonQuery();
                con.Close();


            }
        }
    }

    • Moved by Carl Cai Thursday, August 22, 2013 1:42 AM
    Wednesday, August 21, 2013 2:18 AM

Answers

  • Hello Ediza,

    Welcome to MSDN Forums. 

    According to your description of the problem, the issue you are experiencing is there was an error in your insert into statement.

    If you write statement like this “INSERT INTO TABLENAME VALUES(VAL1,VAL2)”, this means your table has two columns need to be added. In fact, if your table has three columns marked as not null, and then this issue comes. So I suggest you to write INSERT INTO statement like this “INSERT INTO TABLENAME(COL1,COL2,COL3) VALUES(VAL1,VAL2,VAL3)”.

    In addition, I think OleDbCommand is more suitable for adding record.

    I have some code below for you to try:

    using (var cn = new OleDbConnection("your connection string"))
                {
                    OleDbCommand cm = new OleDbCommand("INSERT INTO ed(COL1,COL2) VALUES (@First, @Second", cn);
                    cm.Parameters.Add("@First", OleDbType.Char).Value = textBox1.Text;
                    cm.Parameters.Add("@Second", OleDbType.Char).Value = textBox2.Text;
                    cn.Open();
                    cm.ExecuteNonQuery();
                    cn.Close();
                }
     

    Best Regards,



    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, August 22, 2013 4:24 AM
    Moderator

All replies

  • Hi,

    According to your description , You have a Syntax error in CommandText. it's missing a bracket about bold .

    da.InsertCommand = new OleDbCommand("INSERT INTO ed VALUES (@First, @Second)", con);

    • Proposed as answer by Murat Res Wednesday, August 21, 2013 8:31 AM
    Wednesday, August 21, 2013 8:14 AM
  • This question should be moved to the Visual C# forum:

    http://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=csharpgeneral


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, August 21, 2013 12:34 PM
  • Hello,

    I have moved this thread to this forum for better response.

    Best regards,


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, August 22, 2013 1:42 AM
  • Hello Ediza,

    Welcome to MSDN Forums. 

    According to your description of the problem, the issue you are experiencing is there was an error in your insert into statement.

    If you write statement like this “INSERT INTO TABLENAME VALUES(VAL1,VAL2)”, this means your table has two columns need to be added. In fact, if your table has three columns marked as not null, and then this issue comes. So I suggest you to write INSERT INTO statement like this “INSERT INTO TABLENAME(COL1,COL2,COL3) VALUES(VAL1,VAL2,VAL3)”.

    In addition, I think OleDbCommand is more suitable for adding record.

    I have some code below for you to try:

    using (var cn = new OleDbConnection("your connection string"))
                {
                    OleDbCommand cm = new OleDbCommand("INSERT INTO ed(COL1,COL2) VALUES (@First, @Second", cn);
                    cm.Parameters.Add("@First", OleDbType.Char).Value = textBox1.Text;
                    cm.Parameters.Add("@Second", OleDbType.Char).Value = textBox2.Text;
                    cn.Open();
                    cm.ExecuteNonQuery();
                    cn.Close();
                }
     

    Best Regards,



    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, August 22, 2013 4:24 AM
    Moderator