CFF Coding Source - Populate triple drop down list from database using Ajax and Classic ASP

Live Editing Disabled for Server-Side Example

HTML

<%
' Country SQL Select statement and codes for the Form and AJAX
Set sqlCountry = CreateObject("ADODB.Command")
sqlCountry.ActiveConnection=objConn
sqlCountry.Prepared = true
sqlCountry.commandtext = "SELECT CountryID, Country FROM Country"
set rsCountry = sqlCountry.execute
%>
<html>
<head>
<title>Triple Ajax dropdown code in Classic ASP</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="javascript" type="text/javascript" src="Load.js"></script>
<link href="Load.css" rel="stylesheet" type="text/css">
</head>
<body>
<form method="post" action="" name="form1">
<div>
  <ul class="columns">
  
  
    <li>Country</li>
    <li>State</li>
    <li>City</li>
      
    <li  width="150">
    <select name="country" onChange="getState(this.value)">
	<option value="">Select Country</option>
    <%while not rsCountry.eof%>
	<option value="<%=rsCountry("CountryID")%>"><%=rsCountry("Country")%></option>
	<%rsCountry.movenext
	wend
	rsCountry.close
	set rsCountry = nothing
	objConn.close%>
    </select></li>

<li><div id="statediv">
      <select name="state" >
	  <option>Select Country First</option>
      </select>
        </div></li>  
    
    <li><div id="citydiv">
    <select name="city">
	<option>Select State First</option>
    </select>
    </div></li>
  </ul>
</form>
</body>
</html>

CSS

JavaScript

//Code originally by: Roshan's
// ASP Classic links added in by: Wayne Barron of Carrz-Fox-Fire Promotions 8:13pm 9/9/2011
function getXMLHTTP() { //fuction to return the xml http object
        var xmlhttp=false;    
        try{
            xmlhttp=new XMLHttpRequest();
        }
        catch(e)    {        
            try{            
                xmlhttp= new ActiveXObject("Microsoft.XMLHTTP");
            }
            catch(e){
                try{
                xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
                }
                catch(e1){
                    xmlhttp=false;
                }
            }
        }
             
        return xmlhttp;
    }
    
    function getState(countryId) {        
        
        var strURL="findState.asp?country="+countryId;
        var req = getXMLHTTP();
        
        if (req) {
            
            req.onreadystatechange = function() {
                if (req.readyState == 4) {
                    // only if "OK"
                    if (req.status == 200) {                        
                        document.getElementById('statediv').innerHTML=req.responseText;                        
                    } else {
                        alert("There was a problem while using XMLHTTP:\n" + req.statusText);
                    }
                }                
            }            
            req.open("GET", strURL, true);
            req.send(null);
        }        
    }
    function getCity(countryId,stateId) {        
        var strURL="findCity.asp?country="+countryId+"&state="+stateId;
        var req = getXMLHTTP();
        
        if (req) {
            
            req.onreadystatechange = function() {
                if (req.readyState == 4) {
                    // only if "OK"
                    if (req.status == 200) {                        
                        document.getElementById('citydiv').innerHTML=req.responseText;                        
                    } else {
                        alert("There was a problem while using XMLHTTP:\n" + req.statusText);
                    }
                }                
            }            
            req.open("GET", strURL, true);
            req.send(null);
        }
                
    }

SQL


