Error: ADODB.Recordset error '800a0bcd' 3

charbrad · Oct 12, 2007

I have the below SQL statement in the beginning of my ASP page

Code:

MySQL = "SELECT * FROM ShipSummary (NOLOCK) where ShipDate = DateDiff(d, 0, GetDate()+1)"

If there are no records found the program stops and gives me an error: ADODB.Recordset error '800a0bcd'...this is because there are no records found that meet the date criteria.

My question is if there is a way around this. I have tried an 'IF' Statement, but this doesn't seem to be working for me...I am sure that I am doing something wrong.

Code:

SET MyRecordSet = SERVER.CREATEOBJECT("ADODB.RECORDSET")
MySQL = "SELECT * FROM ShipSummary (NOLOCK) where ShipDate = DateDiff(d, 0, GetDate()+1)"
MyRecordSet.OPEN MySQL, XXXXXX
MyArray = MyRecordSet.GETROWS()
MyRecordSet.CLOSE
FOR MyCursor = LBOUND(MyArray,2) TO UBOUND(MyArray,2)
IF MyArray(3,MyCursor) = Date()+1 then 
body = body + "<TR><TD BGCOLOR=""" & SHADE & """><B>" & "&nbsp;</TD><TD BGCOLOR=""" & SHADE & """><B>" & "</TD><TD BGCOLOR=""" & SHADE & """><B>" & 
"&nbsp;</TD><TD COLSPAN=4 BGCOLOR=""" & SHADE & """><B>"& MyArray(0,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & 
MyArray(1,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & MyArray(2,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & 
"""><B>" & MyArray(3,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & MyArray(4,MyCursor) & "</TD><TD BGCOLOR=""" & SHADE & """>"& 
"</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """></TD></TR>" & vbcrlf
END IF

Chopstik · Oct 12, 2007

You've got your If statement in the wrong place. You need to first check if you have a recordset and then do your processing (if necessary).

Code:

SET MyRecordSet = SERVER.CREATEOBJECT("ADODB.RECORDSET")
MySQL = "SELECT * FROM ShipSummary (NOLOCK) where ShipDate = DateDiff(d, 0, GetDate()+1)"
MyRecordSet.OPEN MySQL, XXXXXX
[COLOR=red]if not MyRecordSet is nothing then[/color]
MyArray = MyRecordSet.GETROWS()
MyRecordSet.CLOSE
FOR MyCursor = LBOUND(MyArray,2) TO UBOUND(MyArray,2)
[COLOR=green]'Not sure if this was your if statement to find out if you had records.  If so, then just delete.[/color]
IF MyArray(3,MyCursor) = Date()+1 then 
body = body + "<TR><TD BGCOLOR=""" & SHADE & """><B>" & "&nbsp;</TD><TD BGCOLOR=""" & SHADE & """><B>" & "</TD><TD BGCOLOR=""" & SHADE & """><B>" & 
"&nbsp;</TD><TD COLSPAN=4 BGCOLOR=""" & SHADE & """><B>"& MyArray(0,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & 
MyArray(1,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & MyArray(2,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & 
"""><B>" & MyArray(3,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & MyArray(4,MyCursor) & "</TD><TD BGCOLOR=""" & SHADE & """>"& 
"</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """></TD></TR>" & vbcrlf
END IF

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

charbrad · Oct 15, 2007

I put my if and end if statements in the right place and now I am getting the below error:

Microsoft VBScript compilation error '800a0400'
Expected statement
/reports/ShipSummary.asp, line 61

END IF
^

Code:

SET MyRecordSet = SERVER.CREATEOBJECT("ADODB.RECORDSET")
MySQL = "SELECT * FROM ShipSummary (NOLOCK) where ShipDate = DateDiff(d, 0, GetDate()+1)"
MyRecordSet.OPEN MySQL, XXXXXX
if not MyRecordSet is nothing then
MyArray = MyRecordSet.GETROWS()
MyRecordSet.CLOSE
FOR MyCursor = LBOUND(MyArray,2) TO UBOUND(MyArray,2)
'Not sure if this was your if statement to find out if you had records.  If so, then just delete.
IF MyArray(3,MyCursor) = Date()+1 then 
body = body + "<TR><TD BGCOLOR=""" & SHADE & """><B>" & "&nbsp;</TD><TD BGCOLOR=""" & SHADE & """><B>" & "</TD><TD BGCOLOR=""" & SHADE & """><B>" & 
"&nbsp;</TD><TD COLSPAN=4 BGCOLOR=""" & SHADE & """><B>"& MyArray(0,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & 
MyArray(1,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & MyArray(2,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & 
"""><B>" & MyArray(3,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & MyArray(4,MyCursor) & "</TD><TD BGCOLOR=""" & SHADE & """>"& 
"</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """></TD></TR>" & vbcrlf
[b]END IF  ....line 61[/b]

