Category checking in C # / ASPX usage

0

Good morning,

I'm trying to validate a situation that I will explain, for example we add a category "Miscellaneous", and this same category is used in the product "Keys", imagine that we will delete the category "Parts" with category "Miscellaneous" remains.

What I really want to validate is if we are going to delete a category, check if that category is not in use in any product.

What I tried was the following in the Stored Procedure:

SELECT Categoria FROM Categorias WHERE (Categoria) NOT IN (SELECT Categoria FROM Produtos);

C # code:

        if (BLL.BD.backoffice.VerificaCatgProdutoUso(Categoria).Rows.Count != 0)
        {
            lblCatgsInfo.Text = "Categoria " + Convert.ToString(selectedRow.Cells[1].Text) + " em uso num determinado produto, tente de novo";
            lblCatgsInfo.Visible = true;
            lblCatgsInfo.ForeColor = System.Drawing.Color.Red;
        }
        if (BLL.BD.backoffice.VerificaCatgProdutoUso(Categoria).Rows.Count != 1)
        {
            int resultado = BLL.BD.backoffice.EliminarCatgProduto(IDCatg);
            lblCatgsInfo.Text = "A categoria " + Convert.ToString(selectedRow.Cells[1].Text) + " com o ID " + Convert.ToString(selectedRow.Cells[0].Text) + " foi eliminada com sucesso!";
            lblCatgsInfo.ForeColor = System.Drawing.Color.Green;
            lblCatgsInfo.Visible = true;
            GridViewManage.DataBind();

        }

However it is not working, can someone tell me what is wrong?

Thank you in advance.

    
asked by anonymous 09.06.2015 / 13:22

1 answer

1

Not In breaks the performance of any DB. Do the reverse query, with IN or Exists and not Not In. If you did this: SELECT Categoria FROM Produtos where Categoria = @Categoria . If you return NULL, then you would know that category is not being used in products and you would do what you should do. If I understood the question would do that. That would be your validation.

A direct delete and then an update in GridView in SP, would not be ideal, type:

delete from categoria where not exists(SELECT Categoria FROM Produtos where categoria.categoria = produtos.categoria)

This all within your SP and then refresh your GridView to update your changes. Does this help you?

    
09.06.2015 / 13:50