Results 1 to 8 of 8

Thread: SQL help!

  1. #1
    Join Date
    Feb 2008
    Posts
    4

    Unanswered: SQL help!

    Hi all

    Was hoping someone could shed some light on this

    I am running a website for job recruitment. I want the recruiter to be able to view the applications that are sitting in an 'application' table in my db.

    To make it clear here are my tables

    JobSeeker
    JobRecruiter
    Application
    Jobs

    I have a text box which he puts in say his Company name and it looks and mathces it in the Jobs table. I then want this to match his RecruiterID(on my site the users do not know their autonumber ID)in the 'Jobs'table as well. Then do a join to the RecruiterID in the 'Application' table which then i would like everything to then be displayed in the 'Application' as long as its the match to the recruiter. I keep getting the folliwng...........................

    Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

    /mkafar/JobRecruiters/posted_applications-jr.asp, line 59

    Below is the asp with the sql which was done in access. Im having trouble and just cant get it right.

    Any suggestions please

    <%
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Mode = adModeReadWrite
    conn.open "driver={Microsoft Access Driver (*.mdb)};" & _
    "dbq=" & Server.MapPath("../db/job_system2V9.MDB")

    user_posted_applications= Request.Form("user_posted_applications")

    sql = "SELECT FROM Jobs WHERE Company='" & user_posted_applications & " AND Jobs.RecruiterID AS Jobs_RecruiterID, Application.RecruiterID AS Application_RecruiterID, Application.ApplicationID, Application.SeekerName, Application.SeekerSurname, Application.DateofBirth, Application.SeekerID, Application.Qualifications, Application.PreviousEmployer, Application.Reference, Application.[Reference Telephone], Application.[Personal Description], Application.[Contact Email], Application.JobID FROM Jobs INNER JOIN Application ON Jobs.JobID = Application.JobID;"
    Set RS = Server.CreateObject ("ADODB.Recordset")
    RS.Open sql, conn, 3, 3

    do until RS.EOF
    for each x in RS.Fields
    Response.Write(x.name)
    Response.Write(" = ")
    Response.Write(x.value & "<br />")
    next
    Response.Write("<br />")
    RS.MoveNext
    loop

    rs.close
    conn.close
    %>

    Will be ever so gratefull if someone could help

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Application is a reserved word. Everywhere you have used Application to refer to a table should be enclosed in square brackets.

    [Application].RecruiterID for example.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The SQL is completely invalid. You have 2 FROM clauses, no field list (or a field list out of place), a listing of fields after the WHERE clause, and the single quote in the WHERE clause is either wrong or you need another after the value if it's text. Here's a shortened version that might show the problems:

    SELECT FROM Jobs WHERE Company='whatever AND fields FROM Jobs INNER JOIN Application ON Jobs.JobID = Application.JobID;

    The proper syntax:

    SELECT...
    FROM...INNER JOIN...
    WHERE...
    Paul

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    That too ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Feb 2008
    Posts
    4
    Thanks for the help. Will sort it straight away :-)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pbaldy
    The proper syntax:

    SELECT...
    FROM...INNER JOIN...
    WHERE...
    dude!

    the proper syntax:

    SELECT...
    FROM...INNER JOIN...
    ON...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by r937
    dude!

    the proper syntax:

    SELECT...
    FROM...INNER JOIN...
    ON...
    Well, I considered the ON as part of the INNER JOIN, but if it means that much to you, you are correct. The relevant point to this thread was that the WHERE clause had to be after the FROM...INNER JOIN...ON.
    Paul

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    all is forgiven, then
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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