• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

Microsoft VBScript runtime error on results page

I want to display the results of searching a field (proc_info) in a MS Access database using various keywords a user may type in the search page.  My database code is below but I am getting the following error after attempting a search:

Microsoft VBScript runtime  error '800a0009'

Subscript out of range

Line 9
 

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\webserver\database\proc.mdb"

Dim kw()
kw()=Split(Request.Form("keywords"), " ")

Dim sep
Dim sWhere

sep = "("
Dim i
For i = LBound(kw) to UBound(kw)
    sWhere = sWhere & sep & "proc_info LIKE '%" & kw(i)  & "%'" 
    sep = " OR "

Next 
sWhere = sWhere & ")"

Dim strSQL
strSQL = "SELECT * FROM proc Where " & sWhere 

Recordset1.Source= strSQL
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()

Recordset1_numRows = 0
%>

Open in new window


Any help or assistance is greatly appreciated. I want a user to have the ability to enter more than one word to search the (proc_info) field. The results should be driven by the words entered.

Thank you.
0
arendt73
Asked:
arendt73
  • 10
  • 7
  • 7
  • +2
4 Solutions
 
Dale FyeCommented:
you might want to take a look at my article on complex text filters
0
 
Rey Obrero (Capricorn1)Commented:
try changing

kw()=Split(Request.Form("keywords"), " ")

with

kw=Split(Request.Form("keywords"), " ")
0
 
arendt73Author Commented:
capricorn1, after appling your suggestion, I now get the following error:

Microsoft VBScript runtime  error '800a000d'

Type mismatch

Line 9
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mbizupCommented:
Try this variation on your loop:

dim x
For each x in kw
    sWhere = sWhere & sep & "proc_info LIKE '%" & x  & "%'" 
    sep = " OR "

Next 

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
Dim kw() as string
kw=Split(Request.Form("keywords"), " ")


do you have string values on ("keywords") ?
0
 
arendt73Author Commented:
mbizup, after applying your code, I get the following:

Microsoft VBScript compilation  error '800a041f'

Unexpected 'Next'
Line 29

Next
^
0
 
arendt73Author Commented:
capcricorn1, after applying your new suggestion, I get the following:

Microsoft VBScript compilation  error '800a0401'

Expected end of statement
Line 9

Dim kw() as string
---------^
0
 
mbizupCommented:
Hmm - just double checking that we're on the same page...

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\webserver\database\proc.mdb"

Dim kw()
kw()=Split(Request.Form("keywords"), " ")

Dim sep
Dim sWhere

sep = "("
dim x
For each x in kw
    sWhere = sWhere & sep & "proc_info LIKE '%" & x  & "%'" 
    sep = " OR "

Next 
sWhere = sWhere & ")"

Dim strSQL
strSQL = "SELECT * FROM proc Where " & sWhere 

Recordset1.Source= strSQL
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()

Recordset1_numRows = 0
%>

Open in new window

0
 
arendt73Author Commented:
mbizup, yes we are. Thanks.
0
 
mbizupCommented:
As an aside, a space is the default delimiter for the Split function.

Assuming that your Request.Form("keywords") is indeed returning the correct value, you can use this for your Split statement:

kw()=Split(Request.Form("keywords"))
0
 
mbizupCommented:
Also - please double check that you only have one NEXT in your code, and that it didn't inadvertently get doubled up when you inserted my code.

Fwiw, this is the reference I was using for the Split function in VBS:
http://www.w3schools.com/vbscript/func_split.asp
0
 
arendt73Author Commented:
mbizup, so that I am not confusing code suggestions from other experts, can you add your updated code to my code and display the results? Thank you.
0
 
mbizupCommented:
Hi,

That's what I did in my earlier post... here you go (this is just my addition):

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\webserver\database\proc.mdb"

Dim kw()
kw()=Split(Request.Form("keywords"), " ")

Dim sep
Dim sWhere

sep = "("
dim x
For each x in kw
    sWhere = sWhere & sep & "proc_info LIKE '%" & x  & "%'" 
    sep = " OR "

Next 
sWhere = sWhere & ")"

Dim strSQL
strSQL = "SELECT * FROM proc Where " & sWhere 

Recordset1.Source= strSQL
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()

Recordset1_numRows = 0
%>

Open in new window


If that does not help, try a simple line of code to print Request.Form("keywords") to the screen - to verify that it indeed contains whatever value(s) you are expecting it to.
0
 
mbizupCommented:
And also try this:

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\webserver\database\proc.mdb"

Dim kw()
kw = Split(Request.Form("keywords"), " ")

Dim sep
Dim sWhere

sep = "("
dim x
For each x in kw
    sWhere = sWhere & sep & "proc_info LIKE '%" & x  & "%'" 
    sep = " OR "

