SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Dec 2000
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ASP construction from DB with lookup

    If, by the end of the time that you read this you aren't totally and completely confused, then see if you can offer a suggestion.

    I need to construct an ASP page that links to an Access database. The database has two tables: Employees and Branches. There is a lookup column on the Employees table that looks up the Branch Number from the Branches table.

    The Branch table contains the branch information (address, phones, etc) and the Employees table contains employee information for each branch. There is one listing per branch on the Branches table, and as many as 25 (per branch) on the Employees table.

    I need to output this information via ASP (or a FrontPage Database Results) to produce the sample below.



    So, my question is how the hell do I do it? I can only seem to get one table going. Do I need a merged table in Access?

    I'll answer whatever questions you might have as best as I can.

    I saw the most recent DB thread, but it didn't immediately appear to answer my questions.
    Amber Chiang

  2. #2
    SitePoint Evangelist azizur_rahman's Avatar
    Join Date
    Nov 2001
    Location
    London, UK
    Posts
    502
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ON you SQL Query uses
    Code:
    SELECT * FROM Employees, Branches
     WHERE
    Employees.Branch_Number = Branches.Branch_Number
    or something like that....

    check if that works if does not than send the SQL query over I'll show you how to do that...


    Hope that helps
    Azizur Rahman
    Web Application Developer

  3. #3
    SitePoint Member
    Join Date
    Dec 2000
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for the help, azizur_rahman. My problem is that I am the worst with SQL (not enough time to practice, I guess), and I'm relying on FP2000's Database Results Wizard to get this done.

    When I enter the code you so generously provided, I am returned a "too few parameters" error. I'm sure there's more I need, but I have no idea what it is.
    Amber Chiang

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,249
    Mentioned
    112 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by amberc
    Thank you for the help, azizur_rahman. My problem is that I am the worst with SQL (not enough time to practice, I guess), and I'm relying on FP2000's Database Results Wizard to get this done.

    When I enter the code you so generously provided, I am returned a "too few parameters" error. I'm sure there's more I need, but I have no idea what it is.
    It sounds like something didn't get populated properly when you copied his SQL query over. Perhaps you didn't have field names spelled properly?

    If you can give us table layouts, I'm sure someone will be able to help you out...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  5. #5
    SitePoint Member
    Join Date
    Dec 2000
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the tip. I did check that out, and there was an error on my side. Upon repair, I was returned the error below.

    I've uploaded the database for you to see the construction. Do I need to get rid of the lookup column within the DB since the SQL shows an equals statement?

    Database

    ____________

    Server error: Unable to retrieve schema information from the query:

    SELECT * FROM Employees, Branches

    WHERE

    Employees.BranchNumber = Branches.BranchNumber

    against a database using the connection string

    DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/tg_6f.mdb.

    The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
    -------------------------------------------------------
    [Microsoft][ODBC Microsoft Access Driver] Type mismatch in expression.

    Source: Microsoft OLE DB Provider for ODBC Drivers
    Number: -2147467259 (0x80004005)
    Amber Chiang

  6. #6
    SitePoint Evangelist azizur_rahman's Avatar
    Join Date
    Nov 2001
    Location
    London, UK
    Posts
    502
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think this may be of any help:

    visit: http://www.asp101.com/samples/viewas...etstring%2Easp

    The SQL query is correct its somehting to do with the connection string or the Driver that you've installed. try visiting that page and see if it works...

    let me know... I'll make code page that wroks and I'll sent it to you.
    Azizur Rahman
    Web Application Developer

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,249
    Mentioned
    112 Post(s)
    Tagged
    1 Thread(s)
    Make sure that BranchNumber is defined the same way on both tables (or if one is an autonumber field, the other is defined as a number)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  8. #8
    SitePoint Member
    Join Date
    Dec 2000
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help, guys. I just found a problem with my DB setup. Once I have that fixed, I'll repost.
    Amber Chiang

  9. #9
    SitePoint Member
    Join Date
    Dec 2000
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've made the DB repair, and now I have output without an error. My next quandary is how to get each branch to list only once, while all the employees list below. Currently, it lists:

    branch
    employee

    branch again
    new employee

    branch yet again
    another new employee

    I'd like

    branch
    employee
    employee
    employee

    And, how do I supress fields with no information?

    Thanks!
    Amber Chiang


Bookmarks

Posting Permissions

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