By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,759 Members | 959 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,759 IT Pros & Developers. It's quick & easy.

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

P: 16
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.
Expand|Select|Wrap|Line Numbers
  1. <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
  2. <!--#include file="Connections/Connect.asp" -->
  3. <%
  4. Set objconn = Server.CreateObject("ADODB.Connection")
  5. objconn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("mypath")
  6. objconn.Open%>
  7. <%
  8. Dim rsQryDisplayFields
  9. Dim rsQryDisplayFields_numRows
  10.  
  11. Set rsQryDisplayFields = Server.CreateObject("ADODB.Recordset")
  12. rsQryDisplayFields.ActiveConnection = objconn
  13. 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"
  14. rsQryDisplayFields.CursorType = 0
  15. rsQryDisplayFields.CursorLocation = 2
  16. rsQryDisplayFields.LockType = 1
  17. rsQryDisplayFields.Open()
  18.  
  19. rsQryDisplayFields_numRows = 0
  20. %>
  21.  
  22. <%
  23.  
  24.  
  25. 'the words entered in the text field stored in a text string in a variable called Keywords
  26. Keywords = Request("txtKeywords2")
  27.  
  28. 'The seleted sea5rch type - All words = AND, Any Words = OR, Exact Phrase = EXACT
  29. 'Stored in a variable called Matchtype
  30. MatchType = Request("selMatchType")
  31.  
  32. If Keywords <> "" then 'if there is something in the keywords 
  33.  
  34.     Searchfields = "CombinedFields" 'Database table column in which we are going look for matches to our keywords
  35.  
  36.     'stored in a textfield variabled called Searhfields
  37.  
  38.     Keywords = replace(Keywords, "'" , "''") 'Replace any single quotes with 2 quotes, to stop the search failing , but matches with words with apostrophes.
  39.  
  40.     If (MatchType <> "EXACT") Then    ' If Searchtype is not EXACT
  41.  
  42.         Keywords = Replace(Keywords, ","," ")     ' Replace any commas, colons, semicolons, dashes, undersores,
  43.         Keywords = Replace(Keywords, ":"," ")     ' forward slashes or back slashes in the text entered in the
  44.         Keywords = Replace(Keywords, ";"," ")     ' Keywords text field with a space.
  45.         Keywords = Replace(Keywords, "-"," ")
  46.         Keywords = Replace(Keywords, "_"," ")
  47.         Keywords = Replace(Keywords, "/"," ")
  48.         Keywords = Replace(Keywords, "\"," ")
  49.         WhereKeywordsString = "WHERE" & Searchfields & " LIKE '%" ' The sql statment where clause stored as text
  50.  
  51.         SearchArray = Split(Keywords," ") 'Split kewyords that are now separated by spaces and sted them in an array
  52.  
  53.         For i = 0 to Ubound(SearchArray)' Repeat the following for each word in the array
  54.  
  55.             if i > 0 Then 
  56.                 WhereKeywordsString = WhereKeywordsString & " " & MatchType & " " & Searchfields & " LIKE '%"  & SearchArray(i) & "%'"' Builds the SWL statment substituting AND/ OR as defined by Matchtype
  57.             Else
  58.                 WhereKeywordsString = WhereKeywordsString & SearchArray(i) & "%'" ' If there is only one word in the array
  59.             End if 
  60.         Next
  61.     Else
  62.  
  63.         WhereKeywordsString = " WHERE CombinedFields LIKE '%" & Keywords & "%'" 'If the search Type seleted was EXACT 
  64.     End if  'End if Searchtype is not EXACT
  65.     Response.Write(WhereKeywordsString)
  66.  
  67. %>
  68. <%
  69.     set rsQryCombinedFields = Server.CreateObject("ADODB.Recordset")
  70.     rsQryCombinedFields.ActiveConnection = objconn
  71.     rsQryCombinedFields.Source = "SELECT DISTINCT RiskCategoryID From qryMatrixAll" & WhereKeywordsString 
  72.     rsQryCombinedFields.CursorType = 0
  73.     rsQryCombinedFields.CursorLocation = 2
  74.     rsQryCombinedFields.LockType = 1
  75.     rsQryCombinedFields.Open()
  76.     rsQryCombinedFields_numRows = 0    
  77.     %>
Expand|Select|Wrap|Line Numbers
  1. Syntax error in from clause.
  2. Error on line 74
And whats funny is that sometimes it works and sometimes it doesnt.
Jul 4 '07 #1
Share this Question
Share on Google+
6 Replies


jhardman
Expert 2.5K+
P: 3,405
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
Jul 5 '07 #2

P: 16
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
Jul 6 '07 #3

jhardman
Expert 2.5K+
P: 3,405
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
Jul 6 '07 #4

P: 16
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
Jul 10 '07 #5

jhardman
Expert 2.5K+
P: 3,405
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:
Expand|Select|Wrap|Line Numbers
  1. on error resume next
near the beginning of the script. This might make your page time out, but the variable should be printed.

Jared
Jul 10 '07 #6

P: 16
you could say:
Expand|Select|Wrap|Line Numbers
  1. 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
Jul 12 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Browse more ASP / Active Server Pages Questions on Bytes