System data oledb oledbexception syntax error in insert into statement

Remove From My Forums

Question

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> 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 AppsStoreService.Service1.RegistrationTest(String username, String password) in C:UsersKaung HtetDocumentsVisual Studio 2010ProjectsAppStoreAppsStoreServiceService1.asmx.cs:line 116 --- End of inner exception stack trace ---

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:

[WebMethod]
        public bool Registration(string firstname, string lastname, string username, string password, string country, string dob, string email)
        {
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Temp\AppsStore.mdb");
            conn.Open();

            OleDbCommand cmd = new OleDbCommand("INSERT INTO Registration_Details (FirstName, LastName, UserName, Password, Country, DOB, Email) VALUES (@firstname, @lastname, @username, @password, @country, @dob, @email)",
            conn);
            cmd.Parameters.AddWithValue("@firstname",firstname);
            cmd.Parameters.AddWithValue("@lastname", lastname);
            cmd.Parameters.AddWithValue("@username", username);
            cmd.Parameters.AddWithValue("@password", password);
            cmd.Parameters.AddWithValue("@country", country);
            cmd.Parameters.AddWithValue("@dob", dob);
            cmd.Parameters.AddWithValue("@email", email);

            bool result;
            if (cmd.ExecuteNonQuery() == 1)
                result = true;
            else
                result = false;

            conn.Close();

            return result;
        }

Answers

In addition, note that Password is a keyword. So, you must enclose it in square brackets:
```
OleDbCommand cmd = new OleDbCommand("INSERT INTO Registration_Details (FirstName, LastName, UserName, [Password], Country, DOB, Email) 
                                               VALUES (?, ?, ?, ?, ?, ?, ?)", conn);
```
Marco Minerva [MCPD]
Blog: http://blogs.ugidotnet.org/marcom
Twitter: @marcominerva
- Edited by
  
  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:
```
OleDbCommand cmd = new OleDbCommand(
      "INSERT INTO Registration_Details (FirstName, LastName, UserName, Password, Country, DOB, Email) 
       VALUES (?, ?, ?, ?, ?, ?, ?)", conn);
```
Marco Minerva [MCPD]
Blog: http://blogs.ugidotnet.org/marcom
Twitter: @marcominerva
- Edited by
  Marco MinervaMVP
  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

Источник

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

Источник

Serg19882016

1 / 1 / 0

Регистрация: 28.09.2016

Сообщений: 43

06.09.2020, 11:53. Показов 1482. Ответов 5

Метки нет (Все метки)

Добрый день коллеги! Подскажите пожалуйста в чём ошибка, код останавливается с текстом «System.Data.OleDb.OleDbException: «Syntax error in INSERT INTO statement.»

VB.NET

 Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        Dim t1, t2, t3, t4, t5, t6, t7 As String
        Dim r As DialogResult
        Form3.ShowDialog()
        t1 = Grid1.CurrentRow.Cells("Код").Value
        t2 = Form3.TextBox2.Text
        t3 = Form3.TextBox3.Text
        t4 = Form3.TextBox4.Text
        t5 = Form3.TextBox5.Text
        t6 = Form3.TextBox6.Text
        t7 = Form3.TextBox7.Text
 
        r = Form3.DialogResult
        Form3.Close()
 
        If r <> DialogResult.OK Then
 
            Exit Sub
 
        End If
 
        Dim c As New OleDbCommand
        c.Connection = conn
        c.CommandText = "insert into Заказы(Код_Клиента, Дата, Препарат, Количество в упаковке, Цена, Количество, Сумма) values ('" & t1 & "', '" & t2 & "', '" & t3 & "', '" & t4 & "','" & t5 & "', '" & t6 & "', '" & t7 & "')"
        c.ExecuteNonQuery()
        RefreshGrid2()
    End Sub

Файлы с базой и проектом прилагаю. Спасибо.

Вложения

	База.rar (20.3 Кб, 1 просмотров)
	MedicineSale.rar (91.3 Кб, 1 просмотров)

__________________
Помощь в написании контрольных, курсовых и дипломных работ, диссертаций здесь

Модератор

3861 / 3184 / 479

Регистрация: 27.01.2014

Сообщений: 5,809

06.09.2020, 13:09

Serg19882016, у вас колонка в базе содержит имя с пробелами, что без кавычек неправильно читается.

Замените в запросе Количество в упаковке на ‘Количество в упаковке’
вокруг названия колонки поставьте одинарные кавычки.

1 / 1 / 0

Регистрация: 28.09.2016

Сообщений: 43

06.09.2020, 13:24

[ТС]

