Hello all!

I am having a hard time getting my database to work properly. I am trying to search multiple rows and return presentations based upon that row. Currently I get the first choice from "FootCare" no matter which option I choose in my form. How do I deliminate these so it searches each one and moves onto the next subject? I am using ASP and using an ACCESS database.

SQL="SELECT Presentations FROM Speakers WHERE Subjects = 'Skeletal';"
SQL="SELECT Presentations FROM Speakers WHERE Subjects = 'Surgical';"
SQL="SELECT Presentations FROM Speakers WHERE Subjects = 'FootCare'"

I think I will have one other bug, but I am not sure if it is connected to this problem.

Thank you!

The first two lines are ignored, because you keep resetting the value of your SQL variable to a new query. This wipes out what used to be there. You would need to process the query after setting it, before setting it to something different.

That would be silly though. Instead, combine the three queries into one, and combine your where clauses with OR statements.

Okay and that is what I thought the change would be. SO I now have it written:

SQL="SELECT Presentations FROM Speakers WHERE Subjects = 'Skeletal' OR 'Surgical' OR 'FootCare'"

I still get it returning only the first option from FootCare which is say "Blisters" on every option I pick from the drop down.

So I am going to guess that the bug is in this bit of code.

Response.write "<colgroup>"
Response.Write("<table border='1'><tr>")
Response.write "<col width=120>"
Response.write "</colgroup>"


For i = 0 to recordset.Fields.Count - 1
Response.Write("<td>" & recordset(i) & "</td>")

Next

response.Write("</tr></table>")

And I know it is horribly written so if anyone could help me out with this I would appreciate it.

Thank you for your fast response.

For starters, you might want to move on to the next record in the recordset?

Yeah that helped, I rewrote that section of the code that had been butchered one too many times. I now get a list of everything in the database for each time I select an option in the dropdown. I guess the good news is that it works fast!

Thank you for the help. I will revist this new problem tomorrow after some sleep.

Okay I have everything working perfectly except when I do my sql search it returns all the results instead of just the one choice I want. Can anyone tell me why my SQL line returns all the options instead of just one value?

Once again I am using ASP and Access:

SQL="SELECT Presentations FROM Speakers WHERE Subjects = 'Skeletal' OR 'Surgical' OR 'FootCare' OR 'DiseaseManagement' OR 'GeneralMedicine' OR 'VaricoseVeins' OR 'BreastCancer' OR 'MedicalMission'"

Thank you for the help!

Alright I was making it too complicated. Rethought it and everything works!

Here is a teaser for anyone having the same problem in the future.

SQL="SELECT * FROM Speakers"

If Request.Form("Subjects") = "Skeletal" Then
SQL = SQL & " WHERE Subjects = 'Skeletal'"
End If

If Request.Form("Subjects") = "FootCare" Then
SQL = SQL & " WHERE Subjects = 'FootCare'"
End If

etc....

Good luck

Good work! I'm glad you figured it out. Let us know if anything else comes up - it's always more interesting to help someone who's actually putting in some effort and learning.

Here's a tip for you: IN. You'll love it, if you can do some string manipulation to make use of it. It's basically a way to shortcut really long OR blends.

SELECT * FROM Speakers WHERE Subjects IN ('Skeletal', 'FootCare', 'Surgical')

Try that, it's the same as the way you're building a long OR clause, only it's a lot easier to work with.

ARGH!! I tried that line and used a semicolon instead of a comma.

Thanks for the tip!