ASP search form for DB content 1

[dwarvenmilitia]

Hey I know this may sound really bad but I have all my db setup and pulling images off and displaying in web fine.

I can create a search bar for text (one variable). BUT

Say I have like 6 catagories ie field names in the db and they are checkboxes (yes/no). How do I go about variables between table names and forms and if only one category is searched or any combination of them how does SQL go?

I have tried to find examples and I have mixed ASP with the SQL which appears to be fine but I have no idea on building the page to relate the selected inputs to each other.

Any help would be awesome.

 

[QatQat]

If I understand correctly you want to dynamically generate a SQL string from mor than one control on your page.

Assuming that you have three controls

<input type=text name="textbox">
<input type="checkbox" name="box1" value="true">
<input type="checkbox" name="box2" value="true">

now your SQL string would be something like

dim strSQL, tbox, cbox1, cbox2

strSQL = "select * from mytable where field1 =" &tbox

if cbox1 = "true" then
strSQL = strSQL & " and field2 = 'true' "
end if

if cbox2 = "true"
strSQL = strSQL & " and field3 = 'true' "
end if

and so on...

cheers

QatQat


Life is what happens when you are making other plans.

 

[dwarvenmilitia]

Hey QatQat

Thats awesome! That is a way better way of doing it. That is only part of the question too! I think?! Nope that was it... Just one more question on top

If I use this to create a search page using POST, how do I go about telling it to open the results in a new window ie. search_response.asp

<form action="search_response.asp" method="post">

Then to get the data to show in the Do While Loop do I put that underneath in search.asp or in search_response.asp and then what happens with the variables I've just called for?

 

[QatQat]

You should use a client side script for that, you would then not submit the values using POST method but simply collect values and send them using a onClick.


You can do something like this, the following client side script uses HMLHTTP

<script language="VBScript">
sub goSrc()
 
    sValue = trim(document.form1("textbox").value) 
    sValue2 = trim(document.form1("checkbox1").value) 
    set obj = CreateObject("Microsoft.XMLHTTP") 
    obj.Open "GET", "SetValue.asp?value1=" & Escape(sValue) & "&value2=" & Escape(sValue2), False 
    obj.Send 

document.location.href = "search_results.asp"
end sub
</script>

now you must create a page called setvalue.asp to pass your values to session variables with the following code

<% 
    Session("value1") = Request.QueryString("value1") 
       session("value2") = request.querystring("value2")
%>

now your search_results.asp

dim v1, v2
v1 = session("value1")
v2 = session("value2")

strSQl = "select * from mytable where field1 ='" & v1 & "'....etc

This way you will have submitted your form values to a different page without using POST.

Cheers

QatQat



Life is what happens when you are making other plans.

 

[QatQat]

Otherwise,

here is a good example of the javascript route

http://evolt.jeffhowden.com/jeff/code/form_to_window/index.cfm

CHeers

QatQat

Life is what happens when you are making other plans.

 

[dwarvenmilitia]

Hey again... I believe I have sorted this out with a sort of round about javascript postback.

But when I run this and search for someone who I know is there... noting returns. Is there something bung with my coding.

<% @ LANGUAGE=VBScript %>
<% option explicit %>

<form action="search.asp" method="post">
<p>Select which  category to search:</p>
<p>
  <input type="checkbox" name="varActors" value="true">
  Actors
  <br>
  <input type="checkbox" name="varPresenters" value="true">
  Presenters
  <br>
  <input type="checkbox" name="varTalent" value="true">
  Talent
  <br>
  Enter a name to search:
  <input type="text" name="varText">
  <input type="submit" value="Submit" onclick="return SubmitTheForm();" />
  <input type="hidden" value="false" name="hdnPostBack" />
</form>

 <script type="text/javascript" language="JavaScript">
  <!--
	var submitted = false;
	function SubmitTheForm() {
	if(submitted == true) { return; }
	document.Form1.hdnPostBack.value = "true";
	document.Form1.submit();
	submitted = true;
    }

  //-->
  </script>
<%   
Dim hdnPostBack
If hdnPostBack = "true" Then

