none
Syntax error in INSERT INTO statement.

    Question

  • 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

    Thursday, October 24, 2013 5:01 AM

All replies