none
DataAdapter doesn't update data RRS feed

  • Question

  • Hello, I have such problem.
    I have a datagridview for modifieng data and a dataadapter for updating this data to reflect on sql server. the problem is that updating data doesn't work, but it calls UpdateCommand command. I checked the DataTable rows and they are marked as Modified. However inserting a new row works fine. I don't understand why update doesn't work. here's the code.

    I generate the dataadapter in such way

    public static SqlDataAdapter CreateServicesAdapter(int invoiceId)
    {
    SqlDataAdapter adapter = new SqlDataAdapter();

    SqlConnection con = new SqlConnection(Settings.ConnectionString);

    SqlCommand command = new SqlCommand("usp_GetServices");
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Connection = con;
    command.Parameters.Add(new SqlParameter("@invoice_id", invoiceId));
    adapter.SelectCommand = command;

    command = new SqlCommand("usp_AddService");
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Connection = con;
    command.Parameters.Add(new SqlParameter("@invoice_id", invoiceId));
    command.Parameters.Add("@service", System.Data.SqlDbType.NVarChar, 50, "service").SourceVersion =
    System.Data.DataRowVersion.Original;
    SqlParameter param = command.Parameters.Add("@price", System.Data.SqlDbType.Decimal);
    param.SourceColumn = "price";
    param.SourceVersion = System.Data.DataRowVersion.Original;
    param = command.Parameters.Add("@quantity", System.Data.SqlDbType.Decimal);
    param.SourceColumn = "quantity";
    param.SourceVersion = System.Data.DataRowVersion.Original;
    adapter.InsertCommand = command;

    command = new SqlCommand("usp_DeleteService");
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Connection = con;
    param = command.Parameters.Add("@id", System.Data.SqlDbType.Int);
    param.SourceColumn = "id";
    param.SourceVersion = System.Data.DataRowVersion.Original;
    adapter.DeleteCommand = command;

    command = new SqlCommand("usp_UpdateService");
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Connection = con;
    param = command.Parameters.Add("@id", System.Data.SqlDbType.Int);
    param.SourceColumn = "id";
    param.SourceVersion = System.Data.DataRowVersion.Original;
    command.Parameters.Add("@service", System.Data.SqlDbType.NVarChar, 50, "service").SourceVersion =
    System.Data.DataRowVersion.Original;
    param = command.Parameters.Add("@price", System.Data.SqlDbType.Decimal);
    param.SourceColumn = "price";
    param.SourceVersion = System.Data.DataRowVersion.Original;
    param = command.Parameters.Add("@quantity", System.Data.SqlDbType.Decimal);
    param.SourceColumn = "quantity";
    param.SourceVersion = System.Data.DataRowVersion.Original;
    adapter.UpdateCommand = command;

    return adapter;
    }

    I call BeginEdit and EndEdit in datagridview's CellBeginEdit and CellEndEdit events. The parameters are passed correctly, I executed a call to the usp_UpdateService SP from Management Studio and it worked fine. it just doesn't work from dataadapter.

    Any ideas? Sad
    Saturday, April 5, 2008 9:06 PM

Answers

  • Could it be that all of your parameters are set to use the original version of the data?

    param.SourceVersion = System.Data.DataRowVersion.Original;

    For an update therefore, the sproc will be called but it will have no effect as you are using the original values of the datarow. (Remember that data rows are versioned and store the original and modified version of the row in order to support optimistic concurrency).

    Try changing the appropriate update params to:

    param.SourceVersion = System.Data.DataRowVersion.Current;


    Hope it helps.

    Imran

    Sunday, April 6, 2008 9:06 AM

All replies

  • SqlCommand is a reference type. "command" is a reference. In the example you provided, adapter.UpdateCommand, adapter.DeleteCommand, adapter.InsertCommand, and adapter.SelectCommand are all the same thing (which should be what you intended UpdateCommand to be by the end of it).

     

    Either declare four different SqlCommand variables, or just use the ones on the adapter directly. I don't know how you're filling data from this adapter.

    Saturday, April 5, 2008 9:55 PM
  • Could it be that all of your parameters are set to use the original version of the data?

    param.SourceVersion = System.Data.DataRowVersion.Original;

    For an update therefore, the sproc will be called but it will have no effect as you are using the original values of the datarow. (Remember that data rows are versioned and store the original and modified version of the row in order to support optimistic concurrency).

    Try changing the appropriate update params to:

    param.SourceVersion = System.Data.DataRowVersion.Current;


    Hope it helps.

    Imran

    Sunday, April 6, 2008 9:06 AM
  • Thank you very much. it worked Smile
    Sunday, April 6, 2008 7:11 PM
  • No problem you can mark the thread as answered by clicking on the button next to my post.
    Sunday, April 6, 2008 9:40 PM