?
Solved

Search and resultspage with multiple seach fields (checkboxes and textfields)

Posted on 2006-03-22
15
Medium Priority
?
183 Views
Last Modified: 2010-04-25
I have a search page (soeg.asp) where I want the users to search from multiple search fields.

The search fields are:
- Name (text field)
- Zip code 1 (text field)
- Zip code 2 (text field), (so that you can narrow results between zip codes)
- 6 different checkboxes

Right now my name search is being ignored - zipcodes and checkboxes are OK.

Below you will find the form search page
<form action="resultat_soeg.asp" method="post" name="form1" target="_self">
<input name="strNavnE" type="text" id="strNavnE">
<input name="strPostnummerET" type="text" id="strPostnummerET" size="10">
<input name="strPostnummerTO" type="text" id="strPostnummerTO" size="10">
<input name="intKvitto" type="checkbox" id="intKvitto" value="1">
<input name="intAfslag" type="checkbox" id="intAfslag" value="1">
<input name="intAfslaaet" type="checkbox" id="intAfslaaet" value="1">
<input name="intTalentBank" type="checkbox" id="intTalentBank" value="1">
<input name="intTlfInterview" type="checkbox" id="intTlfInterview" value="1">
<input name="intPersInterview" type="checkbox" id="intPersInterview" value="1">
<input type="submit" name="Submit" value="S&oslash;g">
<input type="reset" name="Submit2" value="Annull&eacute;r">
</form>                                        

Below you will find the dreamweaver genereated code from the resultspage:
<%
Dim RSCV__soegNavn
RSCV__soegNavn = "noName"
If (Request.Form("strNavnE")    <> "") Then
  RSCV__soegNavn = Request.Form("strNavn")  
End If
%>
<%
Dim RSCV__soegPostnummerET
RSCV__soegPostnummerET = "999999"
If (Request.Form("strPostnummerET")                   <> "") Then
  RSCV__soegPostnummerET = Request.Form("strPostnummerET")                  
End If
%>
<%
Dim RSCV__soegPostnummerTO
RSCV__soegPostnummerTO = "9999"
If (Request.Form("strPostnummerTO")                   <> "") Then
  RSCV__soegPostnummerTO = Request.Form("strPostnummerTO")                  
End If
%>
<%
Dim RSCV__soegKvitto
RSCV__soegKvitto = "0"
If (Request.Form("intKvitto") <> "") Then
  RSCV__soegKvitto = Request.Form("intKvitto")
End If
%>
<%
Dim RSCV__soegAfslag
RSCV__soegAfslag = "0"
If (Request.Form("intAfslag") <> "") Then
  RSCV__soegAfslag = Request.Form("intAfslag")
End If
%>
<%
Dim RSCV__soegAfslaaet
RSCV__soegAfslaaet = "0"
If (Request.Form("intAfslaaet") <> "") Then
  RSCV__soegAfslaaet = Request.Form("intAfslaaet")
End If
%>
<%
Dim RSCV__soegTalentbank
RSCV__soegTalentbank = "0"
If (Request.Form("intTalentbank") <> "") Then
  RSCV__soegTalentbank = Request.Form("intTalentbank")
End If
%>
<%
Dim RSCV__soegTlfinterview
RSCV__soegTlfinterview = "0"
If (Request.Form("intTlfinterview") <> "") Then
  RSCV__soegTlfinterview = Request.Form("intTlfinterview")
End If
%>
<%
Dim RSCV__soegPersinterview
RSCV__soegPersinterview = "0"
If (Request.Form("intPersInterview") <> "") Then
  RSCV__soegPersinterview = Request.Form("intPersInterview")
End If
%>

'The SQL from the SQL dialog box:
SELECT * FROM tblAnsog
WHERE (strNavn LIKE '%soegNavn%') AND
(strPostnummer BETWEEN (soegPostnummerET) AND (soegPostnummerTO)) AND
((intKvitto = 1 and soegKvitto = 1) OR soegKvitto = 0) AND
((intAfslag = 1 AND soegAfslag = 1) OR soegAfslag = 0) AND
((intAfslaaet = 1 and soegAfslaaet = 1) OR soegAfslaaet = 0) AND
((intTalentbank = 1  and soegTalentbank = 1) OR soegTalentbank = 0) AND
((intTlfinterview = 1  and soegTlfinterview = 1) OR soegTlfinterview = 0) AND
((intPersinterview = 1  and soegPersinterview = 1) OR soegPersinterview = 0)
0
Comment
Question by:jensjakobsen
  • 9
  • 6
15 Comments
 
LVL 1

Author Comment

by:jensjakobsen
ID: 16267926
:(

No experts?
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 16278607
Can you show me how you call the SQL statement from inside ASP?  I need to see how ASP is passing the parameters to the database
0
 
LVL 1

Author Comment

by:jensjakobsen
ID: 16293416
Hi - thank you for answering.

I would prefer if I can keep everything within Dreamweavers ability to edit via it's WYSIWYG interface.

Below is the WHOLE code:


<!--%@LANGUAGE="VBSCRIPT"%-->
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/connFeedback.asp" -->
<%
Dim RSCV__soegNavn
RSCV__soegNavn = "0"
If (Request.Form("strNavn")      <> "") Then
RSCV__soegNavn = Request.Form("strNavn")    
End If
%>
<%
Dim RSCV__soegPostnummerET
RSCV__soegPostnummerET = "999999"
If (Request.Form("strPostnummerET")                   <> "") Then
RSCV__soegPostnummerET = Request.Form("strPostnummerET")                  
End If
%>
<%
Dim RSCV__soegPostnummerTO
RSCV__soegPostnummerTO = "9999999"
If (Request.Form("strPostnummerTO")                    <> "") Then
RSCV__soegPostnummerTO = Request.Form("strPostnummerTO")                  
End If
%>
<%
Dim RSCV__soegKvitto
RSCV__soegKvitto = "0"
If (Request.Form("intKvitto") <> "") Then
RSCV__soegKvitto = Request.Form("intKvitto")
End If
%>
<%
Dim RSCV__soegAfslag
RSCV__soegAfslag = "0"
If (Request.Form("intAfslag") <> "") Then
RSCV__soegAfslag = Request.Form("intAfslag")
End If
%>
<%
Dim RSCV__soegAfslaaet
RSCV__soegAfslaaet = "0"
If (Request.Form("intAfslaaet") <> "") Then
RSCV__soegAfslaaet = Request.Form("intAfslaaet")
End If
%>
<%
Dim RSCV__soegTalentbank
RSCV__soegTalentbank = "0"
If (Request.Form("intTalentbank") <> "") Then
RSCV__soegTalentbank = Request.Form("intTalentbank")
End If
%>
<%
Dim RSCV__soegTlfinterview
RSCV__soegTlfinterview = "0"
If (Request.Form("intTlfinterview") <> "") Then
RSCV__soegTlfinterview = Request.Form("intTlfinterview")
End If
%>
<%
Dim RSCV__soegPersinterview
RSCV__soegPersinterview = "0"
If (Request.Form("intPersInterview") <> "") Then
RSCV__soegPersinterview = Request.Form("intPersInterview")
End If
%>
<%
Dim RSCV
Dim RSCV_numRows

Set RSCV = Server.CreateObject("ADODB.Recordset")
RSCV.ActiveConnection = MM_connFeedback_STRING
RSCV.Source = "SELECT *  FROM tblAnsog  WHERE (strNavn LIKE '%" + Replace(RSCV__soegNavn, "'", "''") + "%') AND (strPostnummer BETWEEN (" + Replace(RSCV__soegPostnummerET, "'", "''") + ") AND (" + Replace(RSCV__soegPostnummerTO, "'", "''") + ")) AND ((intKvitto = 1 and " + Replace(RSCV__soegKvitto, "'", "''") + " = 1) OR " + Replace(RSCV__soegKvitto, "'", "''") + " = 0) AND ((intAfslag = 1 AND " + Replace(RSCV__soegAfslag, "'", "''") + " = 1) OR " + Replace(RSCV__soegAfslag, "'", "''") + " = 0) AND ((intAfslaaet = 1 and " + Replace(RSCV__soegAfslaaet, "'", "''") + " = 1) OR " + Replace(RSCV__soegAfslaaet, "'", "''") + " = 0) AND ((intTalentbank = 1  and " + Replace(RSCV__soegTalentbank, "'", "''") + " = 1) OR " + Replace(RSCV__soegTalentbank, "'", "''") + " = 0) AND ((intTlfinterview = 1  and " + Replace(RSCV__soegTlfinterview, "'", "''") + " = 1) OR " + Replace(RSCV__soegTlfinterview, "'", "''") + " = 0) AND ((intPersinterview = 1  and " + Replace(RSCV__soegPersinterview, "'", "''") + " = 1) OR " + Replace(RSCV__soegPersinterview, "'", "''") + " = 0)  ORDER BY strPostnummer ASC"
RSCV.CursorType = 0
RSCV.CursorLocation = 2
RSCV.LockType = 1
RSCV.Open()

RSCV_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
RSCV_numRows = RSCV_numRows + Repeat1__numRows
%>
<%
'  *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

Dim RSCV_total
Dim RSCV_first
Dim RSCV_last

' set the record count
RSCV_total = RSCV.RecordCount

' set the number of rows displayed on this page
If (RSCV_numRows < 0) Then
RSCV_numRows = RSCV_total
Elseif (RSCV_numRows = 0) Then
RSCV_numRows = 1
End If

' set the first and last displayed record
RSCV_first = 1
RSCV_last  = RSCV_first + RSCV_numRows - 1

' if we have the correct record count, check the other stats
If (RSCV_total <> -1) Then
If (RSCV_first > RSCV_total) Then
RSCV_first = RSCV_total
End If
If (RSCV_last > RSCV_total) Then
RSCV_last = RSCV_total
End If
If (RSCV_numRows > RSCV_total) Then
RSCV_numRows = RSCV_total
End If
End If
%>

<%
' *** Recordset Stats: if we don't know the record count, manually count them

If (RSCV_total = -1) Then

' count the total records by iterating through the recordset
RSCV_total=0
While (Not RSCV.EOF)
RSCV_total = RSCV_total + 1
RSCV.MoveNext
Wend

' reset the cursor to the beginning
If (RSCV.CursorType > 0) Then
RSCV.MoveFirst
Else
RSCV.Requery
End If

' set the number of rows displayed on this page
If (RSCV_numRows < 0 Or RSCV_numRows > RSCV_total) Then
RSCV_numRows = RSCV_total
End If

' set the first and last displayed record
RSCV_first = 1
RSCV_last = RSCV_first + RSCV_numRows - 1

If (RSCV_first > RSCV_total) Then
RSCV_first = RSCV_total
End If
If (RSCV_last > RSCV_total) Then
RSCV_last = RSCV_total
End If

End If
%>

<%
Dim MM_paramName
%>
<%
' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters

Dim MM_keepNone
Dim MM_keepURL
Dim MM_keepForm
Dim MM_keepBoth

Dim MM_removeList
Dim MM_item
Dim MM_nextItem

' create the list of parameters which should not be maintained
MM_removeList = "&index="
If (MM_paramName <> "") Then
MM_removeList = MM_removeList & "&" & MM_paramName & "="
End If

MM_keepURL=""
MM_keepForm=""
MM_keepBoth=""
MM_keepNone=""

' add the URL parameters to the MM_keepURL string
For Each MM_item In Request.QueryString
MM_nextItem = "&" & MM_item & "="
If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
MM_keepURL = MM_keepURL & MM_nextItem & Server.URLencode(Request.QueryString(MM_item))
End If
Next

' add the Form variables to the MM_keepForm string
For Each MM_item In Request.Form
MM_nextItem = "&" & MM_item & "="
If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
MM_keepForm = MM_keepForm & MM_nextItem & Server.URLencode(Request.Form(MM_item))
End If
Next

' create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm
If (MM_keepBoth <> "") Then
MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
End If
If (MM_keepURL <> "")  Then
MM_keepURL  = Right(MM_keepURL, Len(MM_keepURL) - 1)
End If
If (MM_keepForm <> "") Then
MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)
End If

' a utility function used for adding additional parameters to these strings
Function MM_joinChar(firstItem)
If (firstItem <> "") Then
MM_joinChar = "&"
Else
MM_joinChar = ""
End If
End Function
%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Admin modul</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="supercss.css" rel="stylesheet" type="text/css">
<script type="text/JavaScript">
<!--
function altRowColors(){ //v2.0
var args = arguments, obj, color0, color1, rowList;
for(var a = 0; a < args.length; a+=3){
obj = args[a+0]; color0 = args[a+1]; color1 = args[a+2];
obj = (isNaN(obj)) ? document.getElementById(obj) : document.getElementsByTagName("TABLE")[obj];
if(obj) rowList = obj.getElementsByTagName("TR");
for(var i = 0; i < rowList.length; i++) rowList[i].style.backgroundColor = eval('color'+(i % 2));
}
}
//-->
</script>
</head>

<body style="margin:0px; height:100%;" onLoad="altRowColors('data','#ffffff','#f0f0f0')">
<table width="100%" height="100%"  cellpadding="0" cellspacing="0" border="0" style="position:absolute; top:0px; bottom:0px; left:0px; right:0px;">
<tr>
<td width="780" align="left" valign="top">
<table width="100%"  border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<table width="100%"  border="0" cellpadding="0" cellspacing="0">
<tr>
<td align="left" valign="top" class="bg_image">&nbsp;</td>
<td width="185" align="left" valign="top"><img src="images/logo.jpg" width="185" height="48"></td>
</tr>
<tr>
<td align="left" valign="top"><%= Request.Form("strNavn") %></td>
<td align="left" valign="top">&nbsp;</td>
</tr>
</table>
</td>
</tr>
<tr>
<td>
<table width="100%"  border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="10">&nbsp;      </td>
<td align="left" valign="top"><table width="100%"  border="0" cellspacing="0" cellpadding="1">
<tr>
<td width="150" align="left" valign="top"><table width="100%"  border="0" cellspacing="0" cellpadding="1">
<tr>
<td class="admin_fat"><a href="admin.asp">Hjem</a></td>
</tr>
<tr>
<td class="newstext">&nbsp;</td>
</tr>
<tr>
<td class="newstext">&nbsp;</td>
</tr>
</table></td>
<td align="left" valign="top"><table width="100%" border="0">
<tr>
<td class="newsheadline">Avanceret s&oslash;geside p&aring; alle ans&oslash;gere (<%=(RSCV_total)%>) </td>
</tr>
<tr>
<td><form action="resultat_soeg.asp" method="post" name="form1" target="_self">
<table width="100%" border="0">
<tr>
<td align="left" valign="top" class="body_fields">Navn</td>
<td align="left" valign="top" class="body_fields"><input name="strNavn" type="text" id="strNavn" value="<%= Request.Form("strNavn") %>"></td>
</tr>
<tr>
<td align="left" valign="top" class="body_fields">Postnummer 1 </td>
<td align="left" valign="top" class="body_fields"><input name="strPostnummerET" type="text" id="strPostnummerET" value="<%= Request.Form("strPostnummerET") %>" size="10"></td>
</tr>
<tr>
<td align="left" valign="top" class="body_fields">Postnummer 2 </td>
<td align="left" valign="top" class="body_fields"><input name="strPostnummerTO" type="text" id="strPostnummerTO" value="<%= Request.Form("strPostnummerTO") %>" size="10"></td>
</tr>
<tr>
<td width="200" align="left" valign="top" class="body_fields">Har sendt kvittering for ans&oslash;gning </td>
<td align="left" valign="top" class="body_fields"><input <%If (CStr(Request.Form("intKvitto")) = CStr("1")) Then Response.Write("checked=""checked""") : Response.Write("")%> name="intKvitto" type="checkbox" id="intKvitto" value="1"></td>
</tr>
<tr>
<td width="200" align="left" valign="top" class="body_fields">Skal have afslag </td>
<td align="left" valign="top" class="body_fields"><input <%If (CStr(Request.Form("intAfslag")) = CStr("1")) Then Response.Write("checked=""checked""") : Response.Write("")%> name="intAfslag" type="checkbox" id="intAfslag" value="1"></td>
</tr>
<tr>
<td width="200" align="left" valign="top" class="body_fields">Har f&aring;et afslag </td>
<td align="left" valign="top" class="body_fields"><input <%If (CStr(Request.Form("intAfslaaet")) = CStr("1")) Then Response.Write("checked=""checked""") : Response.Write("")%> name="intAfslaaet" type="checkbox" id="intAfslaaet" value="1"></td>
</tr>
<tr>
<td width="200" align="left" valign="top" class="body_fields">Skal i talentbank </td>
<td align="left" valign="top" class="body_fields"><input <%If (CStr(Request.Form("intTalentBank")) = CStr("1")) Then Response.Write("checked=""checked""") : Response.Write("")%> name="intTalentBank" type="checkbox" id="intTalentBank" value="1"></td>
</tr>
<tr>
<td width="200" align="left" valign="top" class="body_fields">Har gennemf&oslash;rt telefoninterview </td>
<td align="left" valign="top" class="body_fields"><input <%If (CStr(Request.Form("intTlfInterview")) = CStr("1")) Then Response.Write("checked=""checked""") : Response.Write("")%> name="intTlfInterview" type="checkbox" id="intTlfInterview" value="1"></td>
</tr>
<tr>
<td width="200" align="left" valign="top" class="body_fields">Har gennemf&oslash;rt personligt interview </td>
<td align="left" valign="top" class="body_fields"><input <%If (CStr(Request.Form("intPersInterview")) = CStr("1")) Then Response.Write("checked=""checked""") : Response.Write("")%> name="intPersInterview" type="checkbox" id="intPersInterview" value="1"></td>
</tr>
<tr>
<td width="200" align="left" valign="top" class="body_fields"><input type="submit" name="Submit" value="S&oslash;g"> <input type="reset" name="Submit2" value="Annull&eacute;r"></td>
<td align="left" valign="top" class="body_fields">For at se ALLE - klik da p&aring; S&Oslash;G uden at markere felter</td>
</tr>
</table>
                        </form>                                            </td>                                          </tr>
</table></td>
</tr>
<tr>
<td align="left" valign="top">&nbsp;</td>
<td align="left" valign="top" class="blue_background">Resultatside (nyeste &oslash;verst) - klik p&aring; link (navn) for at redigere </td>
</tr>
<tr>
<td align="left" valign="top">&nbsp;</td>
<td align="left" valign="top"><table width="100%"  border="0" id="data">
<tr>

<td class="admin_normal">&nbsp;</td>
<td class="admin_normal">Navn </td>
<td class="admin_normal">Alder </td>
<td class="admin_normal">Postnr.</td>
<td class="admin_normal">Kvitt.</td>
<td class="admin_normal">Afsl&aring;s</td>
<td class="admin_normal">Afsl&aring;et</td>
<td class="admin_normal">Talent</td>
<td class="admin_normal">Tlf.int.</td>
<td class="admin_normal">Pers.int.</td>
<td class="admin_normal">Slet</td>
</tr>
<% If Not RSCV.EOF Or Not RSCV.BOF Then %>
<%
While ((Repeat1__numRows <> 0) AND (NOT RSCV.EOF))
%>
<tr>

<td class="body_fields"><%
dim billede
billede = (RSCV.Fields.Item("strPic").Value)
if billede <> "" then
response.write("<img src=../images/camera.gif>")
else
response.write("")
end if
%></td>
<td class="body_fields"><A HREF="cv_profile.asp?<%= Server.HTMLEncode(MM_keepNone) & MM_joinChar(MM_keepNone) & "ansogID=" & RSCV.Fields.Item("ansogID").Value %>"><%=(RSCV.Fields.Item("strNavn").Value)%></A></td>
<td class="body_fields"><%=(RSCV.Fields.Item("strAlder").Value)%></td>
<td class="body_fields"><%=(RSCV.Fields.Item("strPostnummer").Value)%></td>
<td><input name="intKvitto" type="checkbox" id="intKvitto" value="1" <%If (CStr((RSCV.Fields.Item("intKvitto").Value)) = CStr("1")) Then Response.Write("checked") : Response.Write("")%>></td>
<td><input name="intAfslag" type="checkbox" id="intAfslag" value="1" <%If (CStr((RSCV.Fields.Item("intAfslag").Value)) = CStr("1")) Then Response.Write("checked=""checked""") : Response.Write("")%>></td>
<td><input <%If (CStr((RSCV.Fields.Item("intAfslaaet").Value)) = CStr("1")) Then Response.Write("checked=""checked""") : Response.Write("")%> name="intAfslaaet" type="checkbox" id="intAfslaaet" value="1"></td>
<td><input <%If (CStr((RSCV.Fields.Item("intTalentBank").Value)) = CStr("1")) Then Response.Write("checked=""checked""") : Response.Write("")%> name="intTalentbank" type="checkbox" id="intTalentbank" value="1"></td>
<td><input <%If (CStr((RSCV.Fields.Item("intTlfInterview").Value)) = CStr("1")) Then Response.Write("checked=""checked""") : Response.Write("")%> name="intTlfinterview" type="checkbox" id="intTlfinterview" value="1"></td>
<td><input <%If (CStr((RSCV.Fields.Item("intPersInterview").Value)) = CStr("1")) Then Response.Write("checked=""checked""") : Response.Write("")%> name="intPersinterview" type="checkbox" id="intPersinterview" value="1"></td>
<td class="body_fields"><A HREF="cv_delete_profile.asp?<%= Server.HTMLEncode(MM_keepNone) & MM_joinChar(MM_keepNone) & "ansogID=" & RSCV.Fields.Item("ansogID").Value %>">Slet</A></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
RSCV.MoveNext()
Wend
%>
<% End If ' end Not RSCV.EOF Or NOT RSCV.BOF %>
<% If RSCV.EOF And RSCV.BOF Then %>
<tr>
<td colspan="12" class="admin_delete">Der var ingen personer der matchede din s&oslash;gning - venligst s&oslash;g igen </td>
</tr>
<% End If ' end RSCV.EOF And RSCV.BOF %>
</table></td>
</tr>
<tr>
<td align="left" valign="top">&nbsp;</td>
<td align="left" valign="top">&nbsp;</td>
</tr>
</table>      </td>
<td width="10">&nbsp;      </td>
</tr>
<tr>
<td>&nbsp;      </td>
<td align="left" valign="top"><%=request.querystring(RSCV.Source)%></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td align="left" valign="top">&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td align="left" valign="top">&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td align="left" valign="top">&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td align="left" valign="top">&nbsp;</td>
<td>&nbsp;</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td align="left" valign="top" class="wastecolor" style="border-left: solid 1px #BBBEC9">&nbsp;</td>
</tr>
</table>
</body>
</html>
<%
RSCV.Close()
Set RSCV = Nothing
%>
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:jensjakobsen
ID: 16293421
The code is from the results page
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 16298282
Okay your logic (or rather dreamweaver's) is doing this;

Declare variable RSCV__soegNavn and set it to 0
If a name value is entered in the form, change value of RSCV__soegNavn to be that value entered, otherwise leave as 0
Select values from database where strNavn is like RSCV__soegNavn

Some debugging is needed here to find out why the value on the form is not being used.  I need you to write the completed SELECT statement into the browser window before the search runs.

Find this line in your code (around line 74)
    RSCV.CursorType = 0

Before it write this line
   Response.Write("<hr/>" & RSCV.Source & "<hr/>")

This will show you the select statement that is being passed to the database.  It will be enclosed in 2 horizontal rules.  Copy that information and paste it into a reply so I can see whats happening.
0
 
LVL 1

Author Comment

by:jensjakobsen
ID: 16300914
Hi again. Thanks a lot for your time.

Here is what the search read.

SELECT * FROM tblAnsog WHERE (strNavn LIKE '%Johnny%') AND (strPostnummer BETWEEN (999999) AND (9999999)) AND ((intKvitto = 1 and 0 = 1) OR 0 = 0) AND ((intAfslag = 1 AND 0 = 1) OR 0 = 0) AND ((intAfslaaet = 1 and 0 = 1) OR 0 = 0) AND ((intTalentbank = 1 and 0 = 1) OR 0 = 0) AND ((intTlfinterview = 1 and 0 = 1) OR 0 = 0) AND ((intPersinterview = 1 and 0 = 1) OR 0 = 0) ORDER BY strPostnummer ASC

The search was a success but I guess all them ANDs and ORs are to blame?
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 16301014
>>The search was a success but I guess all them ANDs and ORs are to blame?

That depends on what you wanted to happen!  The OR's are actually in brackets which means they are not directly influencing the results.  The AND's are the important part here.  Here's the statement broken down making it easier to read:

SELECT * FROM tblAnsog WHERE (strNavn LIKE '%Johnny%')
  AND (strPostnummer BETWEEN (999999) AND (9999999))
  AND ((intKvitto = 1 and 0 = 1) OR 0 = 0)
  AND ((intAfslag = 1 AND 0 = 1) OR 0 = 0)
  AND ((intAfslaaet = 1 and 0 = 1) OR 0 = 0)
  AND ((intTalentbank = 1 and 0 = 1) OR 0 = 0)
  AND ((intTlfinterview = 1 and 0 = 1) OR 0 = 0)
  AND ((intPersinterview = 1 and 0 = 1) OR 0 = 0)
ORDER BY strPostnummer ASC

Every single one of those AND statements much match before the search finds anything.  Therefore, its actually working because if it found Johnny's name then "Johnny" was definitely being searched for from the form.
If you miss "Johnny" out it won't find anything because it will resort back to strNavn = 0, and create a search command like this:

SELECT * FROM tblAnsog WHERE (strNavn LIKE '%0%')
  AND (strPostnummer BETWEEN (999999) AND ............................. etc etc

therefore only finding names containing the value 0.
0
 
LVL 1

Author Comment

by:jensjakobsen
ID: 16301135
OK - what I wanted to happen that I wanted to find any name called Johnny - even though I didnt search in any other fields. But my search didnt match anything :/

So basically I want the search to display results even if I don't fill out any search fields.
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 16301161
In that case you would change the AND's to OR's, so that the rest of the search is not necessary.  Try this (but backup the original first!):

SELECT * FROM tblAnsog WHERE (strNavn LIKE '%Johnny%')
OR
((strPostnummer BETWEEN (999999) AND (9999999))
  AND ((intKvitto = 1 and 0 = 1) OR 0 = 0)
  AND ((intAfslag = 1 AND 0 = 1) OR 0 = 0)
  AND ((intAfslaaet = 1 and 0 = 1) OR 0 = 0)
  AND ((intTalentbank = 1 and 0 = 1) OR 0 = 0)
  AND ((intTlfinterview = 1 and 0 = 1) OR 0 = 0)
  AND ((intPersinterview = 1 and 0 = 1) OR 0 = 0))
ORDER BY strPostnummer ASC

Dreamweaver isn't very clever at doing anything but simple searches, so eventually you've got no choice but to get stuck in and learn how to do the searches yourself.
Within the search dialog, just paste my attempt above over the top of whatever dreamweaver currently has.  You might have to go into Expert mode.
0
 
LVL 1

Author Comment

by:jensjakobsen
ID: 16304507
Thx for your advice - I'll try it tomorrow at this time.
0
 
LVL 1

Author Comment

by:jensjakobsen
ID: 16308152
How odd - I changed the SQL as you asked me to but the search gave me this result when I searched for Johnny:

SELECT * FROM tblAnsog WHERE (strNavn LIKE '%0%') OR (strPostnummer BETWEEN (999999) AND (9999999)) AND ((intKvitto = 1 and 0 = 1) OR 0 = 0) AND ((intAfslag = 1 AND 0 = 1) OR 0 = 0) AND ((intAfslaaet = 1 and 0 = 1) OR 0 = 0) AND ((intTalentbank = 1 and 0 = 1) OR 0 = 0) AND ((intTlfinterview = 1 and 0 = 1) OR 0 = 0) AND ((intPersinterview = 1 and 0 = 1) OR 0 = 0) ORDER BY strPostnummer ASC

and the result is still no matches (but of course there is a matching name in my database)...
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 16308374
This might be because I didn't convert the SQL back to the format dreamweaver was expecting it.  Go to the line that starts with:

   RSCV.Source =

and replace it with this statement:

RSCV.Source = "SELECT *  FROM tblAnsog  WHERE (strNavn LIKE '%" + Replace(RSCV__soegNavn, "'", "''") + "%') OR ((strPostnummer BETWEEN (" + Replace(RSCV__soegPostnummerET, "'", "''") + ") AND (" + Replace(RSCV__soegPostnummerTO, "'", "''") + ")) AND ((intKvitto = 1 and " + Replace(RSCV__soegKvitto, "'", "''") + " = 1) OR " + Replace(RSCV__soegKvitto, "'", "''") + " = 0) AND ((intAfslag = 1 AND " + Replace(RSCV__soegAfslag, "'", "''") + " = 1) OR " + Replace(RSCV__soegAfslag, "'", "''") + " = 0) AND ((intAfslaaet = 1 and " + Replace(RSCV__soegAfslaaet, "'", "''") + " = 1) OR " + Replace(RSCV__soegAfslaaet, "'", "''") + " = 0) AND ((intTalentbank = 1  and " + Replace(RSCV__soegTalentbank, "'", "''") + " = 1) OR " + Replace(RSCV__soegTalentbank, "'", "''") + " = 0) AND ((intTlfinterview = 1  and " + Replace(RSCV__soegTlfinterview, "'", "''") + " = 1) OR " + Replace(RSCV__soegTlfinterview, "'", "''") + " = 0) AND ((intPersinterview = 1  and " + Replace(RSCV__soegPersinterview, "'", "''") + " = 1) OR " + Replace(RSCV__soegPersinterview, "'", "''") + " = 0))  ORDER BY strPostnummer ASC"

If this doesn't work then we're going to have to use 2 sql statements and select one depending on what form values are selected.
0
 
LVL 1

Author Comment

by:jensjakobsen
ID: 16313284
Sorry - didn't work :(
0
 
LVL 25

Accepted Solution

by:
Rouchie earned 2000 total points
ID: 16318921
That's okay.  Let's attack it from a more simple angle then.  Do you have a version of the SQL code that works when a combination of name and checkboxes are used?  If so please can you post it?  We can then craft a seperate search for the name only.
0
 
LVL 1

Author Comment

by:jensjakobsen
ID: 16319037
will do - haven't got the time today. I'll get back to you.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
Integration Management Part 2
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question