Ошибка синтаксиса в инструкции insert into - Инструкции, руководства, мануалы

Проблема даже совсем непонятная. Вроде все правильно но выдает ошибку: Ошибка синтаксиса в инструкции INSERT INTO.

(База на MS Access)

OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password='';Data Source=myFirma.mdb");
OleDbCommand myData = new OleDbCommand("select * from Users", myConnection);
OleDbCommand myQuery = new OleDbCommand("insert into Users (name,surname,login,password,action)values('" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "', '" + textBox4.Text + "', '" + textBox5.Text + "')",myConnection);
myConnection.Open();
myQuery.ExecuteNonQuery();
myConnection.Close();

Попробовал другой вариант:

String myConn = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=myDataBase.mdb;";
String myQuery = "INSERT INTO Users ( name, surname, login, password, action) VALUES ('" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "', '" + textBox4.Text + "', '" + textBox5.Text + "')";
OleDbConnection cn = new OleDbConnection(myConn);
cn.Open();
OleDbCommand cmd = new OleDbCommand(myQuery, cn);
cmd.ExecuteNonQuery();
cn.Close();

Кроме этих пробовал еще варианты, обыскал весь Гугл, ниче не помогло. Все время одна и та же ошибка на одной и той же строчке:

.ExecuteNonQuery();

Ошибка: Ошибка синтаксиса в инструкции INSERT INTO.

Последняя надежна на вас.

Перемещено

