Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Regular Coder
    Join Date
    Aug 2002
    Posts
    247
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sql action question

    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.

    Hope this isn't confusing.

    If anyone can help, I would appreciate it.

    Gary

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is very confusing.

    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?

  • #3
    Regular Coder
    Join Date
    Aug 2002
    Posts
    247
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Thanks

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Why would anyone mind if you attach a textfile of ten pages ?
    Those who want to look into it, will download it.
    So go ahead.

    To set you completely at easy:

    Please, don't get mad at him. I'm asking you nicely. raf

  • #5
    Regular Coder
    Join Date
    Aug 2002
    Posts
    247
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Thanks,
    Gary
    Attached Files Attached Files

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'll look in to it at lunchtime or this evening. (9.30 int he morning now). I need to draw it out to get my head around it.

    Looks to me you've overcompliceted your db. A star scheme design will probably be better. I'll let you know if i've found anything.

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK. I took a look at it.

    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

    <a href=""ind_listing.asp?subdep="&rsPhoneResult.Fields("SubdeptID")&""">

    On the last page, you select all emlployees with that subdeptID

    Sql=select * from PhoneNumbers where SubDeptID = thesubdept”
    Sql=replace(sql,”thedept”,request.querystring(“SubdeptID”,”’”,”’’”)))



    Nothing more to it then that.

    Or whasn't this the thing you wanted to do?

  • #8
    Regular Coder
    Join Date
    Aug 2002
    Posts
    247
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Gary

  • #9
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think i’m starting to understand 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

    <a href="ind_listing.asp?dept=<%=rsadresses.Fields("deptID")%>&town=<%=rsadresses.Fields("town1")%>">

    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”)


    Let me know if you still have questions/problems

  • #10
    Regular Coder
    Join Date
    Aug 2002
    Posts
    247
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Let me know.
    Gary

  • #11
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Did you try what i said in my last post?

    It's pretty straightforward. If you want, i can write it for you.
    Wat db? MySQL or Access?With PHP or ASP?

  • #12
    Regular Coder
    Join Date
    Aug 2002
    Posts
    247
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Thanks,
    Gary
    Attached Files Attached Files

  • #13
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    try replacing the link you wrote with this one

    response.write("<br><font size='2'>Click <a href='ind_listing.asp?thedept="& rsPhoneResult.Fields("DeptID") & "&town=" & rsPhoneResult.Fields("Town1") & "'><b>here</b></a> for Individual Listings</font>")


    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

  • #14
    Regular Coder
    Join Date
    Aug 2002
    Posts
    247
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    You tell me what to do cause I'm stuck.

  • #15
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    send me the complete code of that page + what fields from which tables need to be returned, and i'll have a look at it.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •