SQL Filter Problem

[Samant]

Hello Thr. I know I am doing it correctly but, missing some very minor thing. I have a form whr I have 5 drop down lists and searching th ose values from a database. I already have entered some values in my database. When I try to run this below mentioned sql query with just one subskill, it runs fine. But when I run it with multiple subskills, it doesnt come up with any result. It says no record found. Please help me.

Below mentioned is my sql query.

t1=request("skill")
t2=request("subskill")

t4=request("simple")
t5=request("medium")
t6=request("complex")




if t1 <> "" and t2 <> "" and t4 <> "" and t5 <> "" and t6 <> "" then

If InStr(t2,",") > 0 Then
arrTerms = Split(t2,",")

For x = 0 to UBound(arrTerms)

login = "(select TOP "&t4&" * from skills where dept Like '%"&t1&"%' and desg Like '%"&arrTerms(x)&"%' and rating='simple')"
'login = login & "UNION (select TOP "&t5&" * from skills where dept Like '%"&t1&"%' and desg Like '%"&arrTerms(x)&"%' and rating='medium')"
'login = login & "UNION (select TOP "&t6&" * from skills where dept Like '%"&t1&"%' and desg Like '%"&arrTerms(x)&"%' and rating='complex')"

If x < UBound(arrTerms) Then 
login = login & " AND "
End If

Next 'x
'login = login & " ORDER BY rating ASC;"

else 
login="(select TOP "&t4&" * from skills where dept Like '%"&t1&"%' and desg Like '%"&t2&"%' and rating='simple' ORDER BY Rnd(Len([question]))) UNION (select 

TOP "&t5&" * from skills where dept Like '%"&t1&"%' and desg Like '%"&t2&"%' and rating='medium' ORDER BY Rnd(Len([question]))) UNION (select TOP "&t6&" * 

from skills where dept Like '%"&t1&"%' and desg Like '%"&t2&"%' and rating='complex' ORDER BY Rnd(Len([question])))"

End If
End If

[Jerry Winston]

Looks like there's a lot of TSQL buried in VB.NET code. Can you post two TSQL scripts: the result of passing one argument and the result of passing two arguments. I'm guessing from your code that "login" is a VB string variable? Place this code after your last END IF:

Clipboard.SetText(login)

disable any execution code that would send the script to the server. Paste your results here and we can see exactly what's being sent to your server.

Btw. Users get a look at this code, there will undoubtably be recommendations to use stored procedures and avoid generating adhoc SQL on the fly in VB.NET code.

[Samant]

Looks like there's a lot of TSQL buried in VB.NET code. Can you post two TSQL scripts: the result of passing one argument and the result of passing two arguments. I'm guessing from your code that "login" is a VB string variable? Place this code after your last END IF:

Clipboard.SetText(login)

disable any execution code that would send the script to the server. Paste your results here and we can see exactly what's being sent to your server.

Btw. Users get a look at this code, there will undoubtably be recommendations to use stored procedures and avoid generating adhoc SQL on the fly in VB.NET code.

Hello there. I have this query on a classic ASP page. I am trying to return values from server but it comes up with no values. I am helpless. I dont know what should I do. Below mentioned is my full code.

<%


if t1 <> "" and t2 <> "" and  t4 <> "" and t5 <> "" and t6 <> ""  then

If InStr(t2,",") > 0 Then
arrTerms = Split(t2,",")

	For x = 0 to UBound(arrTerms)

	response.write ""&arrTerms(x)&"<br>"

login = "(select TOP "&t4&" * from skills where dept Like '%"&t1&"%' and desg Like '%"&arrTerms(x)&"%' and rating='simple')"
'login = login & "UNION (select TOP "&t5&" * from skills where dept Like '%"&t1&"%' and desg Like '%"&arrTerms(x)&"%' and rating='medium')"
'login = login & "UNION (select TOP "&t6&" * from skills where dept Like '%"&t1&"%' and desg Like '%"&arrTerms(x)&"%' and rating='complex')"

		If x < UBound(arrTerms) Then 
			login = login & " AND "
		End If

	Next 'x
	'login = login & " ORDER BY rating ASC;"

else 
login="(select TOP "&t4&" * from skills where dept Like '%"&t1&"%' and desg Like '%"&t2&"%' and rating='simple' ORDER BY Rnd(Len([question]))) UNION (select 

TOP "&t5&" * from skills where dept Like '%"&t1&"%' and desg Like '%"&t2&"%' and rating='medium' ORDER BY Rnd(Len([question]))) UNION (select TOP "&t6&" * 

from skills where dept Like '%"&t1&"%' and desg Like '%"&t2&"%' and rating='complex' ORDER BY Rnd(Len([question])))"

End If

End If



%>




<% 
    Function GetGuid() 
        Set TypeLib = CreateObject("Scriptlet.TypeLib") 
        GetGuid = Left(CStr(TypeLib.Guid), 38) 
        Set TypeLib = Nothing 
    End Function 
    'Response.Write "This is the way to generate Random ID's in ASP " & GetGuid()  & ""


%>



<%


i=1
RS.open login,Con


%>


<form  name="fm1" action="addqcand.asp" method="post">

<br>
Questions under Skill Category-- <font color=mahroon>

<% response.write ""&t1&""%> / <% response.write ""&t2&""%></font> rating are as follows:<br><br>



            <TABLE border=0 width=100% bgcolor=silver>


                    <TR>
                      <TD class=text valign=top>Q.No. </TD>
                      <TD class=text></TD>
                      <TD class=text valign=top width=80% align=center><label>
                      Question in Detail
                      </label></TD>
			<td align=right  width=10%>Question Type</td>

                      
                    </TR></table>
<%


if not RS.EOF then
While not RS.EOF

rating = RS("rating")
%>





            <TABLE border=0 width=100%>


                    <TR>
                      <TD class=text valign=top><font size=2 color=mahroon>Question <%=i%> </font></TD>
                      <TD class=text valign=top>:</TD>
                      <TD class=text valign=top width=80%><label>
                        <font size=2> <%=RS("question")%></font>
                      </label></TD>
			<td align=right width=10%><font size=1 color=mahroon><i><%=RS("dept")%>/<%=RS("desg")%></i></font> <br>  
<% Response.write "<font color=green><b><u>"&rating&"</b></font>" 

%><input type="hidden" name="q<%=i%>" value="<%=RS("question")%>"><input type="hidden" name="a<%=i%>" value="<%=RS("answer")%>">

			</td>
             </tr>

      <TR>
                      <TD class=text valign=top><font size=2 color=green>Answer <%=i%> </font></TD>
                      <TD class=text valign=top>:</TD>
                      <TD class=text valign=top width=80%><label>
                        <font size=1> <%=RS("answer")%></font>
                      </label></TD>
			<td align=right width=10%>

			</td>
             </tr>
<hr>

<tr>
	<td align="Center" height=10 valign="top" width=1550>
	
	
	</td>
</tr>
  <%
i=i+1
  RS.MoveNext

   Wend
%>



<%
else
Response.Write "<b>No Records Found.</b><br><br>"
end if
   RS.Close
  Con.Close
 Set RS = Nothing
 %>




 </table>


</form>


<!--#include file ="footer.asp"-->

[NeoPa]

Samant,

I suggest if you want SQL help rather than classic ASP, you respond to the request in post #2 for your SQL code. Posting ASP code in a SQL forum (especially after explicit instructions to post the SQL) is not likely to get you any help. Not many ASP experts hang around the SQL forums.

If you decide you'd rather have some ASP help then just let me know and I'll move this thread to the ASP forum for you.

[Samant]

Samant,

I suggest if you want SQL help rather than classic ASP, you respond to the request in post #2 for your SQL code. Posting ASP code in a SQL forum (especially after explicit instructions to post the SQL) is not likely to get you any help. Not many ASP experts hang around the SQL forums.

If you decide you'd rather have some ASP help then just let me know and I'll move this thread to the ASP forum for you.

Hi NeoPa.. sorry for any inconvenience caused. Actually i am confused, where the problem is... is it in my sql query or in my asp code... cos it works fine with one subskill value but doesnt show any record for multiple selections..... Pls help me if you can.

[NeoPa]

Hi NeoPa.. sorry for any inconvenience caused. Actually i am confused, where the problem is... is it in my sql query or in my asp code... cos it works fine with one subskill value but doesnt show any record for multiple selections..... Pls help me if you can.

Two of us have tried to help you already Samant, but you keep ignoring what we say. Read either of our posts again and provide the information we require before we can help.

[Jerry Winston]

Since you're using classic ASP, use this :

response.write("Login TSQL: [" & login & "]")

after your second END IF statement. Post the results you get from selecting one option and the result from selecting two and n results.