Recordset object bombs [Answered]RSS

2 replies

Last post Sep 17, 2012 09:30 AM by pmgerholdt

  • Recordset object bombs

    Sep 14, 2012 01:01 PM|pmgerholdt|LINK

    In the following, I am connecting to the database and returning data.

    If I simply write out the data without trying to loop through the recordset, I can write the data. No problem if there were only one record coming back. But there are multiples, and so I need to loop.

     As soon as I do:

     Do While Not RecordsetObject.EOF

    'write stuff

    RecordsetObject.MoveNext

    Loop

     The thing bombs. Lines in the sub that I write after this are not written. See the code below; works fine if I comment out the reference to RecordsetObject.EOF, but bombs if I uncomment and try to loop through normally.

    I do this all the time ... well, I used to in the old days, but in this particular app I am looping through recordsets all over the place. What on earth am I doing wrong here?

    Thanks for any help - I've wasted too many hours on this already!

    dim Emp_ID
    dim HRComm
    dim param
    dim HRSQLConn
    dim HR_ConString - value not included but it is not the issue
    dim HRSQLRec

    Emp_ID = 19724

    set HRSQLConn = server.CreateObject("ADODB.Connection")
    set HRSQLRec = server.CreateObject("ADODB.Recordset")
    set HRComm = Server.CreateObject("ADODB.Command")

    HRComm.CommandType = adCmdStoredProc
    HRComm.CommandText = "GetEmpBudgetLineItems"

    set param = HRComm.Parameters
    param.append HRComm.CreateParameter("@Emp_ID",adInteger,adParamInput,,Emp_ID)

    HRSQLConn.Open(HR_ConString)

    set HRComm.ActiveConnection = HRSQLConn

    HRSQLRec = HRComm.Execute()
     
    '      Do While not HRSQLRec.EOF
           response.write HRSQLRec("ChargeToAccount") & "<br />"
           response.write HRSQLRec("TTL_CS_CD") & "<br />"
           response.write HRSQLRec("BDG_DTB_FTE_PCT") & "<br />"
           response.write HRSQLRec("BDG_DTB_SAL_PCT") & "<br />"
           response.write HRSQLRec("BDG_DTB_FTE") & "<br />"
           response.write HRSQLRec("BDG_DTB_SAL") & "<br />"
     
    '       HRSQLRec.MoveNext
    '      Loop

    I get good data back with the .EOF reference commented out; I get nothing if I reference .EOF.

    I have tried

    If HRSQLRec.EOF Then

    response.write "I'm at end of file"

    End If

    but that bombs too.

  • Re: Recordset object bombs

    Sep 17, 2012 03:03 AM|LiveFlakes|LINK

    use the set keyword and try. Also, check if the returned recordset is nothing before checking for EOF

    set HRSQLRec = HRComm.Execute()

     

    if NOT  HRSQLRec is Nothing then

          Do While not HRSQLRec.EOF
           response.write HRSQLRec("ChargeToAccount") & "<br />"
           response.write HRSQLRec("TTL_CS_CD") & "<br />"
           response.write HRSQLRec("BDG_DTB_FTE_PCT") & "<br />"
           response.write HRSQLRec("BDG_DTB_SAL_PCT") & "<br />"
           response.write HRSQLRec("BDG_DTB_FTE") & "<br />"
           response.write HRSQLRec("BDG_DTB_SAL") & "<br />"
     
           HRSQLRec.MoveNext
          Loop

    end if

  • Re: Recordset object bombs

    Sep 17, 2012 09:30 AM|pmgerholdt|LINK

    "Set" keyword did the trick.

     Thanks. Had to be something simple and obvious.