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!

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

Populating Combo Boxes From A Database 2

Status
Not open for further replies.
Figzus

Figzus

Programmer
Mar 21, 2005
36
0
0
US
I figured out how to populate text boxes from a datbase but how do I populate a combo box from the database for a single value? Like the division that the record belongs to? Thanks
 
Sort by date Sort by votes
can you explain a bit more about what you are after - I dont fully understand what you're trying to do

Tony
[red]_________________________________________________________________[/red]
Webmaster -
 
Upvote 0 Downvote
Ah yes sorry about that. Okay I am populating a form that has several text boxes and combo boxes on it with an ID I got from the previous page. I have got the text boxes to populate with the correct information that I need but what I am having trouble is populating the combo boxes. Here is what I have:
<label for="cboDivision">Division:</label>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<select class="sizem" id="cboDivision" name="cboDivision" type="combo">
<option selected></option>
<option value="<%=rsMOCellWeb("DIVISION")%>"></option>
<option value="ADMINISTRATIVE SERVICES">ADIMINISTRATIVE SERVICES</option>
<option value="DIRECTORS OFFICE">DIRECTORS OFFICE</option>
<option value="FISHERIES">FISHERIES</option>
<option value="FORESTRY">FORESTRY</option>
<option value="HUMAN RESOURCES">HUMAN RESOURCES</option>
<option value="OUTREACH AND EDUCATION">OUTREACH AND EDUCATION</option>
<option value="PRIVATE LAND SERVICES">PRIVATE LAND SERVICES</option>
<option value="PROTECTION">PROTECTION</option>
<option value="RESOURCE SCIENCE">RESOURCE SCIENCE</option>
<option value="WILDLIFE">WILDLIFE</option>
</select>

For some reason it is not populating the combo box with the division from that specifc record.
 
Upvote 0 Downvote
you would need 2 sql statements

1 using "where" which you give you the values of the chosen record

and 1 select without the where to populate the dropdown

something like this

Code: 
<select name="select">
<%
'sql2 here

