Hi there!

I'm trying to get this SQL statement to work but I'm not sure the syntax. I'm calling on an Access database using ASP.

<%
sql = "SELECT Survey.Topic, Survey.ExpertId, Survey.[Last Name], Survey.[First Name] FROM Survey INNER JOIN [TopicTable] ON TopicTable.TopicID = [Survey].Topic WHERE (((TopicTable.TopicID)=" & Request.QueryString("TopicID") & ")) "

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn
%>

Ok...this code works but what I want to do is have like an OR that would do something like this (this code doesn't work )

<%
sql = "SELECT Survey.Topic, Survey.ExpertId, Survey.[Last Name], Survey.[First Name] FROM Survey INNER JOIN [TopicTable] ON TopicTable.TopicID = [Survey].Topic OR TopicTable.TopicID = [Survey].Topic2 OR TopicTable.TopicID = [Survey].Topic3 OR TopicTable.TopicID = [Survey].Topic4 OR TopicTable.TopicID = [Survey].Topic5 WHERE (((TopicTable.TopicID)=" & Request.QueryString("TopicID") & ")) "

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn
%>

What I've bolded is what I want but I need the correct syntax. I want to be able to check and see if TopicTable.TopicID equals Survey.Topic, Survey.Topic2, Survey.Topic3, Survey.Topic4, or Survey.Topic5.

Can this even be done...I think it can...I don't use INNER JOIN often.

Hopefully this makes sense...

Thanks!

i am not sure with access but you could try this:

TopicTable.TopicID = (Survey.Topic OR Survey.Topic2 OR Survey.Topic3 OR Survey.Topic4 OR Survey.Topic5)

or

TopicTable.TopicID = ((Survey.Topic) OR (Survey.Topic2) OR (Survey.Topic3) OR (Survey.Topic4) OR (Survey.Topic5))

that could work or may not

Thanks...it's still not working but at least I'm getting a different error.

Error Type:
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.
/News_Events/NSCC_Experts/qry_by_topic.asp, line 38

What I'm trying to set up is on one page a listing of "topics"; when you click on the topic it will give you a list of people who belong to that topic. I want to allow any given person to belong to a max of 5 topics and them to be listed accordingly.

Right now the topics listing works. If I only allow for one topic per person the whole thing works but I need to allow for them to possibly be under 5 categories.

It's probably something really simple I'm not doing right.

heh maybe i should stick to php

i am not sure then maybe some one who knows asp/access can help.

Thanks for trying

Have you tried an "IN" statement?

"SELECT Survey.Topic, Survey.ExpertId, Survey.[Last Name], Survey.[First Name] FROM Survey WHERE " & Request.QueryString("TopicID") &" IN ([Survey].Topic, [Survey].Topic2, [Survey].Topic3, [Survey].Topic4, [Survey].Topic5)"

Works in some databases ... not sure about access though ... can't remember now ...

I'm still getting the "No value" error. I'm thinking it's a problem with my database.

I have two tables. One called TopicsTable and one called Survey. In TopicsTable there are 2 fields used TopicID and Topic in Survey there are five fields called Topic, Topic2, Topic3, Topic4, and Topic5. Each of these are linked to the TopicTable's TopicID and Topic.

Have you checked that you're passing a "TopicID" parameter in to the page?

eg: http://...../News_Events/NSCC_Expert....asp?TopicID=3

You could maybe try hard-coding the SQL first with a topic ID that you know exists.

My advice is to try this SQL statement in your database directly ...
Once you will get it to work, try calling from ASP page.
If you are using SQL Server, I would also recommend using Stored Procedures for complicated queries.

Originally posted by jonnyj
Have you checked that you're passing a "TopicID" parameter in to the page?

eg: http://...../News_Events/NSCC_Expert....asp?TopicID=3

You could maybe try hard-coding the SQL first with a topic ID that you know exists.

I have a topics listing that has each topicID linked this way and this is still what i'm getting. If I only call on one Topic field in my Survey database it works. I can change the Topic field to Topic2 or Topic3 etc and everything works fine. The only time I get this error is when I'm trying to use this "OR" type statement.

Maybe this isn't how I should be doing this. What I want to do is have each topic listed (e.g. Technology) and in this topic I want a list of people who have this topic tied to them. I can get this working but if i want someone tied to more than one topic (e.g Technology, New Media, Applied Arts, Geomatics, and Construction) it gives me this error.

It's still a bit hard without seeing the whole database, but a more standard and more normalised (to get all DBA-like on you) way of handling the multiple topicIDs in the survey would be to have them in another table. This would also mean you could have as many topics as you wanted in the survey and not be limited.

i.e. remove the various topic1, topic2, topic3 columns from the survey table and insert each one as a separate row in a new table (surveyTopics or something) which just has two foreign keys - surveyID and topicID.

so your sql would be something like:

SELECT
Survey.Topic,
Survey.ExpertId,
Survey.[Last Name],
Survey.[First Name]
FROM Survey
JOIN SurveyTopics ON
SurveyTopics.SurveyID = Survey.surveyID
JOIN TopicTable ON
TopicTable.TopicID = SurveyTopics.TopicID
WHERE TopicTable.TopicID =" & Request.QueryString("TopicID")


This keeps the SQL simple(ish) and allows you an arbitrary amount of topics in your survey without changing the database stucture.