Update query for Access Database for certain Parameters

Question

Hi,

I am using MS Access as a database and using c#, .net for updating some records in it. But it is giving error saying

"No value given for one or more required parameters."

There are 5 colums in Table and I want to update only 2, for that I have written the query like

"update User_DTL set user_role_id = '" + _UserRole + "', auth_id ='" + _authId + "'"
                                    + " WHERE Id = '" + _Id + "' ";

where _UserRole, _authId, _Id are strings.

What may be the error. Do I need to give every parameter in update statement or there is some other way.

Thanks

Answer 1

Whenever you encounter such an error, stick a breakpoint in and examine your query to ensure it looks as you expect. For example, is there actually a _UserRole, _authId and _Id present in the query.

You could also add some defensive code to check them before you prepare the statement - this example checks to make sure the _UserRole isn't null or empty.

if (!string.IsNullOrEmpty(_UserRole)) { ...

Answer 2

Hard to say without seeing your code, but based on the error message I'm guessing one of the following:

1) One of the following fields does not exist in User_DTL: user_role_id, auth_id, Id
2) _UserRole, _authId, _Id contains a single-quote character.

The best way to troubleshoot this is to print the actual concatenated query string and then open a SQL Query in Access and run it. It should be pretty obvious what the problem is then.

BTW: You likely have some SQL Injection vulnerabilities with this code.