Advanced Dynamic SQL Search
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=16723
Printed Date: 29 March 2026 at 4:41am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Advanced Dynamic SQL Search
Posted By: Roman
Subject: Advanced Dynamic SQL Search
Date 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... |
Replies:
Posted By: michael
Date 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. ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
Posted By: Roman
Date 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,
|
Posted By: Roman
Date 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  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, |
Posted By: Roman
Date 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?
 Thanks in anticipation...
|
Posted By: Roman
Date 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!
|
Posted By: Roman
Date 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...
 |
Posted By: michael
Date 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. ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
Posted By: Roman
Date Posted: 03 October 2005 at 9:11am
|
I've discovered why I'm getting multiple search results. The Financial table has a 1 to many relationship with the Member's table (the rest are 1 to 1). After counting the number of duplicate search results for each member I found the number of duplicates equals the number of records they have in the Financial table.
The Financial table has two key fields, one of the fields is the UserID, which is common across all the tables, and the other is a 6 digit random number to ensure each financial record for a member is still unique.
How do I join the Financial table into the SQL search string so that a member's multiple financial records don't add extra rows to the recordset? - I think my SQL is adding duplicate field names to the recordset which is adding new rows?
|