C# windowsform - Getting Syntax Error at Insert into statement

Question

I'm trying to remake my system and my older system is 100% working, but when I changed something (I added a lot of columns in ms access database) I did the correct format in inserting data of each textboxes but it still says "error in insert into statement".

This is my code. Please take your time reading the query for that is only the error I got. I double checked the spelling or capitalization on each field from database as well as here.

    try
        {
            connection.Open();                                      //open connection
            OleDbCommand command = new OleDbCommand();              // command object , we can execute to validate our database
            command.Connection = connection;                     // make a connection for the command

            command.CommandText = " insert into StudentsRecord([StudentID],Name,Section,Semester,MathPrelim,MathMidterm,MathFinals,MathAverage,MathFinalGrade,EnglishPrelim,EnglishMidterm,EnglishFinals,EnglishAverage,EnglishFinalGrade,SciencePrelim,ScienceMidterm,ScienceFinals,ScienceAverage,ScienceFinalGrade,StatisticsPrelim,StatisticsMidterm,StatisticsFinals,StatisticsAverage,StatisticsFinalGrade,ReadandWritePrelim,ReadandWriteMidterm,ReadandWriteFinals,ReadandWriteAverage,ReadandWriteFinalGrade) values ('" + txtStudentID.Text + "' , '" + txtName.Text + "' , '" + txtSection.Text + "' , '" + cmbSemester.SelectedItem + "', '" + txtMathp.Text + "' , '" + txtMathm.Text + "' , '" + txtMathf.Text + "' , '" + txtMatha.Text + "' , '" + txtMathFG.Text + "' , '" + txtEnglishp.Text + "' , '" + txtEnglishm.Text + "', '" + txtEnglishf.Text + "','" + txtEnglisha.Text + "','" + txtEnglishFG.Text + "','" + txtMathFG.Text + "','" + txtSciencep.Text + "','" +txtSciencem.Text+ "','" + txtSciencef.Text + "','" + txtSciencea.Text + "','" + txtScienceFG.Text + "','" + txtStatisticsp.Text + "','" + txtStatisticsm.Text + "','" + txtStatisticsf.Text + "','" + txtStatisticsa.Text + "','" + txtStatisticsFG.Text + "','" + txtReadandWritep.Text + "','" + txtReadandWritem.Text + "','" + txtReadandWritef.Text + "','" + txtReadandWritea.Text + "','" + txtReadandWriteFG.Text + "')";
            /* this is a string or a query used to execute.  asterisk is used
            to give you all column data from your database ,declaration of query  */


            command.ExecuteNonQuery();          // this is used to inserting data , updating or deleting data   , this command will execute the above query  
            MessageBox.Show(" Saved! ");

        }
        catch (Exception a)
        {
            MessageBox.Show(" Error " + a.Message);
        }
        connection.Close();

What is the error message and what is the content of command.CommandText? — NineBerry, Oct 09 '16 at 02:49
the error message is " Error in INSERT INTO statement" already double checked fields, spelling and capitalization as well as their position but still it gives me that error. commandtext is the query , that syntax is the one i used in my previous version of grading system. and i works. no idea why it doesnt work now. — Sky, Oct 09 '16 at 03:01

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

I see in the first part you have 29 fields, but the inserted fields are 30... In addition, you should use parameterized queries to avoid sql injection.

edited May 23 '17 at 12:33

Community

1
1

answered Oct 09 '16 at 03:14

Antonio Veneroso Contreras

274
7
14

Hi, i already deleted the extra field on the inserted fields, however, the error still occurs. the heck, i dont know what to do anymore. this syntax seems to work on my previous system help guys – Sky Oct 09 '16 at 03:28
Btw dont worry it's ok to have any kind of queries since this system is only used for school purposes specifically this is a defense. I wont make this a formally system used by others. – Sky Oct 09 '16 at 03:29

score 0 · Answer 2 · answered Oct 09 '16 at 03:26

You need to debug codes and paste your CommandText to SSMS(SQL Sever Management Studio) to figure out errors.

Other suggestions:

command.CommandText = string.format("insert into StudentsRecord(..) VALUES(@...)); sql params to avoid sql injection
using(SqlConnection conn = ...)

score 0 · Answer 3 · edited Oct 09 '16 at 20:51

0

Already solved the problem just now by putting [ ] on each field. which will be something like this:

insert into StudentsRecord(
    [StudentID],[Name],[Section],[Semester],
   [MathPrelim],[MathMidterm],[MathFinals]
) values ('"++"'..) ... etc.

Thanks everyone who tried to help me, have a good day!

edited Oct 09 '16 at 20:51

halfer

19,824
17
99
186

answered Oct 09 '16 at 04:04

Sky

1
2

C# windowsform - Getting Syntax Error at Insert into statement

3 Answers3