Athmaus wrote:
Here is the code, it works on a fresh database that i setup, but it
is not working on the already established database wehre i put 2 new
columns in
If Session("login") = FALSE Then
Response.Redirect "http://www.yahoo.com"
Else
Dim myconn, verify, blnLoggedIn, user, pass, site, logged, objRS,
exceeded Set verify = Server.CreateObject("ADODB.Connection")
verify.open = "connection string"
Hopefully you are using a sqloledb connection string ...
http://www.aspfaq.com/show.asp?id=2126
Set myconn = Server.CreateObject("ADODB.Connection")
myconn.open = "connection string"
Why two connection objects? Are these separate database servers? If not,
only one connection is needed. Don't be wasteful of your network and server
rewources.
'Response.Write(Session("username"))
'Response.Write(Session("password"))
user = CStr(Session("username"))
pass = CStr(Session("password"))
exceeded = 5
Set objRS = myconn.execute("SELECT id, download, totaldl FROM
regfreeup WHERE username='" & user & "' AND pass='" & pass & "';")
If objRS.EOF Then '''NO RECORDS MATCH. USER DID NOT LOG IN CORRECTLY
blnLoggedIn = False
Bad technique here. Always close and destroy your ADO objects when finished
with them. The lines of code appearing after a redirect will NOT get
executed.
Response.Redirect "http://www.yahoo.com"
Else
If objRS("download") >= exceeded Then 'LOGGED IN AN ABNORMAL TIME
blnLoggedIn = false
Response.Redirect "http://www.google.com"
Else '''EVERYTHING PASSED PROCEEDE WITH DOWNLOAD
blnLoggedIn = True
verify.execute("UPDATE regfreeup set download = (download + 1) ,
totaldl = (totaldl + 1) WHERE username='" & user & "' AND pass='" &
pass & "';")
Response.Redirect "http://www.ps2.ign.com"
objRS.Close
Set objRS= Nothing
myconn.Close
Set myconn= Nothing
verify.Close
Set verify= Nothing
End If
End If
End If
My recommendations:
1. to facilitate debugging, comment out the redirects
2. Insert some response.write statements so you can follow the execution of
the code.
3. When using dynamic sql, assign your sql statements to variables so they
can be written to response for debugging
4. Use indenting
5. Use parameters
6. Use stored procedures to minimize the trips to the database
Here is how I would rewrite this code:
I would first create a stored procedure on your server, like this:
CREATE PROCEDURE VerifyUser (
@user varchar(50),
@pass varchar(50),
@limit int) AS
IF NOT EXISTS (SELECT * FROM regfreeup WHERE
username= @user AND pass = @pass)
RETURN 1
DECLARE @downloads int
SET @downloads = (SELECT download FROM regfreeup
WHERE username= @user AND pass = @pass)
IF @downloads > @limit
RETURN 2
UPDATE regfreeup set download = (download + 1) ,
totaldl= (totaldl + 1)
WHERE username= @user AND pass = @pass
IF @@ERROR =0
RETURN 0
ELSE
RETURN 3
Then, in ASP, I would use a Command object as follows
<%
Dim myconn, retVal, user, pass, site, logged, exceeded
dim sURL
If Session("login") = FALSE Then
sURL = "http://www.yahoo.com"
Response.Write "Not Logged In. <BR>"
Else
Set myconn = CreateObject("ADODB.Connection")
myconn.open = "connection string"
user = CStr(Session("username"))
pass = CStr(Session("password"))
exceeded = 5
set cmd=createobject("adodb.command")
arParms = array(user,pass)
cmd.commandtext="VerifyUser"
cmd.ActiveConnection = myconn
set params = cmd.Parameters
params.append cmd.CreateParameter("RETURN_VALUE", _
3,4)
params.append cmd.CreateParameter("@user", _
200,1,50,user)
params.append cmd.CreateParameter("@pass", _
200,1,50,pass)
params.append cmd.CreateParameter("@limit", _
3,1,,exceeded)
cmd.Execute ,,129
retVal = params(0).value
select case retVal
case 0
sURL="http://www.ps2.ign.com"
Response.Write "No problems. <BR>"
case 1
sURL = "http://www.yahoo.com"
Response.Write "No problems. <BR>"
case 2
sURL = "http://www.google.com"
Response.Write "Improper login. <BR>"
case 3
sURL = "http://www.microsoft.com"
Response.Write "The update failed. <BR>"
end select
set params=nothing
set cmd=nothing
myconn.close: set myconn=nothing
End If
Response.Write "Redirecting to " &
Server.htmlencode(sURL)
'Response.Redirect sURL
%>
When finished debugging, comment out the response.writes and uncomment the
redirect.
HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.