Ошибка синтаксиса в инструкции insert into access

Проблема даже совсем непонятная. Вроде все правильно но выдает ошибку: Ошибка синтаксиса в инструкции 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#)

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's user avatar

Shin

6642 gold badges12 silver badges30 bronze badges

asked Jan 7, 2011 at 16:13

Howard's user avatar

4

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 Skeet's user avatar

Jon SkeetJon Skeet

1.4m858 gold badges9079 silver badges9157 bronze badges

13

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

Community's user avatar

answered Jan 7, 2011 at 17:28

Tim M.'s user avatar

Tim M.Tim M.

53.5k13 gold badges121 silver badges162 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

user3183270's user avatar

1

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 Vincent's user avatar

Brennan VincentBrennan Vincent

10.6k9 gold badges32 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

user1289578's user avatar

1

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

CLARK's user avatar

CLARKCLARK

887 bronze badges

I am facing this error when I try to insert a data row in to table in ms access file.
dataTable is table I got using select * from TableName,

I got it, displayed it, made changes, now I want to replace previous one with new one. So I am going to delete all previous rows and add each row one by one from new table. But I am not able to insert any row.

I am getting this error

«Syntax error in INSERT INTO statement.»

String query = "INSERT INTO [" + TableName + "] (TaskID, HTMLTopic, [Group], nKey,"
    + " nText, nImage, nSelImage, nFontName, nFontInfo, Keywords) VALUES (@TaskID,"
    + " @HTMLTopic, @Group, @nKey, @nText, @nImage, @nSelImage, @nFontName, "
    + " @nFontInfo, @Keywords)";
OleDbCommand command = new OleDbCommand(query, mdbConnection);
command.Parameters.AddWithValue("@TaskID", dataTable.Rows[0]["TaskID"]);
command.Parameters.AddWithValue("@HTMLTopic", dataTable.Rows[0]["HTMLTopic"]);
command.Parameters.AddWithValue("@Group", dataTable.Rows[0]["Group"]);
command.Parameters.AddWithValue("@nKey", dataTable.Rows[0]["nKey"]);
command.Parameters.AddWithValue("@nText", dataTable.Rows[0]["nText"]);
command.Parameters.AddWithValue("@nImage", dataTable.Rows[0]["nImage"]);
command.Parameters.AddWithValue("@nSelImage", dataTable.Rows[0]["nSelImage"]);
command.Parameters.AddWithValue("@nFontName", dataTable.Rows[0]["nFontName"]);
command.Parameters.AddWithValue("@nFontInfo", dataTable.Rows[0]["nFontInfo"]);
command.Parameters.AddWithValue("@Keywords", dataTable.Rows[0]["Keywords"]);
mdbConnection.Open();
command.ExecuteNonQuery();
mdbConnection.Close();

Edit:
Changed it just for debugging to

        String query = "INSERT INTO [" + TableName + "] (TaskID, HTMLTopic, nRelative, [Group], nKey,"
           + " nText, nImage, nSelImage, nFontName, nFontInfo, Keywords) VALUES ('" + dataTable.Rows[0]["TaskID"]
           + "', '" + dataTable.Rows[0]["HTMLTopic"] + "', '" + dataTable.Rows[0]["nRelative"] + "', '" + dataTable.Rows[0]["Group"]
           + "', " + dataTable.Rows[0]["nKey"] + ", '" + dataTable.Rows[0]["nText"] + "', '" + dataTable.Rows[0]["nImage"]
           + "', '" + dataTable.Rows[0]["nSelImage"] + "', '" + dataTable.Rows[0]["nFontName"] + "', '" + dataTable.Rows[0]["nFontInfo"]
           + "', '" + dataTable.Rows[0]["Keywords"] + "')";

        OleDbCommand command = new OleDbCommand(query, mdbConnection);
        Debug.Print(command.CommandText);
        mdbConnection.Open();
        command.ExecuteNonQuery();
        mdbConnection.Close();

I added some single quotes so database can understand them as string.

Не помогло, та же ошибка.

Кликните здесь для просмотра всего текста

Ошибка сервера в приложении ‘/’.

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

Описание: Необработанное исключение при выполнении текущего веб-запроса. Изучите трассировку стека для получения дополнительных сведений о данной ошибке и о вызвавшем ее фрагменте кода.

Сведения об исключении: System.Data.OleDb.OleDbException: Ошибка синтаксиса в инструкции INSERT INTO.

Ошибка источника:

Строка 36: OleDbCommand command = new OleDbCommand(QInsert, constr);
Строка 37: constr.Open();
Строка 38: command.ExecuteNonQuery();
Строка 39: constr.Close();
Строка 40: }

Исходный файл: e:decanatWebApplication12WebApplication12reg.a spx.cs Строка: 38

Трассировка стека:

[OleDbException (0x80040e14): Ошибка синтаксиса в инструкции INSERT INTO.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(OleDbHResult hr) +1008156
System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS dbParams, Object& executeResult) +255
System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult) +188
System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior, Object& executeResult) +58
System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior behavior, String method) +161
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +113
WebApplication12.reg.Button1_Click(Object sender, EventArgs e) in e:decanatWebApplication12WebApplication12reg.a spx.cs:38
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
System.Web.UI.WebControls.Button.RaisePostBackEven t(String eventArgument) +110
System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

Информация о версии: Платформа Microsoft .NET Framework, версия:2.0.50727.8669; ASP.NET, версия:2.0.50727.8662

Syntax 


             Exception Details:
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

            Source Error:

                     

