Hi there,

I try to build a dynamic where. Since I always used it on numeric values, I didn’t encounter any problems. But now I want to build one to search columns of text.
And then it doesn’t work at all and I can’t seem to find the right syntax.
When amending the sql string with single quotes so the recordset knows the input is a string instead of numeric, Dreamweaver persists in making the single to double quotes. Even when inserted in a string like:
<%
Dim sql
If (request.form("naam"<>"" and (request.form("kleur"<>""then
sql="WHERE Product = " & "'" & Request.form("naam"& "'" & " AND kleuren = "& "'"& Request.form("kleur"& "'"
end if
%>
The outcome is (when submitting Mirny as name and wit as kleur in a form directing to the result.asp) a syntax error which says that an operator is missing in the query and then displays:
Product = "Mirny" AND kleuren = "wit"
So it made single to double quotes

So I’m stuck trying to use a dynamic where clause on columns containing text. And I still don’t understand how Dreamweaver can make single to double quotes and how to prevent it from doing so.
Down below I made an example of what I try to do. So you can see it is based on numeric values. When these columns should be text, how can I amend my code to function?
Who can help me out here?

JJF





<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/final.asp" -->

<%
Dim sql
If (request.form("naam"<>"-1" and (request.form("kleur"<>"-1"then
sql="WHERE Product = " & Request("naam"& " AND kleuren = "& Request("kleur"
end if
If (request.form("naam"<>"-1" and (request.form("kleur"="-1"then
sql="WHERE Product = "&Request("naam"
end if
If (request.form("naam"="-1" and (request.form("kleur"<>"-1"then
sql="WHERE kleuren = "&Request("kleur"
end if
%>
<%
Dim Recordset1__sql
Recordset1__sql = "where id<>-1"
if (sql <> "" then Recordset1__sql = sql
%>
<%
set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = MM_final_STRING
Recordset1.Source = "SELECT * FROM Producten " + Replace(Recordset1__sql, "'", "''" + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>

<%
Recordset1.Close()
%>



Edited by - Jumping Jack Flash on 04 Sep 2003 13:15:16

<img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>Try this:

&lt;%
Dim sql
If (request.form("naam"&lt;&gt;"" and (request.form("kleur"&lt;&gt;""then
sql="WHERE Product = " & "'" & Request("naam" & "'" & " AND Kleuren = " & "'" & Request("kleur" & "'"
end if
If (request.form("naam"&lt;&gt;"" and (request.form("kleur"=""then
sql="WHERE Product = " & "'" & Request("naam" & "'"
end if
If (request.form("naam"="" and (request.form("kleur"&lt;&gt;""then
sql="WHERE Kleuren = " & "'" & Request("kleur" & "'"
end if
%&gt;


Modify the recordset from this:

&lt;%
Dim Recordset1__sql
Recordset1__sql = "where id&lt;&gt;-1"
if (sql &lt;&gt; "" then Recordset1__sql = sql
%&gt;
&lt;%
set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = MM_final_STRING
Recordset1.Source = "SELECT * FROM Producten " + Replace(Recordset1__sql, "'", "''" + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0
%&gt;


To this:

&lt;%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = MM_final_STRING
Recordset1.Source = "SELECT * FROM Producten " & Recordset1__sql
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()

Recordset1_numRows = 0
%&gt;

Good luck!

Edited by - jellejacob on 04 Sep 2003 13:55:30

JJ you're great as ever my friend
Let's rock this Internet

JJF