variable that does not work when I use a query but does not throw exception c#

Question

When I try to run it with query6 --> that [religion] = @religionVar it returns me into the data table only the titles of the table but when i do it to the query5 it return me to the table well

string query5 = "SELECT * FROM[PhoneTableSql] WHERE[district] = '5' AND([religion] = @religionVar OR[religion] = '4')";
string query6 = "SELECT * FROM[PhoneTableSql] WHERE[district] = '6' AND([religion] = @religionVar OR[religion] = '4')";

and if i do it like here query6 returns the table good

string query5 = "SELECT * FROM[PhoneTableSql] WHERE[district] = '5' AND([religion] = @religionVar OR[religion] = '4')";
string query6 = "SELECT * FROM[PhoneTableSql] WHERE[district] = '6' AND([religion] = '1' OR[religion] = '4')";

I tried to run it mssql and its work fine but i cant Explain why it works first and does not work on the other

string query5 = "SELECT * FROM[PhoneTableSql] WHERE[district] = '5' AND([religion] = @religionVar OR[religion] = '4')";
string query6 = "SELECT * FROM[PhoneTableSql] WHERE[district] = '6' AND([religion] = '1' OR[religion] = '4')";

using (SqlConnection con = new SqlConnection(@"Data Source=CATISERVER1\SQLEXPRESS;Initial Catalog=dsa;Persist Security Info=True;User ID=mcsager;Password=*******"))
{
    try
    {                    
        SqlCommand cmd5 = new SqlCommand(query5, con);
        cmd5.Parameters.AddWithValue("@religionVar", 1);
        SqlCommand cmd6 = new SqlCommand(query6, con);
        cmd6.Parameters.AddWithValue("@religionVar", 1);
        SqlDataAdapter adpter5 = new SqlDataAdapter(cmd5);
        adpter5.Fill(MyAppManager.ChangeFactorWeightsInstance.DtDistrict_5);
        SqlDataAdapter adpter6 = new SqlDataAdapter(cmd6);
        adpter6.Fill(MyAppManager.ChangeFactorWeightsInstance.DtDistrict_6);
    }
    catch{}
}

Probably because you're passing in an `INT` value, but the column is a `VARCHAR` (as indicated by the single-quotes in your query). — Siyual, Jul 24 '17 at 13:21
Did you notice there is no space between `OR` and `[religion]` ? — Chetan, Jul 24 '17 at 13:24
`MyAppManager.ChangeFactorWeightsInstance.DtDistrict_6` what does this line do to your data table? If running in MSSQL works as expected, you must be doing something different with the return results. What happens if you pass `query5` into `adapter6`? They appear to be of the same structure. — Tommy, Jul 24 '17 at 13:26
You might want to take a peek at this article. http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ — Sean Lange, Jul 24 '17 at 13:28
_"but does not throw exception"_ Well you have swallowed it here: `catch{}`. Empty catch blocks [are a bad idea](https://stackoverflow.com/questions/1234343/why-are-empty-catch-blocks-a-bad-idea) most times. It's the programming equivalent of putting black tape over an engine warning light. — Tim Schmelter, Jul 24 '17 at 13:36
I would guess there are non-numeric entries in the `religion` column - when comparing with `'1'`, it's string comparisons all the way. When comparing with an `int` parameter, we've got `int` conversions in play, and the non-numeric value is triggering an error. (But due to a lack of guarantee on evaluation order in SQL, by no means is it certain that those non-numeric values match the remaining `WHERE` criteria for the failing query, nor that the working queries won't suddenly start failing also) — Damien_The_Unbeliever, Jul 24 '17 at 14:36

Peter · Answer 1 · 2017-07-25T06:57:45.050

You are using the same variable on one connection twice. Give each query it's own variables in sql. I took your code and replaced @religionvar with @religiongvar1 and @religionvar2.

string query5 = "SELECT * FROM[PhoneTableSql] WHERE[district] = '5' AND([religion] = @religionVar1 OR[religion] = '4')";
string query6 = "SELECT * FROM[PhoneTableSql] WHERE[district] = '6' AND([religion] = @religionVar2 OR[religion] = '4')";

    using (SqlConnection con = new SqlConnection(@"Data Source=CATISERVER1\SQLEXPRESS;Initial Catalog=dsa;Persist Security Info=True;User ID=mcsager;Password=*******"))
    {
        try
        {                    
            SqlCommand cmd5 = new SqlCommand(query5, con);
            cmd5.Parameters.AddWithValue("@religionVar1", 1);
            SqlCommand cmd6 = new SqlCommand(query6, con);
            cmd6.Parameters.AddWithValue("@religionVar2", 1);

            SqlDataAdapter adpter5 = new SqlDataAdapter(cmd5);
            adpter5.Fill(MyAppManager.ChangeFactorWeightsInstance.DtDistrict_5);
            SqlDataAdapter adpter6 = new SqlDataAdapter(cmd6);
            adpter6.Fill(MyAppManager.ChangeFactorWeightsInstance.DtDistrict_6);
        }
        catch{}
    }

I am fairly confident this would not cause an issue, especially the one described in the original question. — Tommy, Jul 24 '17 at 13:30
It'd be nice if you pointed our exactly what you changed. It's like a game of spot the difference — Liam, Jul 24 '17 at 13:35

variable that does not work when I use a query but does not throw exception c#

1 Answers1