Query is executed with 0 rows affected, but when trying to run directly using the database it works

I have the following Microsoft Access database structure:

I am trying to create a C# function in the class book: Return(), that is used to return the book from the client that borrowed it. This is done by setting a return date on the borrow table (is being set to the time the function got called).

Here is the function:

 public bool Return()
        {
            if (IsFree())
                return false;
            const string SQL = "UPDATE Borrow SET Return_date = @Date WHERE Book_ID = @Id AND Return_date IS NULL";
            conn.Open();
            using (OleDbCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = SQL;
                cmd.Parameters.AddRange(new OleDbParameter[]
                    {
                        new OleDbParameter("@Id", Id),
                        new OleDbParameter("@Date", DateTime.Now)
                    });
                conn.Close();
            }
            return true;
        }

When I run it I get no error, but nothing changes in the database, and trying to get the numbers of rows affected returns 0.

Other functions that involve the database work fine so it is not the connection string.

When I try to run the query in Microsoft Access it works fine.

what have I done wrong?

Answer

Change the order of the following two lines from:

new OleDbParameter("@Id", Id),
new OleDbParameter("@Date", DateTime.Now)

To:

new OleDbParameter("@Date", DateTime.Now),
new OleDbParameter("@Id", Id)

OLE DB does not recognise named parameters, but merely populates the parameters in the order in which they appear in the SQL statement.

Advertisement

Answer