Comparing against database value

Question

What I am trying to do is grab the current logged in users username and compare that against a database which contains users, and also includes an Active flag and an Admin flag. I want to compare the current logged in user in the tbl_Person table and their respective user in the table to see if they are marked as Active and Admin. If both are true, they get access to an Admin page. I have the below so far which isn't working. Some of which I know why, some I don't. I think I am on the right track, that being said I am sure I am not doing it correctly. I know you use ExecuteScalar() to return something along with OUTPUT in the query string but couldn't get that to work. The other glaring issue is that I am trying to return integers when the username is a string and the active and admin flags are Bools. I know that I only have Active in there are the moment. I was trying to get that to work before adding in something else.

I read that with the ExecuteScalar, you could Parse and convert ToString, but that didn't work and I found evidence that this might not be the correct thing to do, but I'm really not sure.

I have got a few different errors. Type errors, invalid column when I've tried to do the OUTPUT. With OUTPUT I tried as just OUTPUT and because I know when returning after inserting, you do inserted.name. I tried selected.name as a hunch, but that didn't work.

I was thinking that if I pulled the info, concatenated them and then did a comparison, that this would do what I want, but I am open to other suggestions. Thanks.

string userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HSEProjRegConnectionString1"].ConnectionString);
conn.Open();
SqlCommand sqlUserName = new SqlCommand("SELECT [username] FROM [tbl_Person]", conn);
SqlCommand sqlActive = new SqlCommand("SELECT [active] FROM [tbl_Person]", conn);
int result1 = ((int)sqlUserName.ExecuteScalar());
int result2 = ((int)sqlActive.ExecuteScalar());

string userInfo = result1 + "." +result2;
string userName = userName + "." +result2;

if (userInfo == userName)
{
    Woo, you have access.
}
else
{
    Sorry, but no.
}

The Query isn't final either. Once it is working, I'll change it to a parameterised query.

You need to go back to the basics of SQL and the SQL client libraries. You probably want a WHERE clause to select only one row from the table. ExecuteScalar() is for when a query returns a single value, e.g. select count(*) from ..., otherwise it will return the first column of the first row of the result set. In your case, it will be the username and the active flag from an essentially arbitrarily chosen row. Also neither are ints. — millimoose
– millimoose, Commented Jun 11, 2013 at 1:17
Voting to close as TL since there's more wrong with the code than right. Gather your thoughts first and get to a point where you know what the one, specific issue that's blocking your progress right now is, and ask a question where that one issue is demonstrated. (Including error messages etc.) Instead of posting a block of essentially nonsensical code and asking someone to straighten what you guess but haven't really exactly determined are the problems. — millimoose
– millimoose, Commented Jun 11, 2013 at 1:19
And how the hell am I meant to do that without some guidance? I am not asking people to fix it for me, but to help me with where I am going wrong. — Trido
– Trido, Commented Jun 11, 2013 at 1:29

Mike Perrenoud · Accepted Answer · 2013-06-11 04:42:40Z

1

Okay, consider the following code:

string userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["HSEProjRegConnectionString1"].ConnectionString))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("SELECT [active] FROM [tbl_Person] WHERE username = @username", conn))
    {
        // since we can literally filter the results, if something comes back
        // we know they are registered
        cmd.Parameters.AddWithValue("@username", userName);

        var res = cmd.ExecuteScalar();
        bool registeredAndActive = (bool)res;

        // unless of course `[active]` is an INT -then do this
        bool registeredAndActive = (int)res == 1 ? true : false;

        // but really -set [active] up as a BIT if it's not **and**
        // please make it non-nullable :D
    }
}

I'm pretty sure it does what you want. But it also shows you some best practices like:

Leverage the using statement for all IDisposable objects.
Filter the query as much as you can and make only one round trip.

answered Jun 11, 2013 at 4:42

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Trido Over a year ago

I swear you're my new best friend. Thanks for this. I have been really banging my head against the desk all day trying to figure this out. Trying to find stuff on the net has also been rather frustrating because it assumes so much knowledge which is why I went back to basics RE that other question.

Mike Perrenoud Over a year ago

@Trido, no worries. At times the community can get tough, but don't give up on it, it's truly the best on the net. Many programmers up here have forgotten where they started -it happens to the best of us.

Collectives™ on Stack Overflow

Comparing against database value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related