CODEWITHSUNDEEP
c#asp.netsqlms-accesssql-update
Cyberpks
Cyberpks

Reputation: 1421

Incorrect syntax in update query: Unable to find one

I am getting a weird error in one of my SQL query in my ASP.NET application. I am trying to update an MS Access Table row, and the following is my code:

try
{
strQry = @"Update UserMaster set Password=@Pwd, RoleID=@RoleID where UserID=@UserID";
cmd.Parameters.AddWithValue("@Pwd", strUserPwd);
cmd.Parameters.AddWithValue("@RoleID", intRoleID);
cmd.Parameters.AddWithValue("@UserID", intUserID);
cmd.Connection = con;
cmd.CommandText = strQry;
con.Open();
intReturn = cmd.ExecuteNonQuery();
con.Close();
}
catch(Exception ex)
{
    throw new Exception(ex.Message);
}

According to me, what I am able to see is that there are no issues with the update query, as I have been using this kind of method from quite long time but, this is really weird that I am getting a Syntax error in UPDATE statement exception.

Can any one help me out

Upvotes: 0

Views: 294

Answers (2)

asifsid88
asifsid88

Reputation: 4701

Actually password is the reserved word and you need to enclose that in a bracket []
So your query will become 

strQry = @"Update UserMaster set [Password]=@Pwd, RoleID=@RoleID where UserID=@UserID";

Rest all if correct :)

Refer list of reserve keywords in MS Access 2007 Database

TIP: It is always advicable to use non-reserved key words, even if you do so then always include [ ] to be on safer side. As in hurry you do not realize which reserve key word you mess up wit

Upvotes: 2

Iswanto San
Iswanto San

Reputation: 18569

I think you're using OleDb to connect to MsAccess.

Try to change your parameter declaration from @parameter to ? like this.

More : OleDbParameter Class

try
{
  strQry = @"Update UserMaster set Password=?, RoleID=? where UserID=?";
  var p1 = command.CreateParameter();
  p1.Value = strUserPwd;
  command.Parameters.Add(p1);

  var p2 = command.CreateParameter();
  p2.Value = intRoleID;
  command.Parameters.Add(p2);

  var p3 = command.CreateParameter();
  p3.Value = intUserID;
  command.Parameters.Add(p3);

  cmd.Connection = con;
  cmd.CommandText = strQry;
  con.Open();
  intReturn = cmd.ExecuteNonQuery();
  con.Close();
}
catch(Exception ex)
{
    throw new Exception(ex.Message);
}

Upvotes: -1

Related Questions