PayPal Integration, IPN & PDT, txn_id general query


I have set up a Buy now button for payment of goods and general custom invoiced items. It returns the user back to a completed page if the transaction is succesful. There is no processing on the completed page. Paypal also uses IPN (instant payment notification) posting results to my processing page.

The query I have is:
Is the Transaction ID (txn_id) that PayPal creates always unique? I Know the docs state this but it appears there are times it is not while I am testing, has me scratching my head. Maybe this can occur from a PayPal error? I know they have been up and down lately...

The simple code I am using is below. There are no database calls before this on the page yet somehow for the last 24 hrs everytime I test it, it trips that it is a duplicate.... Does anyone know of any gotchas here?

            ' check that TransactionID has not been previously processed

            strSQL = "Select * FROM tb_Payments WHERE TransactionID='" & TransactionID & "'"
            rs.Open strSQL, Conn, adOpenStatic, adLockReadOnly
                  if rs.eof Then
                        validID = True
                        error_str = error_str & "<br>error:transactionID(TransactionID)=Duplicate"
                        validID = False
                  end if
            set rs = nothing

Who is Participating?
thefritterfatboyConnect With a Mentor Commented:
>> So in effect, PayPal's documents, stating to check if the transaction ID is unique as a security measure is not so good because then you would block out a pending transaction...

No, because you need to also check if the status is "COMPLETED" too. If the txn_id matches another COMPLETED transaction then you know something is wrong. I think PayPal's documentation tells you to check for the "COMPLETED" flag.
You sometimes need to watch out when testing online payments like this that you are re-completeing the entire process and not just one step, and also that all variables are cleared at the beginning , at the end and in the case of an error. A prob I always come up with is, when starting a trans, you get assigned an ID and move to page 1, fill out details, click submit, page does some work and logs the trans, and moves you on to page 2, if at any point you are trying to reload these pages, you may already have these variables set up in the DB, hence the dupes error. This is by no means a solution, just something I have had probs with in the past.
Chris_GrangerAuthor Commented:
The "buying" pages are not the pages creating the txn_id - this should be paypal, it shouldn't matter what variable I send to PayPal, if the buy goes through their pages I would have thought the txn_id would always be unique. The page detecting the duplication "ipn.asp " is a server page and is only ever called directly from paypal after the buyer has completed their the transaction within the PayPal buying pages. All pages are under SSL and have the following at the top:

      Response.CacheControl = "no-cache"
      Response.AddHeader "pragma", "no-cache"
      Response.AddHeader "cache-control", "private"
      Response.Expires = -1

I don't think the paypal processing pages or the Msxml2.ServerXMLHTTP method they use to post to mine after completion can ever be using stale data. Is a mystery and making me age before my time...  :-) Along with the PayPal support system of course. In fact maybe my question should be, where is the PayPal support?  :-)  Goes to show the harm a monopoly creates.  I do like the power their system has though, must be the best on the market for its functionality...  Anyway..
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

txn_id is ALWAYS unique - however when you issue a refund - the IPN will notify your processing page again.

Another problem is if your script does not complete. Your script may connect to your database, write your values and then fall over a bit further on. If Paypal do not get a HTTP 200 status from your processing page they will keep trying. For buy now processing - the first thing you should do is write a text file and append the request.form value in there with a time. this will allow you to open the text file and view EVERYTHING paypal sent to your server.
Another good debug trick is to look at your site's log files. If your site's files log the data that was posted to your site then you can find out what is being sent that way.
Chris_GrangerAuthor Commented:
Ok, I've written my own log and been monitoring the server logs:

I can see 2 things happening.

1. there is an error ocurring on my asp page but not sure what the log record means and what line it is actually referring to, could someone explain the results to me?

2. The page is getting called twice..   eeekk..  maybe because of the error i hope...

heres the server log:

2004-08-06 10:10:49 myip - W3SVC942 NT2 my.ip 443 POST /accounts/myipn.asp |243|80040e14|Line_1:_Incorrect_syntax_near_'_'. 500 0 674 742 750 HTTP/1.0 - - -

and then 20 seconds later:

2004-08-06 10:11:08 myip- W3SVC942 NT2 myip 443 POST /accounts/myipn.asp - 200 0 348 742 609 HTTP/1.0 - - -

Your error seems to be in the SQL.

Can you show me how you get the "TransactionID"?

As for the 20 seconds gap thing... this is bizarre. Your server throws an error on the first attempt - but accepts the second attempt and returns a HTTP 200 status code.

When posting items from your log - can you also copy and paste the headers at the top of the file. Not all servers put these in the same order or include the same data.
Chris_GrangerAuthor Commented:
This is the recieving code:

      ' read post from PayPal system and add 'cmd'
      str = Request.Form & "&cmd=_notify-validate"
      ' post back to PayPal system to validate
      set objHttp = Server.CreateObject("Msxml2.ServerXMLHTTP") "POST", "", false
      objHttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
      objHttp.Send str

      TransactionID = URLDecode(Request.Form("txn_id"))

Both times the page loads it gets the same information:
and both get the following for payment status: payment_status=Completed, which means there is nothing pending...
they also both get the correct xml response text:  objHttp.responseText: VERIFIED

Likewise all my request form variables are working also
Any chance of getting some more code?

According to your logs - the error is on line 1. This sounds crazy to me. If you don't want to provide all the code - could you provide line 1 and your urlDecode function.
Can you also tell me what Data type your transactionID is stored as in the database. This error may be due to your SQL looking for a string in a numeric field.
Chris_GrangerAuthor Commented:
I finally found the error, was quite hard to debug but pretty obvious error!  All is working now, still baffles me how it was getting called twice by an asp error.

the dumbo error was: overageid_arr = Split(CartItems)  instead of  overageid_arr = Split(CartItems, ",")

So, even in the case of a "Pending" from PayPal, will the transaction sent through to state it is completed after this be unique?
>>So, even in the case of a "Pending" from PayPal, will the transaction sent through to state it is completed after this be unique?

The TXN is transaction unique. If the status is pending then when the complete notification comes through it will post the same TXN.
Chris_GrangerAuthor Commented:
So in effect, PayPal's documents, stating to check if the transaction ID is unique as a security measure is not so good because then you would block out a pending transaction...

I guess this would also require a record check to see if this transaction was pending and then let it through...
Chris_GrangerAuthor Commented:
...aha, so it's okay for it to be a duplicate transaction as long as it's not a duplicate completed transaction...

Chris_GrangerAuthor Commented:
One last question regarding payments of a non-completed type:  I assume these don't have any transaction number?  I just did a test with the pending and it gave me a 0 for the transaction number - damn!  Be hard to automate that task without first creating a payment record and passing its id through before the item is pruchased... I'm using the transaction number as a reference to db records... Will have to create a temp, table for pending and other non-completed payments and manually mark their status to completed with the transaction number that comes through when it's completed...
The question you have to ask is how important is it to log pending sales? I'm not entirely sure what causes pending sales types but your customers will know when Paypal is pending so just tell them that until you get a "payment complete" notification, your server will not acknowledge payment.
All Courses

From novice to tech pro — start learning today.