Hi,

I'm setting up an ASP page where a column (randomKey) in a recordset (rsKeywords) drawn from a SQL table needs to be filtered by a value (KeywordString) which is generated at the top of the page.

Specifically: if the value of the colum randomKey is contained anywhere in KeywordString, then that record needs to be included in the recordset. eg. if the value of KeywordString is "12345, abcde, 54321", a record where the value in the column randomKey is 'abcde' would be included in the recordset - one where the value is 'zyxwv' wouldn't! I hope that makes sense.

I've been able to filter a repeat region AFTER the recordset has loaded on to the page by including...

IF InStr(KeywordString,rsKeyWords.fields.item("randomKey") THEN

...which works OK but apart from being a slower process, I'd prefer to do the filtering as the recordset loads, not in the middle of the page.

If anyone out there can shed any light on this, I'll be unbelievably grateful as I just feel like I've been going round in circles with the various (unsuccessful) solutions I've been trying!

Thanks in advance.

Martin.

Unless you can include your instr command inside the sql command you will have to do the sorting after you have obtained all the records, search for an sql command that will allow you to do this. I am sure there is one, cant think of the top of my head... I will have a look for you too.

Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

Thanks for replying Vince. Apparently CHARINDEX and SUBSTRING are meant to provide comparable functionality in SQL, but I haven't been able to work out how! I've pasted the code below (down as far as where I create rsKeywords) in case it sheds any light on how I might be able to solve this.

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

<%
' the first recordset, rsLibraryItems is filtered here based on a search entered on an earlier page...
Dim tfm_andor,tfm_exact
tfm_andor = "AND"
tfm_exact = "false"
If Cstr(request("filters")<> "" Then
Dim tfm_SQLstr,tfm_searchField,tfm_databaseFields,bellChar
tfm_SQLstr = " WHERE (("
tfm_searchField = lcase(request("filters")
tfm_databaseFields = Split("docTitle,ownerName,docKeywords,docKeysections,docKeyCountries,docKeyCommType,docDescription",","
bellChar = chr(7)
If InStr(tfm_searchField,chr(34)) Or tfm_exact = "true" Then
tfm_searchField = Replace(tfm_searchField,chr(34),""
tfm_andor = "OR"
ElseIf InStr(lcase(tfm_searchField)," or " Then
tfm_searchField = Replace(tfm_searchField," or ",bellChar)
tfm_andor = "OR"
ElseIf InStr(tfm_searchField,"," Or InStr(tfm_searchField," " Or InStr(lcase(tfm_searchField)," and " Then
tfm_searchField = Replace(tfm_searchField," and ",bellChar)
tfm_searchField = Replace(tfm_searchField,",",bellChar)
tfm_searchField = Replace(tfm_searchField," ",bellChar)
End If
splitField = Split(tfm_searchField,bellChar)
For i = 0 to ubound(splitField)
For j = 0 to ubound(tfm_databaseFields)
tfm_SQLstr = tfm_SQLstr & "(" & tfm_databaseFields(j) & " LIKE '%" & Replace(splitField(i),"'","''" & "%')"
If j < ubound(tfm_databaseFields) Then tfm_SQLstr = tfm_SQLstr & " OR "
Next
If i < ubound(splitField) Then tfm_SQLstr = tfm_SQLstr & " " & tfm_andor & " ("
Next
tfm_SQLstr = tfm_SQLstr & ")"
Else
tfm_SQLstr = ""
End If

Dim rsLibraryItems
Dim rsLibraryItems_numRows

Set rsLibraryItems = Server.CreateObject("ADODB.Recordset"
rsLibraryItems.ActiveConnection = MM_ENVISAGE_STRING
rsLibraryItems.Source = "SELECT * FROM dbo._EnvInt_rsLibraryItems "+tfm_SQLstr+" ORDER BY docDate desc"
rsLibraryItems.CursorType = 0
rsLibraryItems.CursorLocation = 2
rsLibraryItems.LockType = 1
rsLibraryItems.Open()

rsLibraryItems_numRows = 0
%>

<%
'generate a keyword string from returned Library items
Dim KeywordString__numRows
Dim KeywordString__index
Dim KeywordString

KeywordString__numRows = -1
KeywordString__index = 0
rsLibraryItems_numRows = rsLibraryItems_numRows + KeywordString__numRows

While ((KeywordString__numRows <> 0) AND (NOT rsLibraryItems.EOF))
KeywordString = KeywordString + " " + (rsLibraryItems.Fields.Item("docKeywords".Value)
KeywordString__index=Repeat1__index+1
KeywordString__numRows=Repeat1__numRows-1
rsLibraryItems.MoveNext()
Wend
%>

<%
' and now I need to create rsKeywords - at present I'm just pulling in all the records, then filtering them against KeywordString during a repeat region later
Dim rskeywords
Dim rskeywords_numRows

Set rskeywords = Server.CreateObject("ADODB.Recordset"
rskeywords.ActiveConnection = MM_ENVISAGE_STRING
rskeywords.Source = "SELECT * FROM tabKeywords"
rskeywords.CursorType = 0
rskeywords.CursorLocation = 2
rskeywords.LockType = 1
rskeywords.Open()

rskeywords_numRows = 0
%>

Any thoughts?!

M.