Pinchas
|
| Posted: 10/16/2002, 8:47 AM |
|
One of the most common use of “Dependent List Boxes” is the implementation of the Country-City Lookup case. This is the case when you need to assign an item (person, weather) to an international “city” and there are too many for one list box.
At least 3 of CodeCharge example applications which I suppose you downloaded to your Hard disc, make use of Both fields: Country and City.
The 3 applications are: Portal App, RegForm App., NewsWap App..
But unfortunately the designer of these examples evades coping with this issue.
Only in one of them, the NewsWap app. its database has design so the Country_id is a foreign key in the Cities Table and this database is appropriate for demonstrating the “Depending Listbox” case.
The Existing example of dependent list boxes written by Alexey Alexapolsky has some problems.
1. This example shows how to perform this task in a “One table” source (categories) and this is not the common use of “Dependent list boxes” which generally uses two tables.
2. A “Tree” form with multilevel entries is not typical case of “Dependent Listboxes” which has usually 2 levels.
3. There is a bug in the example. When selecting parameters and press the “search” button” you are stuck with first parameter (top level) and in order to perform another search you have to alter the first parameter twice in order to go on with another select.
4. As a matter of fact I am not capable of using the existing example for doing the appropriate line modifications in the custom code to have a new “Two table for Two List boxes” page. And I believe many others who tried couldn’t do it.
I turned to Support for some help and I even offer to charge me for doing that piece of programming job, but the answer was:
“Writing custom code is entirely your responsibility and we don't do this for any of our clients”
as well “we don't write custom code for people payment”.
I tried to convince the support team that this is a “study case” and it will be a good Idea to publish a new improved example of ”dependent ListBox” or do it in one of the existing examples, with specific remarks on the lines where modifications should be done.
Not only me but all of us will gain.
I hope you admit that this issue deserves its own type of “Internal Form” in the future release of CC. But until we see this feature in the future version (if any), I wonder if one of you, experts, is ready to help me or us with a sample of code (ASP, CC2).
As I mentioned the example Application of NewsWap with its database is suitable for demonstrating this issue. The “Weather” Table has a foreign key “city_id” which we select from “”Countries” and “Cities”
My email is: webmaster@forum-horim.com and thanks in advance for your help.
I. Pinchas
|
|
|
 |
aarl
|
| Posted: 10/17/2002, 7:08 AM |
|
without getting into custom code, the best way to handle this is by creating many forms on one page. Each form represents a list box and would be a 'search' form type. So, selecting Level 1 then search would pass the parameter from level 1 to the next search form on the same page and selecting level 2 in the list based on parameter for level ...you could go on and on...
It is a pain, but the trade off is getting around 'custom' coding....
I have seen good examples of custom dependent lists, here is a vb/asp/sql one:
<%@ Language=VBScript %>
<% Option Explicit %>
<%
Call Main()
Sub Main()
' If the form is submitted, just display the selected country and state
If Request.Form("cmdSubmit") <> "" Then
Response.Write "<B> Geographical Region = " & Request.Form("lstGeoRegion") & _
" Country = " & Request.Form("lstCountry") & " State = " & Request.Form("lstState") & "</B>"
Exit Sub
End If
Dim objConnection ' ADO Connection object
Dim strSQL ' SQL query to be executed
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.ConnectionString = "DSN=Hierarchy;UID=sa;PWD=;"
objConnection.Open
Dim rsGeoRegion ' recordset that holds the Geographical Region Information
Dim rsCountry ' recordset that holds the Country Information
Dim rsState ' recordset that holds the State Information
Dim strGeoRegion ' holds the Geographical Region ID
Dim strCountry ' holds the Country ID
Dim strState ' holds the State ID
Set rsGeoRegion = Server.CreateObject("ADODB.Recordset")
Set rsCountry = Server.CreateObject("ADODB.Recordset")
Set rsState = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM GEO_REGION"
Set rsGeoRegion = objConnection.Execute(strSQL)
' Check if theres a Geographical Region selected by the user
' If not, just take the first Geographical Region as the
' filter for the Country List
strGeoRegion = Request.Form("lstGeoRegion")
If strGeoRegion = "" Then
If Not rsGeoRegion.EOF Then
strGeoRegion = rsGeoRegion("GEO_REGION_ID")
End If
End If
If strGeoRegion <> "" Then
strSQL = "SELECT * FROM COUNTRY WHERE GEO_REGION_ID = '" & strGeoRegion & "'"
Set rsCountry = objConnection.Execute(strSQL)
strCountry = Request.Form("lstCountry")
If strCountry = "" Or Request.Form("hid_GeoRegion_Changed") = "True" Then
If Not rsCountry.EOF Then
strCountry = rsCountry("COUNTRY_ID")
End If
End If
strSQL = "SELECT * FROM STATE WHERE COUNTRY_ID='" & strCountry & "'" & _
" AND GEO_REGION_ID = '" & strGeoRegion & "'"
Set rsState = objConnection.Execute(strSQL)
End If
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<CENTER>
<FORM NAME=frmHierarchy METHOD=post ACTION="Hierarchy.asp">
<INPUT TYPE=HIDDEN NAME=hid_GeoRegion_Changed>
<INPUT TYPE=HIDDEN NAME=hid_Country_Changed>
<P><H2>Hierarchial Selection of Items</H2></P>
<TABLE CELLSPACING=5 CELLPADDING=5 BORDER=0 ALIGN="CENTER">
<TR>
<TD>Geographical Region: </TD>
<TD><SELECT id=lstGeoRegion name=lstGeoRegion onchange="ChangeGeoRegion()">
<%
' Add the geographical regions to the list
If Not rsGeoRegion.EOF Then
Do While Not rsGeoRegion.EOF
strGeoRegion = rsGeoRegion("GEO_REGION_ID") & " - " & rsGeoRegion("GEO_REGION")
If rsGeoRegion("GEO_REGION_ID") = Request.Form("lstGeoRegion") Then %>
<OPTION VALUE="<%=rsGeoRegion("GEO_REGION_ID")%>" SELECTED> <%=strGeoRegion%></OPTION>
<%
Else
%>
<OPTION VALUE="<%=rsGeoRegion("GEO_REGION_ID")%>" > <%=strGeoRegion%></OPTION>
<%
End If
rsGeoRegion.MoveNext
Loop
End If
'Reset the record pointer to the first record
rsGeoRegion.MoveFirst
%>
</SELECT></TD>
</TR>
<TR>
<TD>Country: </TD>
<TD><SELECT id=lstCountry name=lstCountry onchange="ChangeCountry()" >
<%
' Add the countries to the list
If Not rsCountry.EOF Then
Do While Not rsCountry.EOF
strCountry = rsCountry("COUNTRY_ID") & " - " & rsCountry("COUNTRY")
If rsCountry("COUNTRY_ID") = Request.Form("lstCountry") Then
%>
<OPTION Value="<%=rsCountry("COUNTRY_ID")%>" SELECTED> <%=strCountry%></OPTION>
<%
Else
%>
<OPTION Value="<%=rsCountry("COUNTRY_ID")%>" > <%=strCountry%></OPTION>
<%
End If
rsCountry.MoveNext
Loop
End If
'Reset the record pointer to the first record
rsCountry.MoveFirst
%>
</SELECT></TD>
</TR>
<TR>
<TD>State: </TD>
<TD><SELECT id=lstState name=lstState>
<%
' Add the States to the list
If Not rsState.EOF Then
Do While Not rsState.EOF
strState = rsState("STATE_ID") & " - " & rsState("STATE")%>
<OPTION Value="<%=rsState("STATE_ID")%>" > <%=strState%></OPTION>
<%
rsState.MoveNext
Loop
End If
%>
</SELECT></TD>
</TR>
</TABLE>
<P><INPUT id=cmdSubmit name=cmdSubmit type=submit value=Submit></P>
<P> </P></FORM>
</FORM>
</CENTER>
</BODY>
<SCRIPT LANGUAGE="JavaScript">
function ChangeGeoRegion()
{
document.frmHierarchy.hid_GeoRegion_Changed.value = "True";
document.frmHierarchy.submit();
}
function ChangeCountry()
{
document.frmHierarchy.hid_Country_Changed.value = "True";
document.frmHierarchy.submit();
}
</SCRIPT>
</HTML>
<%
' Release the connection and recordsets
Set objConnection = Nothing
Set rsCountry = Nothing
Set rsState = Nothing
End Sub
%>
and the tables to build:
if exists (select * from sysobjects where id = object_id(N'[dbo].[STATE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[STATE]
GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[COUNTRY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[COUNTRY]
GO
if exists (select * from sysobjects where id = object_id(N'[dbo].[GEO_REGION]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GEO_REGION]
GO
CREATE TABLE [dbo].[GEO_REGION] (
[GEO_REGION_ID] [varchar] (10) NOT NULL ,
[GEO_REGION] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[COUNTRY] (
[COUNTRY_ID] [varchar] (10) NOT NULL ,
[GEO_REGION_ID] [varchar] (10) NOT NULL ,
[COUNTRY] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[STATE] (
[STATE_ID] [varchar] (10) NOT NULL ,
[COUNTRY_ID] [varchar] (10) NOT NULL ,
[GEO_REGION_ID] [varchar] (10) NOT NULL ,
[STATE] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[GEO_REGION] WITH NOCHECK ADD
CONSTRAINT [PK_GEO_REGION] PRIMARY KEY NONCLUSTERED
(
[GEO_REGION_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[COUNTRY] WITH NOCHECK ADD
CONSTRAINT [PK_COUNTRY] PRIMARY KEY NONCLUSTERED
(
[COUNTRY_ID],
[GEO_REGION_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[STATE] WITH NOCHECK ADD
CONSTRAINT [PK_STATE] PRIMARY KEY NONCLUSTERED
(
[STATE_ID],
[COUNTRY_ID],
[GEO_REGION_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[COUNTRY] ADD
CONSTRAINT [FK_COUNTRY_GEO_REGION] FOREIGN KEY
(
[GEO_REGION_ID]
) REFERENCES [dbo].[GEO_REGION] (
[GEO_REGION_ID]
)
GO
ALTER TABLE [dbo].[STATE] ADD
CONSTRAINT [FK_STATE_COUNTRY] FOREIGN KEY
(
[COUNTRY_ID],
[GEO_REGION_ID]
) REFERENCES [dbo].[COUNTRY] (
[COUNTRY_ID],
[GEO_REGION_ID]
)
GO
/* Insert values into the tables */
PRINT 'Inserting values in to GEO_REGION'
INSERT INTO GEO_REGION VALUES('AS', 'ASIA')
INSERT INTO GEO_REGION VALUES('NA', 'NORTH AMERICA')
GO
PRINT 'Inserted values in to GEO_REGION'
PRINT 'Inserting values in to COUNTRY'
INSERT INTO COUNTRY VALUES('US', 'NA', 'UNITED STATES')
INSERT INTO COUNTRY VALUES('CA', 'NA', 'CANADA')
INSERT INTO COUNTRY VALUES('IN', 'AS', 'INDIA')
INSERT INTO COUNTRY VALUES('PK', 'AS', 'PAKISTAN')
GO
PRINT 'Inserted values in to COUNTRY'
PRINT 'Inserting values in to STATE'
INSERT INTO STATE VALUES('AK', 'US', 'NA', 'ALASKA')
INSERT INTO STATE VALUES('AL', 'US', 'NA', 'ALABAMA')
INSERT INTO STATE VALUES('AR', 'US', 'NA', 'ARKANSAS')
INSERT INTO STATE VALUES('AZ', 'US', 'NA', 'ARIZONA')
INSERT INTO STATE VALUES('BC', 'US', 'NA', 'BRITISH COLUMBIA')
INSERT INTO STATE VALUES('AB', 'CA', 'NA', 'ALBERTA')
INSERT INTO STATE VALUES('MB', 'CA', 'NA', 'MANITOBA')
INSERT INTO STATE VALUES('TN', 'IN', 'AS', 'TAMIL NADU')
INSERT INTO STATE VALUES('AP', 'IN', 'AS', 'ANDHRA PRADESH')
INSERT INTO STATE VALUES('PB', 'PK', 'AS', 'PUNJAB')
INSERT INTO STATE VALUES('SI', 'PK', 'AS', 'SINDH')
GO
PRINT 'Inserted values in to STATE'
|
|
|
|
Dunkie
|
| Posted: 10/17/2002, 7:47 AM |
|
Hi Pinchas!
I couldn't put it more plainly than you've put in your mail! I've requested for this country-city setup and none has been forthcoming. If anyone comes across a .ccs for this kindly mail it todunkie@dunkie.net, I'll most certainly appreciate and include your name in my apps!!!
Lastly, any chance that someone out there has the .ccs for deleting several records at once using checkboxes in a grid?
Thnx in adv!
Dunkie
|
|
|
|
keijen
|
| Posted: 10/17/2002, 2:24 PM |
|
Since the beginning of CC2.0 thru to CCS1.07, this is a constant request.
I've given up on hoping it will ever arrive.
For CC2 the code I use, and it needs only your SQL statements and changing a few field name references, is on the tips site at
http://www.gotocode.com/art.asp?art_id=90&
Wish that author, or one smarter than me, could do similar for CCS.
|
|
|
|
|