USE [Virtual-Class-01]
GO
/****** Object:  Table [dbo].[City]    Script Date: 10/31/2022 9:12:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[City](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [StateID] [int] NOT NULL,
    [CountryID] [int] NOT NULL,
    [City] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Country]    Script Date: 10/31/2022 9:12:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Country](
    [CountryID] [int] IDENTITY(1,1) NOT NULL,
    [Country] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED 
(
    [CountryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[State]    Script Date: 10/31/2022 9:12:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[State](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CountryID] [int] NOT NULL,
    [Statename] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[City] ON 
GO
INSERT [dbo].[City] ([ID], [StateID], [CountryID], [City]) VALUES (1, 2, 1, N'Los Angeles')
GO
INSERT [dbo].[City] ([ID], [StateID], [CountryID], [City]) VALUES (2, 1, 1, N'New York New York')
GO
INSERT [dbo].[City] ([ID], [StateID], [CountryID], [City]) VALUES (3, 4, 2, N'Toranto')
GO
INSERT [dbo].[City] ([ID], [StateID], [CountryID], [City]) VALUES (4, 3, 2, N'Vancovour')
GO
INSERT [dbo].[City] ([ID], [StateID], [CountryID], [City]) VALUES (5, 5, 1, N'Sanford')
GO
INSERT [dbo].[City] ([ID], [StateID], [CountryID], [City]) VALUES (6, 5, 1, N'New Bern')
GO
INSERT [dbo].[City] ([ID], [StateID], [CountryID], [City]) VALUES (8, 5, 1, N'Moncure')
GO
SET IDENTITY_INSERT [dbo].[City] OFF
GO
SET IDENTITY_INSERT [dbo].[Country] ON 
GO
INSERT [dbo].[Country] ([CountryID], [Country]) VALUES (1, N'USA')
GO
INSERT [dbo].[Country] ([CountryID], [Country]) VALUES (2, N'Canada')
GO
SET IDENTITY_INSERT [dbo].[Country] OFF
GO
SET IDENTITY_INSERT [dbo].[State] ON 
GO
INSERT [dbo].[State] ([ID], [CountryID], [Statename]) VALUES (1, 1, N'New York')
GO
INSERT [dbo].[State] ([ID], [CountryID], [Statename]) VALUES (2, 1, N'California')
GO
INSERT [dbo].[State] ([ID], [CountryID], [Statename]) VALUES (3, 2, N'British Columbia')
GO
INSERT [dbo].[State] ([ID], [CountryID], [Statename]) VALUES (4, 2, N'Toranto')
GO
INSERT [dbo].[State] ([ID], [CountryID], [Statename]) VALUES (5, 1, N'North Carolina')
GO
SET IDENTITY_INSERT [dbo].[State] OFF
GO

Classic ASP

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="ADOVBS.inc"-->
<%
siteurl = "192.168.2.12/cs"
getServer = "sqlcorecs-01"
getInstance = "sql2019"
getID = "testuser"
getPW = "testuser"

Set objConn = CreateObject("ADODB.Connection")
objConn.Open("Provider=SQLOLEDB; Data Source="&getServer&"\"&getInstance&"; Initial Catalog=Virtual-Class-01;User ID="&getID&";Password="&getPW&";")

' to prevent SQL and XSS Injection against your database and your site
' updated: January 7th, 2010
Function ProtectSQL(SQLString)
SQLString = Replace(SQLString, "'", "''") ' replace single Quotes with Double Quotes
SQLString = Replace(SQLString, ">", ">") ' replace > with >
SQLString = Replace(SQLString, "<", "<") ' replace < with <
SQLString = Replace(SQLString, "(","(") ' replace ( with (
SQLString = Replace(SQLString, ")",")") ' replace ) with )
SQLString = Trim(SQLString)
ProtectSQL = SQLString
End Function
%>

<%
'--------------------------------------------------------------------
' Microsoft ADO
'
' (c) 1996 Microsoft Corporation.  All Rights Reserved.
'
'
'
' ADO constants include file for VBScript
'
'--------------------------------------------------------------------

'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3

'---- CursorOptionEnum Values ----
Const adHoldRecords = &H00000100
Const adMovePrevious = &H00000200
Const adAddNew = &H01000400
Const adDelete = &H01000800
Const adUpdate = &H01008000
Const adBookmark = &H00002000
Const adApproxPosition = &H00004000
Const adUpdateBatch = &H00010000
Const adResync = &H00020000
Const adNotify = &H00040000

'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4

'---- ExecuteOptionEnum Values ----
Const adRunAsync = &H00000010

'---- ObjectStateEnum Values ----
Const adStateClosed = &H00000000
Const adStateOpen = &H00000001
Const adStateConnecting = &H00000002
Const adStateExecuting = &H00000004

'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3

'---- DataTypeEnum Values ----
Const adEmpty = 0
Const adTinyInt = 16
Const adSmallInt = 2
Const adInteger = 3
Const adBigInt = 20
Const adUnsignedTinyInt = 17
Const adUnsignedSmallInt = 18
Const adUnsignedInt = 19
Const adUnsignedBigInt = 21
Const adSingle = 4
Const adDouble = 5
Const adCurrency = 6
Const adDecimal = 14
Const adNumeric = 131
Const adBoolean = 11
Const adError = 10
Const adUserDefined = 132
Const adVariant = 12
Const adIDispatch = 9
Const adIUnknown = 13
Const adGUID = 72
Const adDate = 7
Const adDBDate = 133
Const adDBTime = 134
Const adDBTimeStamp = 135
Const adBSTR = 8
Const adChar = 129
Const adVarChar = 200
Const adLongVarChar = 201
Const adWChar = 130
Const adVarWChar = 202
Const adLongVarWChar = 203
Const adBinary = 128
Const adVarBinary = 204
Const adLongVarBinary = 205

'---- FieldAttributeEnum Values ----
Const adFldMayDefer = &H00000002
Const adFldUpdatable = &H00000004
Const adFldUnknownUpdatable = &H00000008
Const adFldFixed = &H00000010
Const adFldIsNullable = &H00000020
Const adFldMayBeNull = &H00000040
Const adFldLong = &H00000080
Const adFldRowID = &H00000100
Const adFldRowVersion = &H00000200
Const adFldCacheDeferred = &H00001000

'---- EditModeEnum Values ----
Const adEditNone = &H0000
Const adEditInProgress = &H0001
Const adEditAdd = &H0002
Const adEditDelete = &H0004

'---- RecordStatusEnum Values ----
Const adRecOK = &H0000000
Const adRecNew = &H0000001
Const adRecModified = &H0000002
Const adRecDeleted = &H0000004
Const adRecUnmodified = &H0000008
Const adRecInvalid = &H0000010
Const adRecMultipleChanges = &H0000040
Const adRecPendingChanges = &H0000080
Const adRecCanceled = &H0000100
Const adRecCantRelease = &H0000400
Const adRecConcurrencyViolation = &H0000800
Const adRecIntegrityViolation = &H0001000
Const adRecMaxChangesExceeded = &H0002000
Const adRecObjectOpen = &H0004000
Const adRecOutOfMemory = &H0008000
Const adRecPermissionDenied = &H0010000
Const adRecSchemaViolation = &H0020000
Const adRecDBDeleted = &H0040000

'---- GetRowsOptionEnum Values ----
Const adGetRowsRest = -1

'---- PositionEnum Values ----
Const adPosUnknown = -1
Const adPosBOF = -2
Const adPosEOF = -3

'---- enum Values ----
Const adBookmarkCurrent = 0
Const adBookmarkFirst = 1
Const adBookmarkLast = 2

'---- MarshalOptionsEnum Values ----
Const adMarshalAll = 0
Const adMarshalModifiedOnly = 1

'---- AffectEnum Values ----
Const adAffectCurrent = 1
Const adAffectGroup = 2
Const adAffectAll = 3

'---- FilterGroupEnum Values ----
Const adFilterNone = 0
Const adFilterPendingRecords = 1
Const adFilterAffectedRecords = 2
Const adFilterFetchedRecords = 3
Const adFilterPredicate = 4

'---- SearchDirection Values ----
Const adSearchForward = 1
Const adSearchBackward = -1

'---- ConnectPromptEnum Values ----
Const adPromptAlways = 1
Const adPromptComplete = 2
Const adPromptCompleteRequired = 3
Const adPromptNever = 4

'---- ConnectModeEnum Values ----
Const adModeUnknown = 0
Const adModeRead = 1
Const adModeWrite = 2
Const adModeReadWrite = 3
Const adModeShareDenyRead = 4
Const adModeShareDenyWrite = 8
Const adModeShareExclusive = &Hc
Const adModeShareDenyNone = &H10

'---- IsolationLevelEnum Values ----
Const adXactUnspecified = &Hffffffff
Const adXactChaos = &H00000010
Const adXactReadUncommitted = &H00000100
Const adXactBrowse = &H00000100
Const adXactCursorStability = &H00001000
Const adXactReadCommitted = &H00001000
Const adXactRepeatableRead = &H00010000
Const adXactSerializable = &H00100000
Const adXactIsolated = &H00100000

'---- XactAttributeEnum Values ----
Const adXactCommitRetaining = &H00020000
Const adXactAbortRetaining = &H00040000

'---- PropertyAttributesEnum Values ----
Const adPropNotSupported = &H0000
Const adPropRequired = &H0001
Const adPropOptional = &H0002
Const adPropRead = &H0200
Const adPropWrite = &H0400

'---- ErrorValueEnum Values ----
Const adErrInvalidArgument = &Hbb9
Const adErrNoCurrentRecord = &Hbcd
Const adErrIllegalOperation = &Hc93
Const adErrInTransaction = &Hcae
Const adErrFeatureNotAvailable = &Hcb3
Const adErrItemNotFound = &Hcc1
Const adErrObjectInCollection = &Hd27
Const adErrObjectNotSet = &Hd5c
Const adErrDataConversion = &Hd5d
Const adErrObjectClosed = &He78
Const adErrObjectOpen = &He79
Const adErrProviderNotFound = &He7a
Const adErrBoundToCommand = &He7b
Const adErrInvalidParamInfo = &He7c
Const adErrInvalidConnection = &He7d
Const adErrStillExecuting = &He7f
Const adErrStillConnecting = &He81

'---- ParameterAttributesEnum Values ----
Const adParamSigned = &H0010
Const adParamNullable = &H0040
Const adParamLong = &H0080

'---- ParameterDirectionEnum Values ----
Const adParamUnknown = &H0000
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003
Const adParamReturnValue = &H0004

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004

'---- SchemaEnum Values ----
Const adSchemaProviderSpecific = -1
Const adSchemaAsserts = 0
Const adSchemaCatalogs = 1
Const adSchemaCharacterSets = 2
Const adSchemaCollations = 3
Const adSchemaColumns = 4
Const adSchemaCheckConstraints = 5
Const adSchemaConstraintColumnUsage = 6
Const adSchemaConstraintTableUsage = 7
Const adSchemaKeyColumnUsage = 8
Const adSchemaReferentialContraints = 9
Const adSchemaTableConstraints = 10
Const adSchemaColumnsDomainUsage = 11
Const adSchemaIndexes = 12
Const adSchemaColumnPrivileges = 13
Const adSchemaTablePrivileges = 14
Const adSchemaUsagePrivileges = 15
Const adSchemaProcedures = 16
Const adSchemaSchemata = 17
Const adSchemaSQLLanguages = 18
Const adSchemaStatistics = 19
Const adSchemaTables = 20
Const adSchemaTranslations = 21
Const adSchemaProviderTypes = 22
Const adSchemaViews = 23
Const adSchemaViewColumnUsage = 24
Const adSchemaViewTableUsage = 25
Const adSchemaProcedureParameters = 26
Const adSchemaForeignKeys = 27
Const adSchemaPrimaryKeys = 28
Const adSchemaProcedureColumns = 29
%>

<!--#include file="ACN.asp"-->
<%
' City SQL Select Statement and Drop box.
countryId = int(request("country"))
stateId = int(request("state"))

Set sqlCity = CreateObject("ADODB.Command")
sqlCity.ActiveConnection=objConn
sqlCity.Prepared = true
sqlCity.commandtext = "SELECT id,city FROM city WHERE stateid=?"
'sqlCity.Parameters.Append sqlCity.CreateParameter("@countryid", 3, 1, , countryId)
sqlCity.Parameters.Append sqlCity.CreateParameter("@stateid", 3, 1, , stateId)
set rsCity = sqlCity.execute
%>
<select name="city">
<option>Select City</option>
<% while not rsCity.eof%>
<option value="<%=rsCity("id")%>"><%=rsCity("city")%></option>
<%rsCity.movenext
wend
rsCity.close
set rsCity = nothing
objConn.close%>
</select>

<!--#include file="ACN.asp"-->
<%
' State SQL Select Statement and Drop box.
country = int(request("country"))

Set sqlStates = CreateObject("ADODB.Command")
sqlStates.ActiveConnection=objConn
sqlStates.Prepared = true
sqlStates.commandtext = "SELECT id,statename FROM state WHERE countryid=?"
sqlStates.Parameters.Append sqlStates.CreateParameter("@countryid", 3, 1, , country)
set rsStates = sqlStates.execute
%>
<select name="state" onchange="getCity(<%=country%>,this.value)">
<option>Select State</option>
<%while not rsStates.eof%>
<option value="<%=rsStates("id")%>"><%=rsStates("statename")%></option>
<%rsStates.movenext
wend
rsStates.close
set rsStates = nothing
objConn.close%>
</select>

Preview

About Company

Top Viewed Articles