Query Syntax on small database

ok basic problem you haven;t described the relation between a user and a customer...


is a user a customer?
can a customer have multiple users?

in a business sense do the user belong to the customer (their employee's)
or are the users your employee's m or are they third parties (i.e. agents) ?

if the below code isn't what is required , please explain what it does return...
and why that isn't correct...

if you have many users per customer / customer request
then you are missing a user/customer request relationship (either need a relationship table(1:m) or a foreign key on the customer request table (1:1))

select cr.*
 from customer_request as cr
 inner join user as u
  on u.user_customer_id=cr.request_customer_id
 where u.user_customer_signon_id
    = 'Your users (ASP) signon userid'
 order by ...

Select allOpen in new window

I'm thinking it will be something closer to the following... (please forgive any VB syntax bloopers as my VB is a bit rusty)

' ---[ psuedo code, because I can't remember the aspnet_* table layouts ]---
select Username from [the aspnet_users VIEW, NOT the table] 
where IsLoggedIn = true '<--- again, I don't recall the exact field name here
 
' This should result in a list if anyone is logged in... so retrieve that and stick it into a List(Of String) 

' Iterate over that List(Of String) to create a formatted string, such as...

Dim loggedInUsers as String = String.Empty

ForEach userName As String In {whatever you called your List(Of String)}
    loggedInUsers = String.Format("{0},'{1}'", loggedInUsers, userName)
Next

' Finally, build your sql string and inject the list of logged in users to the where clause...

select cr.* 
 from customer_request as cr inner join user as u
  on u.user_customer_id = cr.request_customer_id
 where u.user_customer_signon_id in (loggedInUsers) '<--- loggedInUsers is the variable you populated in the previous ForEach

Select allOpen in new window

sorry I added realtionship between user and customer does that help
new photo of DB
Database.JPG

I will be back in an hour belive it or not its actaully my wedding day.... and this is what im doing and no bacholer party last night hahaha..


Azarc:

a little more detail...

Im trying to create teh grideview with the queriy builder...
 like this image

How do i caputre the userID thats currently  logged in that way to make a query with the table adapter  ?

Query-Builder.JPG

sorry I added realtionship between user and customer does that help

No

you need to explain the relationship between a user and a customer request

to help

define User

define customer

now tell us what a customer request is , and how that relates to individual users/customers

Unless you have logic that removes people from your Users table when they log off or their session is lost, your basis problem with using the TableAdapter is that you're going to have to query the ASPNETDB database in order to see who's actually online.

Is the authority for who is logged in the ASPNETDB or can we assume it is the Users table in your created database?

If it's the ASPNETDB then I think you're in a pickle because unless I'm mistaken there's a 1:1 relationship between a TableAdapter and a database. To get around this you're going to have to create a new ObjectDataSource and write your own data access methods to use in the TableAdapter. I just tested this and wrote 3 methods to give me a filtered result...

Method #1: Returns the logged in users as a DataTable (no parameters in signature)
Method #2: Returns Requests by specific users, accepts Method 1 as a datatable parameter
Method #3: Calls Methods #1 and #2 and returns a DataTable to bind to the GridView

Method 2 sounds like the best  

a  Customer_Request is a work order

it reads like this

requestID 1 (meaning work order number 1)
CustomerID 1 (ID  who made the work order)
and hte rtest is data

the goal od the grideview is when the user logs in and goes to the screen the gridview only shows the customer requests they made

meaninf if there were 5 customer requests in the customer request table but only 3 of them were made by the  user logged in..only show those 3


 
 

so a user is a customer  in a 1:1 relationship?

I see. Ok, you can scrap Methods 1 and three... They were functions, really, not "options".

@lowfatspread, that's what it sounds like to me.

So it seems that you need to add a SelectParameter to your existing TableAdapter so it can filter based on the logged in user. You also need to add the rest of the tables you want to show in the GridView.

What I don't understand is what level of help you need... Just the 10,000ft view we've already sketched out for you or an actual implementation?

yes The user is  the customer 1 to 1

 

im user table


USER_ID = 1
User_Customer_Signon_ID= Johndoe
User_Customer_Password = xxxxx
User_Customer_ID = 1   (this is equal to Customer_ID in Customer Table
Which is also equal to  Request_Customer_ID in Customer request table )




 

i need help with the query syntax
so i can implement

Thats  EXACTLY what I need

I have a table adapter quesry that brings back the data but i need the select parameter to fiter the data an only show the data requested by the  current logged in user


So it seems that you need to add a SelectParameter to your existing TableAdapter so it can filter based on the logged in user. You also need to add the rest of the tables you want to show in the GridView

Ok, need to get back to my computer. Will be right back.

YAY  I might get married today after all :)

so what was the problem with this?

select cr.*
 from customer_request as cr
 inner join user as u
  on u.user_customer_id=cr.request_customer_id
 where u.user_customer_signon_id
    = 'Your users (ASP) signon userid'
 order by ...

select cr.*
 from customer_request as cr
 inner join user as u
  on u.user_customer_id=cr.request_customer_id
 where u.user_customer_signon_id
   = 'Your users (ASP) signon userid'
 order by ...


(how does it determine whoch ID is currenttly signed in) ?

does this help?

http://forums.asp.net/t/1082892.aspx/1?Get+current+username

that doesnt  hel[p me becuase thats not going to go into a table ad adapter

@Lowfatspread, partially. You're right though; you will need this value --> Page.User.Identity.Name

Congratulations! :)  Getting married is good.  :)

This post is for VS2005 but it will still work for you... http://www.vbforums.com/showthread.php?t=512444

