OleDb inserts an integer value into Access that is different from the Parameter value

Question

The query is an insert into select statement where the parameters are generated from a csv file. The query fails because it cannot find the relationship between two of the tables. The table definition is as follows:

Users
----------
AgentID <PK>(Long Integer)
....

Chages
----------
ChangeID <PK>(Auto Number)
AgentID <FK>(Long Integer)
....

The relationship is between Users.AgentID = Changes.AgentID and the value being inserted into Changes can be found in Users.

The query is as follows:

    string sqlInsertChange = "INSERT INTO Changes" +
                                "(AgentID, ChangeReasonID, RecDateID, EffDateID) " +
                             "SELECT " +
                                "@AID AS AgentID, C.ChangeReasonID, " +
                                "(SELECT D.DateID FROM DateCodes AS D WHERE D.DateValue = @RD) AS RecDateID, " +
                                "(SELECT D.DateID FROM DateCodes AS D WHERE D.DateValue = @ED) AS EffDateID " +
                             "FROM " +
                                "ChangeReasons AS C " +
                             "WHERE " +
                                "C.ChangeReason = @CR;";



    UpdateChageCmd = new OleDbCommand(sqlInsertChange, conn);

    UpdateChageCmd.Parameters.Add("@AID", OleDbType.Integer);
    UpdateChageCmd.Parameters.Add("@RD", OleDbType.Date);
    UpdateChageCmd.Parameters.Add("@ED", OleDbType.Date);
    UpdateChageCmd.Parameters.Add("@CR", OleDbType.VarWChar);


    UpdateChageCmd.Parameters["@AID"].Value = chg.AgentID;
    UpdateChageCmd.Parameters["@RD"].Value = chg.recDate;
    UpdateChageCmd.Parameters["@ED"].Value = chg.effDate;
    UpdateChageCmd.Parameters["@CR"].Value = chg.reason;

    UpdateChageCmd.ExecuteNonQuery();

chg.AgentID is a long datatype.

I can put the query in Access, replacing the parameters with values, and it inserts without any problems.

Any ideas as to why this is not working?

EDIT: I hard-coded an AgentID value that I know is in the DB into the parameter but it still gave the error.

Answer 1

I was able to recreate your issue. It is very strange.

It seems that OleDb is getting confused by ... SELECT @AID AS AgentID .... Regardless of the value I gave to that Parameter (e.g., 1) it always got inserted into the database as 41641. When I tried to use .AddWithValue I got a "Data type mismatch" exception when I tried to execute the query.

This seems to work with OleDb, but it's a bit ugly:

string sqlInsertChange = 
        "INSERT INTO Changes" +
            "(AgentID, ChangeReasonID, RecDateID, EffDateID) " +
        "SELECT " +
            chg.AgentID + " AS AgentID, C.ChangeReasonID, " +
            "(SELECT D.DateID FROM DateCodes AS D WHERE D.DateValue = @RD) AS RecDateID, " +
            "(SELECT D.DateID FROM DateCodes AS D WHERE D.DateValue = @ED) AS EffDateID " +
        "FROM " +
            "ChangeReasons AS C " +
        "WHERE " +
            "C.ChangeReason = @CR;";

using (var UpdateChageCmd = new OleDbCommand(sqlInsertChange, conn))
{
    //UpdateChageCmd.Parameters.Add("@AID", OleDbType.Integer);
    UpdateChageCmd.Parameters.Add("@RD", OleDbType.Date);
    UpdateChageCmd.Parameters.Add("@ED", OleDbType.Date);
    UpdateChageCmd.Parameters.Add("@CR", OleDbType.VarWChar);

    //UpdateChageCmd.Parameters["@AID"].Value = chg.AgentID;
    UpdateChageCmd.Parameters["@RD"].Value = chg.recDate;
    UpdateChageCmd.Parameters["@ED"].Value = chg.effDate;
    UpdateChageCmd.Parameters["@CR"].Value = chg.reason;

    UpdateChageCmd.ExecuteNonQuery();
}

Or, if you're willing to switch from OleDb to Odbc then this works, too:

string sqlInsertChange = 
        "INSERT INTO Changes" +
            "(AgentID, ChangeReasonID, RecDateID, EffDateID) " +
        "SELECT " +
            "? AS AgentID, C.ChangeReasonID, " +
            "(SELECT D.DateID FROM DateCodes AS D WHERE D.DateValue = ?) AS RecDateID, " +
            "(SELECT D.DateID FROM DateCodes AS D WHERE D.DateValue = ?) AS EffDateID " +
        "FROM " +
            "ChangeReasons AS C " +
        "WHERE " +
            "C.ChangeReason = ?;";

using (var UpdateChageCmd = new OdbcCommand(sqlInsertChange, conn))
{
    UpdateChageCmd.Parameters.Add("?", OdbcType.Int);
    UpdateChageCmd.Parameters.Add("?", OdbcType.DateTime);
    UpdateChageCmd.Parameters.Add("?", OdbcType.DateTime);
    UpdateChageCmd.Parameters.Add("?", OdbcType.NVarChar);

    UpdateChageCmd.Parameters[0].Value = chg.AgentID;
    UpdateChageCmd.Parameters[1].Value = chg.recDate;
    UpdateChageCmd.Parameters[2].Value = chg.effDate;
    UpdateChageCmd.Parameters[3].Value = chg.reason;

    UpdateChageCmd.ExecuteNonQuery();
}