Updating a record

[RFH Reseller: UKFlyer]

Mysql = "UPDATE Acts SET Featured = 1, FeaturedUntil = Date_Add(now(),INTERVAL 1 YEAR) " &_
"WHERE ActID=361"
StrConnect.execute(mySQL)

Does what I want it to do, however where 361 goes should be a variable.

Response.Write("" & itemName & "") gives me the variable, however trying to replace 361 with the right bit of code so it works is proving a bit of a problem. Some assistance would be appreciated - thanks in advance.

[Staff: Warren Ashcroft]

Is this what you need?

MySQL = "UPDATE Acts SET Featured = 1, FeaturedUntil = DATE_ADD(NOW(), INTERVAL 1 YEAR) WHERE ActID=" & CInt(itemName)
StrConnect.Execute(MySQL)

[RFH Reseller: UKFlyer]

Didn't work. The idea is for someone to make a subscription payment itemName equates to the userID when PayPal returns back, I want to be able to set a date and enable featured as '1' for that record automatically. ItemName is returning OK (Response.Write("" & itemName & "") gives 361) and if I fix the WHERE statement it updates, but I spent four hours on it yesterday using various combinations to get the record to update and could get it to work. The whole script is below if that helps.....

<%@LANGUAGE="VBScript"%>
<!--#include file='dbconnect.asp'-->
<!--#include file='settings.asp'-->
<%
Dim authToken, txToken
Dim query
Dim objHttp
Dim sQuerystring
Dim sParts, iParts, aParts
Dim sResults, sKey, sValue
Dim i, result
Dim firstName, lastName, itemName, itemNumber, mcGross, mcCurrency

authToken ="OVR_8SAD0DvnGmCHWho5obeVWggn2ImeBuM1VWy7kCwsL5S1vLZqN3HXVTa"
txToken = Request.Querystring("tx")

query = "cmd=_notify-synch&tx=" & txToken & "&at=" & authToken

set objHttp = Server.CreateObject("Microsoft.XMLHTTP")
' set objHttp = Server.CreateObject("Msxml2.ServerXMLHTTP")
objHttp.open "POST", "http://www.sandbox.paypal.com/cgi-bin/webscr", false
objHttp.Send query

sQuerystring = objHttp.responseText

If Mid(sQuerystring,1,7) = "SUCCESS" Then
sQuerystring = Mid(sQuerystring,9)
sParts = Split(sQuerystring, vbLf)
iParts = UBound(sParts) - 1
ReDim sResults(iParts, 1)
For i = 0 To iParts
aParts = Split(sParts(i), "=")
sKey = aParts(0)
sValue = aParts(1)
sResults(i, 0) = sKey
sResults(i, 1) = sValue

Select Case sKey
Case "first_name"
firstName = sValue
Case "last_name"
lastName = sValue
Case "item_name"
itemName = sValue
Case "mc_gross"
mcGross = sValue
Case "mc_currency"
mcCurrency = sValue
End Select
Next

Response.Write("<p><h3>Your order has been received.</h3></p>")
Response.Write("<b>Details</b><br>")
Response.Write("<li>Name: " & firstName & " " & lastName & "</li>")
Response.Write("<li>Amount: " & mcCurrency & " " & mcGross & "</li>")
Response.Write("<hr>")

Mysql = "UPDATE Acts SET Featured = 1, FeaturedUntil = Date_Add(now(),INTERVAL 1 YEAR) " &_
"WHERE ActID=361"
StrConnect.execute(mySQL) 
Response.Write("" & itemName & "")

Else
'log for manual investigation
Response.Write("ERROR")
End If
%>

[Staff: Warren Ashcroft]

When you say it didn't work, what actually happened? If itemName always contains a number (the ActID) I can't see where the two lines I gave could go wrong :\

[RFH Reseller: UKFlyer]

The page returns correctly from Paypal, but the time and Featured status remains unchanged in the MySQL database.

[RFH Reseller: UKFlyer]

Just thought I'd post to say I found the solution...

dim strItemName
strItemName = itemName
Mysql = "UPDATE Acts SET Featured = 1, FeaturedUntil = Date_Add(now(),INTERVAL 1 YEAR) WHERE ActID= " & strItemName
StrConnect.execute(mySQL)

Thanks for the help.

[Staff: Warren Ashcroft]

That's weird, so you had to put itemName into another variable (strItemName) before it would work? I would still do a CInt() on the SQL Query so that you prevent and SQL Injection.

dim strItemName
strItemName = itemName
Mysql = "UPDATE Acts SET Featured = 1, FeaturedUntil = Date_Add(now(),INTERVAL 1 YEAR) WHERE ActID= " & CInt(strItemName)
StrConnect.execute(mySQL)