Hi everyone,

Who can help? I need urgent assistance with a problem i have been struggling for days now. My deadline is rapidly approaching.

We are building an ASP website with a real estate database in Ultradev. It is the search results page that gives trouble. People can search for real estate by following a simple two step process.
On page one they choose a region and they pick the type of real estate they look for (office building/industrial/other). On the next page the client wants to present them a checkboxlist of cities in that region with
real estate of the choosen kind. The visitor should be able to select multiple cities via the checkboxes. The visitor also can determine the number of square meters he is looking for.

He is then taken to the results masterpage where he gets an overview of the available projects.
It is this page that is killing my nights

Because the checkboxes are dynamically rendered, they all have the same name; "RECity".
On the resultspage i wrote a simple script that takes the Querystring and produces a variable string like "Amsterdam, Schiphol, Rotterdam".
The next line in my script replaces all comma-spaces with ";". Because this is the format MS Acces requires for an IN operator.
Access wants it SQL like this; "WHERE REcity IN ("Amsterdam";"Schiphol";"Rotterdam"".
I have tested the query in Access and it works fine.

The problem lies in Ultradev and the way it handles code and server behaviours.

I actually have a working page which shows the records i want. But in that page i wrote some ASP myself. And that gives me big trouble with the other server behaviours on that page.
Because i rewrote the ASP for the recordset, the other server extensions don't work anymore. The Recordset "rsCats" is not recognised by Ultradev anymore.
And the logic in the page is fairly complex so i need those server behaviours.

So i guessed that the right way to handle this is to keep the recordset from Ultradev intact and adjust some surrounding code instead.
Utradev does some "replaces" and defines some variables for this recordset.
If i can come up with the right adjustment it should be possible the have the recordset send the appropriate SQL to the database.

But i just don't see it or i don't understand it.
Anyone who would help would be more than welcome.
I am pretty desperate now.
I know it is something stupid and small, like a comma or a hyphen or something like that.

Friendly greetings, a happy newyear to you all and many thanks to the one who helps me out..


