Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2008
    Posts
    10

    Angry Unanswered: Phantom missing quotation mark...

    Hi,

    I keep getting an error message from my code saying that there's a missing quotation mark... but I can't for the life of me see where it is! Am I missing something really obvious?

    Error:

    Microsoft VBScript runtime error '800a01a8'
    Object required: ''
    ****************/rewards.asp, line 109

    Code:
    strUserName = Replace(Session("userName"), "'", "''")
    dim conn
    
    if Request.querystring("mode") = "dopledge" then
    if request.form("amount") <> "" then
    
    conn.Provider="Microsoft.Jet.OLEDB.4.0"        '****LINE 109 HERE***
    set rs=Server.CreateObject("ADODB.Recordset")
    conn.Open "**\*****\*****\*******\db\data.mdb"
    
    SQL= "UPDATE bstat SET amount=" & CLng(request.form("amount")) & " WHERE pname='" & strUserName & "'"
    
    rs.open SQL,conn
    conn.close
    
    else
    	response.write("Error! No Value!")
    end if
    response.Redirect("http://brainfishes.brinkster.net/AOD/home/rewards.asp?mode=view")
    end if
    There is, much, much more code, but I've pulled out an extract for where I think the problem lies... I can post the whole page of code if neccisary.

    Thanks,

    Brainfishes.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You haven't created the connection object yet
    Code:
    set conn=Server.CreateObject("ADODB.Connection")
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2008
    Posts
    10
    I've now added in the code to create the connection object, but the problem persists. It seems to be some sort of syntax error, but I can't see where it is! Gah!

    Can anyone see the problem?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh, and you don't use a recordset to execute UPDATE statements; you do that directly on the connection.
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2008
    Posts
    10
    It works on my other page... I use the same update code elsewhere and it works fine. But in any case, this isn't solving my problem... do you have any idea why I'm getting the error message above?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you post some code that does work then?
    Generally an UPDATE should be performed like this
    Code:
    <%
    'Create connection object
    set conn=Server.CreateObject("ADODB.Connection")
    
    'Set provider
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    
    'Open connection
    conn.Open
    
    'SQL Command
    sql = "UPDATE someTable SET someValue = 'foo'"
    
    'Execute command
    conn.Execute sql
    
    'Close connection
    conn.Close
    
    'Tidy up
    Set conn = Nothing
    %>
    Your error message
    Microsoft VBScript runtime error '800a01a8'
    Object required: ''
    ****************/rewards.asp, line 109
    Suggested to me that the connection object had yet to be created correctly, hence my initial suggestion. If I were you I would try the method I have suggested above and see if you get the same problem.
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2008
    Posts
    10

    Haha!

    It woorks!!!

    Thanks so much for your help, I finally got it working and I think I'm a step forward in understanding how this recordset thing works!

    My code looks like this now... I'll post the whole lot, just to make you frown and shake your head at the mess of it, hehe!

    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    <link href="../css/styles.css" rel="stylesheet" type="text/css" />
    </head>
    
    <body bgcolor="#000000">
    <body valign="top" align="center">
    <table align="left" valign="top" width="700">
    <tr><td valign="top">&nbsp;</td></tr>
    <tr><td valign="top">&nbsp;</td></tr>
    <tr><td valign="top" align="center"><b>Rewards Bank</b><br><br></td></tr>
    <tr>
    <td valign="top" align="left">
    
    <%
    strUserName = Replace(Session("userName"), "'", "''")
    dim conn
    dim btot
    
    If Request.querystring("mode") = "view" then
    
    
    	set conn=Server.CreateObject("ADODB.Connection")
    	conn.Provider="Microsoft.Jet.OLEDB.4.0"
    	conn.Open "*:\***\*****\*********\db\data.mdb"
    	set rs=Server.CreateObject("ADODB.Recordset")
    	rs.open "SELECT amnt FROM bank WHERE bid=1",conn
    	
    	If Not rs.EOF Then
    		rs.MoveFirst
    		Do While Not rs.EOF
    			btot = rs.fields("amnt")
    			rs.MoveNext
    		loop
    		rs.MoveFirst
    	end if
    	conn.close
    
    	response.write("<br>The total amount in the rewards bank is: <b>" & btot & " Credits</b></td></tr>")
    	response.write("<tr><td align=""left"">&nbsp;</td></tr>")
    	response.write("<tr><td align=""center"">&nbsp;</td></tr>")
    	response.write("<tr><td align=""center""><b>Link Clicks</b><br><br></td></tr>")
    	response.write("<tr><td align=""left"">")
    	response.write("<table align=""center"" border=""0"" cellpadding=""0"" cellspacing=""0"" width=""400"">")
    	response.write("<tr><td align=""center"" width=""200"">Name</td>")
    	response.write("<td align=""center"" width=""200"">Link Clicks</td></tr>")
    	response.write("<tr><td align=""center"">&nbsp;</td></tr>")
    	conn.Open "*:\***\*****\*********\db\data.mdb"
    	set rs=Server.CreateObject("ADODB.Recordset")
    	rs.open "SELECT * FROM clicks ORDER BY clks DESC",conn
    	
    	If Not rs.EOF Then
    		rs.MoveFirst
    		Do While Not rs.EOF
    			response.write("<tr><td align=""center"" width=""200"">" & rs.fields("cname") & "</td>")
    			response.write("<td align=""center"" width=""200"">" & rs.fields("clks") & "</td></tr>")
    			rs.MoveNext
    		loop
    		rs.MoveFirst
    	end if
    	conn.close
    	
    	response.write("</table></td></tr>")
    	response.write("<tr><td align=""left"">&nbsp;</td></tr>")
    	response.write("<tr><td align=""center""><b>Pledges</b><br></td></tr>")
    	response.write("<tr><td align=""left"">")
    	response.write("<table align=""center"" border=""0"" cellpadding=""0"" cellspacing=""0"" width=""400"">")
    	response.write("<tr><td align=""center"" width=""200"">Name</td>")
    	response.write("<td align=""center"" width=""200"">Amount</td></tr>")
    	response.write("<tr><td align=""center"">&nbsp;</td></tr>")
    	conn.Open "*:\***\*****\*********\db\data.mdb"
    	set rs=Server.CreateObject("ADODB.Recordset")
    	rs.open "SELECT * FROM bstat ORDER BY amount DESC",conn
    	
    	If Not rs.EOF Then
    		rs.MoveFirst
    		Do While Not rs.EOF
    			response.write("<tr><td align=""center"" width=""200"">" & rs.fields("pname") & "</td>")
    			response.write("<td align=""center"" width=""200"">" & rs.fields("amount") & "</td></tr>")
    			rs.MoveNext
    		loop
    		rs.MoveFirst
    	end if
    	
    	conn.close
    	response.write("</table>")
    	response.write("<br><br><a href=""rewards.asp?mode=pledge""><b>Pledge Credits Now</b></a>")
    	If Session("userAdmin") = 1 then
    		response.write("</td></tr><tr><td align=""center"">&nbsp;</td></tr><tr><td align=""center"">")
    		response.write("<a href=""rewards.asp?mode=resetbank"">Reset Bank</a><br>")
    		response.write("<a href=""rewards.asp?mode=resetlinks"">Reset Links</a><br>")
    	end if
    end if
    
    If Request.querystring("mode") = "pledge" then
    
    	response.write("<form method=""post"" action=""rewards.asp?mode=dopledge"">")
    	response.write("<table align=""left"" border=""0"" cellpadding=""0"" cellspacing=""0"" width=""200""><tr>")
    	response.write("<td align=""left"" width=""100"">Amount: <input name=""amount""></td>")
    	response.write("<td align=""left"" width=""100""><input name=""p_name"" type=""hidden"" value=""" & strUserName & """></td></tr>")
    	response.write("<tr><td colspan=""2"" align=""center""><input type=""submit"" value=""Pledge Amount""></td></tr>")
    	response.write("<tr><td align=""center"" colspan=""2"">&nbsp;</td></tr></table></form>")
    end if
    
    if Request.querystring("mode") = "resetbank" then
    	set conn=Server.CreateObject("ADODB.Connection")
    	conn.Provider="Microsoft.Jet.OLEDB.4.0"
    	conn.Open "*:\***\*****\*********\db\data.mdb"
    	
    	sql = "UPDATE bstat SET amount = 0"
    	
    	conn.Execute sql
    	conn.Close
    
    	set conn=Server.CreateObject("ADODB.Connection")	
    	conn.Provider="Microsoft.Jet.OLEDB.4.0"
    	set rs=Server.CreateObject("ADODB.Recordset")
    	conn.Open "*:\***\*****\*********\db\data.mdb"
    	
    	strUpdate = "Update bank SET amnt=0 WHERE bid = 1"
    
    	rs.open strUpdate,conn
    	conn.close
    	response.Redirect("http://brainfishes.brinkster.net/AOD/home/rewards.asp?mode=view")
    end if
    
    if Request.querystring("mode") = "resetlinks" then
    	set conn=Server.CreateObject("ADODB.Connection")
    	conn.Provider="Microsoft.Jet.OLEDB.4.0"
    	conn.Open "*:\***\*****\*********\db\data.mdb"
    	
    	sql = "UPDATE clicks SET clks = 0"
    	
    	conn.Execute sql
    	conn.Close
    	response.Redirect("http://brainfishes.brinkster.net/AOD/home/rewards.asp?mode=view")
    end if
    
    if Request.querystring("mode") = "dopledge" then
    
    if request.form("amount") <> "" then
    	dim holder
    	
    	set conn=Server.CreateObject("ADODB.Connection")
    	conn.Provider="Microsoft.Jet.OLEDB.4.0"
    	conn.Open "*:\***\*****\*********\db\data.mdb"
    	set rs=Server.CreateObject("ADODB.Recordset")
    	rs.open "SELECT amount FROM bstat WHERE pname='" & strUserName & "'",conn
    	
    	If Not rs.EOF Then
    		rs.MoveFirst
    		Do While Not rs.EOF
    			holder = rs.fields("amount")
    			rs.MoveNext
    		loop
    		rs.MoveFirst
    	end if
    	conn.close
    	
    	holder = holder + CLng(request.form("amount"))
    	set conn=Server.CreateObject("ADODB.Connection")
    	conn.Provider="Microsoft.Jet.OLEDB.4.0"
    	conn.Open "*:\***\*****\*********\db\data.mdb"
    	
    	sql = "UPDATE bstat SET amount = " & holder & " WHERE pname='" & strUserName & "'"
    	
    	conn.Execute sql
    	conn.Close
    	
    	conn.Provider="Microsoft.Jet.OLEDB.4.0"
    	conn.Open "*:\***\*****\*********\db\data.mdb"
    	set rs=Server.CreateObject("ADODB.Recordset")
    	rs.open "SELECT amnt FROM bank WHERE bid=1",conn
    	
    	if Not rs.EOF Then
    		rs.MoveFirst
    		Do While Not rs.EOF
    			btot = rs.fields("amnt")
    			rs.MoveNext
    		loop
    		rs.MoveFirst
    	end if
    
    	rs.close
    	conn.close
    	btot = btot + CLng(request.form("amount"))
    	
    	conn.Provider="Microsoft.Jet.OLEDB.4.0"
    	set rs=Server.CreateObject("ADODB.Recordset")
    	conn.Open "*:\***\*****\*********\db\data.mdb"
    	
    	strUpdate = "Update bank SET amnt=" & btot & " WHERE bid = 1"
    
    	rs.open strUpdate,conn
    	conn.close
    	
    	btot = 0
    else
    	response.write("Error! No Value!")
    end if
    response.Redirect("http://brainfishes.brinkster.net/AOD/home/rewards.asp?mode=view")
    end if
    
    %>
    </td></tr></table>
    </body>
    </html>

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well done matey!

    I've taken a quick look at your code and I have a couple of suggestions
    • Never use SELECT *
    • After you've looped through your recordsets you issue another .MoveFirst() - ask yourself; is this necessary?
    • Remember, remember, remember: a user can so, so easily change the querysting!
    • How often in that oen file are you setting up a connection object? Could you consolidate that into a single call at the page open, and a close at page end?
    • Always clean up your objects; after closing them for good, set them to Nothing; simple as:
      Code:
      Set conn = Nothing


    More "enlightenment" on request
    Oh and for ado help: www.w3schools.com/ado is a good a start point as any!

    Good luck
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2008
    Posts
    10


    I've made a few changes to the code and taken onboard some of your suggestions, I've also added comments, which is something I rarely do!

    Here is the revised code;

    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    <link href="../css/styles.css" rel="stylesheet" type="text/css" />
    </head>
    
    <body bgcolor="#000000">
    <body valign="top" align="center">
    <table align="left" valign="top" width="700">
    <tr><td valign="top">&nbsp;</td></tr>
    <tr><td valign="top">&nbsp;</td></tr>
    <tr><td valign="top" align="center"><b>Rewards Bank</b><br><br></td></tr>
    <tr>
    <td valign="top" align="left">
    
    <%
    ' Set variables
    strUserName = Replace(Session("userName"), "'", "''")
    dim btot
    
    ' Set initial connection (hope this works!)
    dim conn
    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open "*:\*****\*****\*****\******\db\data.mdb"
    set rs=Server.CreateObject("ADODB.Recordset")
    
    ' Display script for the view page
    If Request.querystring("mode") = "view" then
    	
    	'Get the running total figure
    	rs.open "SELECT amnt FROM bank WHERE bid=1",conn
    
    	If Not rs.EOF Then
    		rs.MoveFirst
    		Do While Not rs.EOF
    			btot = rs.fields("amnt")
    			rs.MoveNext
    		loop
    	end if
    	
    	rs.close
    	
    	'Display the running total
    	response.write("<br>The total amount in the rewards bank is: <b>" & btot & " Credits</b></td></tr>")
    	
    	response.write("<tr><td align=""left"">&nbsp;</td></tr>")
    	response.write("<tr><td align=""center"">&nbsp;</td></tr>")
    	
    	' User clicks Table
    	response.write("<tr><td align=""center""><b>Link Clicks</b><br><br></td></tr>")
    	response.write("<tr><td align=""left"">")
    	response.write("<table align=""center"" border=""0"" cellpadding=""0"" cellspacing=""0"" width=""400"">")
    	response.write("<tr><td align=""center"" width=""200"">Name</td>")
    	response.write("<td align=""center"" width=""200"">Link Clicks</td></tr>")
    	response.write("<tr><td align=""center"">&nbsp;</td></tr>")
    
    	' Get the user link clicks figures and display all names/amounts
    	rs.open "SELECT cname,clks FROM clicks ORDER BY clks DESC",conn
    	
    	If Not rs.EOF Then
    		rs.MoveFirst
    		Do While Not rs.EOF
    			response.write("<tr><td align=""center"" width=""200"">" & rs.fields("cname") & "</td>")
    			response.write("<td align=""center"" width=""200"">" & rs.fields("clks") & "</td></tr>")
    			rs.MoveNext
    		loop
    	end if
    	
    	rs.close
    	
    	response.write("</table></td></tr>")
    	response.write("<tr><td align=""left"">&nbsp;</td></tr>")
    	
    	' User Pledges Table
    	response.write("<tr><td align=""center""><b>Pledges</b><br></td></tr>")
    	response.write("<tr><td align=""left"">")
    	response.write("<table align=""center"" border=""0"" cellpadding=""0"" cellspacing=""0"" width=""400"">")
    	response.write("<tr><td align=""center"" width=""200"">Name</td>")
    	response.write("<td align=""center"" width=""200"">Amount</td></tr>")
    	response.write("<tr><td align=""center"">&nbsp;</td></tr>")
    
    	' Get the user totals and display all names/amounts
    	rs.open "SELECT pname,amount FROM bstat ORDER BY amount DESC",conn
    	
    	If Not rs.EOF Then
    		rs.MoveFirst
    		Do While Not rs.EOF
    			response.write("<tr><td align=""center"" width=""200"">" & rs.fields("pname") & "</td>")
    			response.write("<td align=""center"" width=""200"">" & rs.fields("amount") & "</td></tr>")
    			rs.MoveNext
    		loop
    	end if
    	
    	rs.close
    	
    	'Display pledge link
    	response.write("</table>")
    	response.write("<br><br><a href=""rewards2.asp?mode=pledge""><b>Pledge Credits Now</b></a>")
    	
    	' If Admin is logged in, display admin reset functions
    	If Session("userAdmin") = 1 then
    		response.write("</td></tr><tr><td align=""center"">&nbsp;</td></tr><tr><td align=""center"">")
    		response.write("<a href=""rewards2.asp?mode=resetbank"">Reset Bank</a><br>")
    		response.write("<a href=""rewards2.asp?mode=resetlinks"">Reset Links</a><br>")
    	end if
    end if
    
    ' Display script for pledge form
    If Request.querystring("mode") = "pledge" then
    
    	response.write("<form method=""post"" action=""rewards2.asp?mode=dopledge"">")
    	response.write("<table align=""left"" border=""0"" cellpadding=""0"" cellspacing=""0"" width=""200""><tr>")
    	response.write("<td align=""left"" width=""100"">Amount: <input name=""amount""></td>")
    	response.write("<td align=""left"" width=""100""><input name=""p_name"" type=""hidden"" value=""" & strUserName & """></td></tr>")
    	response.write("<tr><td colspan=""2"" align=""center""><input type=""submit"" value=""Pledge Amount""></td></tr>")
    	response.write("<tr><td align=""center"" colspan=""2"">&nbsp;</td></tr></table></form>")
    end if
    
    ' Action Script reset user totals and running total to 0
    if Request.querystring("mode") = "resetbank" then
    
    	' Update all records in user total
    	sql = "UPDATE bstat SET amount = 0"
    	conn.Execute sql
    
    	' Update the running total figure	
    	strUpdate = "Update bank SET amnt=0 WHERE bid = 1"
    	rs.open strUpdate,conn
    	
    	response.Redirect("http://brainfishes.brinkster.net/AOD/home/rewards2.asp?mode=view")
    end if
    
    ' Action Script reset links to 0
    if Request.querystring("mode") = "resetlinks" then
    	
    	sql = "UPDATE clicks SET clks = 0"
    	conn.Execute sql
    
    	response.Redirect("http://brainfishes.brinkster.net/AOD/home/rewards2.asp?mode=view")
    end if
    
    ' If mode is dopledge, ie, action script for the pledge.
    if Request.querystring("mode") = "dopledge" then
    ' IF the form has not been left blank
    if request.form("amount") <> "" then
    	dim holder
    	
    	' Get the current figure for the current user
    	rs.open "SELECT amount FROM bstat WHERE pname='" & strUserName & "'",conn
    	
    	If Not rs.EOF Then
    		rs.MoveFirst
    		Do While Not rs.EOF
    			holder = rs.fields("amount")
    			rs.MoveNext
    		loop
    	end if
    	
    	rs.close
    	
    	' Add the chosen amount to the user's total
    	holder = holder + CLng(request.form("amount"))
    	
    	' Update the figure for the chosen user
    	sql = "UPDATE bstat SET amount = " & holder & " WHERE pname='" & strUserName & "'"
    	conn.Execute sql
    
    	' Get the current figure for the running total
    	rs.open "SELECT amnt FROM bank WHERE bid=1",conn
    		
    	if Not rs.EOF Then
    		rs.MoveFirst
    		Do While Not rs.EOF
    			btot = rs.fields("amnt")
    			rs.MoveNext
    		loop
    		rs.MoveFirst
    	end if
    	
    	rs.close
    
    	' Add the chosen amount to the running total
    	btot = btot + CLng(request.form("amount"))
    	
    	' Update the bank table with the new figure
    	strUpdate = "Update bank SET amnt=" & btot & " WHERE bid = 1"
    	rs.open strUpdate,conn
    '	rs.close
    else
    	response.write("Error! No Value!")
    end if
    response.Redirect("http://brainfishes.brinkster.net/AOD/home/rewards2.asp?mode=view")
    end if
    
    conn.close
    set conn = Nothing
    %>
    </td></tr></table>
    </body>
    </html>

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, after another quick look I have another couple of pointers / questions:

    First bit; and I'm being picky here...
    You're looking at the same query string value in a number of places; each time you have to send a request out, which has some overhead... Why not just stick the querystring value in a variable and then compare against that?

    Also, what happens if the user changes the querystring to something like www.example.com?mode=blahblahblah ? Will they get anything sent back to them at all?

    Next up is this beauty
    Code:
    	If Not rs.EOF Then
    		rs.MoveFirst
    		Do While Not rs.EOF
    			btot = rs.fields("amnt")
    			rs.MoveNext
    		loop
    	end if
    Can you explain the purpose of this loop?
    You appear to cycle through the recordset, assign the value to a variable but do nothing with it! Why on earth do you need to iterate if you're just overwriting the value each pass?

    Where have you declared the variable rs? You've dimmed the conn variable, but not the rs!

    At the end, you need ot add the foloowing line too!
    Code:
    set rs = Nothing
    And my final point for today (promise!): you have a session variable for admins, to display links... However, all I have to do to perform one of the admin functions is to know the right querystring! I think you should test for your session variable when you test for the admin function querystrings too... Remember my above point about shoving something you need more than once in a variable? Perhaps the session variable would be worth doing this way too







    ...Next week we introduce you to CSS

    lol

    George
    Home | Blog

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I knew I'd be back...

    ...Is your hosting good then? How much web space do you get and what's the response time like? And the biggie; when I give them my email address, will I get spammed like nobodies business?
    George
    Home | Blog

  12. #12
    Join Date
    Mar 2008
    Posts
    10
    I use Brinkster, for my money they're one of the best free hosting solutions out there.

    You get a subdomain (brainfishes.brinkster.net in my case) with ASP/SQL/Access DB, but there are a few restrictions that I've found so far... they don't allow msgboxes and there's no Application hosting support, I'm limited to 30MB web space and 16.7 MB traffic per day.

    I'm not sure what you mean by response time, I get full KB and support access and the support people tend to reply within a day or so, I've never had any problems with delays on my pages coming up or queries running or anything...

    Spam wise, I get a monthly newsletter from Brinkster and aside from that I've never had any problem with spam at all.

    The best thing about brinskter's free package is the lack of big, ugly ad banners, they have a little text thingie that just says 'Hosted by Brinkster', and that's it.

    You can get a better look at what I've got here.

    I use them all the time and I've never had a reason to complain.

  13. #13
    Join Date
    Mar 2008
    Posts
    10
    Hey,

    I'm off to work in an hour or so, but I've got some refined code for you before I go.

    Repeat Statements, resolved I think. The variable only ever gets set once now.

    The loop, the loop was in there because it was part of the default template from w3schools, however, that perticular query only ever pulls one record from the db so I figure it's not required. Removed in this version.

    I've put Admin test If's in on the admin functions, moved all my variable dim's up to the top of the page and put corresponding = Nothing's at the end.

    Let me know what you think!

    Code:
    <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    <link href="../css/styles.css" rel="stylesheet" type="text/css" />
    </head>
    
    <body bgcolor="#000000">
    <body valign="top" align="center">
    <table align="left" valign="top" width="700">
    <tr><td valign="top">&nbsp;</td></tr>
    <tr><td valign="top">&nbsp;</td></tr>
    <tr><td valign="top" align="center"><b>Rewards Bank</b><br><br></td></tr>
    <tr>
    <td valign="top" align="left">
    
    <%
    ' Set variables
    strUserName = Replace(Session("userName"), "'", "''")
    dim btot
    dim conn
    dim rs
    dim sql
    dim holder
    dim strUpdate
    
    ' Set initial connection (hope this works!)
    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open "*:\*****\*****\*****\******\db\data.mdb"
    set rs=Server.CreateObject("ADODB.Recordset")
    
    rs.open "SELECT amnt FROM bank WHERE bid=1",conn
    btot = rs.fields("amnt")
    rs.close
    
    If Request.querystring("mode") <> "view" _ 
    	and Request.querystring("mode") <> "pledge" _
    	and Request.querystring("mode") <> "resetbank" _
    	and Request.querystring("mode") <> "resetlinks" _
    	and Request.querystring("mode") <> "dopledge" _
    	and Request.querystring("mode") <> "error" then
    		response.redirect("rewards.asp?mode=error")
    end if
    
    If Request.querystring("mode") = "error" then
    	response.write("<br><b>Don't fsuk with the url, foo! Use the menu links!</td></tr>")
    end if
    
    ' Display script for the view page
    If Request.querystring("mode") = "view" then
    	
    	'Get the running total figure
    	'Display the running total
    	response.write("<br>The total amount in the rewards bank is: <b>" & btot & " Credits</b></td></tr>")
    	
    	
    	response.write("<tr><td align=""left"">&nbsp;</td></tr>")
    	response.write("<tr><td align=""center"">&nbsp;</td></tr>")
    	
    	' User clicks Table
    	response.write("<tr><td align=""center""><b>Link Clicks</b><br><br></td></tr>")
    	response.write("<tr><td align=""left"">")
    	response.write("<table align=""center"" border=""0"" cellpadding=""0"" cellspacing=""0"" width=""400"">")
    	response.write("<tr><td align=""center"" width=""200"">Name</td>")
    	response.write("<td align=""center"" width=""200"">Link Clicks</td></tr>")
    	response.write("<tr><td align=""center"">&nbsp;</td></tr>")
    
    	' Get the user link clicks figures and display all names/amounts
    	rs.open "SELECT cname,clks FROM clicks ORDER BY clks DESC",conn
    	
    	If Not rs.EOF Then
    		rs.MoveFirst
    		Do While Not rs.EOF
    			response.write("<tr><td align=""center"" width=""200"">" & rs.fields("cname") & "</td>")
    			response.write("<td align=""center"" width=""200"">" & rs.fields("clks") & "</td></tr>")
    			rs.MoveNext
    		loop
    	end if
    	
    	rs.close
    	
    	response.write("</table></td></tr>")
    	response.write("<tr><td align=""left"">&nbsp;</td></tr>")
    	
    	' User Pledges Table
    	response.write("<tr><td align=""center""><b>Pledges</b><br></td></tr>")
    	response.write("<tr><td align=""left"">")
    	response.write("<table align=""center"" border=""0"" cellpadding=""0"" cellspacing=""0"" width=""400"">")
    	response.write("<tr><td align=""center"" width=""200"">Name</td>")
    	response.write("<td align=""center"" width=""200"">Amount</td></tr>")
    	response.write("<tr><td align=""center"">&nbsp;</td></tr>")
    
    	' Get the user totals and display all names/amounts
    	rs.open "SELECT pname,amount FROM bstat ORDER BY amount DESC",conn
    	
    	If Not rs.EOF Then
    		rs.MoveFirst
    		Do While Not rs.EOF
    			response.write("<tr><td align=""center"" width=""200"">" & rs.fields("pname") & "</td>")
    			response.write("<td align=""center"" width=""200"">" & rs.fields("amount") & "</td></tr>")
    			rs.MoveNext
    		loop
    	end if
    	
    	rs.close
    	
    	'Display pledge link
    	response.write("</table>")
    	response.write("<br><br><a href=""rewards.asp?mode=pledge""><b>Pledge Credits Now</b></a>")
    	
    	' If Admin is logged in, display admin reset functions
    	If Session("userAdmin") = 1 then
    		response.write("</td></tr><tr><td align=""center"">&nbsp;</td></tr><tr><td align=""center"">")
    		response.write("<a href=""rewards.asp?mode=resetbank"">Reset Bank</a><br>")
    		response.write("<a href=""rewards.asp?mode=resetlinks"">Reset Links</a><br>")
    	end if
    end if
    
    ' Display script for pledge form
    If Request.querystring("mode") = "pledge" then
    
    	response.write("<form method=""post"" action=""rewards.asp?mode=dopledge"">")
    	response.write("<table align=""left"" border=""0"" cellpadding=""0"" cellspacing=""0"" width=""200""><tr>")
    	response.write("<td align=""left"" width=""100"">Amount: <input name=""amount""></td>")
    	response.write("<td align=""left"" width=""100""><input name=""p_name"" type=""hidden"" value=""" & strUserName & """></td></tr>")
    	response.write("<tr><td colspan=""2"" align=""center""><input type=""submit"" value=""Pledge Amount""></td></tr>")
    	response.write("<tr><td align=""center"" colspan=""2"">&nbsp;</td></tr></table></form>")
    end if
    
    ' Action Script reset user totals and running total to 0
    if Request.querystring("mode") = "resetbank" then
    	If Session("userAdmin") = 1 then
    		' Update all records in user total
    		sql = "UPDATE bstat SET amount = 0"
    		conn.Execute sql
    	
    		' Update the running total figure	
    		strUpdate = "Update bank SET amnt=0 WHERE bid = 1"
    		rs.open strUpdate,conn
    		
    		response.Redirect("http://brainfishes.brinkster.net/AOD/home/rewards.asp?mode=view")
    	else
    		response.write("<br><br>You are not an Admin! Permission Denied!")
    	end if
    end if
    
    ' Action Script reset links to 0
    if Request.querystring("mode") = "resetlinks" then
    	If Session("userAdmin") = 1 then
    		sql = "UPDATE clicks SET clks = 0"
    		conn.Execute sql
    	
    		response.Redirect("http://brainfishes.brinkster.net/AOD/home/rewards.asp?mode=view")
    	else
    		response.write("<br><br>You are not an Admin! Permission Denied!")
    	end if
    end if
    
    ' If mode is dopledge, ie, action script for the pledge.
    if Request.querystring("mode") = "dopledge" then
    ' IF the form has not been left blank
    if request.form("amount") <> "" then
    		
    	' Get the current figure for the current user
    	rs.open "SELECT amount FROM bstat WHERE pname='" & strUserName & "'",conn
    	
    	If Not rs.EOF Then
    		rs.MoveFirst
    		Do While Not rs.EOF
    			holder = rs.fields("amount")
    			rs.MoveNext
    		loop
    	end if
    	
    	rs.close
    	
    	' Add the chosen amount to the user's total
    	holder = holder + CLng(request.form("amount"))
    	
    	' Update the figure for the chosen user
    	sql = "UPDATE bstat SET amount = " & holder & " WHERE pname='" & strUserName & "'"
    	conn.Execute sql
    
    	' Add the chosen amount to the running total
    	btot = btot + CLng(request.form("amount"))
    	
    	' Update the bank table with the new figure
    	strUpdate = "Update bank SET amnt=" & btot & " WHERE bid = 1"
    	rs.open strUpdate,conn
    '	rs.close
    else
    	response.write("Error! No Value!")
    end if
    response.Redirect("http://brainfishes.brinkster.net/AOD/home/rewards.asp?mode=view")
    end if
    
    conn.close
    set rs = Nothing
    set strUpdate = Nothing
    set btot = Nothing
    set holder = Nothing
    set conn = Nothing
    set sql = Nothing
    %>
    </td></tr></table>
    </body>
    </html>

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your very first recordset "SELECT amnt FROM bank WHERE bid=1", you've not tested for EOF and BOF; this code will fail if no record is returned.

    Pseudo code
    Code:
    open recordset query
    
          text for EOF and BOF with an If statement
              MoveFirst
              myVariable = recordset value
          else
              myVariable = 0?
          end if
    
    close recordset
    This little feller could still use optimising
    Code:
    If Request.querystring("mode") <> "view" _ 
    	and Request.querystring("mode") <> "pledge" _
    	and Request.querystring("mode") <> "resetbank" _
    	and Request.querystring("mode") <> "resetlinks" _
    	and Request.querystring("mode") <> "dopledge" _
    	and Request.querystring("mode") <> "error" then
    		response.redirect("rewards.asp?mode=error")
    end if
    To
    Code:
        Dim mode
        
        mode = Request.QueryString("mode")
        
        If mode <> "view" And mode <> "pledge" ... Then
            Response.Write("tut-tut, don't even try it")
            Response.End()
        End If
    You can also use your mode variable over a request throughout

    Also, move this test to the very start of the page; no point in opening anything or creating any objects if something is wrong!

    - You've not dimmed strUsername

    This one is a personal preference of mine that you may wish to adopt;
    Where you have
    Code:
    If Session("userAdmin") = 1 Then
    I would change to something like
    Code:
    'at the topp of page (after checking for valid querysting values (obviously)
    Dim isAdmin
    
    If Session("userAdmin") = 1 Then
        isAdmin = True
    Else
        isAdmin = False
    End If
    
    ....snip....
    
    If isAdmin Then
        Response.Write("You're an admin!")
    Else
        Response.Write("No admin, no access!")
    End If
    I just feel that the above is more reasonable, and also we will only ever call for this particular session variable once (which is a nice bonus too)!

    - what happens if there is no value for strUsername?
    - Or worse yet, what would happen if the username was
    '; DELETE FROM bstat WHERE ''='
    SQL injection can occur in some beautiful ways, eh?

    This little fella has occured again!
    Code:
                If Not rs.EOF Then
                    rs.MoveFirst()
                    Do While Not rs.EOF
                        holder = rs.fields("amount")
                        rs.MoveNext()
                    Loop
                End If
    This, if only ever returning one value should become
    Code:
                If Not rs.EOF Then
                    rs.MoveFirst()
                    holder = rs.fields("amount")
                End If
    No looping, genius!

    In fact, you appear to be opening a recordset to get the value for holder, and then using this value in your update statement!

    Cut out the middle man; just use your update!
    Code:
    sql = "UPDATE bstat SET amount = amount + " & Request.Form("amount") & " WHERE pname='" & strUserName & "'"
    Can a user enter a negative value for the amount?

    Final, minor point; You only have to set objects to Nothing, strings, numbers, etc can just be left.

    Fantastic; vast improvements so far well done!
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I appreciate that I'm bombarding you with information; please don't be afraid to ask questions. Don't just take everything I say as gospel because everyone knows that's far from the truth

    Good luck!
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •