BOF-EOF

Consider the following code which retrieves records from a MS-Access
database table:

<%
Dim objConn
Set objConn=Server.CreateObject("ADODB.CONNECTION")
'open connection using ConnectionString

Dim strSQL1
strSQL1="SELECT......"

Dim objRS1
Set objRS1=Server.CreateObject("ADODB.RECORDSET")
objRS1.Open strSQL1,objConn,adOpenKeyset

If Not(objRS1.EOF)
Response.Write(objRS1("Col1"))
Else
Dim strSQL2
strSQL2="SELECT....."

Dim objRS2
Set objRS2=Server.CreateObject("ADODB.RECORDSET")
objRS2.Open strSQL2,objConn

Response.Write(objRS2("Col1"))

objRS2.Close
Set objRS2=Nothing
End If

Response.Write(objRS1("Col2"))
%>

Note that both the SQL queries will retrieve only ONE record. Now when
I execute the above code, ASP generates the

Exception occured.

error without pointing to any line. What could be the cause of the
error? I guess it could be because of the last Response.Write line
which spits out the value of objRS1("Col2") but I don't understand
what could be the reason for the last Response.Write line to generate
the above error.

If I add the following line

objRS1.MoveFirst

immediately after the End If line & just before the last
Response.Write line, then the error changes to

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

pointing to the objRS1.MoveFirst line. Can someone please point out
what's wrong with the above code?

Re: BOF-EOF

rn5a@rediffmail.com wrote on 11 Apr 2007 17:14:41 -0700:

> Consider the following code which retrieves records from a MS-Access
> database table:
>
> <%
> Dim objConn
> Set objConn=Server.CreateObject("ADODB.CONNECTION")
> 'open connection using ConnectionString
>
> Dim strSQL1
> strSQL1="SELECT......"
>
> Dim objRS1
> Set objRS1=Server.CreateObject("ADODB.RECORDSET")
> objRS1.Open strSQL1,objConn,adOpenKeyset
>
> If Not(objRS1.EOF)
> Response.Write(objRS1("Col1"))
> Else
> Dim strSQL2
> strSQL2="SELECT....."
>
> Dim objRS2
> Set objRS2=Server.CreateObject("ADODB.RECORDSET")
> objRS2.Open strSQL2,objConn
>
> Response.Write(objRS2("Col1"))
>
> objRS2.Close
> Set objRS2=Nothing
> End If


The following line will cause an error is objRS1 is empty

> Response.Write(objRS1("Col2"))
> %>

> pointing to the objRS1.MoveFirst line. Can someone please point out
> what's wrong with the above code?


Hopefully it'll be obvious now why it errors. Also, you should check objRS2
isn't empty before writing it's Col1 value too.

Dan