CODEWITHSUNDEEP
c#asp.netsqlif-statement
Oshrib
Oshrib

Reputation: 1880

Get the wrong return , SQL - if

I have the next method:

    private bool bla()
{

    int Minbuy, ordersTillNow;
    {
        SqlConnection connection = new SqlConnection("Data Source=****;Initial Catalog=****;User ID=****;Password=****;Integrated Security=False;");
        string commandtext = "SELECT Minbuy FROM items WHERE main = 1";
        string commandtext2 = "SELECT ordersTillNow FROM items WHERE main = 1";

        SqlCommand command = new SqlCommand(commandtext, connection);
        SqlCommand command2 = new SqlCommand(commandtext2, connection);

        connection.Open();

        Minbuy = (int)command.ExecuteScalar();
        ordersTillNow = (int)command2.ExecuteScalar();

        if (Minbuy < ordersTillNow)
            return true;
        else
            return false;

    }



}

And the use of the method on page_ load:

        if (bla())
    {
        Image_V.Visible = true;
    }
    else
    {
       Image_X.Visible = true;
    }

The values on the result in query in the SQL is:

MinBuy = 5

ordersTillNow = 1

The weird thing is - nevermind what is the value in the db (i've changed the value to: MinBuy = 1 and ordersTillNow = 8) - it's display the image_v . (on the aspx page - both of the image visble set to false).

What is wrong in the code ?

The table desgin:

table name: items

columns: itemId(int) , main (bit) , MinBuy (int) , ordersTillNow(int) .

Upvotes: 1

Views: 139

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Well, let's hit the database only once, and get it to answer the question for us:

private bool bla()
{
    using(SqlConnection connection = new SqlConnection("Data Source=****;Initial Catalog=****;User ID=****;Password=****;Integrated Security=False;"))
    {
        string commandtext = "SELECT CONVERT(bit,CASE WHEN Minbuy < ordersTillNow THEN 1 ELSE 0 END) FROM items WHERE main = 1";
        SqlCommand command = new SqlCommand(commandtext, connection);
        connection.Open();
        return (bool)command.ExecuteScalar();    
    }
}

This code also makes sure to close the connection, that you were not previously taking care of. But if you're still not seeing the expected results, then as others have suggested, you need to check that the image display code is working as expected. Maybe have:

Image_V.Visible = bla();
Image_X.Visible = !Image_V.Visible;

instead.

Upvotes: 1

ysrb
ysrb

Reputation: 6740

What I can think that might be the problem is that the result from the above SQL might return more than 1 row. Thus, it will give the wrong result.

Try changing the code to :

string commandtext = "SELECT TOP 1 Minbuy FROM items WHERE main = 1";
string commandtext2 = "SELECT TOP 1 ordersTillNow FROM items WHERE main = 1";

Instead of having 2 selects you can use one select

string queryString = "SELECT Minbuy, ordersTillsNow FROM items WHERE main = 1";
using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        connection.Open();

        SqlCommand command = new SqlCommand(queryString, connection);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            MinBuy = int.Parse(reader[0].ToString());
            ordersTillsNow = int.Parse(reader[1].ToString());
            //Console.WriteLine(String.Format("{0}", reader[0]));
        }
    }

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

Perhaps you need:

if (bla())
{
    Image_V.Visible = true;
    Image_X.Visible = false;
}
else
{
   Image_V.Visible = false;
   Image_X.Visible = true;
}

Upvotes: 1

Related Questions