Теперь вот такая ошибка: System.Data.OleDb.OleDbException: «The INSERT INTO statement contains the following unknown field name: »Сумма». Make sure you have typed the name correctly, and try the operation again.»

Модератор

3861 / 3184 / 479

Регистрация: 27.01.2014

Сообщений: 5,809

06.09.2020, 19:04

Serg19882016, а колонка «Сумма» точно есть в базе?

Добавлено через 2 минуты
Попробуйте вместо одинарных кавычек поставить квадратные скобки [ ].

UBUNTU

204 / 190 / 37

Регистрация: 04.02.2015

Сообщений: 1,263

06.09.2020, 22:25

T-SQL

"insert into Заказы ([Код_Клиента], [Дата],[Препарат], [Количество в упаковке], [Цена], [Количество], [Сумма]) values (" & t1 & ", " & t2 & ", " & t3 & ", " & t4 & ", " & t5 & "', " & t6 & ", " & t7 & ")

1 / 1 / 0

Регистрация: 28.09.2016

Сообщений: 43

07.09.2020, 09:37

[ТС]

Спасибо всем за помощь!

Источник

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

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

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

Источник

VBForums
Visual Basic
Database Development
[RESOLVED] Syntax error in INSERT INTO statement

Jun 21st, 2013, 05:54 AM

#1
Thread Starter

Fanatic Member
[RESOLVED] Syntax error in INSERT INTO statement
using vs 2012

Code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & Application.StartupPath & "confess.accdb") cn.Open() Dim sqlstr As String = "INSERT INTO [names]([name],address,phone,note,birthday,job)values('" & Trim(TextBox1.Text) & "','" & Trim(TextBox2.Text) & "','" & Trim(TextBox4.Text) & "','" & Trim(TextBox6.Text) & "','" & DateTimePicker1.Text & "','" & Trim(TextBox5.Text) & "')" Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(sqlstr, cn) cmd.ExecuteNonQuery() cmd = Nothing cn.Close() End Sub

I get this error Syntax error in INSERT INTO statement
Last edited by new1; Jun 21st, 2013 at 06:23 AM.
Jun 21st, 2013, 06:28 AM

#2

Re: Syntax error in INSERT INTO statement

It is hard to guess what is in:

«INSERT INTO [names]([name],address,phone,note,birthday,job)values(‘» & Trim(TextBox1.Text) & «‘,'» & Trim(TextBox2.Text) & «‘,'» & Trim(TextBox4.Text) & «‘,'» & Trim(TextBox6.Text) & «‘,'» & DateTimePicker1.Text & «‘,'» & Trim(TextBox5.Text) & «‘)»

Do a debug.print sqlstr and let us see what is being passed to SQL.
Jun 21st, 2013, 06:30 AM

#3

Thread Starter

Fanatic Member

Re: Syntax error in INSERT INTO statement
Jun 21st, 2013, 06:34 AM

#4

Re: Syntax error in INSERT INTO statement

Right after:

Dim sqlstr As String = «INSERT INTO [names]([name],address,phone,note,birthday,job)values(‘» & Trim(TextBox1.Text) & «‘,'» & Trim(TextBox2.Text) & «‘,'» & Trim(TextBox4.Text) & «‘,'» & Trim(TextBox6.Text) & «‘,'» & DateTimePicker1.Text & «‘,'» & Trim(TextBox5.Text) & «‘)»

key in

Debug.print sqlstr

run your program until the error occurs.

Open up the immeadiate window and cut and paste what is displayed there into here.
Jun 21st, 2013, 06:43 AM

#5

Thread Starter

Fanatic Member

Re: Syntax error in INSERT INTO statement

INSERT INTO [names]([name],address,phone,note,birthday,job)values(»,»,»,»,’21/6/2013′,»)
A first chance exception of type ‘System.Data.OleDb.OleDbException’ occurred in System.Data.dll
System.Transactions Critical: 0 : <TraceRecord xmlns=»http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord» Severity=»Critical»><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>Confess.vshost.exe</AppDomain><Exception><ExceptionType>System.Data.OleDb.OleDbException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Syntax error in INSERT INTO statement.</Message><StackTrace> at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object&amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object&amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at Confess.Data.Button1_Click(Object sender, EventArgs e) in E:ConfessConfessData.vb:line 14
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message&amp; m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message&amp; m)
at System.Windows.Forms.ButtonBase.WndProc(Message&amp; m)
at System.Windows.Forms.Button.WndProc(Message&amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp; m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp; msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoCompo nentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at Confess.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>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&amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object&amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at Confess.Data.Button1_Click(Object sender, EventArgs e) in E:ConfessConfessData.vb:line 14
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message&amp; m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message&amp; m)
at System.Windows.Forms.ButtonBase.WndProc(Message&amp; m)
at System.Windows.Forms.Button.WndProc(Message&amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp; m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp; m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp; msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoCompo nentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at Confess.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()</ExceptionString></Exception></TraceRecord>
Jun 21st, 2013, 06:43 AM

#6

Thread Starter

Fanatic Member

Re: Syntax error in INSERT INTO statement
Jun 21st, 2013, 06:56 AM

#7

Re: Syntax error in INSERT INTO statement

That’s a little overkill but t will do. This is what I was interested in:

INSERT INTO [names]([name],address,phone,note,birthday,job)values(»,»,»,»,’21/6/2013′,»)

That is syntactically correct on MS SQL 2005. In some DBMS address is a reserved word. Try putting it in brackets like below:

INSERT INTO [names]([name],[address],phone,note,birthday,job)values(»,»,»,»,’21/6/2013′,»)
Jun 21st, 2013, 07:12 AM

#8

Thread Starter

Fanatic Member

Re: Syntax error in INSERT INTO statement

I cant put it in brackets
Jun 21st, 2013, 07:13 AM

#9

Re: Syntax error in INSERT INTO statement

Originally Posted by new1

I cant put it in brackets

Why?
Jun 21st, 2013, 07:21 AM

#10

Thread Starter

Fanatic Member

Re: Syntax error in INSERT INTO statement

vs dosnt accept brackets for values
Jun 21st, 2013, 07:42 AM

#11

Re: Syntax error in INSERT INTO statement

Originally Posted by new1

vs dosnt accept brackets for values

Then what about this:

INSERT INTO [names]([name],address,phone,note,birthday,job)values(»,»,»,»,’21/6/2013′,»)
Jun 21st, 2013, 07:46 AM

#12
Thread Starter

Fanatic Member
Re: Syntax error in INSERT INTO statement
Code:

Dim sqlstr As String = "INSERT INTO [names]([name],address,phone,note,[birthday],job)values('" & Trim(TextBox1.Text) & "','" & Trim(TextBox2.Text) & "','" & Trim(TextBox4.Text) & "','" & Trim(TextBox6.Text) & "','" & DateTimePicker1.Text & "','" & Trim(TextBox5.Text) & "')"

same error
Jun 21st, 2013, 07:49 AM

#13

Re: Syntax error in INSERT INTO statement

We aren’t communicating well

I mean this:

Dim sqlstr As String = «INSERT INTO [names]([name],[address],phone,note,[birthday],job)values(‘» & Trim(TextBox1.Text) & «‘,'» & Trim(TextBox2.Text) & «‘,'» & Trim(TextBox4.Text) & «‘,'» & Trim(TextBox6.Text) & «‘,'» & DateTimePicker1.Text & «‘,'» & Trim(TextBox5.Text
Jun 21st, 2013, 07:52 AM

#14

Thread Starter

Fanatic Member

Re: Syntax error in INSERT INTO statement
Jun 21st, 2013, 08:07 AM

#15

Re: Syntax error in INSERT INTO statement

What database are you using? As Tyson said, that syntax [I]should [I] be fine for SQL Server so that implies you’re using something else.

The best argument against democracy is a five minute conversation with the average voter — Winston Churchill

Hadoop actually sounds more like the way they greet each other in Yorkshire — Inferrd
Jun 21st, 2013, 08:53 AM

#16

Thread Starter

Fanatic Member

Re: Syntax error in INSERT INTO statement

i’m using access database
Jun 21st, 2013, 08:57 AM

#17
Re: Syntax error in INSERT INTO statement
I almost never use access so I risk getting egg on my face here but I think access expects dates to be presented enclosed in hashes rather than single quotes. Does this work:-

Code:

Dim sqlstr As String = "INSERT INTO [names]([name],[address],phone,note,[birthday],job)values('" & Trim(TextBox1.Text) & "','" & Trim(TextBox2.Text) & "','" & Trim(TextBox4.Text) & "','" & Trim(TextBox6.Text) & "',#" & DateTimePicker1.Text & "#,'" & Trim(TextBox5.Text) & "')"

I also just spotted that Tyson accidentally dropped the end off his string when he posted it. If you just cut and pasted his code straight in (in which case, shame on you… you ought to be trying to understand the problem) then you would get the error.
The best argument against democracy is a five minute conversation with the average voter — Winston Churchill

Hadoop actually sounds more like the way they greet each other in Yorkshire — Inferrd
Jun 21st, 2013, 09:23 AM

#18
Thread Starter

Fanatic Member
Re: Syntax error in INSERT INTO statement
Originally Posted by FunkyDexter

I almost never use access so I risk getting egg on my face here but I think access expects dates to be presented enclosed in hashes rather than single quotes. Does this work:-

Code:

Dim sqlstr As String = "INSERT INTO [names]([name],[address],phone,note,[birthday],job)values('" & Trim(TextBox1.Text) & "','" & Trim(TextBox2.Text) & "','" & Trim(TextBox4.Text) & "','" & Trim(TextBox6.Text) & "',#" & DateTimePicker1.Text & "#,'" & Trim(TextBox5.Text) & "')"

I also just spotted that Tyson accidentally dropped the end off his string when he posted it. If you just cut and pasted his code straight in (in which case, shame on you… you ought to be trying to understand the problem) then you would get the error.

still the same error I tries # before and it didn’t work
Jun 21st, 2013, 12:07 PM

#19

Re: Syntax error in INSERT INTO statement

Access does require dates to be enclosed in #, so that is right. If you enclose it in single quotes then it will certainly fail. However, since that didn’t fix the problem, there are other issues. One thing I note is that you have a European date, since there there is no month 21 in the US date system (actually, I have no idea what the real demarcation of those date systems are). Access may not handle DD/MM/YYYY, so you might try it as MM/DD/YYYY. That seems unlikely to fix the problem, but I have some vague memory of seeing something about this one time. It’s only a VERY vague memory, though, and it’s probably wrong.

The next thing to do is use a parameterized query. After all, concatenating in user input leaves you open to SQL Injection attacks, in general (though for some programs that isn’t an issue because they are used by only one or two trusted people and nobody would ever bother destroying the database behind them). Parameterized queries in Access are kind of weird, since all the parameters are just ?. You still supply names for the parameters, so any example you find for parameterized queries based on SQL Server will still work, except that the parameters in the SQL string are replaced with ?.

My usual boring signature: Nothing
Jun 21st, 2013, 05:26 PM

#20

Thread Starter

Fanatic Member

Re: Syntax error in INSERT INTO statement
Jun 21st, 2013, 06:41 PM

#21

Re: Syntax error in INSERT INTO statement

0) use DateTimePicker1.Value instead of .Text…. but you may get errors about mixing your datatypes… which would then defeat the purpose.
1) after making the changes above to account for the date format… and ensuring that you’re using the # for dates… take the SQL it spits out, go into access, and run it directly in there… I think you can do that in the query builder tool… see what it says…
2) do as Shaggy suggested…. look into using parameters…
3) try it again with DateTimePicker1.Value when you use parameters

