Easy A
Would you like to react to this message? Create an account in a few clicks or log in to continue.


 
HomeHome  Latest imagesLatest images  SearchSearch  RegisterRegister  Log inLog in  

Easy A :: recordset error in loop Edit-trash Useless :: TrashShare
 

 recordset error in loop

View previous topic View next topic Go down 
AuthorMessage
Aero
Member
Member
Aero

Posts : 6
Join date : 2011-05-20

recordset error in loop Empty
PostSubject: recordset error in loop   recordset error in loop EmptyThu Jun 23, 2011 6:01 am
recordset error in loop

I am getting this error on my second pass in the loop on line 147. error '80020009'

Line 147 is:
Code:

If rsGetNotes("Notes") = "" then

The complete loop is:
Code:

Do until vCount <= 0
sqlGetNotes = "select Q" & vCount & "notes as Notes from " & rsSections("tableid") & " where Q" & vCount & "notes <> " & "' '" & " and AuditID = '" & AuditID & "'"

rsGetNotes.Open sqlGetNotes, PADBConn
response.write sqlGetNotes & "<br />"

If rsGetNotes("Notes") = "" then
response.write "no notes <br />"
else
response.write "Notes : " & rsGetNotes("Notes") & "<br />"
end if

vCount = vCount - 1
sqlGetNotes = ""
rsGetNotes.Close
loop
Back to top Go down
Amore
Member
Member
Amore

Posts : 3
Join date : 2011-05-20

recordset error in loop Empty
PostSubject: Re: recordset error in loop   recordset error in loop EmptyThu Jun 23, 2011 6:02 am
You aren't checking to see if you got any results!
Code:

...
rsGetNotes.Open sqlGetNotes, PADBConn

If rsGetNotes.EOF Then
    Response.Write "No records found for vcount = " & vcount & " and AuditID = " & AuditID & "<hr/>" & vbNewline
Else
    If rsGetNotes("Notes") = "" then
        response.write "no notes <br />"
    else
        response.write "Notes : " & rsGetNotes("Notes") & "<br />"
    end if
End If
...
Back to top Go down
Amore
Member
Member
Amore

Posts : 3
Join date : 2011-05-20

recordset error in loop Empty
PostSubject: Re: recordset error in loop   recordset error in loop EmptyThu Jun 23, 2011 6:03 am
But your SQL is kind of silly.

You are asking to find notes that are not equal to a SINGLE SPACE:
Code:

....
" where Q" & vCount & "notes <> " & "' '" & " ...
...

If you want to only find notes that are non blank, the LOOK for non-blanks!

Code:

...
fld = "Q" & vCount & "notes"
sqlGetNotes = "select " & field & " as Notes from " & rsSections("tableid") _
    & " where " & fld & " <> '' and AuditID = '" & AuditID & "'"
...

Finally, I think you are making a mistake using a loop and getting one record each time.

I think you should simply get all the fields and then loop inside the record:
Code:

sqlGetNotes = "select * FROM " & rsSections("tableid") & " WHERE AuditID = '" & AuditID & "'"
Set rsGetNotes = PADBConn.Execute(sqlGetNotes)

If rsGetNotes.EOF Then
    Response.Write "No record for AuditID " & AuditID & "<hr>"
Else
    For vnum = vCount To 1 Step -1
        notes = Trim( rsGetNotes( "Q" & vnum & "notes" ) )
        If notes <> "" Then
            Response.Write vnum & ": " & notes & "<br>"
        End If
    Next
End If
rsGetNotes.Close
...
Back to top Go down
Aero
Member
Member
Aero

Posts : 6
Join date : 2011-05-20

recordset error in loop Empty
PostSubject: Re: recordset error in loop   recordset error in loop EmptyThu Jun 23, 2011 6:04 am
What I am doing is I have 17 tables, and each has a random number of qnnotes columns. For each table that has a qnnotes field with data I need to display it.

Thank you for the great solutions.
Back to top Go down
Sponsored content




recordset error in loop Empty
PostSubject: Re: recordset error in loop   recordset error in loop Empty
Back to top Go down
 

recordset error in loop

View previous topic View next topic Back to top 
Page 1 of 1

 Similar topics

-
» Simple for loop question
» Object Error... Why????
» Error in rails gem installation
» w3c validation - please help correcting the error
» Python error handling

Permissions in this forum:You cannot reply to topics in this forum
Easy A :: recordset error in loop Edit-trash Useless :: Trash-