In an effort to help out those individuals wanting to select a value within a combo box, I am providing the following code. Feel free to use this code in many of your applications. I require not mention of my name unless you feel obligated. After reviewing these first 3 functions, see below for my supporting functions that assist in the main purpose of this FAQ.
NOTE: that some code may be wrapped due to my indenting. These lines of code should be on the same line if you don't want to run into any errors.
Let's move to the first function, which builds the combo box with list of values from database and selects the appropriate option. This is based on the valToMatch value passed into this function.
The input parameters are: rsSelect - Recordset object that will populate the combo box optName - Name to give to combo box valToMatch - The value to match up with the recordset's optValue
Function BuildComboBox(rsSelect,optName,valToMatch) Dim strList
'NOTE: The recordset option value must represented by the first item => rsSelect(0).Value, ' whereas the option text must be represented by the second item => rsSelect(1).Value
' build up the combo box list. strList = "<SELECT NAME=" & DblQt(optName) & ">" & vbCRLF With rsSelect .MoveFirst do while not .EOF strList = strList & "<OPTION VALUE=" & DblQt(rsSelect(0)) & _ setComboOption(rsSelect(0),valToMatch,1) & ">" & rsSelect(1) & "</OPTION>" & vbCRLF .MoveNext loop End With strList = strList & "</SELECT>" & vbCRLF
' return the combo box list to the caller BuildComboBox = strList
The second function sets the appropriate value depending on the type of control is calling this control.
The input parameters are: optValue - This is the value from the lookup table DeltaValue - This is the value that we should select the option value on. OptionRadio - 0 or 1 to determine if it is a ComboBox or Checkbox control
Function setComboOption (optValue,DeltaValue,OptionRadio) ' if there is a match then select/check it if optValue = DeltaValue Then Select Case OptionRadio Case 1 ' select setComboOption = " selected " Case 2 ' checkbox setComboOption = " checked " End Select End if End Function
Now that we have these functions created, we can go ahead and call them from our application. I like to encapsulate my different combo box routines within one more function to keep things clean, since I could have many different combo boxes on one form. The following function stores my logic to retrieve all of my clients and their Id values specified in the database.
This third function builds the combo box with list of values from database and selects the appropriate client name based on the DeltaValue passed into this function. (i.e. This value can be a form or querystring value that has been passed from another page)
The input parameter is: DeltaValue - option value to select in the combo box
' open recordset to get the clientId and client name Call openRS(rs) rs.Open "SELECT ClientID, ClientName FROM tblClient ORDER BY ClientName ASC", GetConnection
' create the combo box by passing recordset object, name of combo box, ' option value, option text, and the value to match up with combo = BuildComboBox(rs,"selClientList",DeltaValue)
' free up the recordset Call CloseRS(rs)
ClientList = combo
USAGE EXAMPLE: On our main ASP page, we will want to call the function called ClientList like such:
SUPPORTING FUNCTIONS BELOW (USED IN THE ABOVE FUNCTIONS): Below are a list of supporting functions for this Combo box select FAQ. Naturally, of course, these supporting functions can be used for other purposes too. Go figure!!!
' PURPOSE: Used for wrapping double quotes around a string ' INPUTS: str - the string to wrap double quotes around Function DblQt(str) DblQt = chr(34) & str & chr(34) End Function
' PURPOSE: Opens the connection and attaches to the connection object Function GetConnection() OpenDB() GetConnection = dbConn End Function
' PURPOSE: Opens a database connection and sets connection object parameters based on application ' object variables set in the global.asa file ' NAMING CONVENTION: AppName_ConnectionObjectName_ConnectionObjectPropertyName Function OpenDB() Set dbConn = Server.CreateObject("ADODB.Connection") dbConn.ConnectionTimeout = Application("gc_dbConn_ConnectionTimeout") dbConn.CommandTimeout = Application("gc_dbConn_CommandTimeout") dbConn.Open Application("gc_dbConn_ConnectionString") End Function
' PURPOSE: Closes the database connection if the connection is open Function CloseDB() ' close database connection if UCase(TypeName(dbConn)) = "CONNECTION" then dbConn.Close Set dbConn = Nothing end if End Function
' PURPOSE: Opens a database connection, then opens a recordset object ' INPUTS: objRS - recordset object that is to be opened. Function OpenRS(objRS) OpenDB() Set objRS = Server.CreateObject("ADODB.Recordset") End Function
' PURPOSE: Closes the recordset object passed into function, then closes the connection if ' the connection object is open ' INPUTS: objRS - recordset object that is to be closed. Function CloseRS(objRS) Set objRS = Nothing CloseDB() End Function
Well, that's it, I know it's a lot of code on this page, but I provided you with all the functions that the topic of this FAQ (Dynamically selecting a drop-down option) calls out to.
Again, I hope this helps you out in your coding experiences.