I've got a web form that I need to make the selection criteria combo boxes dynamic.
There are 4 combo boxes and they all need to dynamic.
--- ANALOGY ---
So, if cboMonth is set to August, then all of the other combo boxes have to have Month = August as the Where Clause.
Then, if cboYear is set to 2007, then all of the other combo boxes have to have Month = August and Year = 2007 as the Where Clause.
The exact same where clause will apply to all 4 combo boxes.
--- ACTUAL SCOPE ---
The exact details are detailed in the attached word document.
Note - database and SQL statements are all provided. The aspx web form is provided with the combo boxes in place and the proper SQL statement (less the WHERE claue) included. I just need someone to be able to change the SQL statement feeding all four combo boxes every time one of the combo boxes is change.
I'm thinking if you can
1- create a function to create a where clause based upon each of the values that is not null
2 - set the results of that function to a variable-WHERE (this will be blank initially).
3 - include variable-WHERE in all 4 of the combo box's select statements.
Note - that I do not know HOW to do this - so it may not be this simple. That's why I want to hire YOU
## Deliverables
# Goals:
1.? ? ? ? ? ? To alter what is in the display data of a form Pinnacle provides based upon what filtering has already occurred.? An **analogy** would be a class schedule table.? ? ? In a class schedule you would see Student, Teacher, Course and Classroom information for each record in a schools schedule table.
a.? ? ? ? ? ? ? Students have multiple teachers and teachers have multiple students.?
b.? ? ? ? ? Students have multiple Courses and Courses have multiple Students.?
c.? ? ? ? ? ? Classrooms have multiple courses and courses have multiple classrooms.? ETC.? ?
d.? ? ? ? ? Ie ??" combinations of data are very diverse.?
e.? ? ? ? ? So, if you want to look at the schedule table ??" you’d see everything.?
f.? ? ? ? ? ? ? If you selected classroom 101, then
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? i.? ? ? ? ? in the student pulldown box ??" you’d only see students with classroom 101 on their schedule,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ii.? ? ? ? ? ? in the teacher combo box , you’d only see teachers that teach in classroom 101
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? iii.? ? ? ? ? ??" ETC.
2.? ? ? ? ? ? To display a very simple report based upon the selection criteria.
# Pinnacle will provide:
* Access 2003 database ??" This database is completely functional and cannot be modified in any way unless agreed upon by both parties.
* web page ??" This will have the required combo boxes and buttons ??" but they will not be functional.? The SQL statements will be provided ??" WITH NO WHERE CLAUSE AND NO VARIALBLE FOR THE WHERE CLAUSE.
* SQL statements ? -? This should make life easier.? Some of the fieldnames may have changed slightly, but will alter that if for whoever is selected for the project.? Note that these are the SQL statements I think you will need, but since I’m not sure how to do this sort of application, I’m not sure what other SQL statements you might need.
* [login to view URL] ??" you will probably need to change this and let me know what changes you make.
?
# Access Database Description
The access database will have about 5 tables.
tblConnectionSpecification ??" this is where the primary data resides.? It has the following fields:
Ø? SpecificationId - unique Identifier
Ø? ConnId ??" foreign key to tblConns
Ø? ? SizeId ??" foreign key to tblSizes
Ø? WtId ??" foreign key to tblWts
Ø? GradeId ??" foreign key to tblGrades
**
**
# Webform Description
The web page has 4 combo boxes and select statements ??" with a variable for the WHERE clause.
Note ??" GlobalWhereClause represents a variable? to put hold the where clause.? The GlobalWhereClause IS NOT INCLUDED.? ? I need you guys to implement this.
·? ? ? ? ? ? ? ? cboConn *to filter ConnId*
Ø? SELECT [login to view URL], tblConnections.[Connection]
FROM (tblConnectionsSpecifications INNER JOIN tblConnections
ON [login to view URL] = [login to view URL])
*<GlobalWhereClause (variable)??" initially blank>*
GROUP BY [login to view URL], tblConnections.[Connection]
ORDER BY tblConnections.[Connection]
·? ? ? ? ? ? ? ? cboSize *to filter SizeId*
Ø? SELECT [login to view URL], tblSizes.[Size]
FROM (tblConnectionsSpecifications INNER JOIN tblSizes
ON [login to view URL] = [login to view URL])
*<GlobalWhereClause (variable)??" initially blank>*
GROUP BY [login to view URL], tblSizes.[Size] ORDER BY tblSizes.[Size]
·? ? ? ? ? ? ? ? CboWt *to Filter WtId*
Ø? SELECT [login to view URL], [login to view URL]
FROM (tblConnectionsSpecifications INNER JOIN tblWtPerFoot
ON [login to view URL] = [login to view URL])
*<GlobalWhereClause (variable)??" initially blank>*
GROUP BY [login to view URL], [login to view URL]
ORDER BY [login to view URL]
·? ? ? ? ? ? ? ? CboGrade *to filter GradeId*
Ø? "SELECT [login to view URL], [login to view URL]
FROM ((tblConnectionsSpecifications INNER JOIN tblGrades
ON [login to view URL] = [login to view URL]) INNER JOIN tblGradeDesignations
ON [login to view URL] = [login to view URL])
*<GlobalWhereClause (variable) ? ??" initially blank>*
GROUP BY [login to view URL], [login to view URL]"
?
The web page has a **Display Tech Data** button to generate a 2nd web page based on the selected criteria
All four of the combo boxes must have a value selected and they can be selected in any order.
?
?
# Sample chronology of events:
1.? ? ? ? ? ? Page is displayed to the user.
2.? ? ? ? ? ? User sees ALL Conn, All Sizes, All Wts and All Grade Designations (in the appropriate combo box) because where clauses is initially blank.
3.? ? ? ? ? ? The user selects Size 3 5/8.
a.? ? ? ? ? ? The key to 3 5/8 is 5.
b.? ? ? ? ? Therefore a GlobalWhereClause of ‘Where SizeId=5’ is generated
c.? ? ? ? ? ? The GlobalWhereClause is added to ALL of the cbo Boxes
d.? ? ? ? ? Now in cboConn ??" only Connections which have a size of 3 5/8 (5) are in combo box.
e.? ? ? ? ? Now in cboSize? ??" only Sizes which have a size of 3 5/8 (5) are in combo box.
f.? ? ? ? ? ? ? Now in cboWt ??" only Wts which have a size of 3 5/8 (5) are in combo box.
g.? ? ? ? ? ? Now in cboGrade ??" only Grades which have a size of 3 5/8 (5) are in combo box.
4.? ? ? ? ? ? The user selects Wt Heavy.
a.? ? ? ? ? ? The key toHeavy is 1
b.? ? ? ? ? Therefore a GlobalWhereClause of ‘Where SizeId=5 and WtId=’1’ is generated
c.? ? ? ? ? ? The GlobalWhereClause is added to ALL of the cbo Boxes
d.? ? ? ? ? Now in cboConn ??" only Connections which have a size of 3 5/8 (5) and wt=Heavy are in combo box.
e.? ? ? ? ? Now in cboSize? ??" only Sizes which have a size of 3 5/8 (5) and wt=Heavy are in combo box.
f.? ? ? ? ? ? ? Now in cboWt ??" only Wts? which have a size of 3 5/8 (5) and wt=Heavy are in combo box.
g.? ? ? ? ? ? Now in cboGrade ??" only Grades which have a size of 3 5/8 (5) and wt=Heavy are in combo box.
5.? ? ? ? ? ? The user selects Conn BTS.
a.? ? ? ? ? ? The key to BTS is 9
b.? ? ? ? ? Therefore a GlobalWhereClause of ‘Where SizeId=5 and WtId=’1’ and ConnId=9? is generated
c.? ? ? ? ? ? The GlobalWhereClause is added to ALL of the cbo Boxes
d.? ? ? ? ? Now in cboConn ??" only Connections which have a size of 3 5/8 (5) and wt=Heavy and Connection =9 are in combo box.
e.? ? ? ? ? Now in cboSize? ??" only Sizes which have a size of 3 5/8 (5) and wt=Heavy and Connection =9 are in combo box.
f.? ? ? ? ? ? ? Now in cboWt ??" only Wts? which have a size of 3 5/8 (5) and wt=Heavy are and Connection =9 in combo box.
g.? ? ? ? ? ? Now in cboGrade ??" only Grades which have a size of 3 5/8 (5) and wt=Heavy and Connection =9 are in combo box.
6.? ? ? ? ? ? The user selects Grade Designation 100P.
a.? ? ? ? ? ? The GradeDesignation key to 100P = 44
b.? ? ? ? ? The GradeKey to 100P = 12
c.? ? ? ? ? ? Therefore a GlobalWhereClause of ‘Where SizeId=5 and WtId=’1’ and ConnId=9? and GradeID = 12 is generated
d.? ? ? ? ? The GlobalWhereClause is added to ALL of the cbo Boxes
e.? ? ? ? ? Now in cboConn ??" only Connections which have a size of 3 5/8 (5) and wt=Heavy and Connection =9 ? and GradeID = 12 are in combo box.
f.? ? ? ? ? ? ? Now in cboSize? ??" only Sizes which have a size of 3 5/8 (5) and wt=Heavy and Connection =9? and GradeID = 12 are in combo box.
g.? ? ? ? ? ? Now in cboWt ??" only Wts? which have a size of 3 5/8 (5) and wt=Heavy are and Connection =9? and GradeID = 12 are in combo box.
h.? ? ? ? ? Now in cboGrade ??" only Grades which have a size of 3 5/8 (5) and wt=Heavy and Connection =9? and GradeID = 12 are in combo box.
i.? ? ? ? ? ? ? ? Note ??" the GradeId Is used to in the where clause ??" but the GradeDesignation Id is needed for the report.
7.? ? ? ? ? ? At any point ??" user can click the CLEAR button which clears the where clause and all values are seen again.
8.? ? ? ? ? ? The user clicks the **Display Tech Data** button.
a.? ? ? ? ? ? A new page is generated with SpecificationId, Conn, Size, Wt, Grade and GradeSpecification the following items displayed for all records in?
SELECT tblConnectionsSpecifications.*
? ? ? ? ? ? ? ? ? ? ? , [login to view URL]
? ? ? ? ? ? ? ? ? ? ? , [login to view URL]
? ? ? ? ? ? ? ? ? ? , [login to view URL]
? ? ? ? ? ? ? ? ? ? , [login to view URL], [login to view URL]
FROM ((tblSizes INNER JOIN ((tblConnections INNER JOIN tblConnectionsSpecifications <GlobalWhereClause>
ON [login to view URL] = [login to view URL])
INNER JOIN tblGrades
ON [login to view URL] = [login to view URL])
? ON [login to view URL] = [login to view URL])
INNER JOIN tblWtPerFoot
ON [login to view URL] = [login to view URL])
INNER JOIN tblGradeDesignations
ON [login to view URL] = [login to view URL];
?
?
Don’t forget about GradeSpecification needing to be kept.
? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
?
?
?