해결됨
ASP Search results page fails after upgrading DW to 8.02
This is DW 8.02, SQL server, ASP (not dot.net).
I have a search function that selects one of 5 column names and queries with user entered text. The old results page works fine until I replace the recordset code with the new parameter based recordsets in 8.02.
My search form uses GET and calls the results page with selColumn and txSearch variables.
Both the old and new results pages have this code to dimension the form variables:
<%
Dim rslocate__reqColumn
rslocate__reqColumn = "COMPANY"
If (Request.QueryString("selColumn") <> "") Then
rslocate__reqColumn = Request.QueryString("selColumn")
End If
%>
<%
Dim rslocate__reqSearch
rslocate__reqSearch = "test"
If (Request.QueryString("txSearch") <> "") Then
rslocate__reqSearch = Request.QueryString("txSearch")
End If
%>
I have also tried Request.Form with no success. The data type in SQL server is nvarchar for all data.
On the results.asp page the old recordset that still works is:
<%
Dim rslocate
Dim rslocate_numRows
Set rslocate = Server.CreateObject("ADODB.Recordset")
rslocate.ActiveConnection = MM_members_STRING
rslocate.Source = "SELECT * FROM ncmortgage.members WHERE " + Replace(rslocate__reqColumn, "'", "''") + " LIKE '%" + Replace(rslocate__reqSearch, "'", "''") + "%' ORDER BY UserID ASC"
rslocate.CursorType = 0
rslocate.CursorLocation = 2
rslocate.LockType = 1
rslocate.Open()
rslocate_numRows = 0
%>
On the new 8.02 results.asp page that shows no errors but does not return any data the code is:
<%
Dim rslocate
Dim rslocate_cmd
Dim rslocate_numRows
Set rslocate_cmd = Server.CreateObject ("ADODB.Command")
rslocate_cmd.ActiveConnection = MM_members_STRING
rslocate_cmd.CommandText = "SELECT * FROM ncmortgage.members WHERE ? LIKE ? ORDER BY UserID ASC"
rslocate_cmd.Prepared = true
rslocate_cmd.Parameters.Append rslocate_cmd.CreateParameter("param1", 200, 1, 10, rslocate__reqColumn) ' adVarChar
rslocate_cmd.Parameters.Append rslocate_cmd.CreateParameter("param2", 200, 1, 35, "%" + rslocate__reqSearch + "%") ' adVarChar
Set rslocate = rslocate_cmd.Execute
rslocate_numRows = 0
%>
I have tried everything over the last two days to get this to work. All of the other functions that I have converted are working fine in 8.02 but this one eludes me. Any help is sincerely appreciated.
tks,
Les H.
I have a search function that selects one of 5 column names and queries with user entered text. The old results page works fine until I replace the recordset code with the new parameter based recordsets in 8.02.
My search form uses GET and calls the results page with selColumn and txSearch variables.
Both the old and new results pages have this code to dimension the form variables:
<%
Dim rslocate__reqColumn
rslocate__reqColumn = "COMPANY"
If (Request.QueryString("selColumn") <> "") Then
rslocate__reqColumn = Request.QueryString("selColumn")
End If
%>
<%
Dim rslocate__reqSearch
rslocate__reqSearch = "test"
If (Request.QueryString("txSearch") <> "") Then
rslocate__reqSearch = Request.QueryString("txSearch")
End If
%>
I have also tried Request.Form with no success. The data type in SQL server is nvarchar for all data.
On the results.asp page the old recordset that still works is:
<%
Dim rslocate
Dim rslocate_numRows
Set rslocate = Server.CreateObject("ADODB.Recordset")
rslocate.ActiveConnection = MM_members_STRING
rslocate.Source = "SELECT * FROM ncmortgage.members WHERE " + Replace(rslocate__reqColumn, "'", "''") + " LIKE '%" + Replace(rslocate__reqSearch, "'", "''") + "%' ORDER BY UserID ASC"
rslocate.CursorType = 0
rslocate.CursorLocation = 2
rslocate.LockType = 1
rslocate.Open()
rslocate_numRows = 0
%>
On the new 8.02 results.asp page that shows no errors but does not return any data the code is:
<%
Dim rslocate
Dim rslocate_cmd
Dim rslocate_numRows
Set rslocate_cmd = Server.CreateObject ("ADODB.Command")
rslocate_cmd.ActiveConnection = MM_members_STRING
rslocate_cmd.CommandText = "SELECT * FROM ncmortgage.members WHERE ? LIKE ? ORDER BY UserID ASC"
rslocate_cmd.Prepared = true
rslocate_cmd.Parameters.Append rslocate_cmd.CreateParameter("param1", 200, 1, 10, rslocate__reqColumn) ' adVarChar
rslocate_cmd.Parameters.Append rslocate_cmd.CreateParameter("param2", 200, 1, 35, "%" + rslocate__reqSearch + "%") ' adVarChar
Set rslocate = rslocate_cmd.Execute
rslocate_numRows = 0
%>
I have tried everything over the last two days to get this to work. All of the other functions that I have converted are working fine in 8.02 but this one eludes me. Any help is sincerely appreciated.
tks,
Les H.
