We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

asp and sql table join recortset problem

Medium Priority
421 Views
Last Modified: 2012-05-06
Hi,

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

Comment
Watch Question

Commented:
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..

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
reb73,

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! ;)
<tr>
<% 
IF (rsJoin.Fields.Item("vacantID").Value)<> "" Then%>
<td><a href="vacant_properties.asp?projectID=<%=(rsJoin.Fields.Item("projectID").Value)%>">Available property</a>
</td>
<%End IF%>
</tr>

Open in new window

Author

Commented:
reb73,

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.

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

Open in new window

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.