---------------------------------------------------------------------
This the relevant code i have in the page now:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Makes = Request.QueryString("ReCity" ' turn the querystring into een nice string like: Amsterdam, Schiphol, ... etc.
MakeCity = Replace(Makes, ", ", "';'" ' Replace the comma-spaces
%>
<%
Dim rsCats__FormCity
rsCats__FormCity = "*"
if (Request("MakeCity" <> "" then rsCats__FormCity = Request("MakeCity"
%>
<%
set rsCats = Server.CreateObject("ADODB.Recordset"
rsCats.ActiveConnection = MM_cnnjll_STRING
rsCats.Source = "SELECT RECity, REActive, RECategory, REHuddlesLink, REID, REKind, REName, RERegion, REShortDescr, RESquareMin, RESquareMax FROM tblNLRE WHERE REActive= 'ja' AND RECategory LIKE '" + Replace(rsCats__FormCat, "'", "''" + "' AND RERegion LIKE '" + Replace(rsCats__FormRegion, "'", "''" + "' AND RECity IN ('" & " + Replace(rsCats__FormCity, "'", "''" + " & "') ORDER BY REHuddlesLink, RECity, REName"
rsCats.CursorType = 0
rsCats.CursorLocation = 2
rsCats.LockType = 3
rsCats.Open()
rsCats_numRows = 0
%>
---------------------------------------------------------------------
This is the code i have in my own page that worked:

<%
makes = Request.QueryString("ReCity" ' maakt een string: Amsterdam, Schiphol, ... etc.
MakeCity = Replace(makes, ", ", "','" ' Vervang komma-spatie in aanhalingteken-punt/komma-aanhalingsteken
MijnSQL = "SELECT RECity, REActive, RECategory, REHuddlesLink, REID, REKind, REName, RERegion, REShortDescr, RESquareMin, RESquareMax FROM tblNLRE WHERE REActive= 'ja' AND ReCity IN ('" & MakeCity & "')"
%>
<%
Dim rsCats__FormCity
rsCats__FormCity = "*"
if (MijnSQL <> "" then rsCats__FormCity = Request("MijnSQL"
%>
<%
'My recordset that works
'set rsBats = Server.CreateObject("ADODB.Recordset"
'rsCats.ActiveConnection = MM_cnnjll_STRING
'rsCats.Source = MijnSQL
'rsCats.CursorType = 0
'rsCats.CursorLocation = 2
'rsCats.LockType = 3
'rsCats.Open()
'rsCats_numRows = 0
%>

Hi.... I am struggling with a simpler version of the same problem in Dreamweaver MX.

I have a database application dealing with event types that I'm trying to write a front end for. I have created a SQL query that selects events based on start date AND multiple event types ( ie MTG for "Meeting", CON for "Conference" etc. )

I am trying to query a SQL Anwywhere database and I have built a query in Infomaker ( Client Server report writer ) that accepts a date and a string array as arguments.

For example:

SELECT ev200_event_master.ev200_evt_id,
ev200_event_master.ev200_evt_desc,
ev200_event_master.ev200_mi_date,
ev200_event_master.ev200_mo_date,
ev200_event_master.ev200_evt_type
FROM ev200_event_master
WHERE ( ev200_event_master.ev200_evt_type in ( :Event_type ) ) AND
( ev200_event_master.ev200_mi_date >= :Start_date )

ORDER BY ev200_event_master.ev200_mi_date ASC

Pretty simple ? You don't have to know much SQL to see how easy this is. It works fine. The problem is that in Dreamweaver, no matter how I construct the query - I can only retrieve ONE event type with the following syntax ( essentially the same )

SELECT EV200_EVT_ID, EV200_EVT_DESC, EV200_MI_DATE, EV200_MO_DATE, EV200_EVT_TYPE
FROM gpcb.EV200_EVENT_MASTER
WHERE EV200_MI_DATE >= ? AND EV200_EVT_TYPE IN (?)
ORDER BY EV200_MI_DATE ASC

I am testing the query directly with the advanced query builder in Dreamweaver MX and it works for ONE type ( ie 'MTG) but if I try to pass the string array THE WAY I KNOW THE DATABASE WANTS IT - it doesn't work.

I admire your efforts to construct the string using code but I'm punching it in directly to the query builder and it just won't work. Why?

I have the same problem with a simple query on a totally different database platform. I can't see why this is so difficult. It's just supposed to pass text to the database engine...

Somebody must have an answer for this?

Yep, it stinks big time!
But i posted the same question on Experts Exchange and this delivered a handfull of answers. Some of them might be usefull.
Check them out yourself at: www.experts-exchange.com/Web/Web_Languages/ASP/Q_20440053.html and on
www.experts-exchange.com/Web/WebDevSoftware/UltraDev/Q_20440048.html.
Willl try them out this morning. I have been truggling till 3 in the night yesterday. Hope i am able to understand what they write <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>
I will let you know.


<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Hi.... I am struggling with a simpler version of the same problem in Dreamweaver MX.

I have a database application dealing with event types that I'm trying to write a front end for. I have created a SQL query that selects events based on start date AND multiple event types ( ie MTG for "Meeting", CON for "Conference" etc. )

I am trying to query a SQL Anwywhere database and I have built a query in Infomaker ( Client Server report writer ) that accepts a date and a string array as arguments.

For example:

SELECT ev200_event_master.ev200_evt_id,
ev200_event_master.ev200_evt_desc,
ev200_event_master.ev200_mi_date,
ev200_event_master.ev200_mo_date,
ev200_event_master.ev200_evt_type
FROM ev200_event_master
WHERE ( ev200_event_master.ev200_evt_type in ( :Event_type ) ) AND
( ev200_event_master.ev200_mi_date &gt;= :Start_date )

ORDER BY ev200_event_master.ev200_mi_date ASC

Pretty simple ? You don't have to know much SQL to see how easy this is. It works fine. The problem is that in Dreamweaver, no matter how I construct the query - I can only retrieve ONE event type with the following syntax ( essentially the same )

SELECT EV200_EVT_ID, EV200_EVT_DESC, EV200_MI_DATE, EV200_MO_DATE, EV200_EVT_TYPE
FROM gpcb.EV200_EVENT_MASTER
WHERE EV200_MI_DATE &gt;= ? AND EV200_EVT_TYPE IN (?)
ORDER BY EV200_MI_DATE ASC

I am testing the query directly with the advanced query builder in Dreamweaver MX and it works for ONE type ( ie 'MTG) but if I try to pass the string array THE WAY I KNOW THE DATABASE WANTS IT - it doesn't work.

I admire your efforts to construct the string using code but I'm punching it in directly to the query builder and it just won't work. Why?

I have the same problem with a simple query on a totally different database platform. I can't see why this is so difficult. It's just supposed to pass text to the database engine...

Somebody must have an answer for this?
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Is an or-operator an option for you?

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Hi.... I am struggling with a simpler version of the same problem in Dreamweaver MX.

I have a database application dealing with event types that I'm trying to write a front end for. I have created a SQL query that selects events based on start date AND multiple event types ( ie MTG for "Meeting", CON for "Conference" etc. )

I am trying to query a SQL Anwywhere database and I have built a query in Infomaker ( Client Server report writer ) that accepts a date and a string array as arguments.

For example:

SELECT ev200_event_master.ev200_evt_id,
ev200_event_master.ev200_evt_desc,
ev200_event_master.ev200_mi_date,
ev200_event_master.ev200_mo_date,
ev200_event_master.ev200_evt_type
FROM ev200_event_master
WHERE ( ev200_event_master.ev200_evt_type in ( :Event_type ) ) AND
( ev200_event_master.ev200_mi_date &gt;= :Start_date )

ORDER BY ev200_event_master.ev200_mi_date ASC

Pretty simple ? You don't have to know much SQL to see how easy this is. It works fine. The problem is that in Dreamweaver, no matter how I construct the query - I can only retrieve ONE event type with the following syntax ( essentially the same )

SELECT EV200_EVT_ID, EV200_EVT_DESC, EV200_MI_DATE, EV200_MO_DATE, EV200_EVT_TYPE
FROM gpcb.EV200_EVENT_MASTER
WHERE EV200_MI_DATE &gt;= ? AND EV200_EVT_TYPE IN (?)
ORDER BY EV200_MI_DATE ASC

I am testing the query directly with the advanced query builder in Dreamweaver MX and it works for ONE type ( ie 'MTG) but if I try to pass the string array THE WAY I KNOW THE DATABASE WANTS IT - it doesn't work.

I admire your efforts to construct the string using code but I'm punching it in directly to the query builder and it just won't work. Why?

I have the same problem with a simple query on a totally different database platform. I can't see why this is so difficult. It's just supposed to pass text to the database engine...

Somebody must have an answer for this?
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>