Why variable isn’t working in select query?-CodePudding

Why this is working when I use absolute value but when i try to use variable as query parameter the query returns no data ?

private void SetBomNumber()
{
   
    try
    {
        da2 = new SqlDataAdapter("SELECT Products.[ProductCode], Max(ISNULL([BomNumber], 0)   1) AS NewBomSerial FROM Products LEFT JOIN Bom ON Products.ProductCode = Bom.ProductCode GROUP BY Products.[ProductCode] HAVING(((Products.[ProductCode]) = '210002')) ", Cn);
        da2.Fill(dt2);
        if (dt2.Rows.Count > 0)
        {
            int BomNumber= Convert.ToInt32(dt2.Rows[0]["NewBomSerial"].ToString());
            txtBomNum.Text = BomNumber.ToString();
            MessageBox.Show("The Next Serial Is :"   BomNumber);
        }
        else
        {
            MessageBox.Show("The Query Doesn’t Work");
        }
        
    }
    catch (Exception Err)
    {
        MessageBox.Show("This Error Occured :"   Err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);


    }

}

With variable this shows the error message "The Query Doesn't work" ?

private void SetBomNumber()

{


try
{

    da2 = new SqlDataAdapter("SELECT Products.[ProductCode], Max(ISNULL([BomNumber], 0)   1) AS NewBomSerial FROM Products LEFT JOIN Bom ON Products.ProductCode = Bom.ProductCode GROUP BY Products.[ProductCode] HAVING(((Products.[ProductCode]) = @prcode)) ", Cn);
    da2.SelectCommand.Parameters.AddWithValue("@prcode", "%"   txtprcode.Text   "%");
    da2.Fill(dt2);
    if (dt2.Rows.Count > 0)
    {
        int BomNumber= Convert.ToInt32(dt2.Rows[0]["NewBomSerial"].ToString());
        txtBomNum.Text = BomNumber.ToString();
        MessageBox.Show("The Next Serial Is :"   BomNumber);
    }
    else
    {
        MessageBox.Show("The Query Doesn’t Work");
    }
    
}
catch (Exception Err)
{
    MessageBox.Show("This Error Occured :"   Err.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

This code works in other form with no problem , Thanks in Advance.

CodePudding user response：

Based on @DRapp comments thanks to him this worked for me : If you need the condition to be the exact value of the variable then use :

da2 = new SqlDataAdapter("SELECT Products.[ProductCode], Max(ISNULL([BomNumber], 0)   1) AS NewBomSerial FROM Products LEFT JOIN Bom ON Products.ProductCode = Bom.ProductCode GROUP BY Products.[ProductCode] HAVING(((Products.[ProductCode]) = @prcode)) ", Cn);
           
da2.SelectCommand.Parameters.AddWithValue("@prcode",  txtprcode.Text );

If you need the condition to be Like the variable you can use :

   da2 = new SqlDataAdapter("SELECT Products.[ProductCode], Max(ISNULL([BomNumber], 0)   1) AS NewBomSerial FROM Products LEFT JOIN Bom ON Products.ProductCode = Bom.ProductCode GROUP BY Products.[ProductCode] HAVING(((Products.[ProductCode]) LIKE @prcode)) ", Cn);
               
   da2.SelectCommand.Parameters.AddWithValue("@prcode", "%"  txtprcode.Text  "%");

Thanks