Recordset error 800a0bb9

I know, I know this is really an ASP question but I'm thinking that you db gurus write some frontend code too. I have an ASP script (see snippet) that connects to my SQL Server 7 db (see connect string below).

16 sqlUser = "SELECT instructorId, schoolId, salutation, firstName, lastName, phone, email " & _
17 "FROM tblInstructors " & _
18 "WHERE (((userName)='"&Request.Form("userName")&"') AND ((password)='"&Request.Form("password")&"'))"
19
20 Set rsUser = Server.CreateObject("ADODB.RecordSet")
21 rsUser.Open sqlUser, oConn

This code runs fine as long as I key in a valid username and password. If I key in a bad username and password combo the db returns the following to my browser:

ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/loginProc.asp, line 21

I tested by writing the finished sql to my browser and pasting it into the Enterprise manager and it runs as expected, I get a record with valid credentials and no record otherwise (but no error).

I'm using SQL Server v7, can anyone shed any light on this? thanks

connect string:

"Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=GradeTrax;" & _
"User ID=sa;" & _
"Password=pword"the db returns the following to my browser:

Ok maybe its not the db returning this message but ADO. Still does anyone have any experience with this?|||What's the line immediately following 21 (empty lines excluded)?

If you're attempting to pull a value from a recordset that is empty, you'll generate an error. Always make sure you're recordset actually contains records:

If NOT rsUser.BOF AND NOT rsUser.EOF Then
'User is valid
Else
'User is invalid
End If|||The next line I'm checking the status of EOF, however the line that is kicking the error is 21, so line 22 is never being processed.|||You don't need all the parens.. try:

"WHERE userName = '" & Request.Form("userName") & "' AND password = '" & Request.Form("password") & "'"

As a note, this will make both the userName and password fields case sensitive. If you want to make just the userName case insensitive, use the keyword LIKE in place of the "=".|||Thanks Seppuku,
This is really becoming very irritating. I've tried what you said and have in fact tried numerous variations, also using the Microsoft KB Article 235892 as guide and still can't get this to work. I've done response.write(sql) and pasted results here:

SELECT instructorId, schoolId, salutation, firstName, lastName, phone, email FROM tblInstructors WHERE userName LIKE 'dude' AND password LIKE 'badpassword'

again I'm convinced that this is not an SQL thing but a ADO thing, and what makes me mad is that it works as long as the db returns at least one record and fails if no records match criteria.

Any other info would be great.|||to all you coding comrads. I appologize for starting this thread. I stink as a programmer, I want to pump gas for a living or mow lawn or flip burgers or something.

I had a piece of code further down that did a redirect if login failed that looped back on the same page instead of redirecting to the login page. My error message came when the page was loaded the second time with the form collection null. I hate it when computers do what you tell them to and not what you want them to.|||PEBCAK - Problem Exists Between Chair and Keyboard.|||I hate it when computers do what you tell them to and not what you want them to.Me too, but the darned things keep right on doing it no matter how often I beat them!

-PatP|||PEBCAK - Problem Exists Between Chair and Keyboard.I like that! We call them id10t errors.

-PatP|||Some great jargon came from early editions of WIRED magazine... PEBCAK was one of those.. unfortunately, they've fizzled out...