System data oledb oledbexception ошибка синтаксиса в инструкции insert into

Your code here can change dynamically depending on the user input. And that is what causing the error.

Let me explain if any of your input fields contain an apostroph [ ‘ ] the sql breaks and has now an unclosed quote.

Not only that it also exposes your code to SQL-Injection Attacks.

so i suggest you use parameters for passing value as parameters are treated differenty and are safe as well as prevent SQL-Injection.

    public void RegisterUser(string passw,string uname ,string fname ,string lname, string email)
{
    string strSql = @"INSERT INTO User (passw,uname,fname,lname,email) values     (@passw,@uname,@fname,@lname,@email)";                                                           
    cn.Open();
    OleDbCommand cmd = new OleDbCommand(strSql,cn);
    cmd.Parameters.AddWithValue("@passw",passw);
    cmd.Parameters.AddWithValue("@uname",uname);
    cmd.Parameters.AddWithValue("@fname",fname);
    cmd.Parameters.AddWithValue("@lname",lname);
    cmd.Parameters.AddWithValue("@email",email);
    int yy= cmd.ExecuteNonQuery();
    cn.Close();
    cn.Dispose();
}

Open and Close Connection:

OleDbConnection conn;
private void ConnectToDatabase()
{
    // Creates a connection to the database using an absolute path.
    conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +Server.MapPath("App_Data\BookRatings.accdb"));
    // Opens the connection.
    conn.Open();
}
private void DisconnectDatabase()
{
    // The connection is closed.
    conn.Close();
}

Register User

 public void RegisterCustomer(string userName, string Address, string Tel, string Email, string Ques, string Ans, string Pass)
{
    // Connect to database.
    ConnectToDatabase();
    // Inserts the necessary values into the database.
    OleDbCommand cmd = conn.CreateCommand();
    cmd.CommandText = (@"INSERT INTO user ([userName], [Address],[telephone], [emailAddress], [Password], [securityQuestion], [securityAnswer]) VALUES ('" + userName + "', '" + Address + "', '" + Tel + "', '" + Email + "', '" + Pass + "', '" + Ques + "', '" + Ans + "')");
    cmd.ExecuteNonQuery();
    // The connection is closed.
    DisconnectDatabase();
}

Error Message

Server Error in ‘/’ Application.

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 100: cmd.ExecuteNonQuery(); Line 101: // The
connection is closed. Line 102: DisconnectDatabase(); Line 103:
} Line 104: [WebMethod]

Source File: *bookClubService.aspx.cs Line: 102

Stack Trace:

[OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult
hr) +1102900
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) +247
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult) +189
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult) +58
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) +162
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +107
Service.RegisterCustomer(String userName, String Address, String Tel,
String Email, String Ques, String Ans, String Pass) in
*bookClubService.aspx.cs:102 Register.btnRegister_Click(Object sender, EventArgs e) in *bookClubRegister.aspx.cs:46
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9752490
System.Web.UI.WebControls.Button.RaisePostBackEvent(String
eventArgument) +196
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
+35 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
+1724

I don’t understand why the close line is giving an issue? Or why it’s crashing on insert…

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