Objects are of the wrong type, are out of acceptable range or are in conflict

[igolf75]

Can someone help me please. I am able to query the database, but when I attempt to do an update on the recordset I get the "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." message on the following line:

objUserRS.Open strUserSQL, objUserConn, adOpenKeyset, adLockPessimistic, adCmdText

When I remove the parameters for the recordset after the connection object I get an error stating the recordset is not updateable - see line in yellow.

Here is the code:

response.Buffer = true
' BEGIN USER CONSTANTS
Dim CONN_STRING
Dim CONN_USER
Dim CONN_PASS
dim strUserSQL
dim objUserConn
dim objUserRS
dim attempts
dim fname
dim lname

response.Cookies("Login")("ses") = "True"
un = request.Form("un")

' Create and open our connection
Set objUserConn = Server.CreateObject("ADODB.Connection")
objUserConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("SummitPump.mdb")

strUserSQL = "SELECT * FROM Users WHERE User='" & request.Form("un") & "'"

Set objUserRS = Server.CreateObject("ADODB.Recordset")
objUserRS.Open strUserSQL, objUserConn, adOpenKeyset, adLockPessimistic, adCmdText
attempts = objUserRS("attempts")
fname = objUserRS("fname")


'determine if first time logging in...if so, force them to change password and PIN
if objUserRS("firsttime") = true then
if objUserRS("password") = "password" then
response.Redirect ("newlogin.asp?un=" & un)
else
response.Redirect ("error.asp?av=1")
end if
else
response.Cookies("Login")("un") = request.Form("un")
response.Cookies("Login").expires = date + 365
dim objUserCmd
if request.Form("save") = "on" then
response.Cookies("Login")("pw") = request.Form("pw")
response.Cookies("Login")("saved") = "Yes"
response.Cookies("Login").expires = date + 365
else
response.Cookies("Login")("pw") = ""
response.Cookies("Login")("saved") = "No"
end if
'determine if username active - username is inactive after 3 failed login attempts
if objUserRS("active") = 1 then
if objUserRS("password") = request.Form("pw") then
strUserSQL = "UPDATE Users Set Attempts = 0 WHERE User = '" & un & "'"
'create the command object
'set objUserCmd = server.CreateObject("ADODB.Command")
objUserRS("Attempts") = request.Form("pw")
objUserRS.update objUserRS.close
response.Write (objUserRS("FName"))
'response.Redirect ("User.asp?nm=" & name & "")
else
'determine if this was third attempt and if so, make username in active
if objUserRS("attempts") = 2 then
strUserSQL = "UPDATE Users Set Attempts = " & objUserRS("attempts") + 1 & ", Active = 0 WHERE UserID = '" & un & "'"
'create the command object
set objUserCmd = server.CreateObject("ADODB.Command")
'set the command object properties
set objUserCmd.ActiveConnection = objUserConn
objUserCmd.CommandText = strUserSQL
'objUserCmd.CommandType = adCmdText

'execute the command
objUserCmd.Execute
response.Redirect ("error.asp?av=0")
else
attempts = attempts + 1
strUserSQL = "UPDATE Users Set Attempts = " & objUserRS("attempts") + 1 & " WHERE UserID = '" & un & "'"
'create the command object
set objUserCmd = server.CreateObject("ADODB.Command")
'set the command object properties
set objUserCmd.ActiveConnection = objUserConn
objUserCmd.CommandText = strUserSQL
'objUserCmd.CommandType = adCmdText

'execute the command
objUserCmd.Execute
'response.Redirect ("error.asp?av=1&no= & attempts & "")")
response.Redirect ("error.asp?av=1&no=" & attempts)
end if
end if
' else
' 'username is not active
' response.Write "Your account is not active. Please contact James to reset your account."
End if
end if

' Close DB objects and free variables
objUserRS.Close
Set objUserRS = Nothing
objUserConn.Close
set objUserConn = Nothing
set objUserCmd = nothing

[Old Pedant]

It's because those parameters (adOpenKeyset, adLockPessimistic, adCmdText) have *NOT* been defined in your page.

You can define them by putting

<!-- #include file="adovbs.inc" -->

at the top of your page (*outside* the <%...%> tags) and then making sure that indeed that file is in your directory. If not, you should be able to download it from MicroSlop.

But...

You really don't need to. First of all, adOpenKeyset is not the best cursor type to use for this. adOpenStatic is.

And, secondly, you really don't want to use adLockPessimistic. That says you are *assuming* that you will be modifying the record. But you won't do so unless this is a failed login attempt. Of use adLockOptimistic.

