OleDB update command not changing data

Question

I'm using Microsoft Access file as database. I have no problem with SELECT and INSERT queries but when I try to UPDATE, record in database does not change.

Below is the code I use to run update. There are no exceptions or errors in debug log.

    cnn = new OleDbConnection(connetionString);

    OleDbCommand command = new OleDbCommand("UPDATE [Wpisy] SET [wpis]=@wpis, [id_kat]=@id_kat, [tytul]=@tytul WHERE [ID]=@id_wpis" , cnn);
    command.Parameters.Add(new OleDbParameter("@wpis", tresc_wpisu.Text));
    command.Parameters.Add(new OleDbParameter("@id_kat", lista_kategorii.SelectedValue));
    command.Parameters.Add(new OleDbParameter("@tytul", tytul_wpisu.Text));
    command.Parameters.Add(new OleDbParameter("@id_wpis", Request["id"].ToString() ));
    command.Connection = cnn;

    try
    {
        if(cnn.State.ToString() != "Open")
        cnn.Open();
        command.ExecuteNonQuery();
        cnn.Close();
    }
    catch (OleDbException ex)
    {
        Response.Clear();
        Response.Write(ex);
        Response.End();
    }

Theobald

Have you verified that Request["id"].ToString() is giving you the proper ID? – Hallsy

Does Request["id"] contain value for parameter @id_wpis and does your update execute properly in database? – Hafer

@BrianDishaw @Hafer yes, Request["id"] contains proper value and there is no error. – Theobald

Does the value Request["id"] exist in Wipsy table? – Hafer

@Hafer yes it does. I used GET method to pass id=10 in URL. in Wpisy table I have record with that value in ID column – Theobald

mikesdotnetting.com/article/26/… – Hafer

Answer 1

I would go to Microsoft Access and enter the command there and see what happens. It should tell you how many rows was affected. If it says zero rows, then break your query into smaller pieces, such as:

select * where [ID]=value

And then you should be able to track down where the problem is.

Benares

Answer 2

I know this isn't an exact answer, but there are some quirks with working with MS Access.

Here is an example method for you with some proper exception handling for databases. For Object, create a class that represents a row fields in your table. I use Exception ex instead of the db library exception since I use DataReaders for selects.

private String connectionString = "someOleDbConnectionString";
public String UpdateObject(Object obj)
{
    OleDbConnection connection = GetMyOleDbConnection(); //returns new OleDbConnection with proper connection string
    String updateSql = "UPDATE [Wpisy] SET [wpis]=@wpis, [id_kat]=@id_kat, [tytul]=@tytul WHERE [ID]=@id_wpis";
    OleDbCommand command = new OleDbCommand(updateSql, connection);
    command.CommandType = System.Data.CommandType.Text; //this can be changed if you have stored procedures in your db.

    //you may have to define the OleDbType of the parameter being defined
    command.Parameters.Add(new OleDbParameter("@wpis", OleDbType.VarChar, obj.tresc_wpisu));
    command.Parameters.Add(new OleDbParameter("@id_kat", OleDbType.VarChar, obj.lista_kategorii));
    command.Parameters.Add(new OleDbParameter("@tytul", OleDbType.VarChar, obj.tytul_wpisu));
    command.Parameters.Add(new OleDbParameter("@id_wpis", OleDbType.Integer, obj.id.ToString())); 

    return Execute(connection, command);
}

private OleDbConnection GetMyOleDbConnection()
{
    return new OleDbConnection(connectionString);
}

private String Execute(OleDbConnection connection, OleDbCommand command)
{
    try
    {
        connection.Open();
        command.ExecuteNonQuery();
        //I also know with Access databases, 
        //sometimes you have to close the table if it is open in MS Access
        connection.Close();
        return "SUCCESS";
    }
    catch (Exception ex)
    {
        connection.Close(); //important or you will have left open connections
        Response.Clear();
        Response.Write(ex.Message);
        Response.End();
        return ex.Message;
    }
}

Ber

Answer 3

I could be wrong, but from what I remember, OleDB doesn't allow named parameters, but instead use "?" as a place-holder, and the parameters need to be added in the same sequence as they appear in the SQL statement. such as

String updateSql = "UPDATE [Wpisy] SET [wpis]=?, [id_kat]=?, [tytul]=? WHERE [ID]=?";

command.Parameters.Add(new OleDbParameter("parm_wpis", OleDbType.VarChar, obj.tresc_wpisu));
command.Parameters.Add(new OleDbParameter("parm_id_kat", OleDbType.VarChar, obj.lista_kategorii));
command.Parameters.Add(new OleDbParameter("parm_tytul", OleDbType.VarChar, obj.tytul_wpisu));
command.Parameters.Add(new OleDbParameter("parm_id_wpis", OleDbType.Integer, obj.id.ToString())); 

Naming the parameters is just for clarification to know which is which. One other issue might be that you named the parameters by the same name as the column being updated, and that may have been the issue almost like a constant...

set X = X instead of now set X = parmX... there is no ambiguity the you are setting to the PARAMETER value being applied. But overall, I do think it will work via using "?" as the parameter place-holder.

Cassel