ASP errors migrating from Access to MSSQL

[bobbyrabbit]

I'm now receiving this error:

Microsoft VBScript runtime error '800a01a8'

Object required: ''

login.asp, line 9

Worked fine with Access. Can somebody help me tidy up my code. login.asp below. Many thanks......................

<%@ language = "VBScript"%>
<% Option Explicit %>
<!--#include file="connection.asp"-->
<%
If Request.Form("login") = "Login" then
Dim v_login_name,v_login_password 'variables to capture username and password
v_login_name = Trim(Request.Form("username"))
v_login_password = Trim(Request.Form("password"))
Con.Open connect_string
Rec.CursorType = 1
Rec.CursorLocation = 2
Rec.LockType = 3
Rec.ActiveConnection= Con
Qry = "SELECT * FROM Login " &_
"WHERE LoginUserName='"&v_login_name &"' "
Rec.Open Qry
If Rec.EOF then
Response.Redirect "login.asp?user=invalid"
Else
Dim compare
compare=StrComp(v_login_password,Rec("LoginPassword"))
If compare <> 0 then
Response.Redirect "login.asp?user=invalid"
ElsEif Rec("UserAuthLevel") = 0 then
Response.Redirect "login.asp?user=inactive"
Else
session("s_user") = Trim(Rec("UserFirstName"))&" "&Trim(Rec("UserLastName"))
session("s_AuthLevel") = Rec("UserAuthLevel")
session("s_branchid") = Rec("UserBranchID")
session("s_userid") = Rec("UserId")
Response.Redirect "default.asp"
End If
End If
Rec.Close
Con.Close
Set Rec = Nothing
Set Con = Nothing
End If

%>

 

[Tarwn]

I've noticed some strange isues in the past whenyou dim your variables in an include and then try to use them in your code. Based on the way they chose to process ASP includes, it shouldn't be a problem, so maybe this is something I am mis-remembering from long time ago. Try moving the Dim statement for your Connection and recordset object out to your main code and see if that changes anything.
I mention this because line 9 appears to bethe first reference to your Connection object.

Also, it isn't necessary to to do a recordset.Open. Instead of instantiating the Recordset, settings it's properties, etc you could simply do:
Set Rec = Con.Execute Qry
This executes the query and returns a new recordset object for you with the results. This is generally a bit faster then the .Open method (though not noticeably so).