Updating Access Database using C# and stored procedure does not work

I have this MS Access database table:

ID Long Integer 4
FirstName ShortText 255

ID is the primary key, indexed

I also have a stored procedure UpdateTest that contains:

UPDATE Test 
SET Test.FirstName = [@FirstName]
WHERE (((Test.ID) = [@ID]));

C# code

public void updateTest(int ID,string FirstName)
{
    OleDbCommand TestCmd = new OleDbCommand("UpdateTest", this.objConn);
    TestCmd.CommandType = CommandType.StoredProcedure;

    OleDbParameter ObjParam;
    ObjParam = TestCmd.Parameters.Add("@ID", OleDbType.Numeric);
    ObjParam.Direction = ParameterDirection.Input;
    ObjParam.Value = ID;
    ObjParam = TestCmd.Parameters.Add("@FirstName", OleDbType.BSTR);
    ObjParam.Direction = ParameterDirection.Input;
    ObjParam.Value = FirstName;

    try
    {
        this.objConn.Open();
        var result = TestCmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        Exception newEx = new Exception(ex.Message);
    }
    finally
    {
        this.objConn.Close();
    }
}

Problem: no errors occur, but the database is not updated. The results is 0 after executing.

When executing the stored procedure in MS Access, it works OK.

What am I missing?

Thanks

Solution

Well, there not really stored procedures in Access. And if you define one in Access (say with a create stored procedure DLL command, then if you open up such a query in Access, you see it is a standard select or update query).

So, say we have this query in Access:

PARAMETERS [@ID] Long, [@FirstName] Text ( 255 );
UPDATE tblHotelsA 
SET tblHotelsA.FirstName = [@FirstName]
WHERE tblHotelsA.ID = [@ID];

Then in code, this would work just fine:

using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB))
{
    using (OleDbCommand cmdSQL = new OleDbCommand("UHotels", conn))
    {
        cmdSQL.CommandType = CommandType.StoredProcedure;
        cmdSQL.Parameters.Add("@ID", OleDbType.Integer).Value = 423;
        cmdSQL.Parameters.Add("@FirstName", OleDbType.VarWChar).Value = "Albert";
        conn.Open();
        cmdSQL.ExecuteNonQuery();
    }
}

So, the above works fine. I also suggest that you do not try to "persist" the connection, but re-create and let the using block destroy the objects, including the connection object.

This advice seems counter-intuitive, since in our industry, when using file-based database engines, the "very" long time advice for better performance was to create a valid open connection, and then in code keep that valid connection object open, and global to the application.

However, with the arrival of .NET, then a connection pool and management of that connection is now automatic and "managed" for you the developer.

This means that no need exists to "keep" some connection object open and scoped to the application since .net will in fact automatic re-cycle these connections for you. So, this "long time" advice to keep a persisted connection object open at all times no longer applies when using .NET.

Thus, you are not only free to dispose the connection object each time it is used, but you the developer is thus now freed from the task of having to manage the database connections in code - .NET will do this for you, and automatic re-cycle the connections for you.

And while I posted a saved query with strong parameter typing?

You can leave out the parameters settings.

HOWEVER! If you leave out the parameter settings?

Then be VERY careful, since the order of your parameters has to match the parameter settings, and if no parameter statement exists in the Access SQL, then the order in which the parameters are added must match the order they are "used" in the SQL.

So, with the "minor" change (removing the parameters), this code DOES NOT work anymore:

cmdSQL.CommandType = CommandType.StoredProcedure;
cmdSQL.Parameters.Add("@ID", OleDbType.Integer).Value = 423;
cmdSQL.Parameters.Add("@FirstName", OleDbType.VarWChar).Value = "Albert";

conn.Open();
cmdSQL.ExecuteNonQuery();

And WHY does it not produce an error? Because you now in effect matching the @ID on first name, and FirstName trying to become your ID value (hence no update occurs).

So, since in above, the FirstName is the FIRST used parameter in the SQL, then you have to change the order in code with @FirstName to come first.

using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB))
{
    using (OleDbCommand cmdSQL = new OleDbCommand("UHotels", conn))
    {
        cmdSQL.CommandType = CommandType.StoredProcedure;

        cmdSQL.Parameters.Add("@FirstName", OleDbType.VarWChar).Value = "Albert";
        cmdSQL.Parameters.Add("@ID", OleDbType.Integer).Value = 423;

        conn.Open();
        cmdSQL.ExecuteNonQuery();
    }
}

The above code will now thus work. So, often mixing up the order of the parameters will not produce any error, but in effect you trying to find an "id" based on FirstName, and then update FirstName to the @ID.

So, for better or worse, the order of the parameters used in the SQL has to match the order in which you add them.

As noted, you CAN get around this order issue by adding the SQL parameters statement as I did to the saved Access query. Thus, the order of the parameters is now defined in the parameters statement, and thus that is the order you need to use in your code, and not the placement of the parameters in the actual SQL.

So, while the order STILL matters, the order used in the parameters will define the correct order regardless of the placement of the parameters in the SQL.

However, with parameters removed such as:

UPDATE tblHotelsA 
SET tblHotelsA.FirstName = [@FirstName]
WHERE (((tblHotelsA.ID) = [@ID]));

Above does NOT have the defined parameters so you code has to match the order in which they are used within the SQL. As above shows, @FirstName is the first parameter used in the SQL, not @ID, and thus you MUST add parameters in that order (@FirstName has to now come first in your code).