Foreach datagridview row update stock

Question

Foreach datagridview row update stock

Navigation

#1 by (3 votes)

1

It is possible to update a stock with stored procedure using a foreach that is, I have this procedure execution:

                SqlConnection conec = new SqlConnection();
                SqlCommand cm = new SqlCommand();
                cm.Connection = con;
                cm.CommandText = "SP_ActualizaInventario";
                cm.CommandType = CommandType.StoredProcedure;

                cm.Parameters.Add(new SqlParameter("@cantidad", SqlDbType.Int));
                cm.Parameters["@cantidad"].Value = Convert.ToInt32(row.Cells["Column13"].Value);

                cm.Parameters.Add(new SqlParameter("@Producto", SqlDbType.VarChar));
                cm.Parameters["@Producto"].Value = Convert.ToString(row.Cells["Column7"].Value);

                cm.Parameters.Add(new SqlParameter("@Tipo", SqlDbType.Int));
                cm.Parameters["@Tipo"].Value = 1;

is going to take the value of the columns that you indicate to execute the procedure but the procedure apparently does not take each line but the last line only as I fix it? I put it inside the foreach or outside?

store procedure

     ALTER PROCEDURE [dbo].[SP_ActualizaInventario]
         @cantidad INT
,@Producto VARCHAR
,@Tipo INT
AS
BEGIN
DECLARE @Existencia INT
SET @Existencia = (SELECT Existencia FROM maestraproductoinventario WHERE [Codigo De Articulo] = @Producto)
if(@Tipo = 1)
begin
UPDATE
 maestraproductoinventario 
 SET
    Existencia = @Existencia - @cantidad 
    WHERE [Codigo De Articulo] = @Producto
end
else
begin
UPDATE
 maestraproductoinventario 
 SET
    Existencia = @Existencia + @cantidad 
    WHERE [Codigo De Articulo] = @Producto
end
END

sql c#

asked by Samuel Ignacio Susana Confesor 06.08.2017 в 20:32

source

1 answer

Random elements of an array How to do a POST of a variable and receive it as an object in PHP using AJAX?

score 3 · Accepted Answer

Good morning

As the part where you get row.Cells["..."].value seems to be that there may be several I suggest you do a foreach or for (as you can in that order) to get each element amount and product ; invoking:

public void actualizarStock(int laCantidad, string elProducto) {
   SqlConnection conec = new SqlConnection();
   SqlCommand cm = new SqlCommand();

   cm.Connection = con;
   cm.CommandText = "SP_ActualizaInventario";
   cm.CommandType = CommandType.StoredProcedure;

   cm.Parameters.Add(new SqlParameter("@cantidad", SqlDbType.Int));
   cm.Parameters["@cantidad"].Value = Convert.ToInt32(laCantidad);

   cm.Parameters.Add(new SqlParameter("@Producto", SqlDbType.VarChar));
   cm.Parameters["@Producto"].Value = Convert.ToString(elProducto);
} // actualizarStock

ejmplo (assuming officer with foreach):

foreach(row fila in objetoconrow.Rows)
{
    actualizarStock(Convert.ToInt32(row.Cells["Column13"].Value), 
                    Convert.ToString(row.Cells["Column7"].Value));
}

The Store Procedure:

[CREATE|ALTER] PROCEDURE [dbo].[SP_ActualizaInventario]
   @cantidad INT
   ,@Producto VARCHAR
AS
BEGIN

    UPDATE maestraproductoinventario 
        SET
            Existencia = Existencia + @cantidad 
    WHERE [Codigo De Articulo] = @Producto;

END

Notes:

CREATE or ALTER as appropriate.

@quantity should receive positive value if it increases or negative if it is decremented (the quantity of product is received or left out of the stock, which apparently determines with @Type).

In case you want to send everything in a single invocation it will be to make changes to the Store procedure so that it receives an array, see this article that will serve you How to pass an array into a SQL Server stored procedure