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.
6 5414 jhardman 3,406
Recognized Expert Specialist
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 WhereKeywordStr ing. I notice that you response.write the WhereKeywordStr ing before you pass it to the db. When you get the error, what is the exact value of this variable?
Jared
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 WhereKeywordStr ing. I notice that you response.write the WhereKeywordStr ing 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 whereKeywordStr ing has the value of
WHERE CombinedFields LIKE '%whatever i type%' .
When i get the error i cant see the whereKeywordStr ing value is there anyway of me seeing what is stored in the whereKeywordStr ing when the error occurs.
Thanks in advance.
Genken
jhardman 3,406
Recognized Expert Specialist
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 whereKeywordStr ing has the value of
WHERE CombinedFields LIKE '%whatever i type%' .
When i get the error i cant see the whereKeywordStr ing value is there anyway of me seeing what is stored in the whereKeywordStr ing 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
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 whereKeywordsSt ring variable.
Please assist.
Thankyou
Genken
jhardman 3,406
Recognized Expert Specialist
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 whereKeywordsSt ring variable.
Please assist.
Thankyou
Genken
you could say:
near the beginning of the script. This might make your page time out, but the variable should be printed.
Jared
you could say:
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Tim Groulx |
last post by:
Hello All,
I am getting the following error when attemping to open a table in
SQL2kSP3a.
________________________________________
SQL Server Enterprise Manager
Database Server: Microsoft SQL Server
Version: 08.00.0760
Runtime Error: Invalid time format
|
by: Luigi Ida' via SQLMonster.com |
last post by:
Hi,
I have a php application connected through odbc to a sqlserver database.
When I try to execute select queries on a smalldatetime table field I
receive this message:
Warning: Numeric value out of
range
The query is something like:
select dateField from table
|
by: FreeOperator |
last post by:
Dear all,
On Win2000 server with SP3, I am trying to access a SQL Server 7.0
database, "TestDB", from VB6 via a SQL Server ODBC system DSN using ADO
2.7. In SQL Server Enterprise Manager, there is a login named "Tester".
In its property window, NO "Server Roles" was assigned but its
"Database Access" was set to "TestDB". This login was also made as the
user of "TestDB" with "public", "db_datareader" and "db_datawriter"
selected as its...
|
by: dcarson |
last post by:
I've read about this error in several other discussions, but still
can't seem to pinpoint the problem with my code. Everything seemed to
be working fine for some time, but it now tends to bomb out on me for
some reason. The Summary field is pulled from a Rich Text Editor that
allows HTML formatting and appears to be the culprit, but I just can't
seem to figure out why. Any guidance is greatly appreciated.
I'm connecting to an Access...
|
by: Dakkar |
last post by:
I wrote a program with c# for connecting mysql and taking data from it
but when someone without me try to execute the program they are taking
this error
ERROR
Data source name not found and
no default driver
| |
by: jwtulp |
last post by:
Hello all,
I receive the following error when updating MSAccess2003 Memo fields
using an ODBC connection in ASP.Net 1.1 when the length of the text to
be updated exceeds 255 characters. Everything works fine when the
length of the text stays below 255 characters. Does anyone have a
clue?:
ERROR Invalid
precision value
|
by: bazzer |
last post by:
hey,
i am using visual basic.net 2003 and have an ASP.NET webform
application thats accessing a microsoft access 2003 database. i kept
getting the following error when i tried to run it:
ERROR General error
Unable to open registry key 'Temporary (volatile) Jet DSN for process
0x8fc Thread 0x934 DBC 0x437b94 Jet'. ERROR Driver's SQLSetConnectAttr failed ERROR
General error Unable to open
registry key 'Temporary (volatile) Jet DSN...
|
by: bazzer |
last post by:
hey,
im trying to access a microsoft access database from an ASP.NET web
application in visual basic 2003.NET. i get the following error when i
try running it:
Server Error in '/CinemaBookingSystem' Application.
--------------------------------------------------------------------------------
ERROR General error
Unable to open registry key 'Temporary (volatile) Jet DSN for process
|
by: bhanu08 |
last post by:
I am get this error
java.sql.SQLException: Data source name not found and no default driver specified
my code for connection is
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:MyDataSource1");
ps=con.prepareStatement("Select * from Reservation where TravelDate=? and FlightNo=?");
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |