469,917 Members | 1,473 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,917 developers. It's quick & easy.

Comparing results from a column to results from a form?

6
Hi all,

Having a few problems with a select statement...

I have a table containing values similar to results from a form and when submitted I want to get all results from the table that aren't in the form...

so far I have everything except how best to do the select statement and was wondering if any of you fanatstic people out there could help me...

Code so far:

<%
For Each x in Request.Form()
userTEAMS = Request.Form(x)
'Response.Write userTEAMS & "
"


userSQL = "SELECT * FROM user_preferences WHERE '"&userTEAMS&"' <> prefTEAM "
Set rsREM = conn.Execute (userSQL)

Do Until rsREM.EOF
remTEAM = rsREM("prefTEAM")

remTEAM = "<font color=green>" & remTEAM & "</font>"
Response.Write(remTEAM & "
")

rsREM.MoveNext
Loop

Next
%>
This just returns all the records in the table and ignores the form values. Would I need to use a case statement to do this?

For example if the:
form contains team1,team2,team3
table contains team1,team2,team3,team4,team5

I need to just get the values team4 and team5 from the table.

I know my select statement is whack, just left it there as a guide!

Cheers

Si
Feb 11 '08 #1
7 1081
amitpatel66
2,367 Expert 2GB
Hi all,

Having a few problems with a select statement...

I have a table containing values similar to results from a form and when submitted I want to get all results from the table that aren't in the form...

so far I have everything except how best to do the select statement and was wondering if any of you fanatstic people out there could help me...

Code so far:

<%
For Each x in Request.Form()
userTEAMS = Request.Form(x)
'Response.Write userTEAMS & "
"


userSQL = "SELECT * FROM user_preferences WHERE '"&userTEAMS&"' <> prefTEAM "
Set rsREM = conn.Execute (userSQL)

Do Until rsREM.EOF
remTEAM = rsREM("prefTEAM")

remTEAM = "<font color=green>" & remTEAM & "</font>"
Response.Write(remTEAM & "
")

rsREM.MoveNext
Loop

Next
%>
This just returns all the records in the table and ignores the form values. Would I need to use a case statement to do this?

For example if the:
form contains team1,team2,team3
table contains team1,team2,team3,team4,team5

I need to just get the values team4 and team5 from the table.

I know my select statement is whack, just left it there as a guide!

Cheers

Si
A very basics of coding is that the constant values should be on the right hand side of the condition and the column name should be on left hand side.

Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. <% 
  3. For Each x in Request.Form() 
  4. userTEAMS = Request.Form(x) 
  5. 'Response.Write userTEAMS & " 
  6.  
  7.  
  8. userSQL = "SELECT * FROM user_preferences WHERE prefTEAM <> '"&userTEAMS&"'" 
  9. Set rsREM = conn.Execute (userSQL) 
  10.  
  11. Do Until rsREM.EOF 
  12. remTEAM = rsREM("prefTEAM") 
  13.  
  14. remTEAM = "<font color=green>" & remTEAM & "</font>" 
  15. Response.Write(remTEAM & " 
  16. ") 
  17.  
  18. rsREM.MoveNext 
  19. Loop 
  20.  
  21. Next 
  22. %> 
  23.  
  24.  
Please clarify how the multiple values are stored in the variable userTEAMS?

Is it something like comma delimited values like team1,team2,team3??
Feb 11 '08 #2
Paigey
6
Please clarify how the multiple values are stored in the variable userTEAMS?

Is it something like comma delimited values like team1,team2,team3??
Hiya,

Yeh tried this way already. It seems to completely ignore the form values and repeats the teams in the table over and over.

userTEAMS is a list of selections taken from checkboxes in a form and the results posted to this page I'm putting together now.

prefTEAM is a column in my user_preferences table in my mssql db and this contains a few teams previously selected by users via the form. What I'm trying to do now is if a team exists in the prefTEAM column but not in the form... i.e. the user has unchecked the box/team in the form write the result to the screen.

Hope that helps and thanks for your advice..

Paigey
Feb 11 '08 #3
amitpatel66
2,367 Expert 2GB
Hiya,

Yeh tried this way already. It seems to completely ignore the form values and repeats the teams in the table over and over.

userTEAMS is a list of selections taken from checkboxes in a form and the results posted to this page I'm putting together now.

prefTEAM is a column in my user_preferences table in my mssql db and this contains a few teams previously selected by users via the form. What I'm trying to do now is if a team exists in the prefTEAM column but not in the form... i.e. the user has unchecked the box/team in the form write the result to the screen.

Hope that helps and thanks for your advice..

Paigey
Can you try this:

Expand|Select|Wrap|Line Numbers
  1. <% 
  2. For Each x in Request.Form() 
  3. userTEAMS = Request.Form(x) 
  4. 'Response.Write userTEAMS & " 
  5.  
  6.  
  7. userSQL = "SELECT * FROM user_preferences WHERE prefTEAM NOT IN  ('"&userTEAMS&"')" 
  8. Set rsREM = conn.Execute (userSQL) 
  9.  
  10. Do Until rsREM.EOF 
  11. remTEAM = rsREM("prefTEAM") 
  12.  
  13. remTEAM = "<font color=green>" & remTEAM & "</font>" 
  14. Response.Write(remTEAM & " 
  15. ") 
  16.  
  17. rsREM.MoveNext 
  18. Loop 
  19.  
  20. Next 
  21. %>
  22.  
  23.  
Please test this and check if it works?
Feb 11 '08 #4
Paigey
6
I've tried this too...

Get the same thing unfortunately.

It seems to ignore <>

If I change <> to = I just get the 4 teams in the prefTEAM column back but when I use <> it returns repeated results of everything rather than just those not in the form values. Frustrating!

Is there a way to do this with CASE maybe?
Feb 11 '08 #5
ck9663
2,878 Expert 2GB
I've tried this too...

Get the same thing unfortunately.

It seems to ignore <>

If I change <> to = I just get the 4 teams in the prefTEAM column back but when I use <> it returns repeated results of everything rather than just those not in the form values. Frustrating!

Is there a way to do this with CASE maybe?
your query, as it is, is correct. so this is more of an ASP/VBSCRIPT question than a sql-server question. but i'll take a crack at it.

try doing this:
Expand|Select|Wrap|Line Numbers
  1. <% 
  2.  
  3. userTEAMS = ""
  4. For Each x in Request.Form() 
  5. userTEAMS = userTEAMS & "','" Request.Form(x) 
  6. next
  7.  
here you are trying to build the list of teams from the form. you should have something like this TEAM1','TEAM2','TEAM3. this will be used on your IN set condition.

you enclosed the entire script inside the FOR-LOOP. that means your query runs every item on your form. you need to gather the value from the form first, then run the query, once.

your objective is to come up with a query that looks like this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT * FROM USER_PREFERENCES WHERE PREFTEAM NOT IN ('TEAM1'.'TEAM2','TEAM3')
  3.  

then try using the rest of your code...


Expand|Select|Wrap|Line Numbers
  1.  
  2. userSQL = "SELECT * FROM user_preferences WHERE prefTEAM NOT IN  ('" & userTEAMS & "')"
  3.  
  4.  
  5. Set rsREM = conn.Execute (userSQL) 
  6.  
  7. Do Until rsREM.EOF 
  8. remTEAM = rsREM("prefTEAM") 
  9.  
  10. remTEAM = "<font color=green>" & remTEAM & "</font>" 
  11. Response.Write(remTEAM & " ") 
  12.  
  13. rsREM.MoveNext 
  14. Loop 
  15.  
  16. %>
i did not test the actual code. that's why i included those explanations. so i hope you understood what am trying to suggest

-- ck
Feb 11 '08 #6
Paigey
6
Cool...

Now that's a much simpler way of doing it than I had thought of. Was trying to make this alot more complicated than it needed to be by the looks. Thanks so much. Works perfectly. Just for the record you missed out an ampersand here but managed to pick it up lol... :p

Expand|Select|Wrap|Line Numbers
  1. userTEAMS = userTEAMS & "','" Request.Form(x)
  2.  
  3. changed to: userTEAMS = userTEAMS & "','" & Request.Form(x)
  4.  
Thanks again CK. Oh btw sorry I didn't reply to the last reply/help you gave me but I managed to come up with a solution based on what you were suggesting and completely forgot to come back and reply to say thank you for your help.

Paigey
Feb 11 '08 #7
ck9663
2,878 Expert 2GB
no problem Paigey. glad to help.

happy coding....

-- ck
Feb 11 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Michael Haas | last post: by
11 posts views Thread by Richard | last post: by
4 posts views Thread by lasmit | last post: by
19 posts views Thread by Dennis | last post: by
3 posts views Thread by bvlmv | last post: by
1 post views Thread by russot00 | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.