Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Help! !

Ok, I've a sign up form that has some checkbox. Each check box has one style of music (like pop, rock, dance, techno, etc) and the user has to choose the styles that he likes most.

Ok, then I get all the data (name, city, country and music style) and put it into a DB

In DB, the result appears like "1, 3, 4" for example... in this example the value 1 = rock, 3 = pop, 4 = techno

But I wanna make a search form... in this search form, the user can choose to search for people that likes the same music style.. for example the user wants to search for people who likes rock and techo (1,4)

How would be the SQL string for this ??


I hope someone can help me...


Thanks !


Comments

  • ManningManning Posts: 1,621Member
    [b][red]This message was edited by Manning at 2003-12-12 12:30:46[/red][/b][hr]
    : Ok, I've a sign up form that has some checkbox. Each check box has one style of music (like pop, rock, dance, techno, etc) and the user has to choose the styles that he likes most.
    :
    : Ok, then I get all the data (name, city, country and music style) and put it into a DB
    :
    : In DB, the result appears like "1, 3, 4" for example... in this example the value 1 = rock, 3 = pop, 4 = techno
    :
    : But I wanna make a search form... in this search form, the user can choose to search for people that likes the same music style.. for example the user wants to search for people who likes rock and techo (1,4)
    :
    : How would be the SQL string for this ??
    :
    :
    : I hope someone can help me...

    Oops didn't meant to post that so fast..anyway..

    What you have there is a many to many relationship because one person could like many styles of music, and one style of music could be liked by many people. What I would do is this:

    Have a user table. I'm assuming you already have this, and that it has a unique identifier of some sort (preferably a UserID)

    Have a genre table. Each style of music should have an entry in this table, again each should have a unique ID.

    Have a UserGenre table. This table will only have two columns, a UserID and GenreID, both foreign keys to the above tables, and when combined they form a primary key. Every user should have one or more records in this table to indicate which styles of music they like.

    This means your ASP is going to have to change a bit. When you insert the users style selections, you'll use something like:

    [code]
    for I = 1 to Request.Form("Styles").Count
    SQL = "INSERT INTO UserGenre (UserID, GenreID) VALUES ([red]UserID[/red], " & Request.Form("Styles")(I) & ")"
    Conn.Execute(SQL)
    next
    [/code]

    I marked UserID in red because it should be the value of the current user's ID, not the actual string "UserID"

    And now finally for the search feature, you would use this:

    [code]
    SQL = "SELECT DISTINCT UserID FROM UserGenre WHERE GenreID IN (" & Request.Form("SearchStyles") & ")"
    Conn.Execute(SQL)
    [/code]

    That'll give the UserID of any user that has at least one style in common with the ones listed in Request.Form("SearchStyles")

    All this was done without any testing, so there may be errors, but that's the gist of how I would set things up. If you want to keep the user's style selections as a text column in the user table, that's still possible, but IMO it'll complicate things compared to this layout.
  • marcoskn1marcoskn1 Posts: 61Member
    That's the reason I love this forum and recommend this to others.. the answers are very fast :-) !!

    Well Manning, I tought many ways to do that, and one of them was like this you suggested, but I didn't know how would be the SQL string..

    I'll try this one you wrote

    Thanks a lot !!


    : [b][red]This message was edited by Manning at 2003-12-12 12:30:46[/red][/b][hr]
    : : Ok, I've a sign up form that has some checkbox. Each check box has one style of music (like pop, rock, dance, techno, etc) and the user has to choose the styles that he likes most.
    : :
    : : Ok, then I get all the data (name, city, country and music style) and put it into a DB
    : :
    : : In DB, the result appears like "1, 3, 4" for example... in this example the value 1 = rock, 3 = pop, 4 = techno
    : :
    : : But I wanna make a search form... in this search form, the user can choose to search for people that likes the same music style.. for example the user wants to search for people who likes rock and techo (1,4)
    : :
    : : How would be the SQL string for this ??
    : :
    : :
    : : I hope someone can help me...
    :
    : Oops didn't meant to post that so fast..anyway..
    :
    : What you have there is a many to many relationship because one person could like many styles of music, and one style of music could be liked by many people. What I would do is this:
    :
    : Have a user table. I'm assuming you already have this, and that it has a unique identifier of some sort (preferably a UserID)
    :
    : Have a genre table. Each style of music should have an entry in this table, again each should have a unique ID.
    :
    : Have a UserGenre table. This table will only have two columns, a UserID and GenreID, both foreign keys to the above tables, and when combined they form a primary key. Every user should have one or more records in this table to indicate which styles of music they like.
    :
    : This means your ASP is going to have to change a bit. When you insert the users style selections, you'll use something like:
    :
    : [code]
    : for I = 1 to Request.Form("Styles").Count
    : SQL = "INSERT INTO UserGenre (UserID, GenreID) VALUES ([red]UserID[/red], " & Request.Form("Styles")(I) & ")"
    : Conn.Execute(SQL)
    : next
    : [/code]
    :
    : I marked UserID in red because it should be the value of the current user's ID, not the actual string "UserID"
    :
    : And now finally for the search feature, you would use this:
    :
    : [code]
    : SQL = "SELECT DISTINCT UserID FROM UserGenre WHERE GenreID IN (" & Request.Form("SearchStyles") & ")"
    : Conn.Execute(SQL)
    : [/code]
    :
    : That'll give the UserID of any user that has at least one style in common with the ones listed in Request.Form("SearchStyles")
    :
    : All this was done without any testing, so there may be errors, but that's the gist of how I would set things up. If you want to keep the user's style selections as a text column in the user table, that's still possible, but IMO it'll complicate things compared to this layout.
    :

  • marcoskn1marcoskn1 Posts: 61Member
    How would be the SQL string if I want to match others interests. Like besides Genre table, I also have Users table...

    If someone wants to search besides the Style of music, also wants to search for the same city...

    how it would be?

    SELECT DISTINCT * FROM genre INNER JOIN users WHERE genre IN (1, 2) AND users.city = 'sao paulo'

    Am I in the right way ?? Because it says there is an error...


    thanks!



    : That's the reason I love this forum and recommend this to others.. the answers are very fast :-) !!
    :
    : Well Manning, I tought many ways to do that, and one of them was like this you suggested, but I didn't know how would be the SQL string..
    :
    : I'll try this one you wrote
    :
    : Thanks a lot !!
    :
    :
    : : [b][red]This message was edited by Manning at 2003-12-12 12:30:46[/red][/b][hr]
    : : : Ok, I've a sign up form that has some checkbox. Each check box has one style of music (like pop, rock, dance, techno, etc) and the user has to choose the styles that he likes most.
    : : :
    : : : Ok, then I get all the data (name, city, country and music style) and put it into a DB
    : : :
    : : : In DB, the result appears like "1, 3, 4" for example... in this example the value 1 = rock, 3 = pop, 4 = techno
    : : :
    : : : But I wanna make a search form... in this search form, the user can choose to search for people that likes the same music style.. for example the user wants to search for people who likes rock and techo (1,4)
    : : :
    : : : How would be the SQL string for this ??
    : : :
    : : :
    : : : I hope someone can help me...
    : :
    : : Oops didn't meant to post that so fast..anyway..
    : :
    : : What you have there is a many to many relationship because one person could like many styles of music, and one style of music could be liked by many people. What I would do is this:
    : :
    : : Have a user table. I'm assuming you already have this, and that it has a unique identifier of some sort (preferably a UserID)
    : :
    : : Have a genre table. Each style of music should have an entry in this table, again each should have a unique ID.
    : :
    : : Have a UserGenre table. This table will only have two columns, a UserID and GenreID, both foreign keys to the above tables, and when combined they form a primary key. Every user should have one or more records in this table to indicate which styles of music they like.
    : :
    : : This means your ASP is going to have to change a bit. When you insert the users style selections, you'll use something like:
    : :
    : : [code]
    : : for I = 1 to Request.Form("Styles").Count
    : : SQL = "INSERT INTO UserGenre (UserID, GenreID) VALUES ([red]UserID[/red], " & Request.Form("Styles")(I) & ")"
    : : Conn.Execute(SQL)
    : : next
    : : [/code]
    : :
    : : I marked UserID in red because it should be the value of the current user's ID, not the actual string "UserID"
    : :
    : : And now finally for the search feature, you would use this:
    : :
    : : [code]
    : : SQL = "SELECT DISTINCT UserID FROM UserGenre WHERE GenreID IN (" & Request.Form("SearchStyles") & ")"
    : : Conn.Execute(SQL)
    : : [/code]
    : :
    : : That'll give the UserID of any user that has at least one style in common with the ones listed in Request.Form("SearchStyles")
    : :
    : : All this was done without any testing, so there may be errors, but that's the gist of how I would set things up. If you want to keep the user's style selections as a text column in the user table, that's still possible, but IMO it'll complicate things compared to this layout.
    : :
    :
    :

  • ManningManning Posts: 1,621Member
    I don't know how you made your tables, but if you did it the way I suggested then you can't directly join the Genre and Users tables, you need to use the 3rd GenreUsers table I mentioned. I never learned the INNER JOIN syntax, so I join the tables the old way. If you want to use INNER JOIN you'll have to change part of the query. But here is what I would use:

    [code]
    SELECT DISTINCT U.*
    FROM GenreUser GU, Users U
    WHERE GU.UserID = U.UserID <== This joins the tables
    AND GU.GenreID IN (1, 2) <== This finds similar music interests
    AND U.City = 'Sao Paulo' <== This limits to the same city
    AND U.UserID <> [red]SEARCH_USER_ID[/red] <== This makes sure the
    user who is searching is
    not returned as a match
    [/code]

    Not tested, but it should work.


    : How would be the SQL string if I want to match others interests. Like besides Genre table, I also have Users table...
    :
    : If someone wants to search besides the Style of music, also wants to search for the same city...
    :
    : how it would be?
    :
    : SELECT DISTINCT * FROM genre INNER JOIN users WHERE genre IN (1, 2) AND users.city = 'sao paulo'
    :
    : Am I in the right way ?? Because it says there is an error...
    :
    :
    : thanks!
    :
    :
    :
    : : That's the reason I love this forum and recommend this to others.. the answers are very fast :-) !!
    : :
    : : Well Manning, I tought many ways to do that, and one of them was like this you suggested, but I didn't know how would be the SQL string..
    : :
    : : I'll try this one you wrote
    : :
    : : Thanks a lot !!
    : :
    : :
    : : : [b][red]This message was edited by Manning at 2003-12-12 12:30:46[/red][/b][hr]
    : : : : Ok, I've a sign up form that has some checkbox. Each check box has one style of music (like pop, rock, dance, techno, etc) and the user has to choose the styles that he likes most.
    : : : :
    : : : : Ok, then I get all the data (name, city, country and music style) and put it into a DB
    : : : :
    : : : : In DB, the result appears like "1, 3, 4" for example... in this example the value 1 = rock, 3 = pop, 4 = techno
    : : : :
    : : : : But I wanna make a search form... in this search form, the user can choose to search for people that likes the same music style.. for example the user wants to search for people who likes rock and techo (1,4)
    : : : :
    : : : : How would be the SQL string for this ??
    : : : :
    : : : :
    : : : : I hope someone can help me...
    : : :
    : : : Oops didn't meant to post that so fast..anyway..
    : : :
    : : : What you have there is a many to many relationship because one person could like many styles of music, and one style of music could be liked by many people. What I would do is this:
    : : :
    : : : Have a user table. I'm assuming you already have this, and that it has a unique identifier of some sort (preferably a UserID)
    : : :
    : : : Have a genre table. Each style of music should have an entry in this table, again each should have a unique ID.
    : : :
    : : : Have a UserGenre table. This table will only have two columns, a UserID and GenreID, both foreign keys to the above tables, and when combined they form a primary key. Every user should have one or more records in this table to indicate which styles of music they like.
    : : :
    : : : This means your ASP is going to have to change a bit. When you insert the users style selections, you'll use something like:
    : : :
    : : : [code]
    : : : for I = 1 to Request.Form("Styles").Count
    : : : SQL = "INSERT INTO UserGenre (UserID, GenreID) VALUES ([red]UserID[/red], " & Request.Form("Styles")(I) & ")"
    : : : Conn.Execute(SQL)
    : : : next
    : : : [/code]
    : : :
    : : : I marked UserID in red because it should be the value of the current user's ID, not the actual string "UserID"
    : : :
    : : : And now finally for the search feature, you would use this:
    : : :
    : : : [code]
    : : : SQL = "SELECT DISTINCT UserID FROM UserGenre WHERE GenreID IN (" & Request.Form("SearchStyles") & ")"
    : : : Conn.Execute(SQL)
    : : : [/code]
    : : :
    : : : That'll give the UserID of any user that has at least one style in common with the ones listed in Request.Form("SearchStyles")
    : : :
    : : : All this was done without any testing, so there may be errors, but that's the gist of how I would set things up. If you want to keep the user's style selections as a text column in the user table, that's still possible, but IMO it'll complicate things compared to this layout.
    : : :
    : :
    : :
    :
    :

  • marcoskn1marcoskn1 Posts: 61Member
    Worked !

    I didn't know this way to do it.. but it's quite simple :-) !


    thanks a lot !!


    : I don't know how you made your tables, but if you did it the way I suggested then you can't directly join the Genre and Users tables, you need to use the 3rd GenreUsers table I mentioned. I never learned the INNER JOIN syntax, so I join the tables the old way. If you want to use INNER JOIN you'll have to change part of the query. But here is what I would use:
    :
    : [code]
    : SELECT DISTINCT U.*
    : FROM GenreUser GU, Users U
    : WHERE GU.UserID = U.UserID <== This joins the tables
    : AND GU.GenreID IN (1, 2) <== This finds similar music interests
    : AND U.City = 'Sao Paulo' <== This limits to the same city
    : AND U.UserID <> [red]SEARCH_USER_ID[/red] <== This makes sure the
    : user who is searching is
    : not returned as a match
    : [/code]
    :
    : Not tested, but it should work.
    :
    :
    : : How would be the SQL string if I want to match others interests. Like besides Genre table, I also have Users table...
    : :
    : : If someone wants to search besides the Style of music, also wants to search for the same city...
    : :
    : : how it would be?
    : :
    : : SELECT DISTINCT * FROM genre INNER JOIN users WHERE genre IN (1, 2) AND users.city = 'sao paulo'
    : :
    : : Am I in the right way ?? Because it says there is an error...
    : :
    : :
    : : thanks!
    : :
    : :
    : :
    : : : That's the reason I love this forum and recommend this to others.. the answers are very fast :-) !!
    : : :
    : : : Well Manning, I tought many ways to do that, and one of them was like this you suggested, but I didn't know how would be the SQL string..
    : : :
    : : : I'll try this one you wrote
    : : :
    : : : Thanks a lot !!
    : : :
    : : :
    : : : : [b][red]This message was edited by Manning at 2003-12-12 12:30:46[/red][/b][hr]
    : : : : : Ok, I've a sign up form that has some checkbox. Each check box has one style of music (like pop, rock, dance, techno, etc) and the user has to choose the styles that he likes most.
    : : : : :
    : : : : : Ok, then I get all the data (name, city, country and music style) and put it into a DB
    : : : : :
    : : : : : In DB, the result appears like "1, 3, 4" for example... in this example the value 1 = rock, 3 = pop, 4 = techno
    : : : : :
    : : : : : But I wanna make a search form... in this search form, the user can choose to search for people that likes the same music style.. for example the user wants to search for people who likes rock and techo (1,4)
    : : : : :
    : : : : : How would be the SQL string for this ??
    : : : : :
    : : : : :
    : : : : : I hope someone can help me...
    : : : :
    : : : : Oops didn't meant to post that so fast..anyway..
    : : : :
    : : : : What you have there is a many to many relationship because one person could like many styles of music, and one style of music could be liked by many people. What I would do is this:
    : : : :
    : : : : Have a user table. I'm assuming you already have this, and that it has a unique identifier of some sort (preferably a UserID)
    : : : :
    : : : : Have a genre table. Each style of music should have an entry in this table, again each should have a unique ID.
    : : : :
    : : : : Have a UserGenre table. This table will only have two columns, a UserID and GenreID, both foreign keys to the above tables, and when combined they form a primary key. Every user should have one or more records in this table to indicate which styles of music they like.
    : : : :
    : : : : This means your ASP is going to have to change a bit. When you insert the users style selections, you'll use something like:
    : : : :
    : : : : [code]
    : : : : for I = 1 to Request.Form("Styles").Count
    : : : : SQL = "INSERT INTO UserGenre (UserID, GenreID) VALUES ([red]UserID[/red], " & Request.Form("Styles")(I) & ")"
    : : : : Conn.Execute(SQL)
    : : : : next
    : : : : [/code]
    : : : :
    : : : : I marked UserID in red because it should be the value of the current user's ID, not the actual string "UserID"
    : : : :
    : : : : And now finally for the search feature, you would use this:
    : : : :
    : : : : [code]
    : : : : SQL = "SELECT DISTINCT UserID FROM UserGenre WHERE GenreID IN (" & Request.Form("SearchStyles") & ")"
    : : : : Conn.Execute(SQL)
    : : : : [/code]
    : : : :
    : : : : That'll give the UserID of any user that has at least one style in common with the ones listed in Request.Form("SearchStyles")
    : : : :
    : : : : All this was done without any testing, so there may be errors, but that's the gist of how I would set things up. If you want to keep the user's style selections as a text column in the user table, that's still possible, but IMO it'll complicate things compared to this layout.
    : : : :
    : : :
    : : :
    : :
    : :
    :
    :

  • marcoskn1marcoskn1 Posts: 61Member
    Now I'm working on Edit page..

    and another question appeared... I have lots of checkboxes with the styles of music.

    When editing, how can I make these checkboxes to be checked with the options that the user selected when signed up ?



    Thanks again !


    : Worked !

    :
    : I didn't know this way to do it.. but it's quite simple :-) !
    :
    :
    : thanks a lot !!
    :
    :
    : : I don't know how you made your tables, but if you did it the way I suggested then you can't directly join the Genre and Users tables, you need to use the 3rd GenreUsers table I mentioned. I never learned the INNER JOIN syntax, so I join the tables the old way. If you want to use INNER JOIN you'll have to change part of the query. But here is what I would use:
    : :
    : : [code]
    : : SELECT DISTINCT U.*
    : : FROM GenreUser GU, Users U
    : : WHERE GU.UserID = U.UserID <== This joins the tables
    : : AND GU.GenreID IN (1, 2) <== This finds similar music interests
    : : AND U.City = 'Sao Paulo' <== This limits to the same city
    : : AND U.UserID <> [red]SEARCH_USER_ID[/red] <== This makes sure the
    : : user who is searching is
    : : not returned as a match
    : : [/code]
    : :
    : : Not tested, but it should work.
    : :
    : :
    : : : How would be the SQL string if I want to match others interests. Like besides Genre table, I also have Users table...
    : : :
    : : : If someone wants to search besides the Style of music, also wants to search for the same city...
    : : :
    : : : how it would be?
    : : :
    : : : SELECT DISTINCT * FROM genre INNER JOIN users WHERE genre IN (1, 2) AND users.city = 'sao paulo'
    : : :
    : : : Am I in the right way ?? Because it says there is an error...
    : : :
    : : :
    : : : thanks!
    : : :
    : : :
    : : :
    : : : : That's the reason I love this forum and recommend this to others.. the answers are very fast :-) !!
    : : : :
    : : : : Well Manning, I tought many ways to do that, and one of them was like this you suggested, but I didn't know how would be the SQL string..
    : : : :
    : : : : I'll try this one you wrote
    : : : :
    : : : : Thanks a lot !!
    : : : :
    : : : :
    : : : : : [b][red]This message was edited by Manning at 2003-12-12 12:30:46[/red][/b][hr]
    : : : : : : Ok, I've a sign up form that has some checkbox. Each check box has one style of music (like pop, rock, dance, techno, etc) and the user has to choose the styles that he likes most.
    : : : : : :
    : : : : : : Ok, then I get all the data (name, city, country and music style) and put it into a DB
    : : : : : :
    : : : : : : In DB, the result appears like "1, 3, 4" for example... in this example the value 1 = rock, 3 = pop, 4 = techno
    : : : : : :
    : : : : : : But I wanna make a search form... in this search form, the user can choose to search for people that likes the same music style.. for example the user wants to search for people who likes rock and techo (1,4)
    : : : : : :
    : : : : : : How would be the SQL string for this ??
    : : : : : :
    : : : : : :
    : : : : : : I hope someone can help me...
    : : : : :
    : : : : : Oops didn't meant to post that so fast..anyway..
    : : : : :
    : : : : : What you have there is a many to many relationship because one person could like many styles of music, and one style of music could be liked by many people. What I would do is this:
    : : : : :
    : : : : : Have a user table. I'm assuming you already have this, and that it has a unique identifier of some sort (preferably a UserID)
    : : : : :
    : : : : : Have a genre table. Each style of music should have an entry in this table, again each should have a unique ID.
    : : : : :
    : : : : : Have a UserGenre table. This table will only have two columns, a UserID and GenreID, both foreign keys to the above tables, and when combined they form a primary key. Every user should have one or more records in this table to indicate which styles of music they like.
    : : : : :
    : : : : : This means your ASP is going to have to change a bit. When you insert the users style selections, you'll use something like:
    : : : : :
    : : : : : [code]
    : : : : : for I = 1 to Request.Form("Styles").Count
    : : : : : SQL = "INSERT INTO UserGenre (UserID, GenreID) VALUES ([red]UserID[/red], " & Request.Form("Styles")(I) & ")"
    : : : : : Conn.Execute(SQL)
    : : : : : next
    : : : : : [/code]
    : : : : :
    : : : : : I marked UserID in red because it should be the value of the current user's ID, not the actual string "UserID"
    : : : : :
    : : : : : And now finally for the search feature, you would use this:
    : : : : :
    : : : : : [code]
    : : : : : SQL = "SELECT DISTINCT UserID FROM UserGenre WHERE GenreID IN (" & Request.Form("SearchStyles") & ")"
    : : : : : Conn.Execute(SQL)
    : : : : : [/code]
    : : : : :
    : : : : : That'll give the UserID of any user that has at least one style in common with the ones listed in Request.Form("SearchStyles")
    : : : : :
    : : : : : All this was done without any testing, so there may be errors, but that's the gist of how I would set things up. If you want to keep the user's style selections as a text column in the user table, that's still possible, but IMO it'll complicate things compared to this layout.
    : : : : :
    : : : :
    : : : :
    : : :
    : : :
    : :
    : :
    :
    :

  • ManningManning Posts: 1,621Member
    : Now I'm working on Edit page..
    :
    : and another question appeared... I have lots of checkboxes with the styles of music.
    :
    : When editing, how can I make these checkboxes to be checked with the options that the user selected when signed up ?


    This is normally what I do (not tested):

    [code]
    strGenres = ",0,"

    ' Select the user's current preferences
    SQL = "SELECT GenreID FROM UserGenreTbl WHERE UserID = [red]xyz[/red]"
    set Rec = Conn.Execute(SQL)
    do while Not(Rec.EOF)
    strGenres = strGenres & Rec("GenreID") & ","
    Rec.MoveNext
    loop
    set Rec = nothing

    ' Select the available genre's to display checkboxes
    SQL = "SELECT GenreID, GenreName FROM GenreTbl"
    set Rec = Conn.Execute(SQL)
    do while Not(Rec.EOF)
    ' Check if genre is in user's selection
    if (InStr(strGenres, "," & Rec("GenreID") & ",") > 0) then
    Response.Write " " & Rec("GenreName") & "
    "
    else
    Response.Write " " & Rec("GenreName") & "
    "
    end if

    Rec.MoveNext
    loop
    set Rec = nothing
    [/code]
  • marcoskn1marcoskn1 Posts: 61Member
    didn't work..

    got this error:

    error '80020009'
    Exception occurred.



    any idea?


    : : Now I'm working on Edit page..
    : :
    : : and another question appeared... I have lots of checkboxes with the styles of music.
    : :
    : : When editing, how can I make these checkboxes to be checked with the options that the user selected when signed up ?
    :
    :
    : This is normally what I do (not tested):
    :
    : [code]
    : strGenres = ",0,"
    :
    : ' Select the user's current preferences
    : SQL = "SELECT GenreID FROM UserGenreTbl WHERE UserID = [red]xyz[/red]"
    : set Rec = Conn.Execute(SQL)
    : do while Not(Rec.EOF)
    : strGenres = strGenres & Rec("GenreID") & ","
    : Rec.MoveNext
    : loop
    : set Rec = nothing
    :
    : ' Select the available genre's to display checkboxes
    : SQL = "SELECT GenreID, GenreName FROM GenreTbl"
    : set Rec = Conn.Execute(SQL)
    : do while Not(Rec.EOF)
    : ' Check if genre is in user's selection
    : if (InStr(strGenres, "," & Rec("GenreID") & ",") > 0) then
    : Response.Write " " & Rec("GenreName") & ""
    : else
    : Response.Write " " & Rec("GenreName") & ""
    : end if
    :
    : Rec.MoveNext
    : loop
    : set Rec = nothing
    : [/code]
    :

  • ManningManning Posts: 1,621Member
    I forgot to double some of the quotes on the Response.Write lines. If that doesn't fix it, you'll have to do a little debugging.

    : didn't work..
    :
    : got this error:
    :
    : error '80020009'
    : Exception occurred.
    :
    :
    :
    : any idea?
    :
    :
    : : : Now I'm working on Edit page..
    : : :
    : : : and another question appeared... I have lots of checkboxes with the styles of music.
    : : :
    : : : When editing, how can I make these checkboxes to be checked with the options that the user selected when signed up ?
    : :
    : :
    : : This is normally what I do (not tested):
    : :
    : : [code]
    : : strGenres = ",0,"
    : :
    : : ' Select the user's current preferences
    : : SQL = "SELECT GenreID FROM UserGenreTbl WHERE UserID = [red]xyz[/red]"
    : : set Rec = Conn.Execute(SQL)
    : : do while Not(Rec.EOF)
    : : strGenres = strGenres & Rec("GenreID") & ","
    : : Rec.MoveNext
    : : loop
    : : set Rec = nothing
    : :
    : : ' Select the available genre's to display checkboxes
    : : SQL = "SELECT GenreID, GenreName FROM GenreTbl"
    : : set Rec = Conn.Execute(SQL)
    : : do while Not(Rec.EOF)
    : : ' Check if genre is in user's selection
    : : if (InStr(strGenres, "," & Rec("GenreID") & ",") > 0) then
    : : Response.Write " " & Rec("GenreName") & ""
    : : else
    : : Response.Write " " & Rec("GenreName") & ""
    : : end if
    : :
    : : Rec.MoveNext
    : : loop
    : : set Rec = nothing
    : : [/code]
    : :
    :
    :

  • marcoskn1marcoskn1 Posts: 61Member
    little mistake from my part.. sorry

    Now I wanna do something with 3 tables.. is it possible?? I tried to do like this:

    sqlPr = "SELECT DISTINCT u.* FROM usuarios u, estilo e, oqtoca o WHERE (e.estilo IN (" & estilo & ")) AND (o.oqtoca IN ('" & oqprocura & "')) AND " & prIdade & prNivel & prQto & prBanda


    But, it ignores my third table..

    The SQL statement looked like:

    SELECT DISTINCT u.* FROM usuarios u, estilo e, oqtoca o WHERE (e.estilo IN (1)) AND (o.oqtoca IN ('baixo')) AND (idade BETWEEN 12 AND 49) AND (nivel >= 0 AND nivel <= 5) AND (qtotempo >= 0) AND (tevebanda = 'sim' OR tevebanda = 'nao')



    : I forgot to double some of the quotes on the Response.Write lines. If that doesn't fix it, you'll have to do a little debugging.
    :
    : : didn't work..
    : :
    : : got this error:
    : :
    : : error '80020009'
    : : Exception occurred.
    : :
    : :
    : :
    : : any idea?
    : :
    : :
    : : : : Now I'm working on Edit page..
    : : : :
    : : : : and another question appeared... I have lots of checkboxes with the styles of music.
    : : : :
    : : : : When editing, how can I make these checkboxes to be checked with the options that the user selected when signed up ?
    : : :
    : : :
    : : : This is normally what I do (not tested):
    : : :
    : : : [code]
    : : : strGenres = ",0,"
    : : :
    : : : ' Select the user's current preferences
    : : : SQL = "SELECT GenreID FROM UserGenreTbl WHERE UserID = [red]xyz[/red]"
    : : : set Rec = Conn.Execute(SQL)
    : : : do while Not(Rec.EOF)
    : : : strGenres = strGenres & Rec("GenreID") & ","
    : : : Rec.MoveNext
    : : : loop
    : : : set Rec = nothing
    : : :
    : : : ' Select the available genre's to display checkboxes
    : : : SQL = "SELECT GenreID, GenreName FROM GenreTbl"
    : : : set Rec = Conn.Execute(SQL)
    : : : do while Not(Rec.EOF)
    : : : ' Check if genre is in user's selection
    : : : if (InStr(strGenres, "," & Rec("GenreID") & ",") > 0) then
    : : : Response.Write " " & Rec("GenreName") & ""
    : : : else
    : : : Response.Write " " & Rec("GenreName") & ""
    : : : end if
    : : :
    : : : Rec.MoveNext
    : : : loop
    : : : set Rec = nothing
    : : : [/code]
    : : :
    : :
    : :
    :
    :

Sign In or Register to comment.