VBScript "Type Mismatch" error
Hamsterlord, Ruler of Rodents
I'm new at VBScript, so I'll understand if this is a particularly stupid
mistake.
I'm doing an ASP that hooks into an Access database and then performs (in
theory) a search on a 'keywords' field in the database (it's basically a
small VBS search page for a single website, with the search data in the MDB
file).
I can access the data, I can print the data out; all those tests ran fine.
However, when I write a routine to take the value of the
dataSearch("Keywords") field from the database and slap it into a string
variable strKeywords, I get a Type Mismatch error. I checked all through the
MSDN CDs, and they seem kind of useless; they _define_ Type Mismatch, but
the only VBScript application of the info is for using SQL Server (which is
a bit high-tech for this purpose).
I've tried figuring out how to specifically declare strKeywords as either a
String or a Variant variable (the "Keywords" field in the database is a Memo
field, because it was too big for a Text field, even at the 255 character
limit); nothing doing; I get all kinds of syntax errors from IE.
I've attached the code (it's short because I am still in the testing each
part phase); ignoring the commented-out sections (where I'm either
commenting the code or have put in dummy events that can be fired while I'm
testing), can anyone tell me why I can't take the string data from a memo
field and put it into a string variable so that I can do an INSTR search
through it?
Thanks in advance
Dj
-----------------
<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<%
'Early attempts at declaring variables to fix the Type Mismatch problem
'dim strKeywords ' as string
'dim checker
dim strKeys, strKeywords, checker
dim dataSearch
set dataSearch=Server.CreateObject("ADODB.Recordset")
dataSearch.Open "SearchData", "DSN=Patrol"
dataSearch.MoveFirst
'Build the beginning of the Results Reporting Page
Response.Write "<center><h1>Search Results</h1></center></p>"
Response.Write "<table width=600 border=2>"
set strKeys = dataSearch("Keywords")
Do while not dataSearch.EOF
'code to get the search terms
'HERE'S WHERE THE PROBLEM SEEMS TO START
set strKeywords = strKeys
'AND HERE'S WHERE THE TYPE MISMATCH ALWAYS COMES UP
checker = instr(strKeywords,"Renegade",1)
'set checker = instr(dataSearch("Keywords"),"Renegade",1)--Test Data while I
test the part of the code
if checker = 0 then 'If the search term isn't found in the keyword string
then
'if instr(,strKeywords,"Renegade",1) = 0 then
dataSearch.MoveNext
else
Response.Write ("<tr><td>") & dataSearch.Index & ("</td><td>") &
dataSearch.URL & ("</a></td></tr>")
Response.Write ("<tr><td colspan=2>") & dataSearch.PageDescription &
("</td></tr>")
' Response.Write ("<tr><td>") & strKeywords & ("</td></tr>") HERE FOR
TESTING PURPOSES ONLY
dataSearch.MoveNext
end if
loop
'Close the HTML
Response.Write ("</table>")
%>
</body>
</html>
B-Mann
news:nWXQ6.36383$qc.51...@news1.rdc1.va.home.com...
I did not look through the entire script, but this stands out:
>
> set strKeys = dataSearch("Keywords")
>
> Do while not dataSearch.EOF
>
Try this instead:
Do While Not dataSearch.EOF
set strKeys = dataSearch.Fields("Keywords")
dataSearch is your recordset, to reference a field in the recordset use the
Fields method passing it the name of the field as a string.
HTH
B-Mann
Hamsterlord, Ruler of Rodents
> I did not look through the entire script, but this stands out:
> >
> > set strKeys = dataSearch("Keywords")
> >
> > Do while not dataSearch.EOF
> >
>
> Try this instead:
>
> Do While Not dataSearch.EOF
> set strKeys = dataSearch.Fields("Keywords")
>
>
> dataSearch is your recordset, to reference a field in the recordset use
the
> Fields method passing it the name of the field as a string.
I'll try it and let you know; thank you for the suggestion.
Davey
Jeremy
this line is wrong, you wanted
strKeywords = strKeys
I think. and you don't have to movefirst, you're already there. ;)
But, you're going about it all wrong. Let adodb do the searching for you, it
will be _much_ faster. Here's conceptually how I would do it: (dims and
stuff left out, and this code may have bugs... )
er, I can't help but mention that index server is what you're really after,
especially if it's a web site. I have to plug it because we have a very
large index and like it a lot. (almost 20 million files in the index now,
and it does searches in abuot 4 seconds on a dual 1ghz server) You'll get
boolean support with index server, which this script does not support... :)
databasename="database_name_goes_here.mdb"
set db = server.createobject("ADODB.Connection")
' put open command here
db.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & _
server.mappath(DatabaseName)
'get the keywords from the users post form (or get form)
search = request("fromform_fieldname_goes_here")
'make a sql statement to get a recordset- yours will be different
sql = "select productid,[name] as ProductName,description from tblproduct
order by name"
'execute the sql
set rs = db.execute(sql)
'set a filter
rs.Filter = "ProductName like '%" & search & "%' or Description like '%" &
search & "%'"
'then just go through the results
do while not rs.eof
response.write rs("fieldname")
'want to bold the hit? use replace function: (sorry if this wraps)
response.write replace(rs("fieldname"),searchingfor,"<B>" & searchingfor
& "</B>")
'move to the next hit
rs.movenext
loop
'ADO = always destroy objects :)
set rs = nothing
db.close
set db = nothing
let me know if this doesn't make sense...
Jeremy
jerm...@intersite.com.remove.this.T0.reply
"Hamsterlord, Ruler of Rodents" <hamsterlord!@home.com> wrote in message
news:nWXQ6.36383$qc.51...@news1.rdc1.va.home.com...
Hamsterlord, Ruler of Rodents
> > set strKeywords = strKeys
>
> this line is wrong, you wanted
> strKeywords = strKeys
>
> I think. and you don't have to movefirst, you're already there. ;)
I know; I was trying everything in the MSDN online help, and commenting out
the original code (which was sometimes duplicated, although never at the
same time active) as I moved things around. *sigh* I'm new at this... :)
> er, I can't help but mention that index server is what you're really
after,
> especially if it's a web site. I have to plug it because we have a very
> large index and like it a lot. (almost 20 million files in the index now,
> and it does searches in abuot 4 seconds on a dual 1ghz server) You'll get
> boolean support with index server, which this script does not support...
:)
Oh, I know; I've used it myself. But this is more along the lines of using a
port (I've written a client-side javascript search routine; that's the
eventual goal of this vbscript. But the project they just put me on deals
with accessing Access and SQL databases--which is easy--and I thought I'd
try a quick port for the practice. Turns out I've got a lot to learn about
VBScript, I guess :) for (eventually) a site that wouldn't require Index
Server to work.
> But, you're going about it all wrong. Let adodb do the searching for you,
it
> will be _much_ faster. Here's conceptually how I would do it: (dims and
> stuff left out, and this code may have bugs... )
<snippage of good-looking code suggestions>
> 'make a sql statement to get a recordset- yours will be different
> sql = "select productid,[name] as ProductName,description from tblproduct
> order by name"
And that's where it runs into trouble. It's not running on an SQL server (or
at least not with an SQL database); it's an Access database (unless I'm
allowed to do an SQL server on my database regardless of the source
format--is that the 'magic' of the ADODB?). Hmmm...
> 'ADO = always destroy objects :)
> set rs = nothing
> db.close
> set db = nothing
Thank you; that much I hadn't known about (us beginners; can't do a thing
with us).
> let me know if this doesn't make sense...
Makes sense; let me adaptate it and see if it solves the problem. (If it
does and I eventually get it onto a website somewhere I'll point you to it
so you can exult in your name in print yet again. :) Thanks!
Davey
Jeremy
as in Structured Query Language, which is what you want to be using to talk
to your db (whatever it is) via ado. (yes, that's the magic of ADO, you
won't have to change much when you upgrade to msde or mssql 7 or mssql
8(2000).
The code example was from a site I built on an access database, in fact. I
had to build it on access because it was being hosted on an ISP-owned
machine. Later I colocated a server there for the client and switched them
to sql 2000. Very slight changes, ADO is pretty good at not having to do
much when changing your "Provider" (the provider being access, sql,
whatever...)
Let me know how it goes...
Jeremy
"Hamsterlord, Ruler of Rodents" <hamsterlord!@home.com> wrote in message
news:RohR6.39450$qc.55...@news1.rdc1.va.home.com...