What is the problem of My code about editting In c#?

Joe Wilson

The Error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error in UPDATE statement.

My code:

public void Edit()
{
Connection C2 = new Connection();
C2.SetData("update Student set Student_Number = " + this.Student_Number + ", Name = '" + this.Name + "', Last Name = " + this.LastName + "',Field = " + this.Field + "',Average=" + this.Average + " where Student_Number = " + this.Student_Number + "',Student_ID=" + this.Student_ID + "");
}

Please Help me solve my problem.

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
Doh, I don't know how we all managed to miss that one!

Anyway glad you've figured it out now :)
Joe Wilson12y ago
I solved my problem Thank you.

I just change Last Name to LastName in
' "LastName ='" + this.LastName + " ';
Vulpes12y ago
You can change it if you like but it won't make any difference as the order in which you update columns is unimportant.

What are the types of each of your columns in the Access database?
Joe Wilson12y ago
Your answer gives me same error.

The arrangements of editing is important?

because in data grid view The arrangements is (Code,Name,Last Name,Field,Average,Student Number,student ID)

So Do you think I must change the arguments arrangements in order to don't receive that error.
Vulpes12y ago
In MS Access, Name and Field are both reserved words though you can still use them as column names if you enclose them with square brackets.

You also have unbalanced single quotes around some of your string columns.

Finally you can join two conditions in a WHERE clause with the AND operator.

So I'd try this (I'm guessing that Student_ID is an integer rather than a string):

public void Edit()
{
Connection C2 = new Connection();
C2.SetData("update Student set Student_Number = " + this.Student_Number + ", [Name] = '" + this.Name + "', Last Name = '" + this.LastName + "', [Field] = '" + this.Field + "', Average = " + this.Average + " where Student_Number = " + this.Student_Number + " and Student_ID = " + this.Student_ID);
}
Joe Wilson12y ago
I used your code but the same error was appeared.
What should I do?
Khan Abrar Ahmed12y ago
Hi I think you have the issue in you string command which you are building , you can try below higlighted section and test.
C2.SetData("update Student set Student_Number = " + this.Student_Number + ", Name = '" + this.Name + "', Last Name = '" + this.LastName + "',Field = '" + this.Field + "',Average=" + this.Average + " where Student_Number = '" + this.Student_Number + "',Student_ID=" + this.Student_ID + "");
Joe Wilson12y ago
That wasn't correct compiler gives error to my code    which sets data when i use edit.
What is my code problem?
Please guide me.

My code:

public void SetData(string s2)
        {
            OleDbConnection CN = new OleDbConnection();
            CN.ConnectionString = "provider = microsoft.Ace.oledb.12.0; Data source = D:\\Database 10.accdb";
            CN.Open();

            OleDbCommand cmd = new OleDbCommand(s2, CN);
            cmd.ExecuteNonQuery();
            CN.Close();
        }
Joe Wilson12y ago
What do you mean?
Khan Abrar Ahmed12y ago
Hi try this
public void Edit()
{
Connection C2 = new Connection();
C2.SetData("update Student set Student_Number = " + this.Student_Number + ", Name = '" + this.Name + "', Last Name = '" + this.LastName + "',Field = '" + this.Field + "',Average=" + this.Average + " where Student_Number = '" + this.Student_Number + "',Student_ID=" + this.Student_ID + "");
}
Sai Sherlekar12y ago
joe, first check whether your query is wrong or not?

for that just use simple statement

like

update student set Name='abc' where Student_Number = 123

means use hard coded value and try simple statements without concatenation.
Joe Wilson12y ago
unfortunately your answer wasn't correct the same error was appeared.
Ranjit Powar12y ago
public void Edit()
{
Connection C2 = new Connection();
C2.SetData("update Student set Student_Number = " + this.Student_Number + ", Name = '" + this.Name + "', Last Name = '" + this.LastName + "',Field ='" + this.Field + "',Average=" + this.Average + " where Student_Number = " + this.Student_Number + ",Student_ID=" + this.Student_ID);
}