Hello,
Questions:
What is the question mark in the SQL 'IN' condition?
How can you help me make the list menu work (as did in DW8)
where the user can select multiple items, submit and then display
multiple results?
Why did CS3 change code that actually worked in DW8?
Issue Described
Below:----------------------------------------------
I recently completed developing a web application for my
organization and the working prototype is being tested in our
development server. I developed the web application in DW8. The web
application uses a form request to search for data in sql server
(or previous in ACCESS database). The code in question developed in
DW8 actually works. This specific code allows the user to select
several items from a list menu and then submit and then the
multiple results are displayed:
In advance recordset this is what I coded in DW8:
SELECT *
FROM tblCustomer
WHERE ProductID IN (MMColParam)
ORDER BY ProductID, Model, Month, Year ASC
In the actual asp page Macromedia DW8 displays:
<%
Dim RsCustomer__MMColParam
RsCustomer__MMColParam = "0"
If (Request.Form("sltForm") <> "") Then
RsCustomer__MMColParam = Request.Form("sltForm")
End If
%>
<%
Dim RsCustomer
Dim RsCustomer_numRows
Set RsCustomer = Server.CreateObject("ADODB.Recordset")
RsCustomer.ActiveConnection = MM_Customer_STRING
RsCustomer.Source = "SELECT * FROM tblCustomer WHERE
ProductID IN (" + Replace( RsCustomer__MMColParam, "'", "''") + ")
ORDER BY ProductID, Model, Month, Year ASC"
RsCustomer.CursorType = 0
RsCustomer.CursorLocation = 2
RsCustomer.LockType = 1
RsCustomer.Open()
RsCustomer_numRows = 0
%>
The above code works when I run the application from my local
using DW8 and now the working prototype is being tested without any
issues in development.
However, we just recently upgraded to CS3. I migrated the
application to CS3 from DW8. When I launch the search page from CS3
, I receive the following error message (note: I am using an ACCESS
database):
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch
in criteria expression.
/customer/asp/DisplaySelect2.asp, line 20
I went into CS3's advance recordset under Parameter section
and changed the data type to 'Text'and the following code on the
actual asp page CHANGED to:
<%
Dim RsCustomer
Dim RsCustomer_cmd
Dim RsCustomer_numRows
Set RsCustomer = Server.CreateObject("ADODB.Recordset")
RsCustomer.ActiveConnection = MM_Customer_STRING
RsCustomer.Source = "SELECT * FROM tblCustomer WHERE
ProductID IN (?) ORDER BY ProductID, Model, Month, Year ASC"
RsCustomer_cmd.Prepared = true
RsCustomer_cmd.Parameters.Append
RsCustomer_cmd.CreateParameter("param1", 200, 1, 255,
RsCustomer__MMColParam) ' adVarChar
Set RsCustomer = RsCustomer_cmd.Execute
RsCustomer_numRows = 0
%>
Now, When I run the application, I can not display my
multiple selections from the list menu. I can only select one item
and display.
I appreciate feedback on this. I can't move forward on this
application if I am having issues with this particular tailored
search function, especially if I have to make further adjustments
on this page in CS3. So your help is greatly needed Thank
you
1
Reply
AdChoices