Solved

Microsoft VBScript runtime error on results page

Posted on 2013-01-09
28
407 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
  • 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 119

Expert Comment

by:Rey Obrero
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
 
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 119

Expert Comment

by:Rey Obrero
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 52

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 52

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 52

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 52

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 52

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 52

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 52

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now