Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > ASP Search

Reply
Thread Tools

ASP Search

 
 
JP SIngh
Guest
Posts: n/a
 
      03-09-2005
Hi All

I am trying to build a search results page for the user.

In the database we have three tables

Employees

Empname - Text
Fullname - Text

Records

Id - Autonumber
Subject - Text
Description - Text

Record Dependents

RecordId - Integer
Username - Text

In record dependents there could be multiple values for each recordid
basically it specifies who is allow to view a particular record.

What I need help with is how do a search that will find what the user has
specified but only bring back results for which he is a RecordDependent.

Can this be done through simple sql? or do I need something more.

I was thinking more on the lines of

"Select * from records where exists (Select * from recorddependents where
empname = '8877YYYY') and description ='" & request.form("description") &
"'"



 
Reply With Quote
 
 
 
 
Steven Burn
Guest
Posts: n/a
 
      03-09-2005
First of all, don't use "Select *"

http://aspfaq.com/show.asp?id=2096

Second, try something along the lines of;

strSQL = "Select [FIELDS] from Records Where [Condition 1]=[Value 1] AND [Condition 2]=[Value 2]"

Replacing [FIELDS] with your field names, and [Condition 1] etc, with the respective conditions and values you want to meet.

--
Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"JP SIngh" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Hi All
>
> I am trying to build a search results page for the user.
>
> In the database we have three tables
>
> Employees
>
> Empname - Text
> Fullname - Text
>
> Records
>
> Id - Autonumber
> Subject - Text
> Description - Text
>
> Record Dependents
>
> RecordId - Integer
> Username - Text
>
> In record dependents there could be multiple values for each recordid
> basically it specifies who is allow to view a particular record.
>
> What I need help with is how do a search that will find what the user has
> specified but only bring back results for which he is a RecordDependent.
>
> Can this be done through simple sql? or do I need something more.
>
> I was thinking more on the lines of
>
> "Select * from records where exists (Select * from recorddependents where
> empname = '8877YYYY') and description ='" & request.form("description") &
> "'"
>
>
>


 
Reply With Quote
 
 
 
 
Bullschmidt
Guest
Posts: n/a
 
      03-10-2005
And in case you ever need it here is an example of one query (QueryB)
based on the results of another query (QueryA):

QueryA = "SELECT CustID FROM tblCUSTOMERS WHERE CustName = 'A%'"

QueryB = "SELECT CustID, CustName FROM tblCUSTOMERS WHERE CustID IN (" &
QueryA & ")"

But the following is even faster and allows for more than one field to
be returned in QueryA:

QueryB = "SELECT tblCUSTOMERS.CustID, CustName FROM (" & strSQLA & ") AS
tblSQLA INNER JOIN tblCUSTOMERS ON tblSQLA.CustID = tblCUSTOMERS.CustID"

So QueryA would include all the CustID's for customers starting with A.

And QueryB would include more fields in the customers table (i.e. not
just the CustID field) for the records returned in QueryA (which was the
customers starting with A).

I suppose it wouldn't hurt to always use LEFT JOIN's in QueryB and build
from the tblSQLA on the left to other tables that have fields you want
to return.

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Google search result like site search!! How? =?Utf-8?B?TGFrc2htaSBOYXJheWFuYW4uUg==?= ASP .Net 3 05-06-2005 02:08 AM
removing search engines from the search bar whatever.or.not@gmail.com Firefox 1 02-17-2005 12:06 PM
Search Bar not displaying search Engines Zimran Douglas Firefox 1 01-07-2005 02:30 PM
search within a search within a search - looking for better way...my script times out Abby Lee ASP General 5 08-02-2004 04:01 PM



Advertisments