Any suggestions?

thompom · Oct 15, 2007

i always use

Code:

if not MyRecordSet.eof then

..also are you missing another endif?

charbrad · Oct 15, 2007

No...in my post I forgot to take out the second "IF" statemtnt what I have looks like:

Code:

SET MyRecordSet = SERVER.CREATEOBJECT("ADODB.RECORDSET")
MySQL = "SELECT * FROM ShipSummary (NOLOCK) where ShipDate = DateDiff(d, 0, GetDate()+1)"
MyRecordSet.OPEN MySQL, XXXXXX
if not MyRecordSet.eof then
MyArray = MyRecordSet.GETROWS()
MyRecordSet.CLOSE
FOR MyCursor = LBOUND(MyArray,2) TO UBOUND(MyArray,2)
body = body + "<TR><TD BGCOLOR=""" & SHADE & """><B>" & "&nbsp;</TD><TD BGCOLOR=""" & SHADE & """><B>" & "</TD><TD BGCOLOR=""" & SHADE & """><B>" & 
"&nbsp;</TD><TD COLSPAN=4 BGCOLOR=""" & SHADE & """><B>"& MyArray(0,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & 
MyArray(1,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & MyArray(2,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & 
"""><B>" & MyArray(3,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & MyArray(4,MyCursor) & "</TD><TD BGCOLOR=""" & SHADE & """>"& 
"</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """></TD></TR>" & vbcrlf
END IF  ....line 61

I am still getting the below error regarding "END IF"

Microsoft VBScript compilation error '800a0400'
Expected statement
/reports/ShipSummary.asp, line 61

END IF
^

Chopstik · Oct 17, 2007

I believe that this line in your most recent post:

Code:

if not MyRecordSet.eof then

should read as follows:

Code:

if not MyRecordSet [COLOR=red]is nothing[/color] then

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

charbrad · Oct 17, 2007

I've changed it to

Code:

if not MyRecordSet is nothing then

I am still getting the error regarding 'END IF' any suggestions?

Chopstik · Oct 17, 2007

Try a Next statement before your End If statement. You have a For with no Next. Something like this:

Code:

SET MyRecordSet = SERVER.CREATEOBJECT("ADODB.RECORDSET")
MySQL = "SELECT * FROM ShipSummary (NOLOCK) where ShipDate = DateDiff(d, 0, GetDate()+1)"
MyRecordSet.OPEN MySQL, XXXXXX
if not MyRecordSet.eof then
MyArray = MyRecordSet.GETROWS()
MyRecordSet.CLOSE
FOR MyCursor = LBOUND(MyArray,2) TO UBOUND(MyArray,2)
body = body + "<TR><TD BGCOLOR=""" & SHADE & """><B>" & "&nbsp;</TD><TD BGCOLOR=""" & SHADE & """><B>" & "</TD><TD BGCOLOR=""" & SHADE & """><B>" & 
"&nbsp;</TD><TD COLSPAN=4 BGCOLOR=""" & SHADE & """><B>"& MyArray(0,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & 
MyArray(1,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & MyArray(2,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & 
"""><B>" & MyArray(3,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & MyArray(4,MyCursor) & "</TD><TD BGCOLOR=""" & SHADE & """>"& 
"</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """></TD></TR>" & vbcrlf
[COLOR=red]Next[/color]
END IF

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

charbrad · Oct 17, 2007

Chopstick,

I put the next before the "END IF" and I am getting a 'unexpected next' error...Microsoft VBScript compilation error '800a041f'. I do have a NEXT after the "END IF" which is for the "FOR".

Chopstik · Oct 17, 2007

You should only have one NEXT statement and it must be contained within your IF statement since that is where your FOR loop begins. If you place the NEXT outside of your IF statement, then it will fail because there is no FOR beginning to it. Does this make sense?

Code:

[COLOR=green]'This is how it should look.  It hits your IF statement and, if true, then goes through your FOR loop.  Else, it skips the loop entirely.[/color]
If var > 0 then
  For var1 = 0 to var
    'Do this
  Next
End If

[COLOR=green]'What I think you have right now which does not work because you have the NEXT statement for a loop that doesn't exist if the IF statement is false.[/color]
If var > 0 then
  For var1 = 0 to var
    'Do this
