none
PROBLEMS WITH UPDATE IN A WEB SERVICE RRS feed

  • Question

  •    Hi! Fist of all, thanks for reading... I´m making an application where you can  look up a customer and its productos and where you can modify them.

       The code of the web service is:

     

    public class Service : System.Web.Services.WebService

    {

    SqlDataAdapter da = new SqlDataAdapter();

    DataSet ds = new DataSet();

     

    public Service () {

    }

     

    [WebMethod(Description = "Obtener Productos")]

    public DataSet GetProduct()

    {

    String connectionString = "Integrated Security=SSPI;Initial Catalog=prueba;Data Source=ZA-002\\SQLEXPRESS";

    SqlConnection connection = new SqlConnection(connectionString);

    da = new SqlDataAdapter("SELECT IDProv, NombreProv, DireccionProv, TfnProv FROM Proveedores", connection);

    da.Fill(ds, "Proveedores");

    da.SelectCommand.CommandText = "SELECT IDProd, NombreProd, CategoriaProd, PrecioProd, IDProv FROM Productos";

    da.Fill(ds, "Productos");

    DataRelation relProvProd = new DataRelation("ProvProd", ds.Tables["Proveedores"].Columns["IDProv"], ds.Tables["Productos"].Columns["IDProv"]);

    ds.Relations.Add(relProvProd);

    return ds;

    }

     

    [WebMethod]

    public DataSet UpdateProduct(DataSet dsCambios)

    {

    if (dsCambios != null)

    {

    da.Update(dsCambios);

    return dsCambios;

    }

    else

    {

    return null;

    }

    }

    }

     

    And the Update code of the application is:

     

    private void SaveItem (object sender, EventArgs e)

    {

    if (dsProy.HasChanges())

    {

    DataSet dsdif = new DataSet();

    dsdif.Merge(dsProy.GetChanges());

    dsdif = miservicio.UpdateProduct(dsdif);

    dsProy.Merge(dsdif, true);

    }

    }

     

    The problem is that when I click Save once, the application save changes but only if the application is running, I mean, if I close and run again, nothing has changed. Moreover, if I make 2 changes, the application throw this exception:

     

    System.Web.Services.Protocols.SoapException: El servidor no puede procesar la solicitud. ---> System.NullReferenceException: Referencia a objeto no establecida como instancia de un objeto.
       en Service.ActualizarProdProv(DataSet dsCambios) en c:\Proyecto\Web_Services\App_Code\Service.cs:línea 54
       --- Fin del seguimiento de la pila de la excepción interna ---

     

    I hope I´ve made a good explication and you can help me. Thanks

     

     

    Wednesday, August 8, 2007 8:50 AM

Answers

  • There are a couple of problems with your code.  Firstly you are combining CommandBuilder with manual commands.  CommandBuilder is used to auto-generate the commands for you given the schema of the data set.  It is generally recommended that you don't use this class.  Build your commands manually instead.

     

    Code Snippet

    public Dataset GetData ( )
    {
       using(SqlConnection conn = new SqlConnection(...))
       {
          SqlDataAdapter da = new SqlDataAdapter();
          DataSet ds = new DataSet();

          //Command
          da.SelectCommand = new SqlCommand(..., conn);
         
          //Run
          da.Fill(ds);
          return ds;
       };
    }

    public void UpdateData ( DataSet ds )
    {
       using(SqlConnection conn = new SqlConnection(...))
       {
          SqlDataAdapter da = new SqlDataAdapter();
        
          //Command
          da.DeleteCommand = new SqlCommand(..., conn);
          da.InsertCommand = new SqlCommand(..., conn);
          da.UpdateCommand = new SqlCommand(..., conn);
         
          //Run
          da.Update(ds);=
       };
    }

     

     

    You can use the dataset designer to auto-generate this code for you and provide you with a strongly typed data set.  You can then use the classes directly or copy the necessary code and drop them into your own classes.

     

    The next problem is your SQL commands.  I have to recommend that you move away from raw SQL and go with stored procedures instead.  sprocs allow you to test the SQL code outside of your .NET code.  This will identify to you and SQL problems.

     

    The INSERT command looks okay.  Your UPDATE command is wrong though.  It'll never update any rows.  The problem is in your where clause.  You are telling the adapter to update each row only if it matches its current row values (you are comparing each row value to what the DB is storing).  Since UPDATE commands only run against changed rows this will never occur.  Each UPDATE will fail to update anything.  When you are trying to update something you should use the primary key or natural key of the table rather than every field value.  The PK should be something that never changes once set.  In your code I would wager the IDProd is the primary key and therefore must be unique and can not change.  Therefore in your UPDATE command use this value as the where clause.

     

    Code Snippet
    UPDATE
    SET categoriaProd = @categoriaProd, ...
    WHERE IDProd = @idProd

     

     

    The above code will update only a single row and it'll match only the single row in the dataset that should be updated.

     

    The DELETE command, while it should work, has a similiar problem.  Use the PK value only.

    Code Snippet
    DELETE FROM ... WHERE IDProd = @idProd

     

     

    Finally be aware that concurrency may be an issue.  In this case you have to decide what should happen if user A retrieves data from the database, makes changes and then attempts to save those changes while user B has (at the same time) gone in and made their own changes).  If this is an issue in your system (such as web sites) then you need to decide what to do.  In most cases each table has a TIMESTAMP column.  The timestamp column is used (in addition to the pK column) in the WHERE clause of the UPDATE and DELETE commands.  If either command returns a 0 back then the row was modified by somebody else and a concurrency error has occurred. 

     

    Michael Taylor - 8/13/07

    http://p3net.mvps.org

     

    Monday, August 13, 2007 12:57 PM

