asp and sql table join recortset problem

Posted on 2009-02-14
Last Modified: 2012-05-06

I have two tables in an Access db; 'Projects' and 'Vacant'. The tables are
joined by a FK vacantID.

I want to show a link on my classic asp page only if a 'Project' record has a
relational record in the Vacant Table.

I am confused as to what syntax to use in my SQL, asp or both? Can anyone
assist please?

Recordset call is currently set to

SELECT projectID, vacantID
FROM Vacant

My ASP uses the following:

IF (project.Fields.Item("vacantID").Value) <> "" Then%>

Present Link

<%End IF%>

Open in new window

Question by:gmaddockgreene
    LVL 25

    Accepted Solution

    SQL construct below -

    SELECT DISTINCT P.projectID, ISNULL(V.vacantID, '') AS vacantID
    FROM Projects P
    LEFT JOIN Vacant V ON V.projectID = P.projectID

    The SQL above will return a recordset where vacantid will be blank if no related record is available in the Vacant table.

    Open the recordset and loop through till not recordset.eof and you ASP code above should be fine..

    LVL 6

    Author Comment


    Thank you for your help. Your suggestion certainly assisted in so much that could learn some more about the join syntax however I am really struggling to get this working. Using your suggested SQL and my asp below I can only get as far as getting a 'Wrong number of arguments used with function in query expression 'ISNULL(V.vacantID, '')'. error.

    I fear I am getting lost here! ;)
    IF (rsJoin.Fields.Item("vacantID").Value)<> "" Then%>
    <td><a href="vacant_properties.asp?projectID=<%=(rsJoin.Fields.Item("projectID").Value)%>">Available property</a>
    <%End IF%>

    Open in new window

    LVL 6

    Author Comment


    Actually I have now solved my problem. Thank you for your sql guidance, whithout which I would not have solved this problem.

    I needed to use the following syntax when using Access db:

    IIF(V.vacantID IS NULL, 1, 0, ) replacing your ISNULL(V.vacantID, '')

    Thanks for your assistance.

    IF (rsJoin.Fields.Item("vacantID").Value) =0 Then%>
    <td><a href="vacant_properties_t11.asp?projectID=<%=(rsJoin.Fields.Item("projectID").Value)%>">available property </a>
    <%End IF%>

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    In this tutorial I will show you how to make a simple HTML bar chart with the usage of WhizBase, If you want more information about WhizBase please read my previous articles at (http://www.experts-ex…
    I hope you'll find this tutorial useful and interesting. So let's try to extend Tcl with a new package.  For anyone more deeply interested please check out the book "Practical Programming in Tcl and Tk". It's really one of the best written books abo…
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    The viewer will learn how to dynamically set the form action using jQuery.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now