INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Dynamic List Boxes

Selecting Value from DB in Combo Box When In Edit Mode by BG12424
Posted: 24 Feb 03 (Edited 8 Apr 03)

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

End Function

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

Function ClientList(DeltaValue)
    
    dim combo

    ' 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

End Function


USAGE EXAMPLE:
On our main ASP page, we will want to call the function called ClientList like such:

<table><tr><td><%=ClientList(intClientValue)%></td></tr></table>

-------------------------------------------------------
-------------------------------------------------------

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.

regards,
bgaines72


Back to Microsoft: ASP (Active Server Pages) FAQ Index
Back to Microsoft: ASP (Active Server Pages) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close