Better still, why not wrap the query into a class to give Record handling functions?
Add the following into a Script Library and reference that library from your script:
'*******************************************************************************
Class ResultSet
Public SQL
Public QueryObj
Private Dic
Public CurRow
Public Rows
Sub Class_Initialize()
Set Dic = CreateObject("Scripting.Dictionary")
End Sub
Public Sub Execute(SQL)
Set QueryObj = Server.Query(SQL)
ColNames = QueryObj.ColumnNames
For i = 0 To QueryObj.ColumnCount - 1
Dic.Add LCase(ColNames(i)), i
Next
Rows = QueryObj.Rows
CurRow = 0
End Sub
Public Sub ExecuteS( myServer, SQL)
Set QueryObj = myServer.Query(SQL)
ColNames = QueryObj.ColumnNames
For i = 0 To QueryObj.ColumnCount - 1
Dic.Add LCase(ColNames(i)), i
Next
Rows = QueryObj.Rows
CurRow = 0
End Sub
Public Property Get ColumnIndex(Name)
'If the column has a space, it is surrounded by quote marks.
ColumnIndex = Dic.Item(Replace(LCase(Name),"""",""))
End Property
Public Property Get Fields(Name)
If Not EOF Then
ColIndex = ColumnIndex(Name)
Fields = Rows(CurRow, ColIndex)
Else
Err.Raise vbObjectError + 1, "RecordSet", "EOF"
End If
End Property
Public Property Get EOF()
If CurRow = QueryObj.RowCount Then
EOF = True
Else
EOF = False
End If
End Property
Public Property Get BOF()
If CurRow = 0 Then
BOF = True
Else
BOF = False
End If
End Property
Public Sub MoveNext()
If Not EOF Then
CurRow = CurRow + 1
Else
Err.Raise vbObjectError + 1, "RecordSet", "EOF"
End If
End Sub
Public Sub MoveFirst()
CurRow = 0
End Sub
Public Sub MoveLast()
CurRow = q.RowCount
End Sub
Public Sub MovePrev()
CurRow = CurRow - 1
If CurRow < 0 Then CurRow = 0
End Sub
End Class
Now you can write code like this:
Set rs = New ResultSet
sql = "SELECT ID, FULLNAME AS FNAME FROM CMimic"
rs.Execute sql
If rs.QueryObj.RowCount = 0 Then
Msgbox "No Mimics found"
Exit Sub
End If
Do While Not rs.EOF
m = rs.Fields("FNAME")
'Do something
rs.MoveNext
Loop
... View more
