Advanced filter recordset asp

Report · Mar 15, 2011

Hi,

I m trying to filter an asp recordset with % wildcart but impossible to do it.

..!!

Have got a asp form with a dropdown list based on a recordset that stores value (numeric) in a variable (id).

So this variable is used on my result asp page to filter a new recordset by this numeric value.

Im using wild card in order to display all records like this SQL query:

SELECT *
FROM proprietes
WHERE ville LIKE %MMColParam%

MMColParam defined like this :
Numeric
Request.Form("ville")

default value %

So when it generates sql query, it gives SELECT *FROM proprietes WHERE ville LIKE '%MMColParam%' and testing it, it works (testing recordset in dream) but saving it and reopens recordset the query becomes : SELECT *FROM proprietes WHERE ville LIKE %MMColParam% and testing recordset gives error !!

Someone can help me?

Thanks a lot.

Report · Mar 15, 2011

try this

"Select * From proprietes
WHERE ville LIKE %" + MMColParam + "%"

Report · Mar 15, 2011

I become crazy it doesn't work and i don't understand !

Thanks

Report · Mar 15, 2011

post your code as it is written and let me check it

Report · Mar 15, 2011

Hi thanks a lot

Here my asp form :

Request form "ville" value is coming from access database and is a numeric value (autoid) wich is used to filer recordset on results page.
I done this type of fiter several times with other version of dreamweaver but with CS4 impossible to do it (or i missed something!!)


<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="Connections/immo.asp" -->
<%
Dim local
Dim local_cmd
Dim local_numRows
Set local_cmd = Server.CreateObject ("ADODB.Command")
local_cmd.ActiveConnection = MM_immo_STRING
local_cmd.CommandText = "SELECT * FROM villes" 
local_cmd.Prepared = true
Set local = local_cmd.Execute
local_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Document sans titre</title>
</head>
<body>
<p> </p>
<p> </p>
<form id="form1" name="form1" method="post" action="resultats3.asp">
  <p> </p>
  <p>
    <label>
      <select name="ville" id="ville">
        <option value="%" <%If (Not isNull((local.Fields.Item("idville").Value))) Then If ("%" = CStr((local.Fields.Item("idville").Value))) Then Response.Write("selected=""selected""") : Response.Write("")%>>Indifférent</option>
        <%
While (NOT local.EOF)
%>
<option value="<%=(local.Fields.Item("idville").Value)%>" <%If (Not isNull((local.Fields.Item("idville").Value))) Then If (CStr(local.Fields.Item("idville").Value) = CStr((local.Fields.Item("idville").Value))) Then Response.Write("selected=""selected""") : Response.Write("")%> ><%=(local.Fields.Item("ville").Value)%></option>
        <%
  local.MoveNext()
Wend
If (local.CursorType > 0) Then
  local.MoveFirst
Else
  local.Requery
End If
%>
      </select>
    </label>
  </p>
  <p> </p>
  <p> </p>
  <p>
    <label>
      <input type="submit" name="button" id="button" value="Envoyer" />
    </label>
  </p>
</form>
<p> </p>
<p> </p>
</body>
</html>
<%
local.Close()
Set local = Nothing
%>

Here my asp results page:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="Connections/immo.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "%"
If (Request.Form("ville") <> "") Then 
  Recordset1__MMColParam = Request.Form("ville")
End If
%>
<%
Dim Recordset1
Dim Recordset1_cmd
Dim Recordset1_numRows
Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_immo_STRING
Recordset1_cmd.CommandText = "SELECT * FROM proprietes WHERE ville Like ?" 
Recordset1_cmd.Prepared = true
Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter("param1", 5, 1, -1, "%" + Recordset1__MMColParam + "%") ' adDouble
Set Recordset1 = Recordset1_cmd.Execute
Recordset1_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Document sans titre</title>
</head>
<body>
<p><br />
  <%
response.write(request.form("ville"))%>
  <br />
  <br />
  SELECT * <br />
  FROM proprietes<br />
  WHERE ville LIKE '%vil%'
</p>
<p> </p>
<p><br />
  <br />
</p>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

Report · Mar 15, 2011

>Recordset1_cmd.Parameters.Append Recordset1_cmd.CreateParameter

>("param1", 5, 1, -1, "%" + Recordset1__MMColParam + "%") ' adDouble

You've defined the parameter type as a double. What is the correct datatype? The SQL Like predicate can only be used with text/char types.

EDIT: Ok, I see in your first post that this is a numeric value. You can't use like for numeric data types. Are you trying to search for a range? Or just search for everything?

Report · Mar 15, 2011

Hi,

Thanks for your response.

EDIT: Ok, I see in your first post that this is a numeric value. You can't use like for numeric data types. Are you trying to search for a range? Or just search for everything?

I m just trying to search for a specific numeric value or display all values for a % default value.

Regards.

Report · Mar 15, 2011

>I m just trying to search for a specific numeric

>value or display all values for a % default value.

I assume it is working fine if searching for a specific value correct?

To return all values, you will need to add code that evaluates the value passed from the form. If it is the wildcard character (or whatever you define), then don't include a where clause in your SQL. I don't think there's a way to do this in the server behavior but I could be wrong. I dont typically use server behaviors for recordsets. Look for something in there like 'optional parameter'. Otherwise, you'll need to code this by hand:

If (Request.Form("ville") <> "%") Then
Recordset1_cmd.CommandText = "SELECT * FROM proprietes WHERE ville Like ?"

Else

Recordset1_cmd.CommandText = "SELECT * FROM proprietes"
End If

Report · Mar 16, 2011

Hi

Thanks for your response.

I will do it like you wrote

This was possible with Ultradev (macromedia) i ve done it with server behavior but it appears that with dremweaver it's impossible.

Have a nice day.