I am having a problem with an SQL statement and I wondered if someone may have a suggestion.
I have a drop-down list on "pageA.asp" with the action set to pageB.asp" that displays all the departments in my county,
and on pageB.asp I have my sql that displays the addresses of the departments.
Now in each record that displays, there is a link that takes you to "pageC.asp".
On pageC I need to list all the employeesk at each address (from pageB records).
In the sql on my pageC.asp is there a way that I can use a WHERE clause that will grab the employees that are at each address AND from each department?
Example: I have this WHERE clause on pageB to grab the records from the drop-down on pageA where it displays the departments and the addresses of each.
WHERE DepartmentID.DeptName LIKE '" & Request.Form("departments")
Now on pageC is there anyway to use a WHERE clause to grab the records from pageA, since the action on pageA is set to go to pageB??
What I need to do is to be able to list records on pageC that are employees of each department from each address.
First of all, why do you use a "like" as operator instead of "=" ?
Second: the sql statement you will need, depends or your db-design. So wa can't actually tell you what stetement you need.
I dont quite understand wy you should need the departement. (the screen A info). Each address wil be linked to only one departement, and each employee to 1 adress. No ?
So the adress alone should be anough to select all the employees. No?
Raf,
I knew this would be confusing. Where can I get help on this? It's kind of a long explanation. I would even be glad to pay somebody $50 to help me. I'm sure it wouldn't take an expert long at all to figure this out. I think it's just too long to describe here. Unless I attach a text file that explains everything, but I don't want to get anyone mad in this forum, so I'll wait to hear from you.
Ok, if anyone out there can take a look at the attached file and help me with what I am trying to do, it would be greatly appreciated. If you look at it and need more detail, please reply to the forum and I will give more detail.
I have been working on this for two weeks and am stuck.
2 option:
- change the db design (you’ll run into problems later on, if departments grow or a new superdepartment is created. This design isn’t flexible enough). What’s more, i can’t imagen there our thousands of departments or subdepartmens, and a table of a few hundred of records is peanuts for a db)
-select on subdeptID
My advice is to change the db design
Else,
On page 1 you select a department. Page two displays the addresses of the subdepartments (adress is a link to the amployees)
Page 1 should post the DeptID.
Page 2 selects all subdepartmens with that DeptID
Sql=select * from SubDepartments where DeptID = thedept”
Sql=replace(sql,”thedept”,request.querystring(“dept”,”’”,”’’”)))
(note: code is VBscript)
You display the adresses and the link,that holds the subdeptID
Raf,
You have what I am talking about almost correct. The SubDept is something that I haven't thought about doing anything with yet, because I'm so confused right at this point. You see the SubDept is actually a sub-department of the main department. So for example, the Aging Department has several locations (this has nothing to do with the SubDept)., then there are also sub-departments within Aging, like Nutrition Program.
So for now, I'm ignoring the SubDept issue.
So everything you told me to do, I would replace the SubDept for just DeptID (I think).
But I'm confused on a thing or two.
You say post the DeptID on page one. Now the first page (index.asp) has the drop-down menu of all the departments. How do you mean post the DeptID, in a hidden value?? And if that is what you mean, how do you do that?
So on the second page I have just the addresses of each department and the link to get to the employees from that address and that department. How would I do that? You see in just the Aging Department there are hundreds of people who work for that department, but maybe 80 of them work at one location. So when you click on the link, on page three it will display those 80 employees from that particular address.
So I don't know if this changes what you told me to do. I'll still mess with it here, but any other help you can give I would appreciate.
I'll give you a small example of what I need the pages to do, maybe that will be easier.
On page 1, the drop down-menu will have "Aging" in it. You select it and on page two you get:
Aging
303 Court St.
Olean, NY 14760
Click here to get individual listings
OR
1700 Lincoln Ave.
Little Valley, NY 14755
Click here to get individual listings
So then if you click on the first link - on page three you get a listing of employees from that address or if you click on the second address link, you get the employees from that address.
Hopefully you understand what I'm trying to do.
If you have any further help, I would appreciate it.
So the pageflow is:
Page 1, dropdown hit department --> addresses of department, link to employees --> select and display employees.
Hmm.
This LocationID in the PhoneNumbers table, that can be joined with the ID in the Towns table. OK
The Town1 etc in the Departments-table, those are text? So i presume those are the text from the towns table?
Well, it would be the easiest (and smartest) to save the Towns ID’s in the departmentstable (for numerous reasons like updatebillity and speed !)
Now what would then happen. You select the department. The departmentID is posted to the second page. Posted means you use the “post” method instead of the “get” method.
You then select the addresses and towns from the departmentstable
Sql = “Select adresses and towns from Departments where ID=theid”
Sql = replace(“sql”, “theid”, request.form(“dropdownname”)
Display the adresses and add a link to it with the town value in the querystring + you add the departmentsID to the querystring
Now how do you get thevalue for town1 under address1? Well 2 possibilitys:
- build an array and then proces that row by row;
- use a nested loop that checks for the last caracter.
When they hit the link, you select all employees with that departmentID and that town value as a Location.
Sql = “Select * from PhoneNumbers where DeptID=theid and Location=thelocation”
Sql = replace(“sql”, “theid”, request.querystrind(“dept”)
Sql = replace(“sql”, “thelocation”, request.querystring(“town”)
If you use the text (description) in the departmentstable, you need to change the label to the value first.
Select * from PhoneNumbers where DeptID=theid and Location IN (Select ID from towns where Name=’thelocation’)”
Sql = replace(“sql”, “theid”, request.querystrind(“dept”)
Sql = replace(“sql”, “thelocation”, request.querystring(“town”)
Raf,
If this is something that is too confusing and you know someone that is willing to do this for me for some cash, let me know. I'd liek to try and do this and whether it takes my database to be changed around or whatever, I'd like to complete this.
Raf,
I think this is over my head. I keep trying things and it's not working right, I'm getting errors and I want to scream!
Let's try another approach. I have attached a text file that shows my code for each page and maybe that will smooth things out. In there is also one record from each table so you can see how they are in there. I still think I may have confused you on some of the tables.
Now in the attached file you will see how my sql statements are on phoneresult.asp and ind_listing.asp. I have them the way they are because I used Access and these queries gave me the results I wanted.
I implemented the suggestions you made on the last post.
You will also notice that in the Departments table that I have fields for Town1, Town2, etc. I put just text in those fields instead of a number identifier because I couldn't seem to get the right Town to display with the code on the phoneresult.asp page. You'll notice in that code that I have "Town1" to display for the first listing, "Town2" for the second listing.
I'm sure this is part of the problem, but i'm not sure how to get it to work right.
So if you can help, I would really appreciate it. If not and you know someone that can help, again it would be appreciated.
You've missed some single quotes, added a few double quotes and forgot the & between the two variables.
Do you have this stuff running somewhere? I don't have time to set up a db and code the pages to try this out.
So uyou'll need to tell me what exactly doesn't work. Which errors when etc
Raf,
I fixed that but now I get a problem with my sql in the phone result.asp page. I just think that someone (an it doesn't have to be you) needs to look at this because something is definitely wrong.
I think the table fields and what code you are telling me to use are not in sync and maybe even a misunderstanding of what I need to be done.
The error I get now is:
No value given for one or more required parameters and I tried to chaneg the sql and nothing - still the same.
Like i said, I'd be glad to pay someone to look at this.
Before you post, read our: 
