Проблема даже совсем непонятная. Вроде все правильно но выдает ошибку: Ошибка синтаксиса в инструкции 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
6642 gold badges12 silver badges30 bronze badges
asked Jan 7, 2011 at 16:13
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 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!)
answered Jan 7, 2011 at 17:28
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
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 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
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
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> </td>»;
studentsInfo +=
«<td> Student ID {0}</td>»;
studentsInfo +=
«</tr>»;
studentsInfo +=
«<tr>»;
studentsInfo +=
«<td> </td>»;
studentsInfo +=
«<td> Student Name {1}</td>»;
studentsInfo +=
«</tr>»;
studentsInfo +=
«<tr>»;
studentsInfo +=
«<td> </td>»;
studentsInfo +=
«<td> Student Address {2}</td>»;
studentsInfo +=
«</tr>»;
studentsInfo +=
«<tr>»;
studentsInfo +=
«<td> </td>»;
studentsInfo +=
«<td> Student DOB {3}</td>»;
studentsInfo +=
«</tr>»;
studentsInfo +=
«<tr>»;
studentsInfo +=
«<td> </td>»;
studentsInfo +=
«<td> Marital Status {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