| Author |
 Topic  |
|
|
Monkeynme
Starting Member
USA
4 Posts |
 Posted - 16 August 2001 : 12:21:00
|
I've created an Access 2000 database of company movies and presentations. UltraDev 4 created an ASP page which uses the database to search through the name, description and keywords of each of the entries. The database has a Query which includes a "SearchField" field that searches all 3 fields for a multiple search in UD4. However, not all fields are being searched for each entry. When I review the results of the Query in Access, the "SearchField" field misses some information in each of the fields. The field may contain anywhere from 0 to 3 of the "name", "description" and "keyword" fields that it should be showing.
The code for the presentations Query is:
SELECT [Demos].[Descrip]+[Demos].[Keywords]+[Demos].[DemoID] AS SearchField, [Demos].[Descrip], [Demos].[Keywords], [Demos].[DemoID], [Demos].[FileSize], [Demos].[Link]
FROM Demos;
I have a screenshot of the Query results, but can't figure out how to attach it here.
Does anyone know what's happening?
Dawn Maestas
OS: Win2000
DB: MS Access 2000
Web Server: IIS |
|
|
Spoon
Average Member
Ireland
507 Posts |
Posted - 16 August 2001 : 17:42:26
|
quote:
I've created an Access 2000 database of company movies and presentations. UltraDev 4 created an ASP page which uses the database to search through the name, description and keywords of each of the entries. The database has a Query which includes a "SearchField" field that searches all 3 fields for a multiple search in UD4. However, not all fields are being searched for each entry. When I review the results of the Query in Access, the "SearchField" field misses some information in each of the fields. The field may contain anywhere from 0 to 3 of the "name", "description" and "keyword" fields that it should be showing.
The code for the presentations Query is:
SELECT [Demos].[Descrip]+[Demos].[Keywords]+[Demos].[DemoID] AS SearchField, [Demos].[Descrip], [Demos].[Keywords], [Demos].[DemoID], [Demos].[FileSize], [Demos].[Link]
FROM Demos;
I have a screenshot of the Query results, but can't figure out how to attach it here.
Does anyone know what's happening?
Dawn Maestas
OS: Win2000
DB: MS Access 2000
Web Server: IIS
CAn you post the whole code of the page you are using plz, then i can help you more
Regards - Spoon
Begineer? Need help installing the forums? - www.aslickpage.com/snitz_help.html
www.ASlickPage.com - Private Messaging |
 |
