Category check in use C#/ASPX

Asked

Viewed 47 times

0

Good morning,

I am trying to validate a situation that I explain, for example we added a category "Miscellaneous", and this same category is used in the product "Keys", let’s imagine that we will delete the category "Parts", the product "Keys" with category "Miscellaneous" remains.

What I really want to validate is if we’re going to delete a category, check that that same 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);

Code C#:

        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, someone can inform me what is incorrect?

Thanks in advance.

c#

  • I don’t understand very well, but is the category not being used as FK? If so, you should delete in a cascateada. I also didn’t understand what you said you tried in SP. I saw only a select and not a delete. You tried putting a BP in your method: Eliminarcatgproduct(Idcatg) to see what happens? Whether you are really calling delete or not.

  • I just want stored to check if the "parts" category is actually being used by any product. If the category "parts" exists in the "category" column of the "products" table, I wanted it to validate the error so as not to delete the category, because it is being used by a product. If not in use, DELETE can be performed, this is all functional, what I really need is the validation of the category in use by the product

  • By what I saw you failed to compare the category table category category of the products table. I edited my answer. There you can adapt what you want.

1 answer

1


Not In breaks the performance of any BD. 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 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 its validation.

A direct delete and then an update in Gridview in SP would not be ideal, like:

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

All this inside your SP and then a refresh in your Gridview to update the changes. This helps you?

  • I believe that in this way it will not work, as stored Procedure does not check both categories tables (category column) and products (category column).

  • got it wrong. I thought what was needed was just what was in products. I will edit my answer.

  • Thank you, I’m waiting.

  • So in this form will erase all categories, something like Idcatg = @Idcatg is missing

  • Like I said, it’s about adapting to your needs. I don’t know your project at all, so just pass parameters to make sure it’s just what you want. An ID, another information. What I went through was purely generic and hypothetical.

  • Something like WHERE Idcatg = @Idcatg is missing to just delete the selected category. ?

  • In this situation at the end of the query is "AND Idcatg = @Idcatg", I am still doing the tests but it seems to me that it is correct in this way.

  • It goes deep that it will work, believe me. I see no other way for what I understood.

Show 3 more comments

Browser other questions tagged c#

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