Line 58:             cmd.CommandText = addStudent;
Line 59:             cmd.Connection = connection;
Line 60:             cmd.ExecuteNonQuery();
Line 61: 
Line 62:             //code to close connection

I am getting the above error when trying to add a fourth item to the insert statement for my access database to hold IDs

here is my code:

using

System;


using

System.Collections.Generic;

using

System.Web;


using

System.Web.UI;


using

System.Web.UI.WebControls;


using

System.Data.OleDb;

namespace

StudentWeb

{

publicpartialclassRegister:
System.Web.UI.
Page


    {

protectedvoidPage_Load(objectsender,
EventArgse)

        {

        }

protectedvoidbtnRegister_Click(objectsender,
EventArgse)

        {

stringtableID = System.Guid.NewGuid().ToString();
//variable for ID in database

//drop in connection string from default page

stringcs =
«Provider =Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id=admin; Password=;»;

            cs =

String.Format(cs, Server.MapPath(«students1.mdb»));

OleDbConnectionconnection;
//create new instant of oledb connection


            connection =

newOleDbConnection();
//instantiate the connection


            connection.ConnectionString = cs;

            connection.Open();

//form variables

stringstudentName = txtName.Text;

stringstudentAddress = txtAddress.Text;

stringdateofBirth = txtDOB.Text;

stringstatus = txtStatus.Text;

stringstudentID2 = (tableID.ToString().Substring(0,
8));
//id of 8 characters

//doing SQL statement to insert new values into the database studentsInfo is the name of my table in the Access document

stringsql =
«INSERT INTO[studentsInfo](StudentName, StudentAddress, StudentDateofBirth, Marital_Status,TableID) VALUES
(‘{0}’,'{1}’,'{2}’,'{3}’,'{4}’)»
;

//(‘{0}’,'{1}’,'{2}’,'{3}’,'{4}’)»;

stringaddStudent =
string.Format(sql,

            studentName,

            studentAddress,

            dateofBirth,

            status,

            studentID2);

OleDbCommandcmd;

//code to place info in database


            cmd =

newOleDbCommand();

            cmd.CommandText = addStudent;

            cmd.Connection = connection;

            cmd.ExecuteNonQuery();

//code to close connection


            connection.Close();

//redirect page back to home


            Response.Redirect(

«Home.aspx»);

        }

    }

}

using

System;


using

System.Collections.Generic;


using

System.Web;


using

System.Web.UI;


using

System.Web.UI.WebControls;


using

System.Data.OleDb;
//bring in OLEDB Connection



namespace

StudentWeb

{

publicpartialclass_Default:
System.Web.UI.
Page


    {

protectedStringoutput;
//code for output


protectedvoidPage_Load(objectsender,
EventArgse)

        {

            output =

«Jane»;

//connection to database using miscrosof jet this jet is for ms access


stringcs =
«Provider =Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id=admin; Password=;»;

                cs =

String.Format(cs, Server.MapPath(«students1.mdb»));

OleDbConnectionconnection;
//create new instant of oledb connection


                connection =

newOleDbConnection();
//instantiate the connection


                connection.ConnectionString = cs;

                connection.Open();

//CODE FOR THE STRING FORMAT PART    


stringstudentsInfo;
// string format variable


                studentsInfo =

«<tr>»;

                studentsInfo +=

«<td> &nbsp;</td>»;

                studentsInfo +=

«<td> Student ID&nbsp;&nbsp;{0}</td>»;

                studentsInfo +=

«</tr>»;

                studentsInfo +=

«<tr>»;

                studentsInfo +=

«<td> &nbsp;</td>»;

                studentsInfo +=

«<td> Student Name&nbsp;&nbsp;{1}</td>»;

                studentsInfo +=

«</tr>»;

                studentsInfo +=

«<tr>»;

                studentsInfo +=

«<td> &nbsp;</td>»;

                studentsInfo +=

«<td> Student Address&nbsp;&nbsp;{2}</td>»;

                studentsInfo +=

«</tr>»;

                studentsInfo +=

«<tr>»;

                studentsInfo +=

«<td> &nbsp;</td>»;

                studentsInfo +=

«<td> Student DOB &nbsp;&nbsp;{3}</td>»;

                studentsInfo +=

«</tr>»;

                studentsInfo +=

«<tr>»;

                studentsInfo +=

«<td> &nbsp;</td>»;

                studentsInfo +=

«<td> Marital Status &nbsp;&nbsp;{4}</td>»;

                studentsInfo +=

«</tr>»;

OleDbDataReaderrdr;

//close connection


//sql statement to select fields in the database


                rdr =

newOleDbCommand(«SELECT
studentID, StudentName, StudentAddress, StudentDateofBirth, Marital_Status from StudentsInfo»
, connection).ExecuteReader();

while(rdr.Read())

                {

                output +=

string.Format(studentsInfo,

                    rdr [

«studentID»].ToString(),

                    rdr[

«StudentName»].ToString(),

                    rdr[

«StudentAddress»].ToString(),

                    rdr[

«StudentDateofBirth»].ToString(),

                    rdr[

«Marital_Status»].ToString()

                );

                }

//close while loop


            rdr.Close();

            connection.Close();

        }

    }

}

Any help would be appreciated

Понравилась статья? Поделить с друзьями:

А вот и еще наши интересные статьи:

  • Инструкция по эксплуатации chevrolet cobalt 2013
  • Resveratrol что это за препарат инструкция по применению
  • Денас пкм инструкция по применению цена отзывы
  • Car camcorder full hd 1080p инструкция на русском
  • Газовый котел уют м инструкция по эксплуатации

  • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии