?
Solved

select databind in code behind

Posted on 2010-01-06
38
Medium Priority
?
207 Views
Last Modified: 2013-12-17
hello, i need to query a table to determine if a table column is set to true. If so then i need to set the enable attribute to false so the menu link is not active. below is my code.
protected void checkProfileCompleteStatus(string userId)
    {
        //sql connection
        SqlConnection dbConnection = new SqlConnection(connectionString);
        //sql command/script
        string selectSQL = "select ProfileComplete from UserInformation where Where UserId = @UserId";
        System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(selectSQL, dbConnection);
        System.Data.SqlClient.SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCmd);
        System.Data.DataSet dataSet = new System.Data.DataSet();
        dataAdapter.Fill(dataSet,"UserInformation");
        foreach (DataRow theRow in dataSet.Tables["UserInformation"].Rows)
        {
            if (theRow["ProfileComplete"].ToString != 1)//errors here and not sure how to use it
            {
                   //disable the menu link - 
                //<asp:Menu ID="Menu1"
                //<asp:MenuItem Text="Doctors" Value="Doctors">
                  //<asp:MenuItem NavigateUrl="~/secure/adddoctor.aspx" Text="Add a Doctor" Value="Add a Doctor"></asp:MenuItem>
                    //Enabled="false"
            }
        }
    }

Open in new window

0
Comment
Question by:vicomin
  • 23
  • 15
38 Comments
 

Author Comment

by:vicomin
ID: 26193244
the db column is a bit and has one of the following values: 0,1,null. the code has 1 as true.
0
 
LVL 21

Expert Comment

by:silemone
ID: 26193322
you have 1 =   should be ==
0
 
LVL 21

Expert Comment

by:silemone
ID: 26193329
oops..i'm incorrect...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Expert Comment

by:silemone
ID: 26193344
if (theRow["ProfileComplete"].ToString != 1)

you're comparing a string and a int/ or bit value...

you need to convert them  or place quotations around 1 like "1"
0
 
LVL 21

Expert Comment

by:silemone
ID: 26193360
like so:

if (theRow["ProfileComplete"].ToString != "1")           <--as you're changing row ToString()...

0
 
LVL 21

Expert Comment

by:silemone
ID: 26193406
or you can use

bool x = false;
if ((Boolean.TryParse(theRow["ProfileComplete"].ToString(), out x) != 1);

or

if (theRow["ProfileComplete"].ToString() != "1")           <--as you're changing row ToString()...

or Convert.ToBoolean method...


either of those methods should work...
0
 

Author Comment

by:vicomin
ID: 26193427
that didn't seem to work.
0
 
LVL 21

Expert Comment

by:silemone
ID: 26193434
so to clean this up:

since you changing value to string:
you could use:

if (theRow["ProfileComplete"].ToString() != "1")  

or

you could use Boolean TryParse
bool x = false;
if ((Boolean.TryParse(theRow["ProfileComplete"].ToString(), out x) != 1)

or  you could use

if ((Convert.ToBoolean(theRow["ProfileComplete"].ToString()) != 1)

0
 

Author Comment

by:vicomin
ID: 26193437
should this be done of the dataset?

if(dataSet.Tables["UserInformation"].Rows[18]["ProfileComplete"] != 1)
0
 
LVL 21

Expert Comment

by:silemone
ID: 26193479
you should definitely get a different error...i may have had a type before...

now to the other problem i see:  

what if the value is null?

but you're already trying to use the ToString() method and you can't convert null to a string...

so you need another if statement

if (theRow["ProfileComplete"] != null)
0
 
LVL 21

Expert Comment

by:silemone
ID: 26193543
'should this be done of the dataset?

if(dataSet.Tables["UserInformation"].Rows[18]["ProfileComplete"] != 1) '  <---no..why are you looping if you're going to use a row index...i would not use this and leave it as in the above...by the way, if this was correct, all it would do would shorten the code and make it less lines, but it would still provide the same affect...
0
 

Author Comment

by:vicomin
ID: 26193545
what about this?

protected void checkProfileCompleteStatus(string userId)
    {
        //sql connection
        SqlConnection dbConnection = new SqlConnection(connectionString);
        //sql command/script
        string selectSQL = "select ProfileComplete from UserInformation where Where UserId = @UserId";
        System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(selectSQL, dbConnection);
        System.Data.SqlClient.SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCmd);
        System.Data.DataSet dataSet = new System.Data.DataSet();
        dataAdapter.Fill(dataSet,"UserInformation");

        foreach (DataRow theRow in dataSet.Tables["UserInformation"].Rows)
        {
        if (theRow["ProfileComplete"].ToString() == "0" || (theRow["ProfileComplete"] == null))
            {
                //disable the menu link -
                //<asp:Menu ID="Menu1"
                //<asp:MenuItem Text="Doctors" Value="Doctors">
                //<asp:MenuItem NavigateUrl="~/secure/adddoctor.aspx" Text="Add a Doctor" Value="Add a Doctor"
                //Enabled="false" ></asp:MenuItem>
 
            }
        }

    }
0
 
LVL 21

Expert Comment

by:silemone
ID: 26193787
no...
have to check for null first

because if its null, and you're using || or &&, it will still check check the second condition...

i.e  it's null, it will check both conditions and still try to change null ToString()...
place if on the outside of code...

if (theRow["ProfileComplete"].ToString() == "0" || (theRow["ProfileComplete"] == null))
0
 
LVL 21

Expert Comment

by:silemone
ID: 26193839
if (theRow["ProfileComplete"].ToString() == "0" || (theRow["ProfileComplete"] == null))

better explanation:  here even if you place null in front as first check, the program will still check the other condition...
foreach (DataRow theRow in dataSet.Tables["UserInformation"].Rows)
        {
          if (theRow["ProfileComplete"] != null)
          {
               bool isNum = false;
               if (Boolean.TryParse(theRow["ProfileComplete"].ToString(), out isNum) != 1)//errors here and not sure how to use it
               {
                   //disable the menu link -
                //<asp:Menu ID="Menu1"
                //<asp:MenuItem Text="Doctors" Value="Doctors">
                  //<asp:MenuItem NavigateUrl="~/secure/adddoctor.aspx" Text="Add a Doctor" Value="Add a Doctor"></asp:MenuItem>
                    //Enabled="false"
                }
           }
        }


}
0
 

Author Comment

by:vicomin
ID: 26193924
error Operator '!=' cannot be applied to operands of type 'bool' and 'int'

errors here
if (Boolean.TryParse(theRow["ProfileComplete"].ToString(), out isNum) != 1)

0
 
LVL 21

Expert Comment

by:silemone
ID: 26194038
oops... change 1 = true in the code...i.e.   != true  that is what 1 should equal, yes?  1 = true ?

otherwise you should use Convert.ToBit(1)
0
 
LVL 21

Expert Comment

by:silemone
ID: 26194068
excuse me...not Convert.ToBit, but Convert.ToBoolean(1)
0
 

Author Comment

by:vicomin
ID: 26194117
the following code is compiling but don't think it is working

protected void checkProfileCompleteStatus(string userId)
    {
        SqlConnection dbConnection = new SqlConnection(connectionString);
        string selectSQL = "select ProfileComplete from UserInformation Where UserId ='" + userId + "'";
        System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(selectSQL, dbConnection);
        System.Data.SqlClient.SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCmd);
        System.Data.DataSet dataSet = new System.Data.DataSet();
        dataAdapter.Fill(dataSet,"UserInformation");

        
        foreach (DataRow theRow in dataSet.Tables["UserInformation"].Rows)
        {
            if (theRow["ProfileComplete"] != null)
            {
                if (theRow["ProfileComplete"].ToString() == "0")//errors here and not sure how to use it
                {
                    Response.Write("Enable should be false");
                    Menu1.FindItem("Doctors").Enabled = false;
                    Menu1.FindItem("Patients").Enabled = false;
                    Menu1.FindItem("Requisitions").Enabled = false;

                }
            }
        }


    }

Open in new window

0
 
LVL 21

Expert Comment

by:silemone
ID: 26194153
the following code is compiling but don't think it is working<-- very general statement...:0)...what makes you think its not working?  place breakpoints  with  F9 in visual studios, run while debugging (if your config files has to be updated, say yes), and f11 to see each line and its value...this will help you with your debugging skills if you don't know how to use the IDE for debugging yet...
0
 
LVL 21

Expert Comment

by:silemone
ID: 26194206
try debuggin this too

protected void checkProfileCompleteStatus(string userId)
    {
        SqlConnection dbConnection = new SqlConnection(connectionString);
        string selectSQL = "select ProfileComplete from UserInformation Where UserId ='" + userId + "'";
        System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(selectSQL, dbConnection);
        System.Data.SqlClient.SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCmd);
        System.Data.DataSet dataSet = new System.Data.DataSet();
        dataAdapter.Fill(dataSet,"UserInformation");

        
        foreach (DataRow theRow in dataSet.Tables["UserInformation"].Rows)
        {
            if (theRow["ProfileComplete"] != null)
            {
                 bool isNum = false;
               if (Boolean.TryParse(theRow["ProfileComplete"].ToString(), out isNum) != Convert.ToBoolean(1))//errors here and not sure how to use it
                {
                    Response.Write("Enable should be true");
                    Menu1.FindItem("Doctors").Enabled = true;
                    Menu1.FindItem("Patients").Enabled = true;
                    Menu1.FindItem("Requisitions").Enabled = true;

                }
            }
        }


    }

Open in new window

0
 
LVL 21

Expert Comment

by:silemone
ID: 26194256
oops in the above i changed things to true, but it should still read false...but that should work...i tested it...
0
 

Author Comment

by:vicomin
ID: 26194329
so if the value is not null and the value is 0 the menu items should be disabled, right

so if it is not null and the value is 1 then the menu items should be enabled, right
0
 
LVL 21

Expert Comment

by:silemone
ID: 26194397
yes...that's what you're going for...i can only check the logic of the code...not the data...or actual menu items...i just set some divs text to show true...
0
 
LVL 21

Expert Comment

by:silemone
ID: 26194408
looks like if you want the value to enable otherwise, you need and else statement after checking whether val == 0  or val != 1
0
 

Author Comment

by:vicomin
ID: 26194571
looks like it is getting there
protected void checkProfileCompleteStatus(string userId)
    {
        SqlConnection dbConnection = new SqlConnection(connectionString);
        string selectSQL = "select ProfileComplete from UserInformation Where UserId ='" + userId + "'";
        System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(selectSQL, dbConnection);
        System.Data.SqlClient.SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCmd);
        System.Data.DataSet dataSet = new System.Data.DataSet();
        dataAdapter.Fill(dataSet, "UserInformation");

        foreach (DataRow theRow in dataSet.Tables["UserInformation"].Rows)
        {
            bool isNum = false;
            if (theRow["ProfileComplete"] == null)
            {
                //nothing happens
                Response.Write("null");
                Menu1.FindItem("Doctors").Enabled = false;
                Menu1.FindItem("Patients").Enabled = false;
                Menu1.FindItem("Requisitions").Enabled = false;
            }
            if (Boolean.TryParse(theRow["ProfileComplete"].ToString(), out isNum) == Convert.ToBoolean(0))
            {
                //this happens if the column value is null
                Response.Write("0");
                Menu1.FindItem("Doctors").Enabled = false;
                Menu1.FindItem("Patients").Enabled = false;
                Menu1.FindItem("Requisitions").Enabled = false;
            }
            if (Boolean.TryParse(theRow["ProfileComplete"].ToString(), out isNum) == Convert.ToBoolean(1))
            {
                //this happens if the column value if 0 or 1
                Response.Write("1");
                Menu1.FindItem("Doctors").Enabled = true;
                Menu1.FindItem("Patients").Enabled = true;
                Menu1.FindItem("Requisitions").Enabled = true;
            }
        }
    }

Open in new window

0
 

Author Comment

by:vicomin
ID: 26194668
so i modified the code a little to this one and got similar results
protected void checkProfileCompleteStatus(string userId)
    {
        SqlConnection dbConnection = new SqlConnection(connectionString);
        string selectSQL = "select ProfileComplete from UserInformation Where UserId ='" + userId + "'";
        System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(selectSQL, dbConnection);
        System.Data.SqlClient.SqlDataAdapter dataAdapter = new System.Data.SqlClient.SqlDataAdapter(sqlCmd);
        System.Data.DataSet dataSet = new System.Data.DataSet();
        dataAdapter.Fill(dataSet, "UserInformation");

        foreach (DataRow theRow in dataSet.Tables["UserInformation"].Rows)
        {

            if (theRow["ProfileComplete"] != null)
            {
                bool isNum = false;
                if (Boolean.TryParse(theRow["ProfileComplete"].ToString(), out isNum) == Convert.ToBoolean(1))
                {
                    Response.Write("1"); //if column value is 0 and if the column value is 1
                    Menu1.FindItem("Doctors").Enabled = true;
                    Menu1.FindItem("Patients").Enabled = true;
                    Menu1.FindItem("Requisitions").Enabled = true;
                }
                else if (Boolean.TryParse(theRow["ProfileComplete"].ToString(), out isNum) == Convert.ToBoolean(0))
                {
                    Response.Write("0"); //if null
                    Menu1.FindItem("Doctors").Enabled = false;
                    Menu1.FindItem("Patients").Enabled = false;
                    Menu1.FindItem("Requisitions").Enabled = false;
                }
                else
                {
                    Response.Write("null");
                    Menu1.FindItem("Doctors").Enabled = false;
                    Menu1.FindItem("Patients").Enabled = false;
                    Menu1.FindItem("Requisitions").Enabled = false;
                }
            }
        }
    }

Open in new window

0
 
LVL 21

Expert Comment

by:silemone
ID: 26194776
what are similar results?  
0
 

Author Comment

by:vicomin
ID: 26195048
how do i write this to check the value if it is a 1 or 0?
0
 
LVL 21

Expert Comment

by:silemone
ID: 26195159
what's wrong with the code? have you debugged it?  you would change boolean.tryparse to int.tryparse...

and Convert.ToBoolean to Convert.toInt()...

what's happening?  you're not being very clear with you one-line messages...i don't know what problems you're having because you're not communicating them...please pass more details...
0
 

Author Comment

by:vicomin
ID: 26195731
int isNum = 0;

if (int.TryParse(theRow["ProfileComplete"].ToString(), out isNum ) == Convert.ToInt32(1))

yields:  Error      1      Operator '==' cannot be applied to operands of type 'bool' and 'int'      
0
 

Author Comment

by:vicomin
ID: 26195792
hey at this line:  else if (Boolean.TryParse(theRow["ProfileComplete"].ToString(), out isNum) == Convert.ToBoolean(0))

how do I know what the value is?
0
 
LVL 21

Expert Comment

by:silemone
ID: 26204496
i don't understand the question?  what 0 is or what 1 is?

please rephrase with more details...
0
 

Author Comment

by:vicomin
ID: 26209333
I want to show different results based on if the value in the ProfileComplete column is null, 0 or 1. Right now if the value is 1 or 0 it will print the first if condition and if the value is null it will print the second if condition.


if ProfileComplete == null
 //do this
else if ProfileComplete == 0
 //do this
else if ProfileComplete == 1
 //do this
else
 //default action


current code

foreach (DataRow theRow in dataSet.Tables["UserInformation"].Rows)
        {
            if (theRow["ProfileComplete"] != null)
            {
                bool isNum = false;
                if (Boolean.TryParse(theRow["ProfileComplete"].ToString(), out isNum) == Convert.ToBoolean(1))
                {
                    Response.Write("1"); //prints if column value is 0 and if the column value is 1
                    Menu1.FindItem("Doctors").Enabled = true;
                    Menu1.FindItem("Patients").Enabled = true;
                    Menu1.FindItem("Requisitions").Enabled = true;
                }
                else if (Boolean.TryParse(theRow["ProfileComplete"].ToString(), out isNum) == Convert.ToBoolean(0))
                {
                    Response.Write("0"); //prints if column value is null
                    Menu1.FindItem("Doctors").Enabled = false;
                    Menu1.FindItem("Patients").Enabled = false;
                    Menu1.FindItem("Requisitions").Enabled = false;
                }
                else
                {
                    Response.Write("null");
                    Menu1.FindItem("Doctors").Enabled = false;
                    Menu1.FindItem("Patients").Enabled = false;
                    Menu1.FindItem("Requisitions").Enabled = false;
                }
            }
        }

Open in new window

0
 
LVL 21

Expert Comment

by:silemone
ID: 26210846
i see what you want...only thing is what do you want it to do
foreach (DataRow theRow in dataSet.Tables["UserInformation"].Rows)
        {
            if (theRow["ProfileComplete"] != null)
            {
                bool isNum = false;
                if (Boolean.TryParse(theRow["ProfileComplete"].ToString(), out isNum) == Convert.ToBoolean(1))
                {
                    Response.Write("1"); //prints if column value is 0 and if the column value is 1
                    Menu1.FindItem("Doctors").Enabled = true;
                    Menu1.FindItem("Patients").Enabled = true;
                    Menu1.FindItem("Requisitions").Enabled = true;
                }
                else if (Boolean.TryParse(theRow["ProfileComplete"].ToString(), out isNum) == Convert.ToBoolean(0))
                {
                    Response.Write("0"); //prints if column value is null
                    Menu1.FindItem("Doctors").Enabled = false;
                    Menu1.FindItem("Patients").Enabled = false;
                    Menu1.FindItem("Requisitions").Enabled = false;
                }
                else
		{	
                    Response.Write("Error. Neither null, 1, nor 0);
                    Menu1.FindItem("Doctors").Enabled = false;
                    Menu1.FindItem("Patients").Enabled = false;
                    Menu1.FindItem("Requisitions").Enabled = false;
                    //set to false
		}
            }
	    else
            {
                Response.Write("null");
                Menu1.FindItem("Doctors").Enabled = false;
                Menu1.FindItem("Patients").Enabled = false;
                Menu1.FindItem("Requisitions").Enabled = false;
            }
        }

Open in new window

0
 

Author Comment

by:vicomin
ID: 26211111
if ProfileComplete == null
 //disable the menu items
else if ProfileComplete == 0
 //disable the menu items or logout as the value should never be 0
else if ProfileComplete == 1
 //enable the menu items
else
 //logout - must be an error
0
 
LVL 21

Expert Comment

by:silemone
ID: 26212272
i don't know to log you out...
i guess you would just redirect to a logout landing page...that page would ideally have a link that says

if you want to login again press here <---takes you to the login page...
0
 

Author Comment

by:vicomin
ID: 26213008
yes - will just do a redirect - i can handle that part
0
 
LVL 21

Accepted Solution

by:
silemone earned 2000 total points
ID: 26285474
did you wrap this one up by adding the re-direct?  don't forget if you're using sessions and capturing userId after login, to also make that session = "" or null when you go to the logout page...then if you try to go backwards, the page (which theoretically should check for a session with userId in it or redirect to login page) will redirect to login page...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Integration Management Part 2
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question