OleDBException in Update statement

Question

1` public void Update(Person oldperson, Person newPerson) { try {

            command.CommandText ="Update TPersons SET [Password1]=?,[Name1]=?,[Expertise]=?,[Email id]=?,[Mobile]=?,[Phone no]=?,[Address]=? WHERE [ID]=?";
            command.Parameters.Add(new OleDbParameter("Para1", newPerson.Password11));
            command.Parameters.Add(new OleDbParameter("Para2", newPerson.Name11));
            command.Parameters.Add(new OleDbParameter("Para3", newPerson.Expertise1));
            command.Parameters.Add(new OleDbParameter("Para4", newPerson.Email1));
            command.Parameters.Add(new OleDbParameter("Para5", newPerson.Mobile1));
            command.Parameters.Add(new OleDbParameter("Para6", newPerson.Phone1));
            command.Parameters.Add(new OleDbParameter("Para7", newPerson.Address1));

            command.CommandType = CommandType.Text;
            connection.Open();

            command.ExecuteNonQuery();
        }

        catch (Exception)
        {

            throw;
        }

        finally
        {
            if (connection != null)
            {
                connection.Close();
            }
        }
    }

I am using this code to update in my access database c# project using visual studio.This is showing me an OleDBException was unhandled.Syntax Error in UPDATE statement.Please help me rsolve this .Thanku !

I am still getting an exception saying syntax error in UPDATE statement. ! — user3815860
– user3815860, Commented Jul 8, 2014 at 10:23
So edit your question with the complete message of the exception. Also see my answer below — Heslacher
– Heslacher, Commented Jul 8, 2014 at 10:24
I have tried all the three possibilities.(two of which are commented ).And none of them are working.I am willing to provide entire code too if it helps me solve this ! — user3815860
– user3815860, Commented Jul 8, 2014 at 10:24
the strange [ is a problem, but also the parameters name. If I remember well jet accept just positional parameters coded as a '?' — Felice Pollano
– Felice Pollano, Commented Jul 8, 2014 at 10:29

Heslacher · Accepted Answer · 2014-07-08 11:12:15Z

1

You have many problems/errors:

first this : ,[Address[) VALUES

then you have tried to use a parameterized update statement, which by default is a good thing, but you did it wrong (for the sql statement ) and also didn`t add any parameters to yout command object ( at least what is shown in your code ).

Check out the docs for a proper parameterized query.

As you are using an access database the default placeholder for a parameter is a ? . Other than qith sqlcommand parameters these needs to be assigned in the exact same order.

So here comes some untested code to give you a start:

command.CommandText = "Update TPersons SET [Password1]=?,[Name1]=?,[Expertise]=?,[Email id]=?,[Mobile]=?,[Phone no]=?,[Address]=? WHERE [ID]=?" ;
command.Parameters.Add(new OleDbParameter("Para1",newPerson.Password11));
command.Parameters.Add(new OleDbParameter("Para2",newPerson.Name11));

... and so on

Read this for examples of using parameters in OldeDB (access) or SQL Server:

Configuring Parameters and Parameter Data Types

edited Jul 8, 2014 at 11:12

answered Jul 8, 2014 at 10:18

Heslacher

2,18722 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

user3815860 Over a year ago

This is my update statement for AN ms access database.Are all of the three ways equally incorrect? Sorry i am just a beginner at this !

user3815860 Over a year ago

I tried this ,but am still geting an error saying syntax error in UPDATE statement !

Heslacher Over a year ago

So please edit your question to show your current code. At the best also without commented lines.

user3815860 Over a year ago

I have entered the edited code and am still getting that Oledbexception was unhandled. No value given for one or more parameters.

Heslacher Over a year ago

You need to provide a parameter for the WHERE [ID]=? like i have written in the comment to your question.

|

Collectives™ on Stack Overflow

OleDBException in Update statement

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related