function fnSqlSelect( byval spConnect, byval spSql, byref apResults )
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' a negative return value signals an error
' a positive return value signals number of rows selected
'
' 0 recordset empty, did not return any rows
' -1 cannot open data source
' -2 could not open recordset, possible invalid sql statement
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
dim aReturn() ' results- row 0 is field names, data starts at row 1
dim nReturn : nReturn=0 ' status indicator
dim sConnect : sConnect=spConnect
dim objConn
dim objRS
dim nRows
dim nFields
dim nRow
dim nField
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' check for database file existence before using ADO to connect to it
redim apResults(0,0)
set objConn=Server.CreateObject("ADODB.Connection")
set objRS=Server.CreateObject("ADODB.Recordset")
on error resume next
objConn.Open sConnect
on error goto 0
' check if we connected
if objConn.State<>1 then
nReturn=-1
else
' configure the record set
objRS.Source = spSql
objRS.ActiveConnection = objConn
objRS.CursorType = 0
objRS.CursorLocation = 3
objRS.LockType = 1
' populate the recordset
on error resume next
objRS.Open
on error goto 0
' check we opened the recordset
if objRS.State=0 then
nReturn=-2
else
' opened a recordset, did we get any rows
if objRS.EOF or objRS.BOF then
nReturn=0
else
nRows =objRS.RecordCount
nFields =objRS.Fields.Count
nReturn=nRows
redim apResults( nRows, nFields )
' now move through the record set filling the cache array
nRow=1
objRS.MoveFirst
do until objRS.EOF
nField=1
for each objField in objRS.Fields
' load this found record into the array
apResults( nRow, nField ) = objField.Value
' next field
nField=nField+1
next
' next record
nRow=nRow+1
objRS.MoveNext
loop
end if
objRS.Close
objConn.Close
end if ' recordset opened
set objRS=Nothing
set objConn=Nothing
end if ' connected to database
' return the status indicator
fnSqlSelect=nReturn
end function
'******************************************************************************************************************************
'******************************************************************************************************************************
'******************************************************************************************************************************