do while not rs2.eof
if rs2("valuefield")=rs1("valuefield") then
strselect="selected"
end if
%>
<%<option value="<%=rs2("valuefield")%>" <%=strselect%>> <%=rs2("valuefield")%></option>
rs2.movenext
loop
'close sql2
%>
[/code
 
Upvote 0 Downvote
Are you able to pull other fields for this record from the recordset object?

Is it possible that the field value is blank?

Is the recordset open and otherwise in good health?

Are there any errors?

Maybe post the rest of your code... the snippet posted above looks OK but maybe you are only seeing the result of an earlier problem.
 
Upvote 0 Downvote
Here is the whole page though I only put the text boxes I have populated and the one text box. I am able to populate the text boxes for the record from the recordset object. The field will never be blank. There are no errors showing up so far on the webpage. It just shows up as a blank space on the combo box.

<%@ Language=VBScript %>
<!DOCTYPE html PUBLIC "-//w3c//dtd xhtml 1.0 strict//en" "http:<HTML>
<HEAD>
<title>MO Cell Phone Edit</title>
<meta content="True" name="vs_snapToGrid">
<style type="text/css" media="screen"> @import url( /css/core.css ); @import url( /css/app.css );
</style>
<style type="text/css" media="print">@import url( /css/print.css );
</style>
<script language="javascript" id="clientEventHandlersJS">

</script>

</HEAD>
<body>
<div id="header">
<!-- #include virtual="/custom/header.html" -->
<!-- #include file="incConnection.asp" -->
<%
Dim strSQL
Dim rsMOCellWeb
Dim adocon

set adocon=server.CreateObject("ADODB.Connection")
set rsMOCellWeb=server.CreateObject("ADODB.Recordset")

adocon.ConnectionString=gstrCon

strSQL = "SELECT tblTransactions.trnCellNo AS [CELL NO]," & _
" tblTransactions.trnLastName AS [LAST NAME], tblTransactions.trnFirstName AS [FIRST NAME]," & _
" tblTransactions.trnDivision AS [DIVISION], tblTransactions.trnMiddleName AS [MIDDLE NAME] " & _
" FROM tblTransactions " & _
" WHERE tblTransactions.trnCellNo = " &request("CellNo")



adocon.Open
rsMOCellWeb.Open strSQL,adocon
%>
<h2>MO Cell Phone Edit</h2>
<div id="help"></div>
</div>
<div id="content">
<div id="sub-nav">
<h2>Navigation</h2>
<ul>
</ul>
</div>
</div>
<div id="main-content">

<h2 align="center">Cell Phone Tracking System</h2>
<h2 align="center">Subscriber Information</h2>
<DIV class="clear">
<div class="l50" id="DIV1" align="left">
<label for="txtCellNumber">Cell Phone:&nbsp; </label><input type="text" class="sizem" id="CellNo" name="CellNo" value="<%=rsMOCellWeb("CELL NO")%>" >&nbsp;

<br>
<label for="txtLastName">Last
Name:&nbsp; </label><input class="sizem" id="Text2" type="text" name="txtLastName" value="<%=rsMOCellWeb("LAST NAME")%>">&nbsp;
<br>
<label for="txtFirstName">First Name:&nbsp;&nbsp;</label><input class="sizem" id="Text3" type="text" name="txtFirstName" value="<%=rsMOCellWeb("FIRST NAME")%>">
<br>
<label for="txtMiddleName">Middle
Name:</label><input class="sizem" id="Text4" type="text" name="txtMiddleName" value="<%=rsMOCellWeb("MIDDLE NAME")%>">&nbsp;
<br>
<label for="cboDivision">Division:</label>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<select class="sizem" id="cboDivision" name="cboDivision" type="combo">
<option value="<%=rsMOCellWeb("DIVISION")%>"></option>
<option selected></option>

<option value="ADMINISTRATIVE SERVICES">ADIMINISTRATIVE SERVICES</option>
<option value="DIRECTORS OFFICE">DIRECTORS OFFICE</option>
<option value="FISHERIES">FISHERIES</option>
<option value="FORESTRY">FORESTRY</option>
<option value="HUMAN RESOURCES">HUMAN RESOURCES</option>
<option value="OUTREACH AND EDUCATION">OUTREACH AND EDUCATION</option>
<option value="PRIVATE LAND SERVICES">PRIVATE LAND SERVICES</option>
<option value="PROTECTION">PROTECTION</option>
<option value="RESOURCE SCIENCE">RESOURCE SCIENCE</option>
<option value="WILDLIFE">WILDLIFE</option>

</select>

<%
rsMOCellWeb.Close
adocon.Close
set rsMOCellWeb=nothing
set adocon=nothing
%>
</div>
<div class="clear">
<div class="f100">
</table>
</div>
<div id="footer"></div>
</div>
</DIV>
</body>
</HTML>
 
Upvote 0 Downvote
i think i misunder stood the question try this

Code: 
<%
sub getdropvalue(thevaluetomatch)
if rsMOCellWeb("DIVISION")= thevaluetomatch then
response.write "selected"
end if
end sub

%>
                    <select class="sizem" id="cboDivision" name="cboDivision" type="combo">
                        <option value="ADMINISTRATIVE SERVICES" <%getdropvalue("ADMINISTRATIVE SERVICES")%>>ADIMINISTRATIVE SERVICES</option>
                        <option value="DIRECTORS OFFICE" <%getdropvalue("ADMINISTRATIVE SERVICES")%>>DIRECTORS OFFICE</option>
                        <option value="FISHERIES" <%getdropvalue("FISHERIES")%>>FISHERIES</option>
                        <option value="FORESTRY" <%getdropvalue("FORESTRY")%>>FORESTRY</option>
                        <option value="HUMAN RESOURCES" <%getdropvalue("HUMAN RESOURCES")%>>HUMAN RESOURCES</option>
                        <option value="OUTREACH AND EDUCATION" <%getdropvalue("OUTREACH AND EDUCATION")%>>OUTREACH AND EDUCATION</option>
                        <option value="PRIVATE LAND SERVICES" <%getdropvalue("PRIVATE LAND SERVICES")%>>PRIVATE LAND SERVICES</option>
                        <option value="PROTECTION" <%getdropvalue("PROTECTION")%>>PROTECTION</option>
                        <option value="RESOURCE SCIENCE" <%getdropvalue("RESOURCE SCIENCE")%>>RESOURCE SCIENCE</option>
                        <option value="WILDLIFE" <%getdropvalue("WILDLIFE")%>>WILDLIFE</option>
                        
                    </select>
 
Upvote 0 Downvote
here hopefully a cleaner looking view

Code: 
<%
sub getdropvalue(thevaluetomatch)
if rsMOCellWeb("DIVISION")= thevaluetomatch then
response.write "selected"
end if
end sub

%>
<select class="sizem" id="cboDivision" name="cboDivision" type="combo">
<option value="ADMINISTRATIVE SERVICES" <%getdropvalue("ADMINISTRATIVE SERVICES")%>>ADIMINISTRATIVE SERVICES</option>
<option value="DIRECTORS OFFICE" <%getdropvalue("ADMINISTRATIVE SERVICES")%>>DIRECTORS OFFICE</option>
<option value="FISHERIES" <%getdropvalue("FISHERIES")%>>FISHERIES</option>
<option value="FORESTRY" <%getdropvalue("FORESTRY")%>>FORESTRY</option>
<option value="HUMAN RESOURCES" <%getdropvalue("HUMAN RESOURCES")%>>HUMAN RESOURCES</option>
<option value="OUTREACH AND EDUCATION" <%getdropvalue("OUTREACH AND EDUCATION")%>>OUTREACH AND EDUCATION</option>
<option value="PRIVATE LAND SERVICES" <%getdropvalue("PRIVATE LAND SERVICES")%>>PRIVATE LAND SERVICES</option>
<option value="PROTECTION" <%getdropvalue("PROTECTION")%>>PROTECTION</option>
<option value="RESOURCE SCIENCE" <%getdropvalue("RESOURCE SCIENCE")%>>RESOURCE SCIENCE</option>
<option value="WILDLIFE" <%getdropvalue("WILDLIFE")%>>WILDLIFE</option>                       
</select>
 
Upvote 0 Downvote
Lets add some feedback to help with debugging:

Code: 
  adocon.Open 
[red]
  IF (adocon.State = 1) THEN
     Response.Write "<BR><BR>ADO Connection Opened<BR><BR>"
  ELSE
     Response.Write "<BR><BR>Failure Opening ADO Connection!<BR><BR>"
  END IF

  Response.Write "strSQL = " & strSQL & "<BR><BR>"
[/red]
  rsMOCellWeb.Open strSQL,adocon
[red]
  IF (rsMOCellWeb.State = 1) THEN
     Response.Write "<BR><BR>ADO Recordset Opened<BR><BR>"
  ELSE
     Response.Write "<BR><BR>Failure Opening ADO Recordset!<BR><BR>"
  END IF

  IF rsMOCellWeb.EOF THEN 
     Response.Write "<BR><BR>ADO Recordset Is Empty!<BR><BR>"
  END IF    
[/red]
        %>
            <h2>MO Cell Phone Edit</h2>
            <div id="help"></div>
        </div>
 ...

The parts to add are in red.
 
Upvote 0 Downvote
Oh, disregard my reply.

I DEFINATELY misunderstood the question!
 
Upvote 0 Downvote
No time like the present to start utilizing OO methodologies for modularity and maintainability:


DButils.asp Class File
[ADO Utility Helper]
Code: 
[COLOR=red yellow]<!-- METADATA TYPE="TypeLib" UUID="{00000205-0000-0010-8000-00AA006D2EA4}" -->[/color]

<%
[COLOR=green]
'==================================================
'  Generic ASP Data Access Class
'
'==================================================
[/color]
Class cDBUtils

[COLOR=green]
	'===========================
	'## Private Class Members ##
	'===========================[/color]
	Private m_sConnect
	Private m_oConnection
	Private m_oTempRS
	Private m_lErrorNumber
	Private m_sErrDescription
	Private m_lErrorCount
	Private vbQuote
	
	[COLOR=green]
	'=============================================
	'## Public Error Number Class Property		##
	'============================================= [/color]
	Public Property Get ErrorNumber()
		ErrorNumber = m_lErrorNumber
	End Property
	
	Public Property Let ErrorNumber(lInput)
		m_lErrorNumber = lInput
	End Property
	
	[COLOR=green]
	'=================================================
	'## Public Error Description Class Property		##
	' holds error msg for LAST error encountered 
	'================================================[/color]
	Public Property Get ErrorDescription()
		ErrorDescription = m_sErrDescription
	End Property
	
	Public Property Let ErrorDescription(sInput)
		m_sErrDescription = sInput
	End Property
	[COLOR=green]
	'=============================================
	'## Public Error Count Class Property		##
	'=============================================[/color]
	Public Property Get ErrorCount()
		ErrorCount = m_lErrorCount
	End Property
	
	Public Property Let ErrorCount(lInput)
		m_lErrorCount = lInput
	End Property
	
	[COLOR=green]
	'=====================================
	'## Public ReadOnly Recordset Property ##
	'===================================== [/color]
	Public Property Get RecordSet()
		Set RecordSet = m_oTempRS
	End Property
	
	Private Property Set RecordSet(ByVal oInput)
		Set RecordSet = oInput
	End Property
	
	[COLOR=green]
	'=====================================
	'## Private ADO Connection Property ##
	'===================================== [/color]
	Private Property Get Connection()
	'## IF m_oConnection is an object and Connection is not already Set
	'## Create a New instance of Connection Property, otherwise reuse existing 
		If isObject(m_oConnection) AND Not isObject(Connection) Then
			Set Connection = m_oConnection
		End IF
	End Property
	
	Private Property Set Connection(oInput)
		IF Not isObject(m_oConnection) Then
			Set m_oConnection = oInput
		End IF
	End Property
	[COLOR=green]
	'=============================================
	'## Public Read/Write ADO Connection String	##
	'============================================= [/color]
	Public Property Get ConnectionString()
		ConnectionString = m_sConnect
	End Property
	
	Public Property Let ConnectionString(sInput)
		m_sConnect = sInput
	End Property
	
	[COLOR=green]
	'============================================
	' ## Opens an ADO Connection
	'============================================ [/color]
	
	Private Sub OpenConnection()
		IF isObject(Connection) Then
			IF (Connection.State = 0) Then
				Connection.Open ConnectionString
			End IF
		Else
			Set Connection = Server.CreateObject("ADODB.Connection")
			Connection.Open ConnectionString
		End IF
	End Sub
	[COLOR=green]
	'=====================================
	'## Private CloseConnection			##
	'## {Closes an Open Connection}		##
	'===================================== [/color]
	Private Sub CloseConnection()
		IF isObject(Connection) AND (Connection.State > 0) Then
			Connection.Close
		End IF
	End Sub
	[COLOR=green]
	'=====================================================
	'## Private GetStatus								##
	'## Returns The State of the Connection				##
	'## Used internally to determine Connection Status  ##
	'===================================================== [/color]
	Private Function GetStatus()
		IF isObject(Connection) Then
			GetStatus = Connection.State
		Else
			GetStatus = 0
		End IF
	End Function
	
	
	[COLOR=green]
	'===============================================================
	'## Function Insertdata
	' Inserts Data into DB via inline inserts
	' Returns:
	'	Boolean (True = Success | False = Error)
	'=============================================================== [/color]
	Public Function InsertData(sTableName, [ParamArray])
		Dim uBoundX, tempString
		Dim iNameIterator, iValueIterator
		Dim sql, oCommand
		
		IF Not isArray([ParamArray]) Then
			IncrementErrorCount
			AddErrortext("Array expected in cDBUtils:InsertData")
			AddErrorNumber(1303)
			InsertData = False
			Exit Function
		End IF
		
		tempString = "INSERT INTO [" & sTableName & "] ("
		
		On Error Resume Next
		uBoundX = uBound([ParamArray],1)
		
		For iNameIterator = 0 to uBoundX
			IF (iNameIterator <> 0) AND (iNameIterator <> uBoundX) AND (iNameIterator > 0) Then
				tempString = tempString & [ParamArray](iNameIterator,0) & ","
			Else
				tempString = tempString & [ParamArray](iNameIterator,0)
			End IF
		Next
			
			tempString = tempString & ") VALUES ("
			
		For iValueIterator = 0 to uBoundX
			IF (iValueIterator <> 0) AND (iValueIterator <> uBoundX) AND (iValueIterator > 0) Then
				IF isNull([ParamArray](iValueIterator,1)) Then
					tempString = tempString & "'<NULL>'" & ","
				Else
					tempString = tempString & [ParamArray](iValueIterator,1) & ","
				End IF
			Else
				IF isNull([ParamArray](iValueIterator,1)) Then
					tempString = tempString & "'<NULL>'"
				Else
					tempString = tempString & [ParamArray](iValueIterator,1)
				End IF
			End IF
		Next
		
		tempString = tempString & ")"
		
		
		sql = tempString
		
		On Error Resume Next
		
		OpenConnection
		Set oCommand = Server.CreateObject("ADODB.Command")
			oCommand.ActiveConnection = Connection
			oCommand.CommandType = &H0001
			oCommand.CommandText = sql
			oCommand.Execute sql
		Set oCommand = Nothing

		
		IF err.number <> 0 Then
			IncrementErrorCount
			AddErrortext(err.Description & " in cDBUtils:InsertData")
			AddErrorNumber(1304)
			InsertData = False
		Else
			InsertData = True
		End IF
		
		
		
	End Function
	
	
	[COLOR=green]
	'===============================================================
	'## Function Updatedata
	' Updates Data via inline inserts
	' Returns:
	'	Boolean (True = Success | False = Error)
	'=============================================================== [/color]
	Public Function UpdateData(sTableName, [ParamArray], sClause)
		Dim uBoundX, tempString
		Dim iNameIterator, iValueIterator
		Dim sql, oCommand
		
		IF Not isArray([ParamArray]) Then
			IncrementErrorCount
			AddErrortext("Array expected in cDBUtils:UpdateData")
			AddErrorNumber(1307)
			InsertData = False
			Exit Function
		End IF
		
		tempString = "UPDATE [" & sTableName & "] "
		
		On Error Resume Next
		uBoundX = uBound([ParamArray],1)
		
		For iNameIterator = 1 to uBoundX
			IF (iNameIterator <> 0) AND (iNameIterator <> uBoundX) AND (iNameIterator > 0) Then
				tempString = tempString & "SET " & [ParamArray](iNameIterator,0) & " = " & [ParamArray](iNameIterator,1) & ","
			Else
				tempString = tempString & "SET " & [ParamArray](iNameIterator,0) & " = " & [ParamArray](iNameIterator,1)
			End IF
		Next
			
		
		sql = tempString & " " & sClause
		
		On Error Resume Next
		
		OpenConnection
		Set oCommand = Server.CreateObject("ADODB.Command")
			oCommand.ActiveConnection = Connection
			oCommand.CommandType = &H0001
			oCommand.CommandText = sql
			oCommand.Execute sql
		Set oCommand = Nothing

		
		IF err.number <> 0 Then
			IncrementErrorCount
			AddErrortext(err.Description & " in cDBUtils:UpdateData")
			AddErrorNumber(1308)
			UpdateData = False
		Else
			UpdateData = True
		End IF
		
		
		
	End Function
	
[COLOR=green]
	'============================================================
	' Creates a DataBound DropDown ListBox
	' Input Params:
	'	oRS - Source Recordset
	'	sElementname - Name of DropDown
	'	sDefaultItem - Initial Selected Item (not mapped to oRS)
	'	sValueField - oRS Field that maps to the Option Value
	'	sNameField - oRS Field that maps to Option Name
	' 	CssClass - Css Class to apply
	'	sSelectedItem - Marks Field Value as Selected
	'============================================================ [/color]
	Public Sub DataBoundDropDown(ByRef oRS, ByVal sElementName, ByVal sDefaultItem, ByVal sValueField, ByVal sNameField, ByVal CssClass, ByVal sSelectedItem )
			
			Dim sTempString
			On Error Resume Next
			
		[COLOR=green]	'====================================
			' Ensure parameters are correct
			'==================================== [/color]
			If Not isObject(oRS) Then
				IncrementErrorCount
				AddErrorText("Expected RecordSet parameter in DBUtils::DataBoundDropDown")
				Exit Sub
			End IF
			
			If isEmpty(sDefaultItem) or isNull(sDefaultItem) Then
				sDefaultItem = Null
			End IF
			
			Dim sFieldValue
			
			
			sTempString = "<Select Class=" & vbQuote & CssClass & vbQuote & " Name=" & vbQuote & sElementName & vbQuote & " ID=" & vbQuote & sElementName & vbQuote & ">" & vbCrLf
			
			If Not isEmpty(sDefaultItem) Then
				sTempString = sTempString & BuildOptions("0", sDefaultItem, False)
			End IF
			
			
			oRS.MoveFirst
			Do While Not oRS.EOF
			
				sFieldValue = oRS.Fields(sValueField).value
				sFieldName = oRS.Fields(sNameField).value
					
				IF lCase(sFieldValue) = lCase(sSelectedItem) Then
					sTempString = sTempString & BuildOptions(sFieldValue, sFieldName, True) & vbCrLf
				Else
					sTempString = sTempString & BuildOptions(sFieldValue, sFieldName, False) & vbCrLf
				End IF
					
				oRS.MoveNext
			Loop
			
			sTempString = sTempString & vbCrLf & "</Select>"
			
			Echo(sTempString)
			
	End Sub
	[COLOR=green]
	'===============================================
	' Helper for DataBoundDropDown
	'=============================================== [/color]
	Private Function BuildOptions(ByVal sOptionValue, ByVal sOptionName, ByVal bSelected) 
		
		Dim sTempString
		
		If Not bSelected Then
			sTempString = "<Option value=" & vbQuote & sOptionValue & vbQuote & ">" & sOptionName & "</Option>"
		Else
			sTempString = "<Option value=" & vbQuote & sOptionValue & vbQuote & " SELECTED>" & sOptionName & "</Option>"
		End IF
		
		'sTempString = sTempString & sTempString
		
		BuildOptions = sTempString
		
	End Function
	
	
	
	Private Function NullHelper(sInput)
		If isNull(sInput) or isEmpty(sInput) Then
			NullHelper = "'<NULL>'"
		End IF
	End Function
	
	[COLOR=green]
	'=================================================================================
	' Executes inLine SQL
	' Populates Class 'Recordset' Property with a RecordSet Object if Successful
	'================================================================================= [/color]
	Public Function ExecuteInlineSql(sql)
	
		On Error Resume Next
		
		OpenConnection
		Set m_oTempRS = Server.CreateObject("ADODB.RecordSet")
		m_oTempRS.Open sql, Connection, 3,1

		
		If err.number <> 0 Then
			IncrementErrorCount
			AddErrortext(Err.Description &  " in cDBUtils:ExecuteInlineSql")
			AddErrorNumber(1305)
			ExecuteInlineSql = False
		Else
			ExecuteInlineSql = True
			
		End IF
	End Function
	
	[COLOR=green]
	'=================================================================================
	' Executes Stored Procedure
	' Populates Class 'Recordset' Property with a RecordSet Object if Successful
	'================================================================================= [/color]
	Public Function ExecProc(sProc, [ParamArray])

		
		Dim uBoundX, uBoundY, tempString
		
		On Error Resume Next [COLOR=green]
		'======================================
		' Determine if proc has params
		'====================================== [/color]
		IF isArray([ParamArray]) Then
			uBoundX  = uBound([ParamArray])
		
		
			Dim iValueIterator
			
			tempString = "EXEC " & sProc & " "
			
			'Retrieve param names
			For iValueIterator = 0 to uBoundX
				IF (iValueIterator <> 0) AND (iValueIterator <> uBoundX) AND (iValueIterator > 0) Then
					tempString = tempString & [ParamArray](iValueIterator) & ","
				Else
					tempString = tempString & [ParamArray](iValueIterator)
				End IF
			Next
		Else
			tempString = "EXEC " & sProc 
		End IF
		
		Dim sql
		sql = tempString
		
		OpenConnection
		
		Set m_oTempRS = Server.CreateObject("ADODB.RecordSet")
		m_oTempRS.Open sql, Connection, 3, 2
		
		
		IF err.number <> 0 Then
			IncrementErrorCount
			AddErrortext(Err.Description &  " in cDBUtils:ExecProc")
			AddErrorNumber(1306)
			ExecProc = False
		Else
			ExecProc = True
		End IF
		
	
	End Function	
	[COLOR=green]
	'====================================
	' Class Member Error Handlers
	'==================================== [/color]
	Private Sub IncrementErrorCount()
		ErrorCount = (ErrorCount + 1)
	End Sub
	
	
	Private Sub AddErrorText(sInput)
		ErrorDescription = sInput
	End Sub
	
	
	Private Sub AddErrorNumber(lInput)
		ErrorNumber = lInput
	End Sub
	
	[COLOR=green]
	'============================================================
	'## Public ResetErrors
	' Returns : nothing
	' Resets Error Properties
	' Use this to reset error properties of this class
	'=========================================================== [/color]
	Public Sub ResetErrors()
		ErrorCount = 0
		ErrorDescription = Null
		ErrorNumber = 0
	End Sub
	
	[COLOR=green]
	'===================================================
	' Public Function RS2XL
	' Input: RecordSet Object
	' OutPut: XML String - format below:
	'
	' 	<DatabaseName>
	' 		<ROW index=1>
	'    		<ColumnName>Column value</ColumnName>
	'    		<ColumnName>Column value</ColumnName>
	'		</ROW>
	' 	</DatabaseName>
	'
	'=================================================== [/color]
	Public Function RS2XML(byRef oRS)
	
		If Not isObject(oRS) Then
			IncrementErrorCount
			AddErrorText("RecordSet Expected in method cDBUtils:RS2XML")
			AddErrorNumber(1301)
			Exit Function
		End IF
		
		
		Dim iRowCount, iColCount
		Dim tempString, sElementName
		iRowCount = 1
		
		On Error Resume Next
		
		oRS.MoveFirst
[COLOR=green]
		'==========================================
		' Add DB Name as Root Element
		'========================================== [/color]
		tempString = "<" & Connection.DefaultDatabase & ">" & vbCrLf
		Do While Not oRS.EOF
			tempString = (tempString & "<ROW Index=" & vbQuote & iRowCount & vbQuote & ">" & vbCrLf)
			For iColCount = 0 To oRS.Fields.Count - 1
				sElementName = oRS.Fields(iColCount).Name
				tempString = (tempString & "<" & sElementName & ">" & Validify(oRS.Fields(iColCount).Value) & "</" & sElementName & ">" & vbCrLf)
			Next
			tempString = tempString & "</ROW>" & vbCrLF
			oRS.MoveNext
			iRowCount = (iRowCount + 1)
		Loop
		
		tempString = (tempString & "</" & Connection.DefaultDatabase & ">")
		
		If err.number <> 0 Then
			IncrementErrorCount
			AddErrorText(err.Description & " in method RS2XML")
			AddErrorNumber(1302)
			RS2XML = False
		Else
		
			RS2XML = tempString
			
		End IF
		
		Set oRS = Nothing
		
	End Function
	
	Private Function Validify(ByRef sInput)
		sInput = Replace(sInput, "]", "")
		sInput = Server.HTMLEncode(sInput)
		
		Validify = sInput
	End Function
	
	Private Sub Echo(ByRef sInput) 
		Response.Write(sInput & vbCrLf)
	End Sub
	
	Private Sub EchoB(ByRef sInput)
		Response.Write(sInput & "<BR/>" & vbCrLF)
	End Sub
	[COLOR=green]
	'=========================================
	'## Class_Terminate						##
	'## Fires during class destruction		##
	'========================================= [/color]
	Private Sub Class_Terminate()
		If IsObject(m_oConnection) Then
			CloseConnection
			Set m_oConnection = Nothing
		End IF
		
		If isObject(m_oTempRS) Then
			If m_oTempRS.State > 0 Then
				m_oTempRS.Close
			End IF
			Set m_oTempRS = Nothing
		End IF
	End Sub
	[COLOR=green]
	'================================
	' Class Constructor / Initializer
	'================================ [/color]
	Private Sub Class_Initialize()
		vbQuote = Chr(34)
		'Place ConnectionString Here or Pass it in via 'ConnectionString' Property
		'ConnectionString = Application("sConnect")
		ConnectionString = ""
		ErrorCount = 0
		ErrorDescription = Null
		ErrorNumber = 0
	End Sub
	
End Class


	
%>
Include that in your page.

Call it like:

Code: 
<%
					Dim oRS
					'=== Initialize Utility Class =='
					Set oDBUtil = new cDBUtils
					
						'=== Set Connection Property ==
						oDBUtil.ConnectionString = MM_connoia_STRING
						
						'======== Execute Query  ==========
						IF oDBUtil.ExecuteInlineSql("SELECT * FROM  dbo.tblUrgency") Then
							
							'====  Populate Class RecordSet Property ===
							Set oRS = oDBUtil.RecordSet
								'=== Builds a DataBound DropDown List ==========
								Call oDBUtil.DataBoundDropDown(oRS, "ddUrgency", "All Levels", "urgency_pk", "urgency_name", "formbox", post_ddUrgency)
							Set oRS = Nothing
						Else
							Response.Write("<font color=red><b>ERROR: </b>" & oDBUtil.ErrorDescription & "</font>")
						End IF
					Set oDBUtil = Nothing
				%>


Watch for Line wrapping !!!
 
Upvote 0 Downvote
Sorry to revive an older thread, but that is a really great example of classes in ASP (esp since Im teachin myself .net this makes things a lot clearer for me)

I'm going to have to use that cDBUtils class in the future! Do you (or anyone else) have any other examples of utilizing classes in classic ASP as posted above??

I would be very interested in seeing some more examples.

Again, JSpicolli, great work.

All hail the INTERWEB!
 
Upvote 0 Downvote
Code: 
<!--#include file="Connections/connoia.asp" -->
<!--#include file="utility/DBUtils.asp" -->


<%  

	Dim isFormPostBack
	
	isFormPostBack = uCase(Request.ServerVariables("REQUEST_METHOD")) = "POST"
	
	Dim SiteID
	SiteID = Request.QueryString("customer_pk")
	
	Dim oDBUtil
	Dim oSiteRS, oStateRS, oRegionRS, oCountryRS
	Dim oStatesRS
	Set oDBUtil = new cDBUtils
	
	oDBUtil.ConnectionString = MM_connoia_STRING
	
	'==========================================
	' Update Site Data on PostBack
	'==========================================
	IF isFormPostBack Then
		oDBUtil.ExecProc("dbo.usp_UpdateSiteData")
	End IF
	
	'====================================
	' GET Data For this Site
	'====================================
	IF oDBUtil.ExecProc("dbo.usp_GetSiteData", array(SiteID)) Then
		Set oSiteRS = oDBUtil.RecordSet
	Else
		oDBUtil.Echo("<FONT COLOR=RED>ERROR - </FONT>" & oDBUtil.ErrorDescription)
		Set oDBUtil = Nothing
		Response.End
	End IF
	
	'========================================
	' GET Demographic data
	'========================================
		oDBUtil.ExecuteInlineSql("SELECT * FROM dbo.tblStates")
			Set oStateRS = oDBUtil.RecordSet
			
		oDBUtil.ExecuteInlineSql("SELECT * FROM dbo.tblCountries")
			Set oCountryRS = oDBUtil.RecordSet
			
		oDBUtil.ExecuteInlineSql("SELECT * FROM dbo.tblRegions")
			Set oRegionRS = oDBUtil.RecordSet
	
	
	Sub Echo(sInput)
		Response.Write(sInput)
	End Sub
	
	

%>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
76
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
67
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
Wrong code in a href ?
Replies
1
Views
80
leifoet
leifoet
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
79
leifoet
leifoet
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
214
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top