Chekbox with filters

Asked

Viewed 92 times

-1

I have a stock query in C# that generates a report with reportviewer. I have a Checkbox that when I click on it it does not show me the items with zero balance and when I uncheck it shows the items with zero balance.

Since I still have 2 filters that he has to follow, but I’m not able to make the filters work when I uncheck the Checkbox and when the Checkbox is checked the report works normally, when this unchecked filters no longer work.

Whereas the query in SQL works perfectly, either passing parameter 1 or 0.

follows the code

private void btnPesquisar_Click(object sender, EventArgs e)
{
    string pSaldo = cbEstoque.Checked == true ? "1" : "0";

    this.SaldoEstoqueTableAdapter.Fill_EstProd(this.Estoque.SaldoEstoque, cbTipo.Text, cbArmazem.Text, pSaldo);

    this.reportViewer1.RefreshReport();
}

follow the query in sql

DECLARE @SALDO VARCHAR(10)
SET @SALDO = 0

SELECT       
   SB.B2_COD, 
   S1.B1_DESC, 
   S1.B1_TIPO, 
   SB.B2_LOCAL, 
   (SB.B2_QATU - SB.B2_RESERVA) AS SALDO
FROM SB2020 AS SB 
    INNER JOIN SB1020 AS S1 WITH (NOLOCK) ON S1.B1_COD = SB.B2_COD
WHERE SB.D_E_L_E_T_ <> '*' AND S1.D_E_L_E_T_ <> '*' AND S1.B1_TIPO = 'MP' AND SB.B2_LOCAL = '01'
AND ((SB.B2_QATU - SB.B2_RESERVA) >= 0 AND @SALDO = 0) OR ((SB.B2_QATU - SB.B2_RESERVA) > 0 AND @SALDO = 1)
AND S1.B1_TIPO = 'MP' AND SB.B2_LOCAL = '01'
ORDER BY S1.B1_DESC

Rovann error the query

SELECT       
   SB.B2_COD, 
   S1.B1_DESC, 
   S1.B1_TIPO, 
   SB.B2_LOCAL, 
   (SB.B2_QATU - SB.B2_RESERVA) AS SALDO
FROM SB2020 AS SB 
    INNER JOIN SB1020 AS S1 WITH (NOLOCK) ON S1.B1_COD = SB.B2_COD
WHERE SB.D_E_L_E_T_ <> '*' AND S1.D_E_L_E_T_ <> '*' AND S1.B1_TIPO = 'MP' AND SB.B2_LOCAL = '01' 
AND (CASE 
     WHEN @SALDO = 1 THEN ((SB.B2_QATU - SB.B2_RESERVA) > 0) 
     ELSE ((SB.B2_QATU - SB.B2_RESERVA)>=0) END) 
ORDER BY SB.B1_DESC

erro 
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '>'.
c#

  • tried to close the parentheses on the OR part ?

  • AND (((SB.B2_QATU - SB.B2_RESERVA) >= 0 AND @ BALANCE = 0) OR ((SB.B2_QATU - SB.B2_RESERVA) > 0 AND @ BALANCE = 1))

  • Good afternoon Rovann, thanks for your attention, the query in sql works perfectly, but when I go to c# it only works when it is true in chekbox.

  • Rovann and the following lets me see if I can be clearer, so this report has 2 more filters, 1 filter per product type and one per warehouse, when the chekbox is marked all filters work perfectly, but when I uncheck the chekbox, it seems that the report has no filter, and the type filter and store this filled.

  • I think I understood, therefore the suggestion to put between parentheses the section that has the OR. The AND operator will always be executed before the OR, and if it is not correctly separated, it will be executed only after all the AND that are in the syntax, and can give true result when not expected.

  • Rovann ok left my query the way you told me, but still nothing in c#.

  • see if my answer helps you, and only one observation (not wrong) in the part of cbEstoque.Checked == true, the Checked property already returns true or false, and there is no need to compare ==true, it could simply be cbEstoque.Checked ? " 1" : "0";

  • It hasn’t changed at all....

Show 3 more comments

2 answers

0

exchange the part of 

AND (((SB.B2_QATU - SB.B2_RESERVA) >= 0 AND @SALDO = 0) OR ((SB.B2_QATU - SB.B2_RESERVA) > 0 AND @SALDO = 1))

for:

AND (CASE  
     WHEN @SALDO = 1 THEN ((SB.B2_QATU - SB.B2_RESERVA) > 0)   
     ELSE ((SB.B2_QATU - SB.B2_RESERVA)>=0)
END)

0


I managed to solve the problem, I changed my code to a datagridview..

Thank you for your attention.

Browser other questions tagged c#

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