In trying to pull a field for a dropdown list, of all degreecodes. I am

trying to SELECT the degree code associated with a specific physician

record with a specific social security number (indicated by variable

intSSNum) so that it is selected in the dropdown list. I am having

trouble showing what degreecode is selected in my dropdown list that is

associated with that physician record. Here is the code that I am using

(any help would be appreciated, thanks!!):



strTemp = "SELECT degreecode, COUNT(degreecode) AS Selected " & _

"FROM svarldwr01.sagentprototype.dbo.physn " & _

"GROUP BY degreecode " & _

"ORDER BY degreecode "

Set rs = HD_CGuser.Execute(strTemp)



strTemp = strTemp & " WHERE ssnum = '" & intSSNum & "' "

strDegreecodeComboBox = "<SELECT NAME='degreecode' >"



Do While Not rs.EOF

strDegreecodeComboBox = strDegreecodeComboBox & "<option value='" & 

rs("degreecode") & "' "



If rs("Selected") Then strDegreecodeComboBox = strDegreecodeComboBox 

& "SELECTED"

strDegreecodeComboBox = strDegreecodeComboBox & ">" & rs("degreecode")

& "  </OPTION>" & vbCRLF

intCounter = intCounter + 1

rs.MoveNext

Loop

strDegreecodeComboBox = strDegreecodeComboBox & "</SELECT>"

intMaxDegreecode = intCounter - 1

rs.Close



Response.Write strTemp & "<br>"

	In situations where I need to create a dropdownlist (combobox) from a

database I used the following routine. It has served me well and I hope you

can find some value in it.



<%

'--------------------------------

' CBO_FromRS(sSQL, sName, siID, iSIZE, bMultiple, sFieldVal, sFieldData,

iSELECTED, bNONE)

'--------------------------------

' PARAMETERS:

' [in]sSQL		- The SQL SELECT Statement that will return your information

(string)

' [in]sName		- The NAME of the ListBox							(string)

' [in]siID		- The ID of the ListBox								(string)

' [in]iSIZE		- The SIZE of the ListBox							(int)

' [in]bMultiple	- Whether the ListBox is a single or multiple				(boolean)

' [in]sFieldVal	- The SQL Field that will populate the VALUE				(string)

' [in]sFieldData	- The SQL Field that will populate the ListBox				(string)

' [in]iSELECTED	- A value that depicts a Selected item					(variant)

' [in]bNONE		- Whether the ListBox has a 'None' Option					(boolean)

'

' ASSUMPTIONS:

' -A Connection object (oConn) has been created and opened.

' -Only one iSELECTED value will ever be passed.

'--------------------------------

function CBO_FromRS(sSQL, sName, siID, iSIZE, bMultiple, sFieldVal,

sFieldData, iSELECTED, iNONE)

	dim oRS

	Dim sHTML, sMultiple, sSelected, iSELECT, sINSERT

	if isNull(iSELECTED) then iSELECTED = -1

	if bNONE = TRUE then sINSERT = "<option value=""0"" SELECTED>None</option>"

	if bMultiple then sMultiple = " MULTIPLE"



	sHTML = "<select name=""" & sName & """ id=""" & siID & """ size=""" &

iSIZE & """" & sMultiple & ">" & VbCrLf

	sHTML = sHTML & sINSERT

	set oRS = oConn.execute(sSQL)

	do while not oRS.EOF

		if cint(iSELECTED) = cint(oRs(sFieldVal)) then

			sSelected = " SELECTED"

		else

			sSelected = ""

		end if

		sHTML = sHTML & "<option value=""" & oRS(sFieldVal) & """" & sSelected &

">" & oRS(sFieldData) & "</option>" & VbCrLf

		oRS.MoveNext

	loop

	sHTML = sHTML & "</select>" & VbCrLf

	oRS.Close

	set oRS = nothing

	CBO_FromRS = sHTML

end function

%>