Comparing results from a column to results from a form?

[Paigey]

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_preference s 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,tea m3
table contains team1,team2,tea m3,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

[amitpatel66]

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_preference s 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,tea m3
table contains team1,team2,tea m3,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:

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


userSQL = "SELECT * FROM user_preferences WHERE prefTEAM <> '"&userTEAMS&"'" 
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 
%>

Please clarify how the multiple values are stored in the variable userTEAMS?

Is it something like comma delimited values like team1,team2,tea m3??

[Paigey]

Please clarify how the multiple values are stored in the variable userTEAMS?

Is it something like comma delimited values like team1,team2,tea m3??

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_preference s 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

[amitpatel66]

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_preference s 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:

<% 
For Each x in Request.Form() 
userTEAMS = Request.Form(x) 
'Response.Write userTEAMS & " 
" 
 
 
userSQL = "SELECT * FROM user_preferences WHERE prefTEAM NOT IN  ('"&userTEAMS&"')" 
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 
%>

Please test this and check if it works?

[Paigey]

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?

[ck9663]

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:

<% 

userTEAMS = ""
For Each x in Request.Form() 
userTEAMS = userTEAMS & "','" Request.Form(x) 
next

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:

 
SELECT * FROM USER_PREFERENCES WHERE PREFTEAM NOT IN ('TEAM1'.'TEAM2','TEAM3')

then try using the rest of your code...

 
userSQL = "SELECT * FROM user_preferences WHERE prefTEAM NOT IN  ('" & userTEAMS & "')"


Set rsREM = conn.Execute (userSQL) 
 
Do Until rsREM.EOF 
remTEAM = rsREM("prefTEAM") 
 
remTEAM = "<font color=green>" & remTEAM & "</font>" 
Response.Write(remTEAM & " ") 
 
rsREM.MoveNext 
Loop 
 
%>

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

[Paigey]

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

userTEAMS = userTEAMS & "','" Request.Form(x)

changed to: userTEAMS = userTEAMS & "','" & Request.Form(x)

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

[ck9663]

no problem Paigey. glad to help.

happy coding....

-- ck