asp/vbscipt

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
                                              

1:
2:
3:
4:
5:
6:

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