asp/vbscipt

30 points?
the error message is correct - you must design the test to catch that reposnce

lol. trying to answer a few questions now to make it worth while. I'm soooo broke. lol.

On topic: I can't open the recordset to check if there are any because it dies if there are none!

Note that I'm not trying to retrieve a record that isn't there, it throws the error if the passed userid doesn't exist. (if it does, it opens fine and call pull the data.)

userid   details
1            abc
3            cde
4            rtd

If I use
strQuery = "Select * From Details Where UserID='2'
it won't open and the error is thrown.

Where are you setting the value of "strUserID" ?  Because you basically need to do an error check to ENSURE that it has a value and if you can't then you need to enclose your database request in an IF that checks that strUserID is set.

Of course it's there.

If I do: (where 4 IS in the database)
strQuery = "Select * From Details Where UserID="4"
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open strConnect
    rs.Open strQuery, conn, adOpenForwardOnly, adLockOptimistic, adCmdText
I can can get the details with rs("details")

If I do: (where 2 IS NOT in the database)
strQuery = "Select * From Details Where UserID='" & strUserID &"2"
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open strConnect
    rs.Open strQuery, conn, adOpenForwardOnly, adLockOptimistic, adCmdText
The rs.open dies with the error.

So, what's in the strUserID variable?  Have you output it to the screen?  That's how you debug - find out what the variable says!

strQuery = "Select * From Details Where UserID='" & strUserID & "2"
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open strConnect
    'Assuming this is ASP on a web page
    Response.Write "<br>strQuery = [" & strQuery & "]<br>strUserID= [" & strUserID & "]<br>"
    rs.Open strQuery, conn, adOpenForwardOnly, adLockOptimistic, adCmdText

Select allOpen in new window

If I request where UserID is 4 (or any other that IS IN the database) I can paint the details to to screen. BUT if UserID is 2 (is NOT in the table) I get an error trying to open the table/recordset.

strQuery = "Select * From Details Where UserID="2"
rs.Open strQuery, conn, adOpenForwardOnly, adLockOptimistic, adCmdText
produces this error:
<b>
Error Type:
ADODB.Field (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/User/staff.asp, line 101
</b>

It is in your first record.
You have to see if there is any record existing, if not then show message.

<%if not rs.eof then%>
<%=rs("Userid")%>
<%
else
response.Write"That ID Does not exist in our database"
end if%>

Good Luck
Carrzkiss

After spending all night trying to figure this out, I uninstalled/reinstalled visual studio this morning. Seems like some components were messed up (ADODB?) because reinstalling seems to have fixed it.

I wasn't trying to look at a record that didn't exist. I couldn't open an EMPTY recordset. Checking for bof or eof wasn't possible because it would error out in the attempt to simply open an empty recordset.

This is an intuitive shot in the dark at the cause: I suspect this may be an obscure bug in the adOpenForwardOnly parameter because in some cases, you can get a recordcount without a movelast in Access Basic, that means it may be looking ahead when the recordset opens. Since I have both Office 2007 and Access 2003 loaded, maybe it created an issue. But for all I know... it could be something as simple as the alignment of the moon and mars with pluto.

me thinks you don't want to give away your points :-)
I belive the solution suggested by carrzkiss has solved the problem

carrzkiss,

I've been doing this since 1992, so I know how to open a recordset. lol.
Of course you have to check to see if there are any records in the recordset (if rs.bof or rs.eof is true then it's empty) before you look at it via an assignment statement like strVar=rs("field")
HOWEVER, and this is where everyone is getting lost.

To look at EOF and BOF you must OPEN the recordset with
rs.Open strQuery ... (ok so far)
If the record in the WHERE clause exists then I can check EOF and BOF. No problem
BUT.... and here's the really important part!!!!!
If the record is not found (doesn't exist) in the table, as in my example above, then I was getting the error AS the recordset was open.
Right at the rs.Open strQuery ... line.
So checking BOF or EOF is impossible at this point. You actually have to get to the If rs.BOF or rs.EOF BREAKPOINT to check if records exist.
...
BTW, I use visual studio and attach the process to debug so I don't have to use response.write for too much.
 
Hope that clears it up.
 
Thanks.