I am trying get it so that a user can search my site by choosing the table they want to search from a drop down list, and then in a text box input their query for that table. Is this possible?

Red Leader

Yup,

Set up a Recordset, enter any old rubbish, because you will have to completely modify it in code view.

Set up the List menu something like:

Option Value

Products tblProucts
Services tblServices
etc.


Then on the results page, replace the top block of UD's Recordset code with something like this:

<%
varTable = Request("selTable"
varWord = Replace(Request("txtWord", "'", "''"

Select Case varTable
Case "tblProducts" varField = "ProductDescription"
Case "tblServices" varField = "ServiceDetails"
' and so on for each table and its relevant field
End Select

varSQLstring = "SELECT FROM " & varTable & " WHERE " & varField & " LIKE '%" & varWord & "%'"
%>

Then modify the RecordsetName.Sourece = "SELECT....... in the second part of the recordset code as follows:

RecordsetName.Sourece = varSQLstring

Take a peek at the link below too for some more ideas.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Edited by - oeastwick on 03 Aug 2002 03:43:56

Many thanks for your reply. I did all you said but it comes back with the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'


[Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

/private _new/adv_search_results.asp, line 26


Line 26 is: Recordset_all.Open()

Once again, your help would be greatly received as I am completely at a loss as to where the problem is.

Simon

Red Leader

There are several reserved words, well quite a lot actually, that are part of the SQL language, make sure you haven't named any of your database fields with one of the reserved words.

A common one is Date, so something like:
SELECT * FROM TableName Where Date = Now() - This will fail.

Rename the database field RecordDate or something:
SELECT * FROM TableName Where RecordDate = Now() - This will work fine.

RESERVED WORDS:
-------------------------------------------------------
ABSOLUTE
ACTION
ADA
ADD
ALL
ALLOCATE
ALTER
AND
ANY
ARE
AS
ASC
ASSERTION
AT
AUTHORIZATION
AVG
BEGIN
BETWEEN
BIT
BIT_LENGTH
BOTH
BY
CASCADE
CASCADED
CASE
CAST
CATALOG
CHAR
CHAR_LENGTH
CHARACTER
CHARACTER_LENGTH
CHECK
CLOSE
COALESCE
COLLATE
COLLATION
COLUMN
COMMIT
CONNECT
CONNECTION
CONSTRAINT
CONSTRAINTS
CONTINUE
CONVERT
CORRESPONDING
COUNT
CREATE
CROSS
CURRENT
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_USER
CURSOR
DATE
DAY
DEALLOCATE
DEC
DECIMAL
DECLARE
DEFAULT
DEFERRABLE
DEFERRED
DELETE
DESC
DESCRIBE
DESCRIPTOR
DIAGNOSTICS
DISCONNECT
DISTINCT
DOMAIN
DOUBLE
DROP
ELSE
END
END-EXEC
ESCAPE
EXCEPT
EXCEPTION
EXEC
EXECUTE
EXISTS
EXTERNAL
EXTRACT
FALSE
FETCH
FIRST
FLOAT
FOR
FOREIGN
FORTRAN
FOUND
FROM
FULL
GET
GLOBAL
GO
GOTO
GRANT
GROUP
HAVING
HOUR
IDENTITY
IMMEDIATE
IN
INCLUDE
INDEX
INDICATOR
INITIALLY
INNER
INPUT
INSENSITIVE
INSERT
INT
INTEGER
INTERSECT
INTERVAL
INTO
IS
ISOLATION
JOIN
KEY
LANGUAGE
LAST
LEADING
LEFT
LEVEL
LIKE
LOCAL
LOWER
MATCH
MAX
MIN
MINUTE
MODULE
MONTH
NAMES
NATIONAL
NATURAL
NCHAR
NEXT
NO
NONE
NOT
NULL
NULLIF
NUMERIC
OCTET_LENGTH
OF
ON
ONLY
OPEN
OPTION
OR
ORDER
OUTER
OUTPUT
OVERLAPS
PAD
PARTIAL
PASCAL
POSITION
PRECISION
PREPARE
PRESERVE
PRIMARY
PRIOR
PRIVILEGES
PROCEDURE
PUBLIC
READ
REAL
REFERENCES
RELATIVE
RESTRICT
REVOKE
RIGHT
ROLLBACK
ROWS
SCHEMA
SCROLL
SECOND
SECTION
SELECT
SESSION
SESSION_USER
SET
SIZE
SMALLINT
SOME
SPACE
SQL
SQLCA
SQLCODE
SQLERROR
SQLSTATE
SQLWARNING
SUBSTRING
SUM
SYSTEM_USER
TABLE
TEMPORARY
THEN
TIME
TIMESTAMP
TIMEZONE_HOUR
TIMEZONE_MINUTE
TO
TRAILING
TRANSACTION
TRANSLATE
TRANSLATION
TRIM
TRUE
UNION
UNIQUE
UNKNOWN
UPDATE
UPPER
USAGE
USER
USING
VALUE
VALUES
VARCHAR
VARYING
VIEW
WHEN
WHENEVER
WHERE
WITH
WORK
WRITE
YEAR
ZONE
-------------------------------------------------------

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Edited by - oeastwick on 06 Aug 2002 12:17:48

It wasn't a reserved word but it needed the * placed between the SELECT and FROM. I am now getting the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

/private _new/adv_search_results.asp, line 26


Am i just fighting a loosing battle or what?!!!!!

Red Leader