Visual Basic > Database SQL Stuff

Display the results of an ADO recordset in a HTML combo (ASP)

Display the results of an ADO recordset in a HTML combo (ASP)

The following ASP routine outputs the contents of a recordset to drop down combo box.

<%
'Purpose   :    Outputs the result of a recordset to a combo
'Inputs    :    oRst			The opened recordset containing the rows to display.
'		lTextColumn		The ordinal position of the column in the recordset to 
'					display as text in the combo.
'		lValueColumn		The ordinal position of the column in the recordset to 
'					be used as the value in the combo.
'		sComboName		The name of the combo.
'		lComboLeft		The left coordinate of the combo.
'		lComboWidth		The width of the combo.
'		sComboAlign		The alignment of the combo.
'               sDefaultItem		The default select item in the combo.
'Outputs   :    Returns -1 on failure else returns the number of rows in the combo.
'Example   :    
'               'Open Recordset
'               oRst.Open "Select UserId, Name From lkpUsers ORDER BY Name ASC", oCon, adOpenForwardOnly
'               'Display Recordset
'		RStoCombo oRst, 1, 0, "UserID", 1000, 156, "LEFT", ""

Function RStoCombo(oRst, lTextColumn, lValueColumn, sComboName, lComboLeft, lComboWidth, sComboAlign, sDefaultItem)
	Dim lNumRows, avResults, lThisRow
	
	On Error Resume Next
	'Set up default parameters
	If IsNumeric(lTextColumn)=False Then
		lTextColumn = 0
	End If
	If IsNumeric(lValueColumn)=False Then
		lValueColumn = lTextColumn
	End If
	If IsNumeric(lComboLeft)=False Then
		lComboLeft = 1
	End If
	If IsNumeric(lComboWidth)=False Then
		lComboWidth = 150
	End If
	If sComboAlign = Empty Then
		sComboAlign = "LEFT"
	End If
	If Len(sDefaultItem) Then
		'Default item is case insensitive
		sDefaultItem = Ucase(sDefaultItem)
	End If	
	
	'Get results into array
	if oRst.EOF= False then
		avResults = oRst.GetRows(-1)
		lNumRows = UBound(avResults, 2) + 1
		RStoCombo = lNumRows
	else
		'Return success code
		RStoCombo = -1
		Exit Function
	end if
	
	'Start combo
	Response.Write vbNewLine & "<SELECT style=""" & sComboAlign & ": " & lComboLeft & "px; WIDTH: " & lComboWidth & "px"" name=" & sComboName & ">" & vbNewLine

	For lThisRow= 0 to lNumRows - 1
		If sDefaultItem = Ucase(avResults(lTextColumn, lThisRow)) Then
			Response.Write "	<option selected value = """ & avResults(lValueColumn, lThisRow) & """>" & avResults(lTextColumn, lThisRow) & "</OPTION>" & vbNewLine
		Else
			Response.Write "	<option value = """ & avResults(lValueColumn, lThisRow) & """>" & avResults(lTextColumn, lThisRow) & "</OPTION>" & vbNewLine
		End If
	Next
	
	'End combo
	Response.Write "</SELECT>" & vbNewLine

	'Close Recordset
	oRst.Close

	If Err.number Then
		'Return failure code
		RStoCombo= -1
		Response.Write "<B>Failed to output recordset results to combo: " & Err.Description 
	End If
End Function
%>