Solved

Microsoft VBScript runtime error on results page

Posted on 2013-01-09
28
412 Views
Last Modified: 2013-01-15
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
Comment
Question by:arendt73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 7
  • +2
28 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38760813
you might want to take a look at my article on complex text filters
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38760817
try changing

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

with

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

Author Comment

by:arendt73
ID: 38760829
capricorn1, after appling your suggestion, I now get the following error:

Microsoft VBScript runtime  error '800a000d'

Type mismatch

Line 9
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:mbizup
ID: 38760846
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38760851
Dim kw() as string
kw=Split(Request.Form("keywords"), " ")


do you have string values on ("keywords") ?
0
 

Author Comment

by:arendt73
ID: 38760858
mbizup, after applying your code, I get the following:

Microsoft VBScript compilation  error '800a041f'

Unexpected 'Next'
Line 29

Next
^
0
 

Author Comment

by:arendt73
ID: 38760867
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38760873
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
 

Author Comment

by:arendt73
ID: 38760889
mbizup, yes we are. Thanks.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38760897
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38760932
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
 

Author Comment

by:arendt73
ID: 38760972
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 250 total points
ID: 38761030
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 250 total points
ID: 38761056
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38761095
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
 
LVL 53

Accepted Solution

by:
Scott Fell,  EE MVE earned 250 total points
ID: 38761186
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
 

Author Comment

by:arendt73
ID: 38761436
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
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
ID: 38761577
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
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
ID: 38761585
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
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
ID: 38761621
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
 

Author Comment

by:arendt73
ID: 38761702
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
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
ID: 38761730
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
 

Author Comment

by:arendt73
ID: 38761776
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
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
ID: 38761798
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
 

Assisted Solution

by:arendt73
arendt73 earned 0 total points
ID: 38763683
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
 
LVL 53

Expert Comment

by:Scott Fell, EE MVE
ID: 38764423
Glad it worked out for you.
0
 

Author Closing Comment

by:arendt73
ID: 38777574
Solutions offered came very close to a solution but I had to use another method in attaining my desired results.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question