Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Selecting Value from DB in Combo Box When In Edit Mode

Dynamic List Boxes

Selecting Value from DB in Combo Box When In Edit Mode

by  BG12424  Posted    (Edited  )
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.

[color red]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.
[/color]

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
Code:
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
Code:
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
Code:
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><%=
Code:
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!!!
Code:
' 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

[img http://home.attbi.com/~gainesme72/AG00280_.gif]
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top