Update quantity in a table with sql server and c#

Asked

Viewed 56 times

-1

I have a datagridview that is filled as soon as I choose the items that in the case are products that contain quantity in the product insert I choose a quantity and add the item to datagridview, after finalizing the purchase I am not getting the low in that quantity. I created an update but only updates the last product added to datagridview. I am using the code below to update the product quantity.

  public void baixaEstoque()
        {

            //..
            string strCon = "Data Source = DESKTOP-MBLAUPI\\SQLEXPRESS; Initial Catalog = db_controle_loja; Integrated Security = True";
            SqlConnection conexao = new SqlConnection(strCon);

            //..
            SqlCommand cmd = new SqlCommand(" update dbo.tb_produto set quantidade = quantidade - " + quantidadeTextBox.Text +
                                            " where id_produto = @id_produto", conexao);


            cmd.Parameters.Add("@id_produto", SqlDbType.Int).Value = codigoTextBox.Text;
            cmd.Parameters.Add("@quantidadeTextBox", SqlDbType.Int).Value = quantidadeTextBox.Text;


            try
            {
                conexao.Open();                                
                cmd.ExecuteScalar();
                //..

            }
            catch (Exception ex)
            {
                MessageBox.Show("Erro " + ex.Message);
                throw;
            }
            finally
            {

                conexao.Close();
            }
        }
c# sql-server

  • After entering the item in the sale you store your items where? In a class? Or you just enter the data in the gridview?

  • insert only in gridview.

  • I have to go through the gridview on each item added and change the amount in the database my code is only worked for one item in case when add two items it change only the amount of the last of my gridview.

2 answers

0

//.. connection. Open();

            foreach (DataGridViewRow dr in DataGridView_itens_vendas.Rows)
            {
                SqlCommand cmd = new SqlCommand(" update dbo.tb_produto set quantidade = quantidade - " + quantidadeTextBox.Text +
                                    " where id_produto = @id_produto", conexao);

                cmd.Parameters.Add("@id_produto", SqlDbType.Int).Value = dr.Cells[0].Value;
                cmd.Parameters.Add("@quantidadeTextBox", SqlDbType.Int).Value = dr.Cells[2].Value;

                cmd.ExecuteScalar();
            }

I did a for going through the gridview and in each row I took the value of each column and performed the update in my table in the quantity field.

0

Hello.

It is possible to perform operations directly in the script SQL, but I suggest that you first recover the amount of items in the bank store in a variable. Make the calculation later appreciate this in the bank. Ex:

// Consultando quantidade
int quantidadeItens = 0;

using (SqlConnection conexao = new SqlConnection(CNN_STRING))
{
    string sql ="SELECT quantidade from tb_produto where id_produto = @id_produto";

    using (var cmd = new SqlCommand(sql, conexao))
    {
        cmd.Parameters.Add("@id_produto", SqlDbType.Int).Value = codigoTextBox.Text;
        quantidadeItens = CMD.ExecuteScalar();
    }
}

Updating quantity, with your code changing only set quantidade = quantidade - " + quantidadeTextBox.Text, for set quantidade = " + quantidadeItens

  • Your example didn’t work very well, what I need is to go through the gridview on each item added and change the amount in the database my code is only worked for one item in case when add two items it change only the amount of the last in my gridview

Browser other questions tagged c# sql-server

You are not signed in. Login or sign up in order to post.