I was getting error msg 800a0e78 earlier and while troubleshooting i added the "SET NO COUNT ON". I removed it now. Thanks for the explanation.
As per expecting an sp, it is possible to use either an sp or just a query in this case. A team member came up with the connectionManager (which uses ADODB.Connection) and we've been using that here ever since. I strongly doubt it is the issue since the recordset is created... the issue seems to be occuring when using the EOF function!
(in order to help eliminate any remaining doubt about the connectionManager, here is the code for it:
<%
class ConnectionManager
private cls_db
private cls_sp
private cls_rs
private cls_CursorType
private cls_LockType
private cls_cmd
private cls_conn
private cls_CnErrors
private cls_CnErrorsCount
private cls_arrHeaders
private cls_msgString
private cls_hasErrorOccured
private cls_toString_Array
public property get Database
Database = cls_db
end property
public property let Database(dbName)
cls_db = dbName
end property
public property get StoredProcedure
StoredProcedure = cls_sp
end property
public property let StoredProcedure(spName)
cls_sp = spName
end property
public property let LockType(lt)
cls_LockType = lt
end property
public property let CursorType(ct)
cls_CursorType = ct
end property
public property get recordset
set recordset = cls_rs
end property
public property get nextrecordset
set cls_rs = cls_rs.nextRecordset
set nextrecordset = cls_rs
end property
public property get cloneRecordset
set cloneRecordset = CloneRs(cls_rs)
end property
public property get connection
set connection = me
end property
public property let setTimeOut(timeOut)
cls_conn.commandTimeout = timeOut
end property
public property get getRows
dim arr
if cls_rs.eof then
arr = null
else
arr = cls_rs.getRows()
end if
getRows = arr
end property
public property get getRowHeaders
dim i
dim maxCols
if cls_rs.eof then
cls_arrHeaders = null
else
maxCols = cls_rs.fields.count
redim cls_arrHeaders(maxCols)
For i = 0 to maxCols -1
cls_arrHeaders(i)=cls_rs(i).Name
Next
end if
getRowHeaders = cls_arrHeaders
end property
public property get connectionErrors
set connectionErrors = cls_CnErrors
end property
public property get connectionErrorCount
connectionErrorCount = cls_CnErrorsCount
end property
public property get hasErrorOccured
hasErrorOccured = cls_hasErrorOccured
end property
public property get messageString
messageString = cls_msgString
end property
public property get toString_Array
'Returns a string array in the form of
'[val1,val2],[val3,val4]...]
'with javascript, can convert it to array with eval(var)
dim res
dim cpt
cpt = 0
if not cls_rs is nothing then
res = res & "["
do while not cls_rs.eof
for each x in cls_rs.fields
if cpt mod 2 = 0 then
res = res & "["
res = res & x.value
res = res & ","
'p2
else
res = res & x.value
res = res & "]"
end if
cpt =cpt + 1
next
cpt = 0
res = res & ","
cls_rs.movenext
loop
res = mid(res,1,len(res)-1)
res = res & "]"
end if
cls_rs.movefirst
toString_Array = res
end property
sub connect
on error resume next
if cls_conn.state = 0 then
cls_conn.open session("udl")
end if
cls_conn.defaultdatabase = cls_db
'set cls_rs = cls_conn.execute(cls_sp)
if cls_rs.state = 1 then
cls_rs.close
end if
cls_rs.open cls_sp, cls_conn, cls_CursorType,cls_LockType
set cls_CnErrors = cls_conn.errors
cls_CnErrorsCount = cls_conn.errors.count
if cls_CnErrorsCount <> 0 then
cls_hasErrorOccured = true
cls_msgString = "<ul class='error'>"
for each objErr in cls_CnErrors
cls_msgString = cls_msgString & "<li class='error' >Error Number : " & objErr.Number & "</li>"
cls_msgString = cls_msgString & "<li class='error' >Error Description : " & objErr.Description & "</li>"
next
cls_msgString = cls_msgString & "</ul>"
' response.write(cls_msgString)
end if
end sub
private function CloneRs(rs)
dim oStream, tmpRs
set oStream = createobject("Adodb.Stream")
set tmpRs = createobject("Adodb.Recordset")
rs.Save oStream
' locktype unspecified or -1
tmpRs.Open oStream,,,3
set CloneRs = tmpRs
set tmpRs = nothing
end function
Private Sub Class_Initialize
cls_db = "Isol_Reporting"
cls_sp = "undefined"
set cls_rs = nothing
set cls_conn = nothing
set cls_CnErrors = nothing
cls_CnErrorsCount = -1
cls_msgString = "<p class='success'>Insertion / update was successfull</p>"
set cls_rs =server.createobject("ADODB.Recordset")
set cls_conn = Server.CreateObject("ADODB.Connection")
cls_hasErrorOccured = false
cls_toString_Array = "undefined"
cls_CursorType = 0
cls_LockType = 1
End Sub
Private Sub Class_Terminate
'on error resume next
'1 = open
if cls_conn.state = 1 then
cls_conn.close
end if
set cls_rs = nothing
set cls_conn = nothing
set cls_CnErrors = nothing
End Sub
end class
%>
)
any other suggestions?
thanks again with your help!
greatly appreciated!