End If
  Next

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

charbrad · Oct 18, 2007

Chopstik:

Thanks for your help & patience. I have made the changes as you noted above and you were right...my NEXT was in the wrong place. Even though I made the changes I don't understand why I am back to my original problem:

I added the "NEXT" before my "END IF" and now it brings me back to the original error to let me know that no records are found.

Code:

"ADODB.Recordset error '800a0bcd' 

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

How can I get pass this error and get the records that are available to display?

Chopstik · Oct 18, 2007

At what line does that happen? Realistically, you should first be checking that you have a recordset object returned before you do anything with it (which appears to be your error right now). If you'll post the section where the error occurs (and the code surrounding it), that should help to find your problem.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

charbrad · Oct 18, 2007

I checked and there are no records to display for this date criteria. I guess my question is why doesn't it display those records that are available. There are additional lines of code that produce valid output. Below is the code that is giving the problem. The error is occurring at line 29.

Code:

SET MyRecordSet = SERVER.CREATEOBJECT("ADODB.RECORDSET")
MySQL = "SELECT * FROM ShipSummary (NOLOCK) where ShipDate = DateDiff(d, 0, GetDate()+1)"
MyRecordSet.OPEN MySQL, XXXXXX
if not MyRecordSet is nothing then
[b][COLOR=red]MyArray = MyRecordSet.GETROWS()---line 29[/color][/b]
MyRecordSet.CLOSE
FOR MyCursor = LBOUND(MyArray,2) TO UBOUND(MyArray,2)
body = body + "<TR><TD BGCOLOR=""" & SHADE & """><B>" & "&nbsp;</TD><TD BGCOLOR=""" & SHADE & """><B>" & "</TD><TD BGCOLOR=""" & SHADE & """><B>" & 
"&nbsp;</TD><TD COLSPAN=4 BGCOLOR=""" & SHADE & """><B>"& MyArray(0,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & 
MyArray(1,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & MyArray(2,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & 
"""><B>" & MyArray(3,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & MyArray(4,MyCursor) & "</TD><TD BGCOLOR=""" & SHADE & """>"& 
"</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """></TD></TR>" & vbcrlf
NEXT
END IF

gmmastros · Oct 18, 2007

I would suggest that you use white-space a little more. It makes the code a lot easier to read. Regardless, while I always check for the recordset object to be = Nothing, I also check to make sure there are records before processing it. Like this...

Code:

SET MyRecordSet = SERVER.CREATEOBJECT("ADODB.RECORDSET")
MySQL = "SELECT * FROM ShipSummary (NOLOCK) where ShipDate = DateDiff(d, 0, GetDate()+1)"
MyRecordSet.OPEN MySQL, XXXXXX

if not MyRecordSet is nothing then
    [!]If Not MyRecordset.Eof Then[/!]
        MyArray = MyRecordSet.GETROWS()---line 29
        MyRecordSet.CLOSE

        FOR MyCursor = LBOUND(MyArray,2) TO UBOUND(MyArray,2)
            body = body + "<TR><TD BGCOLOR=""" & SHADE & """><B>" & "&nbsp;</TD><TD BGCOLOR=""" & SHADE & """><B>" & "</TD><TD BGCOLOR=""" & SHADE & """><B>" &
            "&nbsp;</TD><TD COLSPAN=4 BGCOLOR=""" & SHADE & """><B>"& MyArray(0,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" &
            MyArray(1,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & MyArray(2,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE &
            """><B>" & MyArray(3,MyCursor) & "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """><B>" & MyArray(4,MyCursor) & "</TD><TD BGCOLOR=""" & SHADE & """>"&
            "</TD><TD ALIGN=""CENTER"" BGCOLOR=""" & SHADE & """></TD></TR>" & vbcrlf
        NEXT
    [!]End If
    Set MyRecordSet = Nothing[/!]
END IF

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Chopstik · Oct 18, 2007

Good catch, George... [thumbsup]

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

charbrad · Oct 18, 2007

Thank you both....have a star!!!!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Error: ADODB.Recordset error '800a0bcd' 3

charbrad

Technical User

Chopstik

Technical User

charbrad

Technical User

thompom

Technical User

charbrad

Technical User

Chopstik

Technical User

charbrad

Technical User

Chopstik

Technical User

charbrad

Technical User

Chopstik

Technical User

charbrad

Technical User

Chopstik

Technical User

charbrad

Technical User

gmmastros

Programmer

Chopstik

Technical User

charbrad

Technical User

Similar threads

Part and Inventory Search

Sponsor