My Access DB will not update. No errors in code

Question

I am using this code to update the database:

var connectionString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;
string commandString = string.Empty;
using (OleDbConnection con = new OleDbConnection(connectionString))
{
    con.Open();
    OleDbCommand command = new OleDbCommand(commandString, con);
    commandString = "UPDATE accesscontrol SET enabled=@isEnabled WHERE proxyFor=@userNameInGrid AND currentlyLoggedOnUser=@userName";

    command.Parameters.AddWithValue("userName", Environment.UserName);
    command.Parameters.AddWithValue("isEnabled", tempPerson.isBool.ToString());
    command.Parameters.AddWithValue("userNameInGrid", tempPerson.Name);
    command.ExecuteNonQuery();
    command.Parameters.Clear();
}

Roman Marusyk · Accepted Answer · 2020-07-06 21:12:32Z

2

Because you use empty commandString

First, you set:

string commandString = string.Empty;

And then you passed empty commandString to OleDbCommand, after that you set the value to variable commandString but not to OleDbCommand

OleDbCommand command = new OleDbCommand(commandString, con);
commandString = "UPDATE accesscontrol SET enabled=@isEnabled WHERE proxyFor=@userNameInGrid AND currentlyLoggedOnUser=@userName";

Change you code to:

commandString = "UPDATE accesscontrol SET enabled=@isEnabled WHERE proxyFor=@userNameInGrid AND currentlyLoggedOnUser=@userName";
OleDbCommand command = new OleDbCommand(commandString, con);

UPDATE

Also, you should add @ before the parameter name

command.Parameters.AddWithValue("@isEnabled", tempPerson.isBool.ToString());
command.Parameters.AddWithValue("@userNameInGrid", tempPerson.Name);
command.Parameters.AddWithValue("@userName", Environment.UserName);

Note: as @LarsTech mentioned, OleDB does not use named parameters correctly. The parameters have to be added in the same index order they appear in the SQL statement

edited Jul 6, 2020 at 21:12

answered Apr 28, 2016 at 15:58

Roman Marusyk

24.8k27 gold badges83 silver badges127 bronze badges

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

3 Comments

Roman Marusyk Over a year ago

@softwareisfun Any news? Was it helpful?

LarsTech Over a year ago

OleDB does not use named parameters correctly. The parameters have to be added in the same index order they appear in the SQL statement, so @isEnabled should be first, etc.

Roman Marusyk Over a year ago

@LarsTech Thanks, I added it to the answer.

Collectives™ on Stack Overflow

My Access DB will not update. No errors in code

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related