Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC Microsoft Ac

[Genken]

Hi I have written a search function to Search through my database but i get this error could anyone please assist as this is quite urgent.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/Connect.asp" -->
<%
Set objconn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("mypath")
objconn.Open%>
<%
Dim rsQryDisplayFields
Dim rsQryDisplayFields_numRows
 
Set rsQryDisplayFields = Server.CreateObject("ADODB.Recordset")
rsQryDisplayFields.ActiveConnection = objconn
rsQryDisplayFields.Source = "SELECT DISTINCT RiskCategoryID,RiskCategory, RiskRef, RiskDescription, APName, APRef, BusinessUnit, ProcessName, Application_Name, Database_Management_System, Operating_System_Name, Physical_Infrastructure_Filepath, Network_Infrastructure_Filepath  FROM qryDisplayFields"
rsQryDisplayFields.CursorType = 0
rsQryDisplayFields.CursorLocation = 2
rsQryDisplayFields.LockType = 1
rsQryDisplayFields.Open()
 
rsQryDisplayFields_numRows = 0
%>
 
<%
 
 
'the words entered in the text field stored in a text string in a variable called Keywords
Keywords = Request("txtKeywords2")
 
'The seleted sea5rch type - All words = AND, Any Words = OR, Exact Phrase = EXACT
'Stored in a variable called Matchtype
MatchType = Request("selMatchType")
 
If Keywords <> "" then 'if there is something in the keywords 
 
    Searchfields = "CombinedFields" 'Database table column in which we are going look for matches to our keywords
 
    'stored in a textfield variabled called Searhfields
 
    Keywords = replace(Keywords, "'" , "''") 'Replace any single quotes with 2 quotes, to stop the search failing , but matches with words with apostrophes.
 
    If (MatchType <> "EXACT") Then    ' If Searchtype is not EXACT
 
        Keywords = Replace(Keywords, ","," ")     ' Replace any commas, colons, semicolons, dashes, undersores,
        Keywords = Replace(Keywords, ":"," ")     ' forward slashes or back slashes in the text entered in the
        Keywords = Replace(Keywords, ";"," ")     ' Keywords text field with a space.
        Keywords = Replace(Keywords, "-"," ")
        Keywords = Replace(Keywords, "_"," ")
        Keywords = Replace(Keywords, "/"," ")
        Keywords = Replace(Keywords, "\"," ")
        WhereKeywordsString = "WHERE" & Searchfields & " LIKE '%" ' The sql statment where clause stored as text
 
        SearchArray = Split(Keywords," ") 'Split kewyords that are now separated by spaces and sted them in an array
 
        For i = 0 to Ubound(SearchArray)' Repeat the following for each word in the array
 
            if i > 0 Then 
                WhereKeywordsString = WhereKeywordsString & " " & MatchType & " " & Searchfields & " LIKE '%"  & SearchArray(i) & "%'"' Builds the SWL statment substituting AND/ OR as defined by Matchtype
            Else
                WhereKeywordsString = WhereKeywordsString & SearchArray(i) & "%'" ' If there is only one word in the array
            End if 
        Next
    Else
 
        WhereKeywordsString = " WHERE CombinedFields LIKE '%" & Keywords & "%'" 'If the search Type seleted was EXACT 
    End if  'End if Searchtype is not EXACT
    Response.Write(WhereKeywordsString)
 
%>
<%
    set rsQryCombinedFields = Server.CreateObject("ADODB.Recordset")
    rsQryCombinedFields.ActiveConnection = objconn
    rsQryCombinedFields.Source = "SELECT DISTINCT RiskCategoryID From qryMatrixAll" & WhereKeywordsString 
    rsQryCombinedFields.CursorType = 0
    rsQryCombinedFields.CursorLocation = 2
    rsQryCombinedFields.LockType = 1
    rsQryCombinedFields.Open()
    rsQryCombinedFields_numRows = 0    
    %>

Syntax error in from clause.
Error on line 74

And whats funny is that sometimes it works and sometimes it doesnt.

[jhardman]

There are several things that can go wrong here, several of them involve special characters that need to be escaped (or changed to safe characters) before sending them to the db, I would guess this is causing the problem, probably in the variable WhereKeywordString. I notice that you response.write the WhereKeywordString before you pass it to the db. When you get the error, what is the exact value of this variable?

Jared

[Genken]

There are several things that can go wrong here, several of them involve special characters that need to be escaped (or changed to safe characters) before sending them to the db, I would guess this is causing the problem, probably in the variable WhereKeywordString. I notice that you response.write the WhereKeywordString before you pass it to the db. When you get the error, what is the exact value of this variable?

Jared

Well i basically have a search page which is made of 3 different types of Search types namely AND, OR, and EXACT words. If the user clicks on AND i get an error or if the user clicks on OR i get an error , but whenever i click on EXACT the search seems to work fine.

When i click on EXACT list option the search works and the whereKeywordString has the value of
WHERE CombinedFields LIKE '%whatever i type%' .

When i get the error i cant see the whereKeywordString value is there anyway of me seeing what is stored in the whereKeywordString when the error occurs.

Thanks in advance.
Genken

[jhardman]

Well i basically have a search page which is made of 3 different types of Search types namely AND, OR, and EXACT words. If the user clicks on AND i get an error or if the user clicks on OR i get an error , but whenever i click on EXACT the search seems to work fine.

When i click on EXACT list option the search works and the whereKeywordString has the value of
WHERE CombinedFields LIKE '%whatever i type%' .

When i get the error i cant see the whereKeywordString value is there anyway of me seeing what is stored in the whereKeywordString when the error occurs.

Thanks in advance.
Genken

that kind of depends on where the error occurs. If the error doesn't come until you connect to the db (after you create and populate the variable) then you should still be able to see it, unless you have some server settings that give "friendly" (worthless) error messages when the asp page gives an error. The error message you described, though, suggests you should see the variable still. At least I don't see any reason why you wouldn't see it.

Jared

[Genken]

Im really sorri, but i cannot see the variables values when the error occurs only the browser error messages.

Is there anyway how i can force the variable to be displayed in command prompt or somthing i know the error lies with the whereKeywordsString variable.

Please assist.

Thankyou
Genken

[jhardman]

Im really sorri, but i cannot see the variables values when the error occurs only the browser error messages.

Is there anyway how i can force the variable to be displayed in command prompt or somthing i know the error lies with the whereKeywordsString variable.

Please assist.

Thankyou
Genken

you could say:

on error resume next

near the beginning of the script. This might make your page time out, but the variable should be printed.

Jared

[Genken]

you could say:

on error resume next

near the beginning of the script. This might make your page time out, but the variable should be printed.

Jared

Hi Thanks for all your assistance and help i got it working today by simply just retyping all my code in a new file. I really couldnt understand why it worked this time, but it did.

I have another question relating to my previous question.

I have everything displaying very nicely now but what i need to do is export all the findings or returning recordset to .csv or .xls format and i have moved from access format to sql server is there help resources you can point me to or do you have some advice.

Thanks in advance
Genken