|
|
Monkeynme
Starting Member
USA
4 Posts |
Posted - 17 August 2001 : 11:46:27
|
quote:
CAn you post the whole code of the page you are using plz, then i can help you more
If you mean the ASP code, it's below. The Search engine works fine. It's the Query (getDemos) built in Access 2000 that it's drawing from that seems to have the problem. That is the code I gave originally.
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="../Connections/TMConn.asp" -->
<%
Dim rsGetDemos__txtSearchField
rsGetDemos__txtSearchField = "xyz"
if (Request("searchfield") <> "") then rsGetDemos__txtSearchField = Request("searchfield")
%>
<%
Dim SQLstr
Dim myField
myField = rsGetDemos__txtSearchField
If instr(myField,chr(34)) Then
myField= Replace(myField,chr(34),"")
SQLstr = " WHERE SearchField LIKE '%"
SQLstr = SQLstr + replace(myField, "'", "''") + "%'"
ElseIf instr(myField,",") Then
Dim splitField
SQLstr = " WHERE"
splitField = split(myField, ",")
for i = 0 to ubound(splitField)
SQLstr = SQLstr & " SearchField LIKE '%"
SQLstr = SQLstr & replace(splitField(i), "'", "''") & "%'"
if i < ubound(splitField) Then SQLstr = SQLstr & " AND "
Next
Else
SQLstr = " WHERE SearchField LIKE '%"
SQLstr = SQLstr & Replace(myField, "'", "''") & "%'"
End If
%>
<%
set rsGetDemos = Server.CreateObject("ADODB.Recordset")
rsGetDemos.ActiveConnection = MM_TMConn_STRING
rsGetDemos.Source = "SELECT DemoID, FileSize, Link, Descrip, SearchField FROM getDemos" + SQLstr
rsGetDemos.CursorType = 0
rsGetDemos.CursorLocation = 2
rsGetDemos.LockType = 3
rsGetDemos.Open()
rsGetDemos_numRows = 0
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rsGetDemos_numRows = rsGetDemos_numRows + Repeat1__numRows
%>
<%
' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters
' create the list of parameters which should not be maintained
MM_removeList = "&index="
If (MM_paramName <> "") Then MM_removeList = MM_removeList & "&" & MM_paramName & "="
MM_keepURL="":MM_keepForm="":MM_keepBoth="":MM_keepNone=""
' add the URL parameters to the MM_keepURL string
For Each Item In Request.QueryString
NextItem = "&" & Item & "="
If (InStr(1,MM_removeList,NextItem,1) = 0) Then
MM_keepURL = MM_keepURL & NextItem & Server.URLencode(Request.QueryString(Item))
End If
Next
' add the Form variables to the MM_keepForm string
For Each Item In Request.Form
NextItem = "&" & Item & "="
If (InStr(1,MM_removeList,NextItem,1) = 0) Then
MM_keepForm = MM_keepForm & NextItem & Server.URLencode(Request.Form(Item))
End If
Next
' create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm
if (MM_keepBoth <> "") Then MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
if (MM_keepURL <> "") Then MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1)
if (MM_keepForm <> "") Then MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)
' a utility function used for adding additional parameters to these strings
Function MM_joinChar(firstItem)
If (firstItem <> "") Then
MM_joinChar = "&"
Else
MM_joinChar = ""
End If
End Function
%>
<html>
<head>
<title>Search Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="../css/techmarketing2.css" type="text/css">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<h2><img src="../images/title_searchres_demos.gif" width="400" height="30"></h2>
<table width="300" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="left" valign="top">
<% If Not rsGetDemos.EOF Or Not rsGetDemos.BOF Then %>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsGetDemos.EOF))
%>
<table width="295" border="0" cellspacing="2" cellpadding="0">
<tr>
<td><A HREF="<%=(rsGetDemos.Fields.Item("Link").Value)%>?<%= MM_keepNone & MM_joinChar(MM_keepNone) & "DemoID=" & rsGetDemos.Fields.Item("DemoID").Value %>"><%=(rsGetDemos.Fields.Item("DemoID").Value)%></A> (<%=(rsGetDemos.Fields.Item("FileSize").Value)%>)</td>
</tr>
</table>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsGetDemos.MoveNext()
Wend
%>
<% End If ' end Not rsGetDemos.EOF Or NOT rsGetDemos.BOF %>
<p><font color="#FF0000"><i><font size="3">
<% If rsGetDemos.EOF And rsGetDemos.BOF Then %>
<b>Sorry, no matches found</b>
<% End If ' end rsGetDemos.EOF And rsGetDemos.BOF %>
</font></i></font></p>
</td>
</tr>
</table>
<h2> </h2>
</body>
</html>
<%
rsGetDemos.Close()
%>
Dawn Maestas
OS: Win2000
DB: MS Access 2000
Web Server: IIS |
|
|
|
Monkeynme
Starting Member
USA
4 Posts |
Posted - 17 August 2001 : 14:05:26
|
Actually, now that I've looked at the Query results again...the problem is that if ANY of the 3 fields (name, description, keywords) is blank (NULL), then no results are generated in the SearchField field at all. So somehow, I need to make the Query ignore the NULL fields and create the SearchField results anyway. Is that possible?
Dawn Maestas
OS: Win2000
DB: MS Access 2000
Web Server: IIS |
|
|
|
Monkeynme
Starting Member
USA
4 Posts |
Posted - 20 August 2001 : 12:41:00
|
I received an answer to this in case anyone is interested. It worked perfectly.
_______________
For one thing, you should use the ampersand character (&) to concatenate the
field contents, not the plus sign (+).
Another cause of this symptom could be the presence of Null data in the
fields. You can use the Nz function to overcome this problem:
SELECT Nz([Demos].[Descrip],"") & Nz([Demos].[Keywords],"") ...
HTH,
Bob Barrows
Dawn Maestas
OS: Win2000
DB: MS Access 2000
Web Server: IIS |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 20 August 2001 : 14:06:00
|
nz is an Access function, it's not built in to VBScript so it won't work in your asp pages.
======
Doug G
====== |
|
|
| |
Topic |
|
|
|
Topic Locked
