CODEWITHSUNDEEP
c#sqlms-accessoledb
mejaw
mejaw

Reputation: 41

C# OleDB Update statement not updating

I'm trying to update an Access database through my C# web application but I'm currently facing a problem in WHERE statement not updating the records and not returning any errors.

I'm trying update a text field and the condition in my WHERE statement is an integer.

        OleDbCommand cmd = new OleDbCommand("UPDATE Data SET Title = @Title WHERE ID = @ID");
        cmd.CommandType = CommandType.Text;
        cmd.Connection = conn;
        cmd.Parameters.AddWithValue("@Title", TextBox1.Text);
        cmd.Parameters.AddWithValue("@ID", param2);

I even tried doing it this way 

        OleDbCommand cmd = new OleDbCommand("UPDATE Data SET Title = ? WHERE ID = ?");
        cmd.CommandType = CommandType.Text;
        cmd.Connection = conn;
        cmd.Parameters.AddWithValue("?", TextBox1.Text);
        cmd.Parameters.AddWithValue("?", param2);

But it's still not updating!
What I found out trying to fix it is that when i replace the first parameter with a string between single quotes (see below), it actually updates the table. 

OleDbCommand cmd = new OleDbCommand("UPDATE Data SET Title = 'test' WHERE ID = @ID");

Does any of you guys have an idea why is this happening?

Edit: This is the full code 

    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        OleDbCommand cmd = new OleDbCommand("UPDATE Data SET Title = @Title WHERE ID = @ID");
        cmd.CommandType = CommandType.Text;
        cmd.Connection = conn;
        cmd.Parameters.AddWithValue("@Title", TextBox1.Text);
        cmd.Parameters.AddWithValue("@ID", param2);
        conn.Open();
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);

        }

Edit 2:
This is my code after trying to set the data types, it's still not working.
To clarify, in my Access database ID is "AutoNumber" and Title is "Long Text"

    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        OleDbCommand cmd = new OleDbCommand("UPDATE Data SET Title = ? WHERE ID = ?");
        cmd.CommandType = CommandType.Text;
        cmd.Connection = conn;
        cmd.Parameters.AddWithValue("Title", TextBox1.Text).OleDbType = OleDbType.VarChar;
        cmd.Parameters.AddWithValue("ID", param2).OleDbType = OleDbType.Integer;
        conn.Open();
        try
        {
            var recordsUpdated = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }

When I check the value of recordsUpdated, it returns "1" but the database isn't being updated.

Upvotes: 1

Views: 3733

Answers (1)

Igor
Igor

Reputation: 62213

You need to call ExecuteNonquery which executes the statement.

// your OleDbConnection should also be wrapped in a using statement to ensure it is closed
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open(); // open the connection
    using(OleDbCommand cmd = new OleDbCommand("UPDATE Data SET Title = ? WHERE ID = ?")) // wrap in using block because OleDbCommand implements IDisposable
    {
        cmd.CommandType = CommandType.Text;
        cmd.Connection = conn;
        cmd.Parameters.AddWithValue("Title", TextBox1.Text).OleDbType = OleDbType.VarChar; // Title, also set the parameter type
        cmd.Parameters.AddWithValue("ID", param2).OleDbType = OleDbType.Integer; // ID, I am guessing its an integer but you should replace it with the correct OleDbType
        var recordsUpdated = cmd.ExecuteNonQuery(); // execute the query

        // recordsUpdated contains the number of records that were affected
    }
}
  • The order op the Parameters that you add must match the order of the parameters in the sql statement.
  • Be sure you specify your OleDbType for each parameter so the command does not have to guess what it is. I guessed Title is a varchar and ID is integer, correct this if it is a wrong assumption.
  • You can replace "?" with parameter names to make the addition of parameters easier to read and keep the ? placeholders in the update statement. Microsoft does this in many of their examples in their documentation
  • ExecuteNonQuery returns the number of rows affected, capture the result and use it for debugging (see code update)

When I check the value of recordsUpdated, it returns "1" but the database isn't being updated.

If 1 is being returned then 1 record is being updated. Here are 2 trouble shooting tips.

  • You should make sure that your query is updating the same record you are checking. During debugging capture the value of param2 and manually use that in a select statement in Access to see the Title column of that record SELECT Title FROM Data WHERE ID = [id here]. Also get the value of TextBox1.Text in debugging and check this value against what is returned from the manually executed select query.
  • As this is Ms Access and the file resides on disk, make sure that you are manually checking against the same database that you are connecting to in your connection string. If you have 2 copies of the database this could easily lead to a wrong conclusion that an update is not being performed.

Upvotes: 2

Related Questions