Dim cnnSearch 
Dim rstSearch  
Dim strDBPath 
Dim strSQL    
Dim strSearch 
Dim varActors
Dim varPresenters
Dim varTalent
Dim varText


strDBPath = Server.MapPath("testbook.mdb")
Set cnnSearch = Server.CreateObject("ADODB.Connection")
cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

strSQL = "SELECT Surname, Name, Image_Thumb, ID, Online FROM dancers WHERE dancers.Online=Yes AND dancers.Surname = '" & varText & "'" 



If varActors = "true" Then
strSQL = strSQL & " AND dancers.Actors = 'Yes' "
End If
If varPresenters = "true" Then
strSQL = strSQL & " AND dancers.Presenters = 'Yes' "
End If
If varTalent = "true" Then
strSQL = strSQL & " AND dancers.Talent = 'Yes' "
End If

strSQL = strSQL & " ORDER BY dancers.Surname;"

Set rstSearch = cnnSearch.Execute(strSQL)


	
	Do While Not rstSearch.EOF
		
%><br />
		<%= rstSearch.Fields("Surname").Value %>
		<%= rstSearch.Fields("Name").Value %>
		

<%
		rstSearch.MoveNext
	Loop

	rstSearch.Close
	Set rstSearch = Nothing
	cnnSearch.Close
	Set cnnSearch = Nothing

End If

%>

Something is really not right with it and I have showing a friend who knows ASP but he can't figure it... Although he is tired.

This would be the ultimate help out.

Cheers for your work so far QatQat

 

[QatQat]

you have initialized variables but yet no value is assigned to them


<%
varTalent = request("vartalent")
etc..
%>



and also some more info

Set rstSearch = cnnSearch.Execute(strSQL)

The execute method of the connection object should better be used for update/delete queries.

Good practice is to create a proper recordset object

Set rstSearch = server.createobject("ADODB.recordset")
rst.open strSQL, cnnSearch, 1, 3


Cheers

QatQat

Life is what happens when you are making other plans.

 

[dwarvenmilitia]

Am I missing Request.Form("checkboxvariables") in my ASP after the Execute SQL Query???

Man I'm tired and I'm bumbed this aint working.

Hey QatQat would you know anywhere there is an example of something similar to this? I spose that is if you don't know where my issue is here.

 

[dwarvenmilitia]

HAHA I answered my own q... almost. It will still work fine with how I have it in the main of the script though?

 

[dwarvenmilitia]

QatQat... I added in the varTalent = Request("varTalent") but yeah nothing happens still. I took out my mates javascript postback and it works with no checkboxes selected and when I search for the exact name with a checkbox it comes up with Data Type mismatch error.

This is really starting to bug me cause surely this isn't hard!?

Cheers

 

[dwarvenmilitia]

I'm kinda doing this for a friend cause the older site is crap and they want to update it. The older search version is here but even the guy who did that didn't quite get it right...

But this is the search menu I effectively want to acheive and I don't have access to it to compare.

http://talentonline.com.au/asp/a31_talent_search.asp

 

[QatQat]

OK, I had a look at that search page, my answer remains the same.

Your SQL query will be something like the one above, and when dealing with min and max for age and height make sure that they enter both and then

if request("agemin") <> "" then
strSQL = strSQL & " and age between " & request("agemin") & " and " & request("agemax") & "
end if

if request("heightmin") <> "" then
strSQL = strSQL & " and height between " & request("heightmin") & " and " & request("heightmax") & "
end if

Cheers

QatQat

Life is what happens when you are making other plans.

 

[dwarvenmilitia]

Awesome... QatQat I thought that might be the case. I'll have to add in some more If fields as some people might search without the text box just for each catagory.

Will try and sort something out.

Gone for a week so can't try it yet but thanks for the help and I'll post again if I have more issues.

Cheers

 

[dwarvenmilitia]

Hi again. If anyone can help me out that would be awesome.

I have sorted out some code here to search and it works entering a name into the db but as soon as I check a checkbox and submit I get an error "No value given for one or more required parameters." Can anyone help???

I know that the post data is saying Actors=true&Text= however when it comes hitting submit, then its error time.... as said above.

