CFF Coding Source - Classic ASP Fill select menu with records from a database, insert selected records with live updated data

Live Editing Disabled for Server-Side Example

HTML

<html lang="en">
  <head>
    <meta charset="UTF-8">
<title>Classic ASP Database Driven Select Menu (Dropdown Menu)</title>
</head>

<body>
<%if getMyID="" then%>
<a href="?MyID=1">Load ID</a> to proceed
<%elseif getMyID<>"" then%>
Choose an option.<br />
This demonstration shows how to load a Select Menu.<br />
Once you select an Item, click on the [Submit It] button.<br />
The record will be inserted into the database and loaded in the div at the bottom.<br />
If a record exists you will be warned of it existing.<br />
<form name="UpdateForm" id="target">
<input type="hidden" name="MyID" value="<%=getMyID%>" />
<select name="MyNum">
<%if getID<>"" then
if not rsDDs.eof then
ids = rsDDs("ddid")
ddNames = rsDDs("ddName")%>
<option value="<%=ids%>"><%=ddnames%></option>
<%end if
end if%>
<option value="0">Choose</option>
<%if not rsDD.eof then
while not rsDD.eof
id = rsDD("ddid")
ddName = rsDD("ddName")%>
<option value="<%=id%>"><%=ddName%></option>
<%rsDD.movenext
wend
end if
rsDD.close
set rsDD = nothing
%>
</select>
<input type="submit" name="Submit" value="Submit It" />
</form><div class="postData">Update</div><hr style="width:250px;" />
<%end if%>
<div id="postDataL">Data Loaded Here</div>
<%
objConn.close
set objConn = Nothing
%>
<script src="https://www.cffcs.com/Assets/jquery-3.6.0.js"></script>
<script type="text/javascript" src="ajaxsbmt.js"></script>
<script type="text/javascript">
// We need the delay to get the database to load to the page after inserting the record.
$(this).delay(1000).queue(function() {
$( "#target" ).submit(function( event ) {
$( "#postDataL" ).load( "Update.asp?MyID=<%=getMyID%>" );
  event.preventDefault();
});
  $(this).dequeue();
});
</script>
</body>
</html>

JavaScript

$(document).ready(function(){
    $('form').change(function(e){
        e.preventDefault();
        $.ajax({
            url: "Update.ashx",
            type: "POST",
            data: $(this).serialize(),
            success: function(data){
                $(".postData").html(data);
            },
            error: function(){
                $(".postData").html("Form submission failed!");
            }
        });

    });
});

SQL

Classic ASP

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
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&";")
%>

' This function will help to protect your Database from SQL and XSS Injections.
' Use this to protect all entries that are being submitted to your database.
Function ProtectXSS(SQLString)
SQLString = SQLString
SQLString = Replace(SQLString, ">", ">") ' replace > with >
SQLString = Replace(SQLString, "<", "<") ' replace < with <
SQLString = Replace(SQLString, "(","(") ' replace ( with (
SQLString = Replace(SQLString, ")",")") ' replace ) with )
SQLString = Replace(SQLString, "&", "&")
SQLString = Replace(SQLString, "@@", "")
SQLString = Replace(SQLString, "/", "/")
SQLString = Replace(SQLString, vblf,"<br />") ' replace vblf with <br /> (This is mainly used for Memo fields. 
SQLString = Replace(SQLString, "%", "%")
SQLString = Trim(SQLString)
ProtectXSS = 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"-->
<%
getMyID = ProtectSQL(Request.QueryString("MyID"))
' Create the Command to start your SQL and then your RecordSet (rsDD)
Set sqlDD = Server.CreateObject("ADODB.Command")
sqlDD.ActiveConnection=objConn
sqlDD.Prepared = true
sqlDD.commandtext="SELECT ddid, ddname from Dropdown"
set rsDD = sqlDD.execute

getID = ProtectSQL(Request.QueryString("id"))
if getID<>"" then
Set sqlDD = Server.CreateObject("ADODB.Command")
sqlDD.ActiveConnection=objConn
sqlDD.Prepared = true
sqlDD.commandtext="SELECT ddid, ddname from Dropdown where ddid=?"
sqlDD.Parameters.Append sqlDD.CreateParameter("@ddid", adInteger, adParamInput, , getID)
set rsDDs = sqlDD.execute
end if
%>

<!--#include file="ACN.asp"-->
<%
getID = ProtectSQL(Request("MyNum"))
getMyID = ProtectSQL(Request("MyID"))
'response.Write getID&"-"&getMyID
'response.End()
if getID="0" then
response.Write "Please choose an option"
else
Set sqlDD = Server.CreateObject("ADODB.Command")
sqlDD.ActiveConnection=objConn
sqlDD.Prepared = true
sqlDD.commandtext="SELECT DropDown.ddID, Dropdown2.MyID FROM DropDown INNER JOIN Dropdown2 ON DropDown.ddID = Dropdown2.ddid WHERE DropDown.ddID = ? AND Dropdown2.MyID = ?"
sqlDD.Parameters.Append sqlDD.CreateParameter("@ddid", adInteger, adParamInput, , getID)
sqlDD.Parameters.Append sqlDD.CreateParameter("@Myid", adInteger, adParamInput, , getMyID)
set rsDD = sqlDD.execute
if not rsDD.eof then
response.Write "Record already added"
else

Set sqlDD = Server.CreateObject("ADODB.Command")
sqlDD.ActiveConnection=objConn
sqlDD.commandtext="insert into Dropdown2 (ddid, MyID)values(?,?)"
sqlDD.Parameters.Append sqlDD.CreateParameter("@ddid", adInteger, adParamInput, , getID)
sqlDD.Parameters.Append sqlDD.CreateParameter("@Myid", adInteger, adParamInput, , getMyID)
sqlDD.execute
' Send the Visitor back to the page
response.Write "Recorded Inserted Successfully!"
end if

rsDD.close
set rsDD = nothing
objConn.close
set objConn = nothing
end if
%>

<!--#include file="ACN.asp"-->
<%
getMyID = request("MyID")
Set sqlDDL = Server.CreateObject("ADODB.Command")
sqlDDL.ActiveConnection=objConn
sqlDDL.Prepared = true
sqlDDL.commandtext="SELECT DropDown.ddID, DropDown.ddName, Dropdown2.MyID FROM DropDown INNER JOIN Dropdown2 ON DropDown.ddID = Dropdown2.ddid WHERE Dropdown2.MyID = ?"
sqlDDL.Parameters.Append sqlDDL.CreateParameter("@Myid", adInteger, adParamInput, , getMyID)
set rsDDL = sqlDDL.execute

if rsDDL.eof then
response.Write "No records available at this time."
else
while not rsDDL.eof
ID = rsDDL("ddID")
theName = rsDDL("ddName")
%>
<%=ID%> - <%=theName%><br /><%
rsDDL.movenext
wend
rsDDL.close
set rsDDL = nothing
end if%>

Preview

About Company

Top Viewed Articles