filter by username

[Jollywg]

I having trouble figuring out how to use the WHERE (or some other feature) to filter my table by the person who is currently logged in. For instance if I log in as JollyWG and my table has a few records (one column of course includes my user name) how can i get it to filter everyone else records out.

Thanks in advance for any feedback!

Jolly

[ThatThatGuy]

select username from Persons where username like 'JollyWG'

[Jollywg]

How can I get that to display when the page is loaded into a gridview?

[Frinavale]

I wouldn't use 'LIKE' in this case.
I would match it exactly because you may have more than one user with a similar name.

For example, you may have a user named "JollyWG" and you may have a user named "Jolly". If you use LIKE username = "Jolly", both "JollyWG" and "Jolly" will be returned. This is probably not what you want.

I recommend:

SELECT username 
FROM Persons 
WHERE username = 'JollyWG'

Please note that when you create your SQL queries you should be using Parameters...yo u should not be directly inserting any information entered by the user into your SQL queries.

If you do not require the whole row be returned, and you just want to see if the user exists in the system consider using COUNT.

SELECT COUNT(*) as "NumberOfUsers"
FROM Persons 
WHERE username > 'JollyWG';

If the count returned is 1, then you know that the user exists.

How can I get that to display when the page is loaded into a gridview?

I'm not sure where you are having problems with this.
I recommend reading the article on How to use a database in your program and How to use a database in your program part II. There are examples in the Part I article that show you how to use Parameters.

-Frinny

[Jollywg]

Let me see if I can explain my problem a little better now that its not 4 am haha. I can grab the current username, and I can display the entire table (orders). Each order has a username associated with it.
This is where my problem is: When the user (who is currently logged in) clicks on "View Cart" it takes them to a page where it filters the orders table by the username and shows the data in a gridview.
I think what I need to do is take the sql statement and when the page is loaded (or another trigger event) run the sql and display the results in a gridview. The only problem is I know how to do one or the other but not both at the same time.

Thanks for the replies!

[Frinavale]

This is where my problem is: When the user (who is currently logged in) clicks on "View Cart" it takes them to a page where it filters the orders table by the username and shows the data in a gridview.

How is this a problem?
This sounds like everything is working...are you having problems filtering the information or are you having problems retrieving the user name?

Why can't you do this at the same time?
Are you getting any error messages when you try?

-Frinny

[Jollywg]

I guess there really isn't a "problem" other than user error. I dont know the syntax to filter a table using sql and apply that filter to a gridview

[Frinavale]

When you execute a SQL command it could return something.
You need to create the right SQL query to do the job though...

In your case, it sounds like you're doing a select from 1 table and all you really need is a WHERE Clause.

If you want every column returned for each record in the table then you would have something like

SELECT * FROM orders

That will return all columns for all rows from a table named "orders".
If you want to restrict this to certain rows that match some criteria you need to add a WHERE clause to the SQL query. In your case you want only the rows/records where the username = "theUserNameSup plied".

So your SQL query looks like:

SELECT * FROM orders
WHERE username = 'theUserNameSupplied'

If you only want specific columns to be returned by the select you can specify that:

SELECT orderID, orderAmount, orderDescription 
FROM orders
WHERE username = 'theUserNameSupplied'

Once the SQL query is executed (as a command) it will return you a table with all of the records that match the SQL query that you supplied. You bind your DataGrid to that table.

What is your SQL query that you're currently using?

-Frinny

[Jollywg]

My statement looks almost identical to the one you posted. What is the syntax to bind a gridview to the query results?

[Frinavale]

myGridView.DataSource = theDataSource
myGridView.DataBind

There are a lot of different controls that you can use for your DataSource. I prefer the DataView because it has paging capabilities and I'm comfortable with it. There are going to be a lot of other controls mentioned in articles all over the web that you can use... and my preference is probably not used as much as, say the SqlDataSource control (especially since you can use this control declarative to auto-magically do things...I'm just not fond of the auto-magically way of doing things because I like to be in control of exactly what happens)

It might be best if you take a look at what MSDN has on the GridView. Most of the information you need on the GridView and any other .NET controls can be found in the MSDN Library. I recommend that you bookmark the MSDN library and use it as your primary reference source when developing in the .NET enviroment. I always refer there first whenever I have a problem.

-Frinny

[Jollywg]

Thanks for your help again Frinny! I'll let you all know how it turns out.

[Jollywg]

Frinny: thanks for being patient with me on this one. I decided to "side step" the whole databinding issue. I added an invisible label that grabbed the username fromt the session and then I told the built in sql wizard for the gridview to filter on the label control.

[Frinavale]

You shouldn't need to use a Label control to do this.
You could just use the user name right out of session couldn't you?

-Frinny

[Jollywg]

Well I tried to grab the username from the session, but for some reason it wouldn't grab it (probably because I'm not sure what needs to be typed into the wizard). It was just quicker for me to use the label and grab its text.

[Frinavale]

Ahh I see, you like to do things the magical way :)
I'm sure there's a way to do it but, like I said, I avoid wizards and automated things because I like to be in control of what happens.

-Frinny