I think my problem is in the If statments of the SQL for varActors, varPresenters, varTalent.

Oh and the form variables are Actors, Presenters etc...

<% @ LANGUAGE=VBScript %>
<% option explicit %>
<%   

Dim cnnSearch 
Dim rstSearch  
Dim strDBPath 
Dim strSQL    
Dim strSearch 
Dim varActors
Dim varPresenters
Dim varTalent
Dim varText

varTalent = Request.Form("Talent")
varActors = Request.Form("Actors")
varPresenters = Request.Form("Presenters")
varText = Request.Form("Text")


strDBPath = Server.MapPath("testbook.mdb")
Set cnnSearch = Server.CreateObject("ADODB.Connection")
cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

strSQL = "SELECT Surname, Name, Image_Thumb, ID, Online FROM dancers WHERE dancers.Online=Yes "

If (Request.Form("Text") <> "") Then 
strSQL = strSQL & " AND dancers.Surname LIKE '%" & varText & "%' "
End If
If varActors = "true" Then
strSQL = strSQL & " AND dancers.Presenters = 'Yes' "
End If
If varPresenters = "true" Then
strSQL = strSQL & " AND dancers.Presenters = 'Yes' "
End If
If varTalent = "true" Then
strSQL = strSQL & " AND dancers.Talent = 'Yes' "
End If

strSQL = strSQL & " ORDER BY dancers.Surname;"

Set rstSearch = cnnSearch.Execute(strSQL)


	
	Do While Not rstSearch.EOF
		
%><br />
		<%= rstSearch.Fields("Surname").Value %>
		<%= rstSearch.Fields("Name").Value %>
		<a href="/profile.asp?ID=<%=(rstSearch.Fields("ID").Value)%>" target="_self"><img src="<%=(rstSearch.Fields("Image_Thumb").Value)%>" border="1" width="150"></a>
		

<%
		rstSearch.MoveNext
	Loop

	rstSearch.Close
	Set rstSearch = Nothing
	cnnSearch.Close
	Set cnnSearch = Nothing

%>

 

[QatQat]

Is your HTML checkbox set to pass the value "true"?

<input type="checkbox" name="Presenters" VALUE="TRUE">


QatQat

Life is what happens when you are making other plans.

 

[dwarvenmilitia]

Hey again... YEP definately is.

Like it will select it but still all of the values are returned. I have set up search function so if no text is entered it retrieves all values. That's OK but I notice that whether I type a name or not and select say presenters checkbox, it still does not filter them by that checkbox. Any ideas? This is the code in the search.asp page and the last post was search_results.asp

<form action="search_results.asp" method="post">
<p>Select which  category to search:</p>
<p>


  <input type="checkbox" name="Actors" value="TRUE">
  Actors
  <br>
  <input type="checkbox" name="Presenters" value="TRUE">
  Presenters
  <br>
  <input type="checkbox" name="Talent" value="TRUE">
  Talent
  <br>
  Enter a name to search:
  <input type="text" name="Text">
  <input type="submit" value="Submit">
</form>

Cheers

--------------------------------------
I'm unique... Just like everyone else!

 

[QatQat]

the only thing I can think of is that the value "Yes" may be passed with some empty spaces to the database.

Try either trimming the value once requested from the form or try to use a LIKE statement instead of = in your SQL query.


QatQat

Life is what happens when you are making other plans.

 

[dwarvenmilitia]

Like doesn't work. I noticed in my sql query that I had dancers.Presenters = 'Yes' as apposed to dancers.Presenters = Yes but still nothing has changed.

Not too sure how to trim. Although when I force a different error I can see the POST data is "Actors=TRUE&Text=" It should be able to pick out the differences as if I have a text input it would be like "Actors=TRUE&Text=john"

Anywhere else you think I could post a thread for more help on this QatQat?

 

[dwarvenmilitia]

Hey QatQat...

One thing I got stuffed up on. dancers.Actors should have been dancers.Actor... Stupid little errors making issues for me that is almost making me lose it! Cheers for all the help.

--------------------------------------
I'm unique... Just like everyone else! Stupid as it would seem today!