All replies

  •  

    The problem is probably with your da variable.  You are assuming that you will create an instance of the service and then call the get routine to initialize the adapter and later call the save method.  This is probably not happening.  More likely an instance is being created and the get method is called.  Later when it is time to save a new instance is created and the save method is called.  However the adapter is not initialized and the service fails. 

     

    I would recommend that you create the adapter as a local variable inside each function, initialize it and then use it.  If you don't then the adapter will hang around along with an open connection to the DB which is bad.  Create the adapter, use it and then clean up the connections on each method call.  It should eliminate your problem.  Note also that you need to define the insert, update and delete commands needed to update the data set.

     

    Code Snippet

    public DataSet GetProduct ( )
    {

       using(SqlConnection conn = new ...)

       {

          SqlDataAdapter da = new ...;

          da.SelectCommand = ...;

          ...

          conn.Open();

       };

    }

     

    public DataSet UpdateProduct ( ... )
    {

       using(SqlConnection conn = new ...)

       {

          SqlDataAdapter da = new ...;

          da.InsertCommand = ...;

          da.UpdateCommand = ...;

          da.DeleteCommand = ...;

          ...

          conn.Open();

          da.Update(...);

       };

    }

     

     

    Michael Taylor - 8/8/07

    http://p3net.mvps.org

     

    Wednesday, August 8, 2007 12:31 PM
  • Thank you very much for the reply. I´ve made the changes you propose, but I might do it wrong, because it doesn´t work. There´s no exception but changes are not saved. While application is running, changes are stored, but when I close it and run again, data has no changes.

     

    The web service has 2 methods: one call GetProvProd and gets all data from database and UpdateProvProd that (and here is were doesn´t work) update database with changes I´ve made in the application.

    The application has a database with 2 datatables related each other. I have a text box where I write the "Proveedor" to look for and textboxes to report the all data of "Proveedores" and a datagridview to "Productos" related with the "Proveedor" I´m looking for.

     

    Here is all the code... Sorry if it´s too large and if I my explication is bad.

     

    WEB SERVICE CODE:

    public class Service : System.Web.Services.WebService

    {

    DataSet ds = new DataSet();

    public Service () {

    //InitializeComponent();

    //http://localhost:2059/Web_Services/Service.asmx

    }

    [WebMethod(Description = "Obtener Productos")]

    public DataSet GetProvProd()

    {

    using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=prueba;Data Source=ZA-002\\SQLEXPRESS"))

    {

    //String connectionString = )

    SqlDataAdapter da = new SqlDataAdapter();

    da = new SqlDataAdapter("SELECT IDProv, NombreProv, DireccionProv, TfnProv FROM Proveedores", connection);

    da.Fill(ds, "Proveedores");

    da.SelectCommand.CommandText = "SELECT IDProd, NombreProd, CategoriaProd, PrecioProd, IDProv FROM Productos";

    da.Fill(ds, "Productos");

    DataRelation relProvProd = new DataRelation("ProvProd", ds.Tables["Proveedores"].Columns["IDProv"], ds.Tables["Productos"].Columns["IDProv"]);

    ds.Relations.Add(relProvProd);

    connection.Open();

    return ds;

    };

    }

    [WebMethod(Description = "Actualizar Productos")]

    public DataSet UpdateProdProv(DataSet dsProy)

    {

    using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=prueba;Data Source=ZA-002\\SQLEXPRESS"))

    {

    SqlDataAdapter da = new SqlDataAdapter();

    SqlCommand cmdInsert = new SqlCommand("INSERT INTO Productos (IDProd, NombreProd, CategoriaProd, PrecioProd) VALUES (@IDProd, @NombreProd, @CategoriaProd, @PrecioProd", connection);

    cmdInsert.Parameters.Add("@IDProd", SqlDbType.NVarChar, 10 , "IDProd");

    cmdInsert.Parameters.Add("@NombreProd", SqlDbType.NVarChar, 50 , "NombreProd");

    cmdInsert.Parameters.Add("@CategoriaProd", SqlDbType.NVarChar, 50, "CategoriaProd");

    cmdInsert.Parameters.Add("@PrecioProd", SqlDbType.SmallMoney, 10, "PrecioProd");

    da.InsertCommand = cmdInsert;

    SqlCommand cmdUpdate = new SqlCommand("UPDATE Proveedores SET IDProd=@IDProd, NombreProd=@NombreProd, CategoriaProd=@CategoriaProd, PrecioProd=@PrecioProd", connection);

    cmdUpdate.Parameters.Add("@IDProd", SqlDbType.NVarChar, 10, "IDProd");

    cmdUpdate.Parameters.Add("@NombreProd", SqlDbType.NVarChar, 50, "NombreProd");

    cmdUpdate.Parameters.Add("@CategoriaProd", SqlDbType.NVarChar, 50, "CategoriaProd");

    cmdUpdate.Parameters.Add("@PrecioProd", SqlDbType.SmallMoney, 10, "PrecioProd");

    da.UpdateCommand = cmdUpdate;

    SqlCommand cmdDelete = new SqlCommand("DELETE FROM Customers", connection);

    da.DeleteCommand = cmdDelete;

     

    connection.Open();

    if (dsProy != null)

    {

    da.Update(dsProy);

    return dsProy;

    }

    else

    {

    return null;

    }

    };

    }

    }

     

    APPLICATION CODE:

    namespace Proyecto_Web

    {

    public partial class Form1 : Form

    {

    CurrencyManager cm;

    BindingSource provbindingsource = new BindingSource();

    BindingSource prodbindingsource = new BindingSource();

    DataSet dsProy = new DataSet();

    public Form1()

    {

    InitializeComponent();

    }

    private void Form1_Load(object sender, EventArgs e)

    {

    ServicioProvProd.Service miservicio = new Proyecto_Web.ServicioProvProd.Service();

    miservicio.Credentials = System.Net.CredentialCache.DefaultCredentials;

    dsProy.Merge(miservicio.GetProvProd());

     

    tbxIDProv.DataBindings.Add("Text", dsProy.Tables["Proveedores"], "IDProv");

    tbxNombreProv.DataBindings.Add("Text", dsProy.Tables["Proveedores"], "NombreProv");

    tbxDireccionProv.DataBindings.Add("Text", dsProy.Tables["Proveedores"], "DireccionProv");

    tbxTfnProv.DataBindings.Add("Text", dsProy.Tables["Proveedores"], "TfnProv");

     

    dataGridView1.DataSource = dsProy.Tables["Proveedores"];

    dataGridView1.DataMember = "ProvProd";

     

    cm = (CurrencyManager)this.BindingContext[dsProy.Tables["Proveedores"]];

     

    txtbuscar.KeyPress += new KeyPressEventHandler(txtbuscar_KeyPress);

    }

    private void SaveToolStripMenuItem_Click(object sender, EventArgs e)

    {

    if (dsProy.HasChanges())

    {

    ServicioProvProd.Service wsActualizar = new Proyecto_Web.ServicioProvProd.Service();

    wsActualizar.Credentials = System.Net.CredentialCache.DefaultCredentials;

    wsActualizar.UpdateProv(dsProy);

    }

    }

    }

    }

     

    Thanks a lot for your help.

    MJ

     

     

    Thursday, August 9, 2007 7:54 AM
  •  

    I'm just taking a guess here but I believe the problem lies in your SQL code in combination with any constraints you have applied to the table.  Your UPDATE command updates every row in the table to contain the same value because you have no WHERE clause on it.  If you have any PK or unique keys then the update will fail.  The DELETE command deletes all entries from the Customers table.  This is bad and if you have any FK constraints it'll fail.

     

    The insert, update and delete commands are called for each changed row in the data set.  Therefore you must use properly configured WHERE statements to filter down to the single row to update.  In almost all cases you would use the PK of the table.

     

    You should confirm that the contents of the DataSet after the update completes is correct.  If the data is wrong then the SQL statements failed.

     

    Michael Taylor - 8/8/07

    http://p3net.mvps.org

    Thursday, August 9, 2007 12:08 PM
  • Thanks a lot for the replay and sorry for my delay replaying...

     

       I´ve read your message several times, I have made a lot of  test changing the code but I´m not able to solve the problem... The code changed is:

     

    connection.Open();

    SqlDataAdapter daProd = new SqlDataAdapter();

    DataSet dataset = new DataSet();

     

    SqlCommand selectSQL = new SqlCommand("SELECT IDProd, NombreProd, CategoriaProd, PrecioProd, IDProv FROM Productos WHERE IDProv=@IDProv AND IDProd=@IDProd AND NombreProd=@NombreProd AND CategoriaProd=@CategoriaProd", connection);

    selectSQL.Parameters.Add("@IDProd", SqlDbType.NVarChar, 10, "IDProd");

    selectSQL.Parameters.Add("@NombreProd", SqlDbType.NVarChar, 50, "NombreProd");

    selectSQL.Parameters.Add("@CategoriaProd", SqlDbType.NVarChar, 50, "CategoriaProd");

    selectSQL.Parameters.Add("@PrecioProd", SqlDbType.SmallMoney, 10, "PrecioProd");

    selectSQL.Parameters.Add("@IDProv", SqlDbType.NVarChar, 10, "IDProv");

    daProd.SelectCommand = selectSQL;

     

    daProd.FillSchema(dataset, SchemaType.Mapped, "Productos");

    daProd.Fill(dataset);

     

    System.Data.SqlClient.SqlCommandBuilder cmdBuilder = new SqlCommandBuilder();

    cmdBuilder.DataAdapter = daProd;

     

    string insertCommandSql = cmdBuilder.GetInsertCommand().CommandText;

    string updateCommandSql = cmdBuilder.GetUpdateCommand().CommandText;

    string deleteCommandSql = cmdBuilder.GetDeleteCommand().CommandText;

     

    daProd.InsertCommand.CommandText = "INSERT INTO Productos (IDProd, NombreProd, CategoriaProd, PrecioProd, IDProv) VALUES (@IDProd, @NombreProd, @CategoriaProd, @PrecioProd, @IDProv)";

    daProd.InsertCommand.Parameters.Add("@IDProd", SqlDbType.NVarChar, 10, "IDProd");

    daProd.InsertCommand.Parameters.Add("@NombreProd", SqlDbType.NVarChar, 50, "NombreProd");

    daProd.InsertCommand.Parameters.Add("@CategoriaProd", SqlDbType.NVarChar, 50, "CategoriaProd");

    daProd.InsertCommand.Parameters.Add("@PrecioProd", SqlDbType.SmallMoney, 10, "PrecioProd");

    daProd.InsertCommand.Parameters.Add("@IDProv", SqlDbType.NVarChar, 10, "IDProv");

    daProd.InsertCommand = cmdBuilder.GetInsertCommand();

     

    daProd.UpdateCommand.CommandText = "UPDATE Productos SET IDProd=@IDProd, NombreProd=@NombreProd, CategoriaProd=@CategoriaProd, PrecioProd=@PrecioProd WHERE IDProd=@IDProd, NombreProd=@NombreProd, CategoriaProd=@CategoriaProd, PrecioProd=@PrecioProd, IDProv=@IDProv";

    daProd.UpdateCommand.Parameters.Add("@IDProd", SqlDbType.NVarChar, 10, "IDProd");

    daProd.UpdateCommand.Parameters.Add("@NombreProd", SqlDbType.NVarChar, 50, "NombreProd");

    daProd.UpdateCommand.Parameters.Add("@CategoriaProd", SqlDbType.NVarChar, 50, "CategoriaProd");

    daProd.UpdateCommand.Parameters.Add("@PrecioProd", SqlDbType.SmallMoney, 10, "PrecioProd");

    daProd.InsertCommand.Parameters.Add("@IDProv", SqlDbType.NVarChar, 10, "IDProv");

    daProd.UpdateCommand = cmdBuilder.GetUpdateCommand();

     

    daProd.DeleteCommand.CommandText = "DELETE FROM Productos WHERE IDProd=@IDProd, NombreProd=@NombreProd, CategoriaProd=@CategoriaProd, PrecioProd=@PrecioProd";

    daProd.DeleteCommand = cmdBuilder.GetDeleteCommand();

     

    daProd.Update(dataset.Tables["Productos"]);

    return dataset;

      

    It doesn´t work, and throw an exception:

    System.Web.Services.Protocols.SoapException: El servidor no puede procesar la solicitud. ---> System.Data.SqlClient.SqlException: Parameterized Query '(@IDProd nvarchar(10),@NombreProd nvarchar(50),@CategoriaProd nv' expects parameter @IDProd, which was not supplied.... and  more

     

    I new programming in C# and Visual Studio and don´t know how to solve the problem... Could you post me an example, please?

     

    Thanks a lot

    MJ

    Monday, August 13, 2007 10:00 AM
  • There are a couple of problems with your code.  Firstly you are combining CommandBuilder with manual commands.  CommandBuilder is used to auto-generate the commands for you given the schema of the data set.  It is generally recommended that you don't use this class.  Build your commands manually instead.

     

    Code Snippet

    public Dataset GetData ( )
    {
       using(SqlConnection conn = new SqlConnection(...))
       {
          SqlDataAdapter da = new SqlDataAdapter();
          DataSet ds = new DataSet();

          //Command
          da.SelectCommand = new SqlCommand(..., conn);
         
          //Run
          da.Fill(ds);
          return ds;
       };
    }

    public void UpdateData ( DataSet ds )
    {
       using(SqlConnection conn = new SqlConnection(...))
       {
          SqlDataAdapter da = new SqlDataAdapter();
        
          //Command
          da.DeleteCommand = new SqlCommand(..., conn);
          da.InsertCommand = new SqlCommand(..., conn);
          da.UpdateCommand = new SqlCommand(..., conn);
         
          //Run
          da.Update(ds);=
       };
    }

     

     

    You can use the dataset designer to auto-generate this code for you and provide you with a strongly typed data set.  You can then use the classes directly or copy the necessary code and drop them into your own classes.

     

    The next problem is your SQL commands.  I have to recommend that you move away from raw SQL and go with stored procedures instead.  sprocs allow you to test the SQL code outside of your .NET code.  This will identify to you and SQL problems.

     

    The INSERT command looks okay.  Your UPDATE command is wrong though.  It'll never update any rows.  The problem is in your where clause.  You are telling the adapter to update each row only if it matches its current row values (you are comparing each row value to what the DB is storing).  Since UPDATE commands only run against changed rows this will never occur.  Each UPDATE will fail to update anything.  When you are trying to update something you should use the primary key or natural key of the table rather than every field value.  The PK should be something that never changes once set.  In your code I would wager the IDProd is the primary key and therefore must be unique and can not change.  Therefore in your UPDATE command use this value as the where clause.

     

    Code Snippet
    UPDATE
    SET categoriaProd = @categoriaProd, ...
    WHERE IDProd = @idProd

     

     

    The above code will update only a single row and it'll match only the single row in the dataset that should be updated.

     

    The DELETE command, while it should work, has a similiar problem.  Use the PK value only.

    Code Snippet
    DELETE FROM ... WHERE IDProd = @idProd

     

     

    Finally be aware that concurrency may be an issue.  In this case you have to decide what should happen if user A retrieves data from the database, makes changes and then attempts to save those changes while user B has (at the same time) gone in and made their own changes).  If this is an issue in your system (such as web sites) then you need to decide what to do.  In most cases each table has a TIMESTAMP column.  The timestamp column is used (in addition to the pK column) in the WHERE clause of the UPDATE and DELETE commands.  If either command returns a 0 back then the row was modified by somebody else and a concurrency error has occurred. 

     

    Michael Taylor - 8/13/07

    http://p3net.mvps.org

     

    Monday, August 13, 2007 12:57 PM
  • Thanks a lot, Michael Taylor!! Thanks for your patience and for your explications.. I´ve made all the changes you said and it works!!  I will try now whit the concurrency.

     

    Thanks again.

    MJ

    Tuesday, August 14, 2007 8:08 AM