Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    kvd
    kvd is offline
    New to the CF scene
    Join Date
    May 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question 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

  • #2
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    You just write one SQL statement that incorporates both cases:
    Code:
    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:
    Code:
    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

  • #3
    kvd
    kvd is offline
    New to the CF scene
    Join Date
    May 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Last edited by kvd; 05-16-2007 at 06:49 PM.

  • #4
    kvd
    kvd is offline
    New to the CF scene
    Join Date
    May 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    double post
    Last edited by kvd; 05-16-2007 at 06:49 PM.

  • #5
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    Quote Originally Posted by kvd View 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

  • #6
    kvd
    kvd is offline
    New to the CF scene
    Join Date
    May 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #7
    Senior Coder TheShaner's Avatar
    Join Date
    Sep 2005
    Location
    Orlando, FL
    Posts
    1,126
    Thanks
    2
    Thanked 40 Times in 40 Posts
    Code:
    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:
    Code:
    SELECT field FROM table1 LEFT JOIN table2 ON table1.key = table2.key
    ...is the same as...
    Code:
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •