Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

Advanced filter recordset asp

Community Beginner ,
Mar 15, 2011 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.

TOPICS
Server side applications
2.8K
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 15, 2011 Mar 15, 2011

try this

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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 15, 2011 Mar 15, 2011

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

Thanks

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 15, 2011 Mar 15, 2011

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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 15, 2011 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
%>

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Mar 15, 2011 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?

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 15, 2011 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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Mar 15, 2011 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

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 16, 2011 Mar 16, 2011
LATEST

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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines