CODEWITHSUNDEEP
c#ms-access
wribit
wribit

Reputation: 605

C# - Complex DELETE statement against an Access Database not affecting any rows

This seems really basic to me but it doesn't seem to want to work.

I'm trying to delete rows from an Access Database table based on records returned from a subquery. I'm not sure if it's a syntax issue or what might be wrong - I tried multiple versions. Whichever version I tried, the outcome was a syntax error or no rows were affected.

Here's my latest code that doesn't return an error:

        public bool DeletePlayerGamesRecords(int PlayerID, int NightID)
    {
        PinnacleConnection pcon = new PinnacleConnection();
        OleDbConnection conn = pcon.createConnection();
        OleDbCommand cmd = new OleDbCommand();
        string sql = "DELETE * FROM tblPlayerGames WHERE fk_player_id = @pID AND fk_game_id IN (SELECT tblGames.[ID] FROM tblGames WHERE fk_night_id = @nightID)";

        try
        {
            conn.Open();
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue("@pID", PlayerID);
            cmd.Parameters.AddWithValue("@nightID", NightID);
            cmd.Connection = conn;
            int affected = cmd.ExecuteNonQuery(); //affected rows always 0!
            conn.Close();

            return true;
        }
        catch (Exception ex)
        {
            this.error = ex.Message;
            return false;
        }
    }

while debugging I determined that six rows should have been deleted, but none actually were.

What am I doing wrong?

Upvotes: 1

Views: 98

Answers (1)

HansUp
HansUp

Reputation: 97131

With OleDb and Access, the parameter names are ignored. You must supply the parameter values in the order in which the db engine expects them. In your query, Access expects the value for @nightID before the value for @pID.

So swap those parameters lines like this ...

cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@nightID", NightID);
cmd.Parameters.AddWithValue("@pID", PlayerID);
cmd.Connection = conn;

Upvotes: 2

Related Questions