Next 
sWhere = sWhere & ")"

Dim strSQL
strSQL = "SELECT * FROM proc Where " & sWhere 

Recordset1.Source= strSQL
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()

Recordset1_numRows = 0
%>

Open in new window

0
 
mbizupCommented:
Btw,

I cant actually test your code, but I think your WHERE clause is going to wind up with an extra "OR" which might present a problem.

If this happens, try replacing this line:

<< sWhere = sWhere & ")" >>

With this:

sWhere = sWhere & " 0)"

Open in new window


You might also want to add a condition to your code to handle cases where no keywords are entered if you are not already handling those cases elsewhere.
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
It looks like you want the user to enter a phrase like, "burgers dogs brats" and end up with sql = (proc_info like %beer%) or (proc_info like %dogs%) or (proc_info like %brats%)

<%
if request.form("keywords")<>"" then
sql=""
kw=split(request.form("keywords")," ")
counter=0
for each c in kw
	counter=counter+1
next
response.write last
for each x in kw
	sql=sql&"(proc_info like %"&x&"%)"
	counter=counter-1
	if counter <>0 then
		sql=sql&" OR "
	end if

next
response.write "<hr>"&sql
end if
%>

Open in new window

0
 
arendt73Author Commented:
padas, the last code you recommended was inserted into my existing code.  It appears that we are almost near a solution. All that needs modifying is the: Recordset1.Source= "SELECT * FROM procedures"

When I entered 12/17/2012 in the search page, the results page displayed:

(proc_info like %12/17/2012%) as well as all of the records in the table.

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\website\database\procedures.mdb"

if request.form("keywords")<>"" then
sql=""
kw=split(request.form("keywords")," ")
counter=0
for each c in kw
	counter=counter+1
next
response.write last
for each x in kw
	sql=sql&"(proc_info like %"&x&"%)"
	counter=counter-1
	if counter <>0 then
		sql=sql&" OR "
	end if

next
response.write "<hr>"&sql
end if

Recordset1.Source= "SELECT * FROM procedures"
Recordset1.CursorType = 0 
Recordset1.CursorLocation = 2 
Recordset1.LockType = 1 
Recordset1.Open()

Recordset1_numRows = 0
%>

Open in new window


Assistance is greatly appreciated with the SELECT statement. Thank you for the help.
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
response.write last on line 15 in your code above is only to show what the sql would be.  Either comment that out or delete it.
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Also, get rid of response.write "<hr>"&sql

And where you have

Recordset1.Source= "SELECT * FROM procedures"

should probably be

Recordset1.Source= "SELECT * FROM procedures WHERE "&sql
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
When you start doing these types of searches with multiple like clauses with wildcards, your database can really start to slow down and you may not get the results you expected.  Depending on what you are doing, it may be worth looking at google's custom search http://www.google.com/cse/.
0
 
arendt73Author Commented:
padas, I again thank you for your help. If possible, I would like to complete the multiple keywords search of  my site.

That being said, after adding the SELECT statement, I now get the following error message after I search with 12/17/2012 as a keyword.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression '(proc_info like %12/17/2012%)'.

Line 31


The error points to: Recordset1.Open()
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Are you searching for dates?  What is the data in the table?    It looks like you at least started to use dreamweaver.  Let's start at the end.  Forget  the dynamic query for now.  Please use dreamweaver's wizard to create your query statically where you are using just one parameter.  As you make your recordset, use the test button and get data.  Once that is done, post your working recordset.  Let us know what data types are in that field.  Show sample results from your recordset.  Then we can go back and tidy up the dynamic sql.
0
 
arendt73Author Commented:
I am searching a text field that contains words and dates. I prefer not to use the DW wizard as it clutters code. I want something that will work without the use of the DW wizard. Thanks again. I feel that the code is very close to completion.
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I don't want to use the DW code in your final solution either although it does not really clutter code that much as mx did.  But something is not working and by you using the wizard and getting a good result will help me help you.  I just need to see the entire recordset DW made for you.    You keep getting errors with each suggestion anybody has made on this post and instead of playing a guessing game back and forth this will help.
0
 
arendt73Author Commented:
mbizup, I used your last suggestion (pasted your code) and I am getting the following error:

Microsoft VBScript runtime  error '800a000d'

Type mismatch

Line 9


Am I better off attaching my search and results page to help you?  

For everyone assisting, all I am trying to do is query a memo field (proc_info) in an Access database with keywords from a search page.  I appreciate all of the effort so far.

Thank you.
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Glad it worked out for you.
0
 
arendt73Author Commented:
Solutions offered came very close to a solution but I had to use another method in attaining my desired results.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 10
  • 7
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now