Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - Advanced Dynamic SQL Search
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Advanced Dynamic SQL Search
 Post Reply Post Reply Page  12>
Author
  Topic Search Topic Search  Topic Options Topic Options
Roman View Drop Down
Newbie
Newbie


Joined: 21 January 2004
Location: Australia
Status: Offline
Points: 39 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Roman Quote  Post ReplyReply Direct Link To This Post Topic: Advanced Dynamic SQL Search
    Posted: 28 September 2005 at 9:10am
Hi All,

I would like some assistance please with an advanced dynamic SQL search form.  If one of you guru types have a pizza and coke getting delivered perhaps you can give me some advice while taking a break from what you're doing.  Sorry if this is not advanced for you, but for me it certainly is!

I'm building an MS Access database on a Windows 2003 server using classic ASP and DNS less ADO connection strings.  The database contains 8 normalised related tables.  The key is autonumber which is copied and inserted into each new related record created in the other tables for the member.  I've completed the Add, Edit and Delete functions and all is working real nice across all the tables.  Now I'm battling to keep my head above water with the Search option.

The Search option will allow the user to use almost all of the form fields to enter search data - most will use = but I intend to also make < and > available for date fields.  I've started writing the IF / END IF code for each field which will dynamically add the SQL to the SQLstr if the search field is <> NULL.  I've used the http://www.sqlcourse.com/ site (and recommend it to everyone) and it seems the JOIN command will successfully create a recordset linking the tables together into one SQL SELECT.

I'm battling with the finer points of putting the SQLstr together if the user uses fields from two or more tables.  Obviously I need many variables and IF / ELSE / END IF loops to accommodate putting the SQL together properly dynamically that could include using 1 to 8 different tables!

Can anyone offer some code that demonstrates a methodology to follow because it is too complex for me to get my head around - there are just too many variations to the final SQLstr.  I have not touched ASP.NET.

I appreciate any help at all.

Cheers...
Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670 		Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post Posted: 28 September 2005 at 6:51pm

As you say your tables are normalized and all related. I must assume now that you are following a star schema, otherwise the outcome will be different so i.e. you have tbl1 with an autonumber and from this all or most tables are related but to a 2nd 3rd etc level....

So I would create a join on all tables in your dynamic query as they are normalized and you use inner joins it should return the right results so start the strSQL String with something like
Select f1,f1.... from
   tbl1 Join tbl2 on tbl1.f1=tbl2=f1
   Join tbl3 on tbl1.f1=tbl3.f4
   Join tbl4 on tbl2.f1=tbl4.f7
  and so on....
once this construct is done, you can add the dynamics like

If NOT field1="" THEN
  strSQL = strSQL & " AND f1.f1=' & field1 & "'"
 
 
Hope you get the idea, just remember to protect the entry fields from injection attacks. Always use sp's and html filter them etc.
Blog | MPG Tracker
Back to Top
Roman View Drop Down
Newbie
Newbie


Joined: 21 January 2004
Location: Australia
Status: Offline
Points: 39 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Roman Quote  Post ReplyReply Direct Link To This Post Posted: 28 September 2005 at 9:03pm
Michael,
Thank-you for the prompt response.  I'll digest your response tonight when I get home from work.  Yes I am using a star schema however it is only to one level i.e. each of the 7 thematic tables branch individually from the central Member's table - this may actually have made my job a little easier.
 
I'll also research Injection Attacks.
 
Cheers,
Back to Top
Roman View Drop Down
Newbie
Newbie


Joined: 21 January 2004
Location: Australia
Status: Offline
Points: 39 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Roman Quote  Post ReplyReply Direct Link To This Post Posted: 30 September 2005 at 9:33pm
Michael, All,

I've now got two tables working great using INNER JOIN.  I did have to make some minor tweeks to the example above which I believe is because of the MS JET connector.  The problem now is getting the syntax right for using more than two tables.  There is a lot of code examples for using two tables but I can't find much reference to using more, except for several other people asking how to do it Smile

For two tables to work I've used:

searchStr = "SELECT tbl1.f1, tbl1.f2,..., tbl2.f1, tbl2.f2,... FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.id WHERE tbl1.id > 0 "

I inserted the WHERE (which selects all records in the primary table which is fine) so that the AND located in the IF loops follow correctly.  This all works well and I get the correct data returned from searches.

I've tried several variations of adding a third table but continue getting  "Syntax error (missing operator) in query expression" returned.  The example above appears to refer to how multiple layer tables are joined - my 7 tables are only 1 level deep from the primary tbl1 - a 1 layer star.

So, do I join each of the 7 tables back to the primary table, e.g. 2 -> 1, 3 -> 1, 4 -> 1 etc,  or do you cascade the join 1 -> 2, 2 -> 3, 3 -> 4, etc ?

Cheers,
Back to Top
Roman View Drop Down
Newbie
Newbie


Joined: 21 January 2004
Location: Australia
Status: Offline
Points: 39 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Roman Quote  Post ReplyReply Direct Link To This Post Posted: 01 October 2005 at 6:28am
hmmm... getting there - slowly.  I've found a reference, including a code example, that the first INNER JOIN must be in parentheses.  This is a copy of what I've got:
 
searchStr = "SELECT tblUsers.id, tblUsers.Fname,...,tblVehicle.id, tblVehicle.make,...,tblTraining.id, tblTraining.crse_basic,..., FROM (tblUsers INNER JOIN tblVehicle ON tblUsers.id=tblVehicle.id) INNER JOIN tblTraining ON tblUsers.id=tblTraining.id "
 
(6 more tables need to be added when I get the third one working - the code now cycles through IF loops for each field in the search form e.g.)
 
if NOT request.form("s_fname") = "" THEN
 searchStr = searchStr & " AND Fname = '" & Request.Form("s_fname") & "'"
end if
 
I get this error:
 
 Microsoft JET Database Engine error '80040e14'

Join expression not supported.

doSearch.asp, line 85

Should I be considering changing to MySQL? Unhappy
 
Thanks in anticipation...
 
 


Edited by Roman - 01 October 2005 at 6:30am
Back to Top
Roman View Drop Down
Newbie
Newbie


Joined: 21 January 2004
Location: Australia
Status: Offline
Points: 39 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Roman Quote  Post ReplyReply Direct Link To This Post Posted: 01 October 2005 at 8:04am
I have it working! but I don't understand why, it doesn't conform with anything I have seen.
 
"SELECT ... FROM tblUsers, tblVehicle, tblTraining WHERE tblUsers.id=tblVehicle.id AND tblVehicle.id=tblTraining.id "
 
Then into the IF loops.
 
I've tested numerous searches and the results, so far, are valid using fields from each table separately, two tables, and then all three.  I'm now adding the remaining tables.  Can anyone explain why this is working without defining JOINs? - or are they implied?
 
Sorry if this thread is starting to look like I'm talking to myself but I'm thinking others are going to find it useful!
 
Back to Top
Roman View Drop Down
Newbie
Newbie


Joined: 21 January 2004
Location: Australia
Status: Offline
Points: 39 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Roman Quote  Post ReplyReply Direct Link To This Post Posted: 02 October 2005 at 8:55am
I've completed adding all eight tables and IF loops for over 60 fields including user selected operators for date fields.  Although my search queries are returning valid records (I'm very pleased about that!), I'm getting multiple returns for the same records.  So it seems I need to use INNER JOINs after all but I can't get the SQL syntax right.
 
This must be commonly used code - any help appreciated... Confused
 
Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670 		Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post Posted: 03 October 2005 at 9:01am
See my pm answer to yours, if you have a shell db (no data) you can send, it might be easier to construct a query for you.
Blog | MPG Tracker
Back to Top
 Post Reply Post Reply Page  12>

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.