HELP asp recordset problem

Hello!

I've been wristling with a problem in my website; it's asp with access database (mdb)

I have 2 tables

tblSchools
with:

schoolNr
schoolName
schoolAdress
schoolPostcode

tblClass

schoolNr
class (string with class name, like bussness, scientics, languages, sports, art, ...)
grade (integer with 1-3)

I get search form POST fields using asp's response object:
class = request.forms("txtGrade") = textfield to enter a classname
grade = request.forms("cmbGrade") = combobox with options 1,2,3,all
postcode = request.forms("txtPostcode") = textfield to enter postcode obious

I now want a recordset with the results from this search.

I make a new object cnnDB and connect with my db
I populate a recordset (ADODB) with a query
strSql= "Select schoolNr from tblClass where ucase(class) like '%" & Ucase(class) & "%' from tblClass"
Set rstClasses = cnnDB.execute (strSql)

Now I have a recordset with all the schoolNr's who have this class.

What i want is all the schools with the postcode = postcode searched

I can set another recordset like above with all the schools in this postcode

but how can I combine the results from these to recordsets, so that i have a recordset with only the schoolNr's who are in both recordsets??

Or schould i try another appraoch?

Hope i clearly describe my problem,
thank's in advance

greetz, KvD

You just write one SQL statement that incorporates both cases:
strSql= "SELECT tblSchools.schoolNr " & _
"FROM tblSchools INNER JOIN tblClass ON tblSchools.schoolNr = tblClass.schoolNr " & _
"WHERE UCase(tblClass.class) LIKE '%" & UCase(class) & "%' " & _
"AND tblSchools.schoolPostcode = '" & postcode & "'"
You may want to touch up a bit on your SQL because your original SQL statement in your post is wrong. The correct way would be:
strSql= "SELECT tblSchools.schoolNr " & _
"FROM tblSchools INNER JOIN tblClass ON tblSchools.schoolNr = tblClass.schoolNr " & _
"WHERE UCase(tblClass.class) LIKE '%" & UCase(class) & "%'"
Hope that gets you on your way.

-Shane

thx for your reply

i never used inner join before, i learn sql on my own...

now i get following error:

Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/design/zoek.asp, line 136

But when I response.write the strSql it says
SELECT * FROM tblSchools INNER JOIN tblClass ON tblSchools.schoolNr = tblClass.schoolNr WHERE UCase(tblClass.class) LIKE '%SPORT%' AND tblSchools.schoolPostcode = '8500'

So it passes the form values but says no value given...

grtz

double post

Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

/design/zoek.asp, line 136

But when I response.write the strSql it says
SELECT * FROM tblSchools INNER JOIN tblClass ON tblSchools.schoolNr = tblClass.schoolNr WHERE UCase(tblClass.class) LIKE '%SPORT%' AND tblSchools.schoolPostcode = '8500'
Always make sure to include the line in your code that is specified in the error. What is line 136?

The error is saying that the function you're attempting to use on line 136 is expecting a value for a required parameter. I can't know what is required without seeing the function that you're using. It could possibly be that since you've used the * to select all in your SQL, if you use rs("schoolNr"), the database doesn't know if you're selecting the schoolNr field from the tblClass table or tblSchools table. You have to specify: rs("tblSchools.schoolNr"). That's why it's always best to avoid using the * and specify each field you want to select, even though that can be tedious.

-Shane

thx for the reply

my bad, i misnamed a field (i speak another language and use other field names than these i provide here so i can't copy/paste)

but i've replaced all the code that i've been using before (with loops and so on) with these INNER JOIN but now i'm wondering how to INNER JOIN 3 tables ?

SELECT fields FROM table 1 INNER JOIN table2 ON table1.nr = table2.nr INNER JOIN table3 ON table1.nr = table3.nr

doesn't work

greetz

SELECT fields
FROM (table 1 INNER JOIN table2 ON table1.nr = table2.nr) INNER JOIN table3 ON table1.nr = table3.nr
The above should work. All I did was put parentheses around the first join. If that isn't it, then you please specify exactly what's not working.

Now, in my examples, I was using INNER JOIN and it worked for what you were trying to do. But just know that there are other kinds of JOINs out there, like LEFT JOIN and RIGHT JOIN. LEFT JOIN and RIGHT JOIN are basically the same however:
SELECT field FROM table1 LEFT JOIN table2 ON table1.key = table2.key
...is the same as...
SELECT field FROM table2 RIGHT JOIN table1 ON table2.key = table1.key
Notice I just changed which tables I selected first and second.

The difference between INNER JOIN and LEFT/RIGHT JOIN is that for INNER JOIN, you will only get results where both tables match on those keys, and for LEFT/RIGHT JOIN, the table that is on the left (for LEFT JOIN) or on the right (for RIGHT JOIN) will get ALL results, regardless if they match with the other table.

You may have to do some reading to get a little more familiar with what I just said, hehe. Good luck!

-Shane