-tg
Jun 21st, 2013, 06:53 PM

#22

Thread Starter

Fanatic Member

Re: Syntax error in INSERT INTO statement

Originally Posted by techgnome

0) use DateTimePicker1.Value instead of .Text…. but you may get errors about mixing your datatypes… which would then defeat the purpose.
1) after making the changes above to account for the date format… and ensuring that you’re using the # for dates… take the SQL it spits out, go into access, and run it directly in there… I think you can do that in the query builder tool… see what it says…
2) do as Shaggy suggested…. look into using parameters…
3) try it again with DateTimePicker1.Value when you use parameters

-tg

I did used DateTimePicker1.Value it didn’t work
I also used the convert method still the same error
Jun 23rd, 2013, 04:07 AM

#23

Hyperactive Member

Re: Syntax error in INSERT INTO statement

Hello,

I would assign variables for every textbox, assign a variable for the tablename and then rebuild the insert into statement without all of the ampersands and inverted commas. It is better practise and gives a similar view to an insert statement if you were creating the same sql as a stored procedure.
This method also allows you to track the variables and the correct data types,ensuring they match the data types in the database. I would also store the date as a string and automatically include the hashes on either side.

Kind regards

Steve

VBForums
Visual Basic
Database Development
[RESOLVED] Syntax error in INSERT INTO statement

Posting Permissions

You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
[VIDEO] code is On
HTML code is Off

Forum Rules

Click Here to Expand Forum to Full Width

Источник

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

Источник

Question

Answers

Syntax

System data oledb oledbexception syntax error in insert into statement

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif" />

Answered by:

Question

Answers

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

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" src="https://www.vbforums.com/images/icons/completeclear.gif" alt="Resolved" /> [RESOLVED] Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

Re: Syntax error in INSERT INTO statement

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" src="https://www.vbforums.com/images/buttons/collapse_40b.png" alt /> Posting Permissions

Solution 1

Solution 2

[RESOLVED] Syntax error in INSERT INTO statement

Posting Permissions