Non-Exact Search 1

[DonP]

I just created a simple search page used for finding records so they can be edited or deleted. It can search by record ID, or one of three other text fields but it is only the ID that is needed for submission to the next form were the editing takes place. Problem is, it seems to require an exact text match and when it doesn't get one, it gives an ugly ODBC error. This is the part of the code that handles the text search, which was generated by DreamWeaver UltraDev:

[tt] ' find the record with the unique column value equal to the parameter value
MM_offset = 0
Do While (Not MM_rs.EOF)
If (Cstr(MM_rs.Fields.Item(MM_uniqueCol).Value) = param) Then
Exit Do
End If
MM_offset = MM_offset + 1
MM_rs.MoveNext
Loop[/tt]

Without changing the syntax much, does anyone know how this can be made to search on partial names and to give a "nicer" error if it doesn't find a match?

Also, if there are multiple matches, I am not sure how to deal with it because the search page, which is very simple, is for only one at a time.

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[DougP]

You should use SQL to do that and it will be much faster than looping. Then put the results in a table so it can grow according to all the matches it finds.

What type of database is it????

Here is one for SQL server 7 as an example:
---------------------------------------------
<%pnum=request.querystring(&quot;PartNumber&quot%>
<%desc=request.querystring(&quot;Description&quot%>
<%
Set Conn = server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open &quot;driver=SQL Server;server=yoursite.com;uid=user;pwd=pass;database=Databasename;&quot;
Set RS1 = Conn.Execute(&quot;SELECT [STOCK_CODE], [Description], [SELLING_PRICE] FROM [Copy-PartMaster Lite] Where [STOCK_CODE] Like N'%&quot; & pnum & &quot;%' And [Description] Like N'%&quot; & desc & &quot;%'&quot


<table border=&quot;1&quot; width=&quot;90%&quot; bordercolor=&quot;#0066FF&quot;>
<tr>
<td width=&quot;18%&quot; align=&quot;left&quot; bordercolor=&quot;#0066FF&quot; bgcolor=&quot;#000000&quot;>
<p align=&quot;left&quot;><font color=&quot;#FFFFFF&quot;>Part Number</font></td>
<td width=&quot;52%&quot; align=&quot;left&quot; bordercolor=&quot;#000000&quot; bgcolor=&quot;#000000&quot;>
<p align=&quot;left&quot;><font color=&quot;#FFFFFF&quot;>Description</font></td>
<td width=&quot;12%&quot; align=&quot;right&quot; bordercolor=&quot;#000000&quot; bgcolor=&quot;#000000&quot;>
<p align=&quot;left&quot;><font color=&quot;#FFFFFF&quot;>Price</font></td>
</tr>
<tr>
<td width=&quot;18%&quot; align=&quot;left&quot;>&nbsp;</td>
<td width=&quot;52%&quot; align=&quot;left&quot;>&nbsp;</td>
<td width=&quot;12%&quot; align=&quot;right&quot;>&nbsp;</td>
</tr>
<%
If Not RS1.EOF Then
<tr>
Do%>
<td width=&quot;18%&quot; align=&quot;left&quot;>
<p align=&quot;center&quot;><%Response.Write RS1(&quot;STOCK_CODE&quot%></td>
<td width=&quot;52%&quot; align=&quot;left&quot;>
<p align=&quot;center&quot;><%Response.Write RS1(&quot;Description&quot %></td>
<td width=&quot;12%&quot; align=&quot;right&quot;>
<p align=&quot;center&quot;><%Response.Write Price1%></td>
</tr>
<% RS1.Movenext
Loop Until RS1.EOF
End If
%>
</table>
--------------------
this gets passed a part number and or desciption and or part of each. so it's very flexible.
Then it passes that information to the database and returns matches into recordset RS1

Then it loops through all of the matches and creates a table. So its only looping through a few records not the entire table.


DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[DonP]

Hi Doug,

Thanks for the reply! Yes, our main search engine is already that way and works fine. It is only on this special administration area that is accessable by a few people that I am having the problem because it was designed for only one entry at a time and assumes one knows specifics to be able to find it. If I search on record ID, I have no problem but with anything else, it needs to be complete and exact. But for now, assuming there are not any duplicate records, just being able to make a partial keyword search would help. Because the whole thing was developed by UltraDev, I need to use their values so the rest of it won't crash. Any ideas? (This is an Access database by the way, and is not running SQL Server.)

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[swilliams]

This is a guess, but try replacing the line

If (Cstr(MM_rs.Fields.Item(MM_uniqueCol).Value) = param) Then

with

If (InStr(MM_rs.Fields.Item(MM_uniqueCol).Value), param) Then

Simon

 

[DonP]

Hi Simon!

Thanks for the idea! I'm home now so will have to try on Monday. I don't have a copy here.

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[swilliams]

I just realised it won't work - there is a syntax error (incorrect brackets)!!!!

Try this line instead:

If InStr((MM_rs.Fields.Item(MM_uniqueCol).Value), param) Then

Simon

 

[DonP]

Hi Simon,

Good timing! I was in the middle of another reply when your correction came. I remembered that I had an online copy that I could try but it has &quot;issues&quot; with the comma. After changing it to an &quot;=&quot; and then to your recent suggestion, I get the same error I had before, though now it does it whether or not the match is exact. This UltraDev code is a bit odd in that it seems to create other values for the record set fields and I don't fully understand how it works. I may have to go through it in more detail on Monday to see if there is more than one place where this needs to be changed. Here is the exact error, though I'm not sure which line it is on. My text editor here does not give line numbers but I am sure it is the same error as before and the line is within the little snippit that was posted. I can still search by ID without the error:

[tt]ADODB.Fields error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.[/tt]


Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[swilliams]

I can guess (again) that this code is working like this:

somewhere in the app you are using to create the page, it is creating a variable MM_uniqueCol. This is either a number (integer or long) or a string.
The app is also creating a recordset called MM_rs.
Then, in the line If InStr((MM_rs.Fields.Item(MM_uniqueCol).Value), param) Then it is trying to access a field, by either the ordinal number or the name of the field, but the field does not exist in the recordset???????????

Very puzzling.

Simon

 

[DonP]

Simon (and Doug),

It IS very puzzling, especially since I am not a programmer. I've worked with VBScript for a little over a year but this one just seems odd. It is posting to itself and to other scripts also created by UltraDev so I thought it best to leave it alone. But now I think I may rewrite it with my own code that I know will work, and just give it new form variables that the other scripts need. I will just simplify a copy of our main search script, which should do the job nicely. Thanks to both of you for your help and ideas!

Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[DonP]

Simon,

I decided to give it one more try and guess what? It works! I found a typo in that I had added an if/else statement elsewhere in the script when I first created the page a few weeks ago but hadn't remmed out the old one. The only problem, which is one I can live with, is that it is case sensitive. Only a few data providers are going to be using it so they can be trained with that it mind. It still gives only one record, which is the first that it finds, but that will probably be OK because from there, the &quot;Previous Record&quot; and &quot;Next Record&quot; buttons can be used.

Thanks a bundle!


Don
don@ctagroup.org

http://www.helpscc.org/

Experienced in HTML, Perl, VBScript, PWS, IIS and Apache. Run OS/2 Warp 4, BeOS v5 and Windows NT (only when I have to!)

 

[palreddy]

Hi,

I just created a simple search page used for finding records.It can search by Last Nmae,First Name, Year of graduation or one of three other text fields.This is the part of the code that handles the text search, which was generated by DreamWeaver UltraDev:

<%@LANGUAGE=&quot;VBSCRIPT&quot;%> <%

Dim RsPublicResult__FirstName
RsPublicResult__FirstName = &quot;%&quot;
if(Request.Form(&quot;FirstName&quot <> &quot;&quot then RsPublicResult__FirstName = Request.Form(&quot;FirstName&quot
Dim RsPublicResult__LastName
RsPublicResult__LastName = &quot;%&quot;
if(Request.Form(&quot;LastName&quot <> &quot;&quot then RsPublicResult__LastName = Request.Form(&quot;LastName&quot

%> <%
set RsPublicResult = Server.CreateObject(&quot;ADODB.Recordset&quot
RsPublicResult.ActiveConnection = &quot;dsn=alumni;&quot;
RsPublicResult.Source = &quot;SELECT * FROM alumni WHERE Fname like '&quot; + Replace(RsPublicResult__FirstName, &quot;'&quot;, &quot;''&quot + &quot;%' AND Lname like '&quot; + Replace(RsPublicResult__LastName, &quot;'&quot;, &quot;''&quot + &quot;%'&quot;
if RsPublicResult.Source= &quot;&quot; then Response.Write &quot;No Records Found&quot;
RsPublicResult.CursorType = 0
RsPublicResult.CursorLocation = 2
RsPublicResult.LockType = 3
RsPublicResult.Open
RsPublicResult_numRows = 0
%><%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
RsPublicResult_numRows = RsPublicResult_numRows + Repeat1__numRows

%>


While ((Repeat1__numRows <> 0) AND (NOT RsPublicResult.EOF))
%>
<tr>
<td>
<div align=&quot;center&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(RsPublicResult.Fields.Item(&quot;Fname&quot.Value)%></font></div>
</td>
<td>
<div align=&quot;center&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(RsPublicResult.Fields.Item(&quot;Lname&quot.Value)%></font></div>
</td>
<td>
<div align=&quot;center&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(RsPublicResult.Fields.Item(&quot;GradYear&quot.Value)%></font></div>
</td>
<td>
<div align=&quot;center&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot;><%=(RsPublicResult.Fields.Item(&quot;Email&quot.Value)%></font></div>
</td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
RsPublicResult.MoveNext()
Wend
%>
Without changing the syntax much, does anyone know how this can be made to give a user friendly message..whenever an invalid name is queried i should be able to give my user ..user friendly message like &quot;No records are found &quot;..instead of a blank screen......

looking forward for your cooperation.

sincerely,

palreddy

 

[mrichards]

put in move first then and if stament an if statement

if eof then

<% sorry no records found %>

else

While ((Repeat1__numRows <> 0) AND (NOT RsPublicResult.EOF))
%>
etc

 

[palreddy]

hi Richards,
Thanks for the idea! I'm home right now so will have to try on Monday. Have a nice weekend.

Thanx again.

warm regards,
Palreddy