Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

asp and sql table join recortset problem

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

0
gmaddockgreene
Asked:
gmaddockgreene
  • 2
1 Solution
 
reb73Commented:
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..

0
 
gmaddockgreeneAuthor 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

0
 
gmaddockgreeneAuthor 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

0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now