query problem... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

query problem...

Author

Topic

clearauthor
Starting Member

3 Posts

Posted - 2004-04-08 : 04:22:55

Hi,

I'm setting up a 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.

clearauthor
Starting Member

3 Posts

Posted - 2004-04-08 : 04:31:50

Forgot to mention - I'm loading the recordset into an ASP page!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-08 : 05:30:35

Martin, what database are you using? You could do this on the server while it is returning the recordset you, that way you will only receive records that match the criteria, rather than receive all records and then filter them on the client. A database engine is likely to be faster at this sort of thing than a scripting language. Post the bit of code where you open the recordset including the SQL statement, and we might be able to help.

OS

clearauthor
Starting Member

3 Posts

Posted - 2004-04-08 : 10:56:06

Hi - thanks for replying. I'm using MSSQL 2000, however I haven't attempted to use the database engine for the query since the string "randomKey" is generated from another recordset, which in turn is filtered from a keyword search on another page. But here's the script from the top anyway...

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>


<%
' the first recordset, rsLibraryItems is searched here...
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(session("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 suggestions will be most welcome!

Martin.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources