|
seneka 7 / 7 / 2 Регистрация: 28.09.2012 Сообщений: 82 |
||||||||
|
1 |
||||||||
|
10.10.2012, 09:17. Показов 14685. Ответов 1 Метки нет (Все метки)
Здравствуйте!!!
Добавлено через 33 минуты
1 |
|
9 / 9 / 5 Регистрация: 08.10.2012 Сообщений: 48 |
|
|
10.10.2012, 17:13 |
2 |
|
У тебя в значениях было @Dat e , а в параметры добавил как @Dat a
0 |
Выдаёт такую ошибку при попытки записать в бд.
System.Data.OleDb.OleDbException: «Ошибка синтаксиса в инструкции INSERT INTO.»
OleDbConnection myConnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\комп\Documents\Visual Studio 2017\Access\myDB.accdb;Persist Security Info=False");
OleDbCommand cmd = new OleDbCommand();
cmd.Connection= myConnection;
myConnection.Open();
if (myConnection.State == ConnectionState.Open)
{
string sql_command = "";
stpWatch.Start();
cmd.CommandType = CommandType.Text;
sql_command += "INSERT INTO person ([name], [sex]) VALES('pap', 'fdd')";
cmd.CommandText = sql_command;
try
{
Console.WriteLine(cmd.ExecuteNonQuery());
stpWatch.Stop();
Console.WriteLine(stpWatch.ElapsedMilliseconds);
}
catch (Exception)
{
throw;
}
- c#
- ado.net
- ms-access
Rasul
1,0917 серебряных знаков14 бронзовых знаков
задан 5 дек 2017 в 17:49
1
-
VALES->VALUES«Потолковать о Ювенале, В конце письма поставить vale …»– user176262
5 дек 2017 в 17:54
1 ответ
Вы сделали ошибку при написании ключевого слова VALUES, корректная команда будет: INSERT INTO person ([name], [sex]) VALUES('pap', 'fdd')
ответ дан 5 дек 2017 в 17:55
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
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.4m868 gold badges9135 silver badges9198 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.7k14 gold badges120 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
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.8k9 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
- Remove From My Forums
-
Question
-
User-601389072 posted
I’m getting this error and not sure why it’s happening. I was able to Insert into the database once at ot worked fine, but once I added the Time information it did not work. I took the time information out thinking that was the problem and it still doesn’t
work. I’ve checked reserved words and didn’t find any. Any help would be appreciated.Here is the error message:
Syntax error in INSERT INTO statement.
Description:
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
Source Error:
Line 37: Dim cmd As New OleDbCommand("INSERT INTO registration (RegistrantName, StreetAddress, City, State, ZIPCode, UScitizen, RegMOvoter, MOcounty, PartyClassification, FindOut, WhoReferredYou, Student, StudentLocation, August5th, Training, TimeStamp)VALUES('" & txtName.Text & "','" & txtAddress.Text & "','" & txtCity.Text & "','" & DDstate.SelectedValue & "','" & txtZIP.Text & "','" & DDcitizen.SelectedValue & "','" & DDRegMoVoter.SelectedValue & "','" & DDMOcounty.SelectedValue & "','" & DDparty.SelectedValue & "','" & DDfindout.SelectedValue & "','" & txtWhoReferredYou.Text & "','" & DDstudent.SelectedValue & "','" & DDstudentlocation.SelectedValue & "','" & DDAug5th.SelectedValue & "','" & DDtraining.SelectedValue & "','" & TimeRegistered & "')", New OleDbConnection(strConn)) Line 38: cmd.Connection.Open() Line 39: cmd.ExecuteNonQuery() Line 40: cmd.Connection.Close() Line 41:Source File: D:\Websites\trumanroot\pollworkers\Default.aspx.vb Line:
39Here is my code:
Protected Sub Button1_Click(ByVal sender
As Object,
ByVal e As System.EventArgs)
Handles Button1.Click
Dim TimeRegistered
As DateTimeTimeRegistered = Now()
Dim strConn As
String = «Provider=Microsoft.Jet.OLEDB.4.0;Data Source= « & Server.MapPath(«App_Data/pollworkers.mdb»)Dim
cmd As New OleDbCommand(«INSERT INTO registration (RegistrantName, StreetAddress, City, State, ZIPCode,
UScitizen, RegMOvoter, MOcounty, PartyClassification, FindOut, WhoReferredYou, Student, StudentLocation, August5th, Training, TimeStamp)VALUES(‘» & txtName.Text &
«‘,'» & txtAddress.Text &
«‘,'» & txtCity.Text &
«‘,'» & DDstate.SelectedValue &
«‘,'» & txtZIP.Text &
«‘,'» & DDcitizen.SelectedValue &
«‘,'» & DDRegMoVoter.SelectedValue &
«‘,'» & DDMOcounty.SelectedValue &
«‘,'» & DDparty.SelectedValue &
«‘,'» & DDfindout.SelectedValue &
«‘,'» & txtWhoReferredYou.Text &
«‘,'» & DDstudent.SelectedValue &
«‘,'» & DDstudentlocation.SelectedValue &
«‘,'» & DDAug5th.SelectedValue &
«‘,'» & DDtraining.SelectedValue &
«‘,'» & TimeRegistered &
«‘)», New OleDbConnection(strConn))cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
Response.Redirect(«default2.aspx»)
End Sub
Answers
-
User-1772909511 posted
INSERT INTO registration (RegistrantName, StreetAddress, City, State, ZIPCode, UScitizen, RegMOvoter, MOcounty, PartyClassification, FindOut, WhoReferredYou, Student, StudentLocation, August5th,
Training, TimeStamp)VALUES(‘From the looks it appears that there is no space between «TimeStamp)» & «VALUES» it should be «TimeStamp) VALUES»
Also please use parameterised SQL query to avoid SQL Injection.
-
Marked as answer by
Thursday, October 7, 2021 12:00 AM
-
Marked as answer by
-
-
Marked as answer by
Anonymous
Thursday, October 7, 2021 12:00 AM
-
Marked as answer by
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
and have tried these as well but still no luck
cmd.Parameters.AddWithValue(@"CheckID", OleDbType.VarChar).Value = txtID.Text;
cmd.Parameters.AddWithValue(@"Vehicle", OleDbType.VarChar).Value = ddlReg.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"Driver", OleDbType.VarChar).Value = ddlDrive.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"Date1", OleDbType.Date).Value = cldDate0.SelectedDate;
cmd.Parameters.AddWithValue(@"FuelLevel", OleDbType.VarChar).Value = cblFuel.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"WindscreenWasher", OleDbType.VarChar).Value = cblWash.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"SteeringWheel", OleDbType.VarChar).Value = cblWheel.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"Brakes", OleDbType.VarChar).Value = cblBrake.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"Clutch", OleDbType.VarChar).Value = cblClutch.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"Horn", OleDbType.VarChar).Value = cblHorn.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"Heater", OleDbType.VarChar).Value = cblHeat.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"SeatBelts", OleDbType.VarChar).Value = cblSBelt.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"WarningLights", OleDbType.VarChar).Value = cblWLight.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"Mirrors", OleDbType.VarChar).Value = cblMirror.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"[Tires/Wheels]", OleDbType.VarChar).Value = cblTire.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"Exhaust", OleDbType.VarChar).Value = cblExhaust.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"[Lights/Reflectors]", OleDbType.VarChar).Value = cblWLight.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"ExteriorLeaks", OleDbType.VarChar).Value = cblExLeaks.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"Body", OleDbType.VarChar).Value = cblBody.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"OilLevel", OleDbType.VarChar).Value = cblOil.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"CoolantLevel", OleDbType.VarChar).Value = cblCool.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"Belts", OleDbType.VarChar).Value = cblBelt.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"EngineLeaks", OleDbType.VarChar).Value = cblEnLeak.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"[LooseBolts/Screws]", OleDbType.VarChar).Value = cblScrew.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"WarningTriangle", OleDbType.VarChar).Value = cblWTri.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"[FireExtinguisher/FirstAidKit]", OleDbType.VarChar).Value = cblFire.SelectedItem.Text;
cmd.Parameters.AddWithValue(@"Defects", OleDbType.VarChar).Value = txtDefect.Text;
cmd.Parameters.AddWithValue(@"AdditionalComments", OleDbType.VarChar).Value = txtAddCom.Text;
cmd.Parameters.AddWithValue(@"CFirstName", OleDbType.VarChar).Value = txtFirstName.Text;
cmd.Parameters.AddWithValue(@"CLastName", OleDbType.VarChar).Value = txtLastName.Text;
and
@"INSERT INTO [tblCheck] (ChecklistID,Vehicle,Driver,[Date],FuelLevel,WindscreenWasher,SteeringWheel,Brakes,Clutch,Horn,Heater,SeatBelts,WarningLights,Mirrors,Tires/Wheels,Exhaust,Lights/Reflectors,ExteriorLeaks,Body,OilLevel,CoolantLevel,Belts,EngineLeaks,LooseBolts/Screws,WarningTriangle,FireExtinguishers/FirstAidKit,Defects,AdditionalComments,CFirstName,CLastName) Values (@txtID, @ddlDrive, @ddlReg, @cldDate0, @clFuel, @cblWash, @cblWheel, @cblBrake, @cblClutch, @cblHorn, @cblHeat, @cblHeat, @cblSBelt, @cblWLight, @cblMirror, @cblTire, @cblExhaust, @cblLights, @cblBody, @cblOil, @cblCool, @cblBelt, @cblBrake, @cblExLeaks, @cblEnLeaks, @cblClutch, @cblBody, @cblScrew, @cblHorn, @cblHeat, @cblSBelt, @cblWLight, @cblMirror, @txtDefect, @txtAddCom, @txtFristName, @txtLastName )";
@"INSERT INTO tblCheck (ChecklistID,Vehicle,Driver,[Date],FuelLevel,WindscreenWasher,SteeringWheel,Brakes,Clutch,Horn,Heater,SeatBelts,WarningLights,Mirrors,Tires/Wheels,Exhaust,Lights/Reflectors,ExteriorLeaks,Body,OilLevel,CoolantLevel,Belts,EngineLeaks,LooseBolts/Screws,WarningTriangle,FireExtinguishers/FirstAidKit,Defects,AdditionalComments,CFirstName,CLastName) Values ('" + txtID + "', '" + ddlDrive + "', '" + ddlReg + "', '" + cldDate0 + "', '" + cblFuel + "', '" + cblWash + "', '" + cblWheel + "', '" + cblBrake + "', '" + cblClutch + "', '" + cblHorn + "', '" + cblHeat + "' , '" + cblSBelt + "', '" + cblWLight + "', '" + cblMirror + "', '" + cblTire + "', '" + cblExhaust + "', '" + cblLights + "', '" + cblBody + "', '" + cblOil + "', '" + cblCool + "', '" + cblBelt + "', '" + cblEnLeak + "', '" + cblScrew + "', '" + cblWTri + "', '" + cblFire + "', '" + txtDefect + "', '" + txtAddCom + "', '" + txtFirstName + "', '" + txtLastName + "')");
Содержание
- System data oledb oledbexception syntax error in insert into statement
- Answered by:
- Question
- Answers
- All replies
- System data oledb oledbexception syntax error in insert into statement
- Syntax error in INSERT INTO statement.
- System data oledb oledbexception syntax error in insert into statement
System data oledb oledbexception syntax error in insert into statement
This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.
Answered by:
Question
Can someone check my error?. I used web service method to connect to database and when I try to INSERT INTO two or more data, my method doesn’t work and shows such error. Here is my web service method:
Answers
In addition, note that Password is a keyword. So, you must enclose it in square brackets:
- Edited by Marco Minerva MVP Wednesday, February 22, 2012 9:14 AM
- Proposed as answer by Heslacher Wednesday, February 22, 2012 9:17 AM
- Marked as answer by Dummy yoyo Monday, March 5, 2012 4:49 AM
OleDb doesn’t support named parameters. Try with:
- Edited by Marco Minerva MVP Monday, February 20, 2012 7:20 PM
- Proposed as answer by Dummy yoyo Wednesday, February 22, 2012 9:38 AM
- Marked as answer by Dummy yoyo Monday, March 5, 2012 4:49 AM
OleDb doesn’t support named parameters. Try with:
- Edited by Marco Minerva MVP Monday, February 20, 2012 7:20 PM
- Proposed as answer by Dummy yoyo Wednesday, February 22, 2012 9:38 AM
- Marked as answer by Dummy yoyo Monday, March 5, 2012 4:49 AM
Welcome to the MSDN forum!
I agree with Marco. Please see:
The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text . In this case, the question mark (?) placeholder must be used. For example:
SELECT * FROM Customers WHERE CustomerID = ?
You may try the following code:
Have a nice day!
Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us
Источник
System data oledb oledbexception syntax error in insert into statement
I have tried that as well before, I am still getting the same error. been stuck on this for a while
do the same for other parameters also.
Have you checked the name of the fields in the access database. I mean there isnt any spaces in the field name ?? Or, else u might be using some reserved words as field name..
Change Date to Date1.
I have tried that aswel Ashim still no luck. I have all the different ways I have tried commented out still in the code just incase
Yes all the fields are spelt the same way and no there is no spaces between them just camel case
it just keeps pointing to this line of code
can you be specific about the error you are getting .
change Date to Date1, in which part? all of them and the database or?
This is the error
Syntax error in INSERT INTO statement.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
Source File: c:UsersIFMComacDocumentsVisual Studio 2013ProjectsIfmAppWebApplication4Checklist.aspx.cs Line: 126
When you are using a character such as / inside a column name, you have to enclose the column name within brackets that is: [Tires/Wheels]
It may work because I believe Access uses just the parameter position rather than its name but the column CheckID is populated using the @txtID parameter and you still use CheckID (that is the column name) rather than txtID when defining the parameter value. So if you are running into another issue it could be that.
If the position is used Vehicle is misplaced ie you have :
CheckID,Vehicle,Driver columnns
@txtID, @ddlDrive, @ddlReg parameters (it seems it should be @ddlReg first and then @ddlDrive ?)
and cmd . Parameters . AddWithValue ( @»Vehicle» , OleDbType . VarChar ). Value = ddlReg . SelectedItem . Text ; // Vehicle is the column name, not the parameter name
cmd . Parameters . AddWithValue ( @»Driver» , OleDbType . VarChar ). Value = ddlDrive . SelectedItem . Text ;
Источник
System data oledb oledbexception syntax error in insert into statement
I am getting the same error. I have tried it two different ways. If someone could tell me what I have done wrong and possibly tell me the prefered method between the two I would greatly appreciate it.
Dim myInsertCommand As New OleDbCommand(«INSERT INTO EmployeeData(InspectorID, Last, First , MI, Street, City, State, Zip, SSN, Phone, Cell, Email, HireDate, TermDate, Title, Pay, Status, EmpEndNotes, AttendanceNotes, EmpMeetings) VALUES(@InspectorID, @Last, @First, @MI, @Street, @City, @State, @Zip, @SSN, @Phone, @Cell, @Email, @HireDate, @Term Date, @Title, @Pay, @Status, @EmpEndNotes, @AttendanceNotes, @EmpMeetings)», conn)
Try
conn.Open()
myInsertCommand.Parameters.AddWithValue(«@InspectorID», txtInspectorID.Text)
myInsertCommand.Parameters.AddWithValue(«@Last», txtLast.Text)
myInsertCommand.Parameters.AddWithValue(«@First», txtFirst.Text)
myInsertCommand.Parameters.AddWithValue(«@MI», txtMI.Text)
myInsertCommand.Parameters.AddWithValue(«@Street», txtStreet.Text)
myInsertCommand.Parameters.AddWithValue(«@City», txtCity.Text)
myInsertCommand.Parameters.AddWithValue(«@State», cbState.selectedValue)
myInsertCommand.Parameters.AddWithValue(«@Zip», txtZip.Text)
myInsertCommand.Parameters.AddWithValue(«@SSN», txtSSN.Text)
myInsertCommand.Parameters.AddWithValue(«@Phone», txtPhone.Text)
myInsertCommand.Parameters.AddWithValue(«@Cell», txtCell.Text)
myInsertCommand.Parameters.AddWithValue(«@Email», txtEmail.Text)
myInsertCommand.Parameters.AddWithValue(«@HireDate», hireDateTimePicker.Value)
If termDateTimePicker.Checked Then
myInsertCommand.Parameters.AddWithValue(«@TermDate», termDateTimePicker.Value)
Else
myInsertCommand.Parameters.AddWithValue(«@TermDate», DBNull.Value)
End If
myInsertCommand.Parameters.AddWithValue(«@Title», cbTitle.SelectedValue)
myInsertCommand.Parameters.AddWithValue(«@Pay», Val(txtPay.Text))
myInsertCommand.Parameters.AddWithValue(«@Status», cbStatus.SelectedValue)
myInsertCommand.Parameters.AddWithValue(«@EmpEndNotes», txtEndNotes.Text)
myInsertCommand.Parameters.AddWithValue(«@AttendanceNotes», txtAttendance.Text)
myInsertCommand.Parameters.AddWithValue(«@EmpMeetings», txtMeetings.Text)
myInsertCommand.ExecuteNonQuery()
Catch ex As Exception
MsgBox(«Error: » & ex.Source & «: » & ex.Message, MsgBoxStyle.OkOnly, «Connection Error»)
End Try
conn.Close()
Dim cb As New OleDbCommandBuilder(da)
Dim dsNewRow As DataRow
Источник
|
|
[RESOLVED] Syntax error in INSERT INTO statement
Originally Posted by new1
OleDbConnection con = new OleDbConnection(@" provider=Microsoft.ace.Oledb.12.0; data source=sisc-erelim4_PrintingVTDBDBVirginiTEADB2.accdb; Persist Security Info=False"); private void button1_Click(object sender, EventArgs e) { try { OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "INSERT INTO Accountstbl (Username, Password)" + "VALUES ('" + textBox1.Text + "','" + textBox2.Text + "')"; cmd.Parameters.AddWithValue("@Username", textBox1.Text); cmd.Parameters.AddWithValue("@Password", textBox2.Text); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { textBox1.Text = ex.ToString(); }
And I always got this error,
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at VirginiTEAcorp.Form3.button1_Click(Object sender, EventArgs e) in C:Documents and Settings12-014sMy DocumentsapplicationsDatabaseWindowsFormsApplication1Form3.cs:line 34
thanks
Updated 11-Feb-13 23:25pm
Comments
Solution 1
Update your insert statement like this
cmd.CommandText = "INSERT INTO Accountstbl (Username, Password)" + " VALUES (@Username,@Password)";
this is because you are already passing the arguments to the query parameter
cmd.Parameters.AddWithValue("@Username", textBox1.Text); cmd.Parameters.AddWithValue("@Password", textBox2.Text);
Comments
Solution 2
You have added parameters into your command, but you have injected the values into the SQL. Change your command text into
INSERT INTO Accountstble (Username, Password) VALUES (?, ?)
Then, remove the @ symbols from the parameter names — they aren’t needed as they are used for SQL Server, not Access.
Comments
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)
| Top Experts | |
| Last 24hrs | This month |
CodeProject,
20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8
+1 (416) 849-8900