1 октября 2010 г. 22:40
MSDN Forums consolidation (От:Visual C#)

Источник

s1 = "INSERT INTO call_requests ([id_users], [id_call_request_types], [id_call_request_statuses], [id_user_contact_types], [id_countries], [contact_value], [contact_name], [time_start], [time_end], [call_request_note], [date_add], [id_user_sessions]) VALUES (" & IIf(id_users > 0, id_users, "NULL") & ", " & id_call_request_types & ", " & id_call_request_statuses & ", " & id_user_contact_types & ", " & id_countries & ", '" & contact_value & "', '" & Replace(contact_name, "'", Chr(34)) & "', " & Nz(time_start, "Null") & ", " & Nz(time_end, "Null") & ", '" & Nz(call_request_note, "") & "', " & DateWinToJet(date_add) & "," & IIf(id_user_sessions > 0, id_user_sessions, "Null") & ")"

Вот что выдает debug.print s1:

INSERT INTO [call_requests] ([id_users], [id_call_request_types], [id_call_request_statuses], [id_user_contact_types], [id_countries], [contact_value], [contact_name], [time_start], [time_end], [call_request_note], [date_add], [id_user_sessions]) VALUES (104696, 8, 1, 1, 170, '92458******', '', 0, 0, '', ,Null)

Источник

I keep getting an error when I attempt to insert values into a Access database.

The error is syntactic, which leads to the following exception:

OleDbException was unhandled Syntax error in INSERT INTO statement.

private OleDbConnection myCon;

public Form1()
{
    InitializeComponent();
    myCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\File.mdb");
}

private void insertuser_Click(object sender, EventArgs e)
{
    OleDbCommand cmd = new OleDbCommand();
    myCon.Open();
    cmd.Connection = myCon;
    cmd.CommandType = CommandType.Text;

    cmd.CommandText = "INSERT INTO User ([UserID], [Forename], [Surname], " +
                                        "[DateOfBirth], [TargetWeight], [TargetCalories], [Height]) " +
                      "VALUES ('" + userid.Text.ToString() + "' , '" +
                                    fname.Text.ToString() + "' , '" +
                                    sname.Text.ToString() + "' , '" +
                                    dob.Text.ToString() + "' , '" +
                                    tarweight.Text.ToString() + "' , '" +
                                    tarcal.Text.ToString() + "' , '" +
                                    height.Text.ToString() + "')";

    cmd.ExecuteNonQuery();
    myCon.Close();
}

Shin

6642 gold badges13 silver badges30 bronze badges

asked Jan 7, 2011 at 16:13

Well, you haven’t specified what the error is — but your first problem is that you’re inserting the data directly into the SQL statement. Don’t do that. You’re inviting SQL injection attacks.

Use a parameterized SQL statement instead. Once you’ve done that, if you still have problems, edit this question with the new code and say what the error is. The new code is likely to be clearer already, as there won’t be a huge concatenation involved, easily hiding something like a mismatched bracket.

EDIT: As mentioned in comments, Jet/ACE is vulnerable to fewer types of SQL injection attack, as it doesn’t permit DML. For this INSERT statement there may actually be no vulnerability — but for a SELECT with a WHERE clause written in a similar way, user input could circumvent some of the protections of the WHERE clause. I would strongly advise you to use parameterized queries as a matter of course:

They mean you don’t have to escape user data
They keep the data separate from the code
You’ll have less to worry about if you ever move from Jet/ACE (whether moving this particular code, or just you personally starting to work on different databases)
For other data types such as dates, you don’t need to do any work to get the data into a form appropriate for the database

(You also don’t need all the calls to ToString. Not only would I expect that a property called Text is already a string, but the fact that you’re using string concatenation means that string conversions will happen automatically anyway.)

answered Jan 7, 2011 at 16:15

Jon SkeetJon Skeet

1.4m873 gold badges9165 silver badges9215 bronze badges

I posted this as a comment to the duplicate question at: Syntax error in INSERT INTO statement in c# OleDb Exception cant spot the error

Put brackets [] around the table name
«User». It’s a reserved word in SQL
Server.

«User» is also a reserved word in Access (judging by the provider in your connection string).

But I completely agree with Jon—if you fix your current implementation, you are just opening up a big security hole (against your User table, no less!)

answered Jan 7, 2011 at 17:28

Tim M.Tim M.

53.8k14 gold badges121 silver badges163 bronze badges

This problem may occur if your database table contains column names that use Microsoft Jet 4.0 reserved words.

Change the column names in your database table so that you do not use Jet 4.0 reserved words.

answered Apr 23, 2014 at 11:43

If TargetWeight, Height, and TargetCalories are floating-point or integer values, they don’t need to be surrounded by quotes in the SQL statement.

Also, not directly related to your question, but you should really consider using a parameterized query. Your code is very vulnerable to SQL injection.

answered Jan 7, 2011 at 19:13

Brennan VincentBrennan Vincent

10.8k9 gold badges33 silver badges54 bronze badges

public decimal codes(string subs)
    {
        decimal a = 0;


        con_4code();
            query = "select SUBJINTN.[SCODE] from SUBJINTN where SUBJINTN.[ABBR] = '" +                         subs.ToString() + "'";
            cmd1 = new OleDbCommand(query, concode);
            OleDbDataReader dr = cmd1.ExecuteReader();

here is error in dr it says syntax error ehile in DBMS its working Well

            if (dr.Read())
            {
                a = dr.GetDecimal(0);
                MessageBox.Show(a.ToString());
            }
            return a;



    }

answered Mar 26, 2012 at 8:35

After this

cmd.CommandText="INSERT INTO User ([UserID], [Forename], [Surname], [DateOfBirth], [TargetWeight], [TargetCalories], [Height]) Values ('" + userid.Text.ToString() + "' , '" + fname.Text.ToString() + "' , '" + sname.Text.ToString() + "' , '" + dob.Text.ToString() + "' , '" + tarweight.Text.ToString() + "' , '" + tarcal.Text.ToString() + "' , '" + height.Text.ToString() + "')";

check what this contains, maybe [DateOfBirth] has illegal format

answered May 12, 2013 at 7:34

CLARKCLARK

887 bronze badges

Источник

C#: «Ошибка синтаксиса в инструкции INSERT INTO» ☑ 0

avyub

11.05.15

✎

21:23

Не сохраняются данные из формы. Выдает ошибку.

Листинг:

        private void btnAdd_Click(object sender, EventArgs e)

        {

            string connString = @»Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DIPLOM\DATABASE\calldata.accdb»;

            conn = new OleDbConnection(connString);

            conn.Open();

            Call cl;

            dontedit = true; //для часов

            cl.date = DateTime.Now.Year + «/» + DateTime.Now.Month + «/» + DateTime.Now.Day;

            cl.time = maskedTxtTime.Text;

            cl.callOperator = myName;

            cl.department = cmbDepartment.Text;

            cl.model = cmbModel.Text;

            cl.source = cmbSource.Text;

            cl.nameSurname = txtClientName.Text;

            cl.contact = txtClientTel.Text;

            //MessageBox.Show(cl.date + » » + cl.time + cl.callOperator + cl.department + cl.model + cl.source + cl.nameSurname + cl.contact);

            OleDbCommand c = new OleDbCommand(«insert into Call (Date, Time, ID_Operator, ID_Department, ID_Model, ID_InfoSourse, ClientName, CotactTel) values (cl.date,cl.time,cl.callOperator,cl.department,cl.model,cl.source,cl.nameSurname’,cl.contact)», conn);

            c.ExecuteNonQuery();

        }

Masquerade

11.05.15

✎

23:56

А где

dontedit = false; ???

Masquerade

11.05.15

✎

23:59

А вообще — по 10-ти бальной шкале трешовости — это тянет на весь червонец.

Почему в одном месте ты пишешь так:

cl.date = DateTime.Now.Year + «/» + DateTime.Now.Month + «/» + DateTime.Now.Day;

А в другом так?

OleDbCommand c = new OleDbCommand(«insert into Call (Date, Time, ID_Operator, ID_Department, ID_Model, ID_InfoSourse, ClientName, CotactTel) values (cl.date,cl.time,cl.callOperator,cl.department,cl.model,cl.source,cl.nameSurname’,cl.contact)», conn);

Masquerade

12.05.15

✎

00:00

Почему не так?

cl.date = «DateTime.Now.Year / DateTime.Now.Month / DateTime.Now.Day»;

Masquerade

12.05.15

✎

00:03

И я не думаю, что ты ты, переписав запрос правильно — получишь результат.

Поля в БД у тебя содержат в названии ID, а ты пытаешься запихнуть туда значения с формы. Сомнительно как-то что-то ты вводишь туда ID.

Отдельно порадовало:

Data Source=C:\DIPLOM\DATABASE\

Диплом значит…

Masquerade

12.05.15

✎

00:04

(0)

Удовлетвори любопытсво — для какого учебного заведения это.. Это печется?

avyub

12.05.15

✎

00:25

MessageBox.Show(cl.date + » » + cl.time + cl.callOperator + cl.department + cl.model + cl.source + cl.nameSurname + cl.contact);

все правильно выдает.

Насчет ID я тоже думал. Там числовой формат, а cl.callOperator и прочие в текстовом (индексы преобразованные в текст)

Masquerade

12.05.15

✎

00:29

(6)

Во первых — ты не ответил на (5).

Во вторых — ты не видишь разницы между кодом в (6) и

???

avyub

12.05.15

✎

00:39

ну во первых я заочник, C# изучали пару дней. Сейчас самостоятельно осваиваю.

под разнице ты ведь не ‘ имеешь ввиду. Сейчас преобразоввываю некоторые переменные из string в int. Потом снова попробую выполнить запрос

Masquerade

12.05.15

✎

00:39

Короче, умного учить — только портить.

1. cl.nameSurname’ — что это за одинарная кавычка?

2. Ты не можешь писать так —

var a = 1;

var b = 2;

OleDbCommand c = new OleDbCommand(«insert into my_table(number1,number2) values (a,b)»,conn);

Это же бред. Кто подставит значения переменных?

Надо либо использовать параметры, либо как-то так:

OleDbCommand c = new OleDbCommand(«insert into my_table(number1,number2) values («+a+»,»+b+»)»,conn);

Masquerade

12.05.15

✎