Let me know if you need further asssistance.

Start at post #7 in the thread of that link: http://www.vbforums.com/showthread.php?t=512444  (this is the same one as before).

Azarc3
 I cant really follow what its saying
I'm not being a crybaby here.. I am VERY sleep deprived..
but i need to finish this while someone is here to help me....

PLEASE walk me though this like im a beginnger.

 

okay i calemd down for a min...

okay were getting warmer here
i did a msgbox(Page.User.Identity.Name)

and it returned the userID that logged in... Cool

so i think to  get me what i needed in a table adapter

would be like this
 can you help me translate...

1. select User_customer_Id from USER where User_id = Page.User.Identity.Name
(which gives me the Customer ID)

2. then select * from customer_request where CustomerID = User_Customer_Id ( from above)

and that woulfd give me the gridview of all customer requests by the customer logged in

im not the best at sql  I confess
and normally i make a lot of stand alone applications where i'm just making tables with SQL queries im still learning my way in the ASP.NEt world
 

No problem, frustration understood.  :)

1. Right click on your existing Table Adapter.
2. Select Add Query
3. Step through to the part where you create your query
4. enter the query...

select cr.*
 from customer_request as cr inner join user as u
  on u.user_customer_id = cr.request_customer_id
 where u.user_customer_signon_id = @UserName

5. Hit Next
6. Enter FillByUserName for the Fill Method, and GetDataByUserName for the GetData method
7. Click Next and continue doing that until you exit the dialog.

8. Go to the page with the GridView and view its code (press F7).
9. In the appropriate event, enter the following code I used (and use your own dataset, tableadapter and method names where appropriate):

Dim ds As New DataSet2TableAdapters.aspnet_UsersTableAdapter
GridView1.DataSource = ds.GetDataByUserName(string.Format(Page.User.Identity.Name)
GridView1.DataBind()

10. That should do it.

As far as your last comment, Table Adapters are useful while you're transitioning but can be inflexible if you have specific and out-of-the-ordinary needs. I'm sure others would want to argue me down about it, but I prefer to write my own data classes or go with an ORM solution (like the Entity Framework or NHibernate, etc).

Hope your special day is everything you and your bride-to-be have hoped for. :)

So CLOSE but

(pictureds below)

and I only have one data set is that wrong

do i have to create another one ?


I was so excited :~(
Error.JPG
Error2.JPG
Error3.JPG

Yours should have been correct with DataSet1.whatever_your_tableadapter_is_called.

Also, you need to move the code to inside of and event, like Page_Load().

Go back and do the steps again, slowly, paying special attention to step #4.

i went through again slow here are more picutres

Database.JPG
tableAdapter.JPG
Step6-after-copying-query.JPG
PageLoad.JPG

Ok; use the query builder to correct the FillByUserName method.

Do you have Windows 7? it may be easier and quicker to send me a Windows Remote Assistance invitation.

no im still stuck on XP :(

ouch.  :/  ok, try using the query builder to fix the query. it may be that it doesn't like the aliases in the query or I've misspelled something.

im here just tell me what to do and I'll do it superfast

untitled.JPG

I'm doing this from memory so you may have to tweak this a little bit...

1. Go to the TableAdapter with the FillByUserName and GetDataByUserName methods on it.
2. (I think) right-click on it and choose whatever will let you edit the methods.
3. Advance through the dialog until you get to the query screen.
4. Click the Query Builder button.
5. clear out the sql code
6. in the top pane, right-click and select Add Tables
7. click the Customer_Requests table.
8. ctrl+click the Users table.
9. Click OK.
10. when the Add Tables dialog goes away, left-click and drag the Request_Customer_ID column on the Customer_Request table to the Customer_ID column on the users table.
11. in the grid pane (should be 2nd from the top) click top left-most field and select Customer_Request.*.
12. in the next field down, click User.Customer_SignOn_ID.
13. uncheck the checkbox on that row in the 3rd colu,mn.
14. in the Filter by field for that row, enter @UserName.
15. Click OK or whatever saves your changes to the query.
16. Click Next and/or Ok or Finish to exit the dialog.

hopefully that will do it.

GOT THE QUERY TO ACCEPT

SELECT     cr.Request_ID, cr.Request_Customer_ID, cr.Request_Category_ID, cr.Request_Area_ID, cr.Request_Description, cr.Request_Date,
                      cr.Request_Type_of_Request, cr.Request_Avail_Discuss_ID, cr.Request_Estimate, cr.Request_Status
FROM         Customer_Request AS cr INNER JOIN
                      [User] AS u ON u.User_Customer_ID = cr.Request_Customer_ID
WHERE     (u.User_Customer_Signon_ID = @Username)

as im making the object datasource  its asking me to define parameters

what should I do ????

(image)

untitled.JPG

pleas dont quit on me now :)

What are the other options in the Parameter Source drop down list?

<<< pleas dont quit on me now :) >>>

Not a chance.  :)

OPTIONS.JPG

Select Session and enter UserName in the next field. Don't put anything in the Default Value field, if you can get away with it.

You also need to insert the following to the RequestHistory.aspx.vb file as the first lines of code in the Page_Load() method:

If Page.User.Identity.IsAuthenticated Then
     Session.Remove("UserName")
     Session.Add("UserName", Page.User.Identity.Name)
End If

did that and got this....


untitled.JPG

THANK  YOU
THANK YOU
THANK YOU

My pleasure, sir, and enjoy your new life.  :)

congratulations

yeah now i have to go clean the patio furniture, get booze, shower, and somewhere after all that say I DO :)

Will definetly follow you

Many blessings and thanks