And, finally, adOpenStatic is simply the number 3 and adLockPessimistic is also just the number 3.

So...

objUserRS.Open strUserSQL, objUserConn, 3, 3

Will work fine.

HOWEVER...

WHY do you use ADO to update the number of attempts to 1 and then to 2 but instead use an UPDATE SQL command to change the record if the number of attempts is 3???

Why not simply *alway* use UPDATE SQL command? It's much better coding practice than using an ADO writable recordset.

How about simplifying the h3ll out of that code?

[Old Pedant]

<%
response.Cookies("Login")("ses") = "True"
un = request.Form("un")

' Create and open our connection
Set objUserConn = Server.CreateObject("ADODB.Connection")
objUserConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("SummitPump.mdb")

strUserSQL = "SELECT * FROM Users WHERE User='" & request.Form("un") & "'"

Set objUserRS = objUserConn.Execute( strUserSQL ) ' much more efficient!

attempts = objUserRS("attempts")
fname = objUserRS("fname")

'determine if first time logging in...if so, force them to change password and PIN
if objUserRS("firsttime") = true then
    if objUserRS("password") = "password" then 
        gowhere = "newlogin.asp?un=" & un
    else
        gowhere = "error.asp?av=1"
    end if
    objUserConn.Close
    Response.Redired gowhere
End If ' you don't need an ELSE because Response.Redirect STOPS the page IN ITS TRACKS!

response.Cookies("Login")("un") = request.Form("un")
response.Cookies("Login").expires = date + 365
if request.Form("save") = "on" then
    response.Cookies("Login")("pw") = request.Form("pw")
    response.Cookies("Login")("saved") = "Yes"
    response.Cookies("Login").expires = date + 365
else
    response.Cookies("Login")("pw") = ""
    response.Cookies("Login")("saved") = "No"
end if
'determine if username active - username is inactive after 3 failed login attempts
if objUserRS("active") = 1 then
    if objUserRS("password") = request.Form("pw") then
        sqlset = "Attempts=0"
        gowhere = "User.asp?nm=" & name 
    elseif attempts = 2 then ' this *MUST* be an ELSEIF
        ' if this was third attempt, make username inactive
        sqlset = "Attempts=3, Active = 0"
        gowhere = "error.asp?av=0"
    else
        sqlset = "Attempts=Attempts+1"
        gowhere "error.asp?av=1&no=" & attempts
    end if
    strUserSQL = "UPDATE Users Set " & sqlset & " WHERE UserID = '" & un & "'"
    objUserConnn.Execute strUserSQL
    objUserConn.Close
    Response.Redirect gowhere
End If ' again, no ELSE needed as response.redirect ends things NOW

' If we get here, username is not active
Your account is not active. Please contact James to reset your account."
objUserConn.Close
%>
<html><body>
<h3>Your account is not active<br/><br/>Please contact James to reset your account.</h3>
</body></html>

Points made:

(1) There is NO REASON AT ALL to use an ADODB.Command object if you are creating a parameterized query. Which you aren't. ADODB.Connection.Execute does the same thing and is faster and simpler.

(2) Technically, it's an error to put parentheses around the argument to Response.Redirect and Response.Write. It works, but it does nothing but make a minor slow-down on your page.

(3) There no reason to pass in Attempts+1 from the ASP code. SQL and add 1 to a value quite simply, as shown.

(4) There's no real reason to close a read-only recordset. Closing the connection will automatically shut down all you care about. And you can set the objects to NOTHING, but it's really pointless for a page this small.

(5) As noted in comments in the code, a RESPONSE.REDIRECT is the same thing as "EXIT FROM THIS PAGE RIGHT NOW". So if you do a response.redirect at the end of an "if" condition, there's no need to use ELSE. By definition if you get to the line after the END IF (after the Redirect) you did *NOT* do the redirect.

(6) On the other hand, if you do RESPONSE.REDIRECT then you will *NEVER GET TO* your code at the end of the page that closes the connection! So you can see that I close it just before the redirect. (This also demonstrates that you don't *REALLY* need to close your connections. 90% of ASP programmers make the same mistake you did, and if closing connections were necessary you'd be crashing servers left and right with all the response.redirects happening.)

[igolf75]

Thanks Old Pendant!!! Great feedback, I definitely learned quite a few things. I will let you know how this works.

[miranda]

Instead of using an include to the adovbs.inc file (which requires you to have it on your site somewhere) just include this which points to the registry
<!--METADATA TYPE="typelib" UUID="00000205-0000-0010-8000-00AA006D2EA4" NAME="ADODB Type Library" -->