Help

cmottershead · #1 (**permalink**) 05-20-04, 10:26 AM

i got the following file for paypal's ipn off the net the email part of the script works but it doesn't write to the database could someone please help?

<%@ Page Language="VB" Debug="False"%>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Text" %>
<%@ import Namespace="System.IO" %>
<%@ import Namespace="System.Net" %>
<%@ import Namespace="System.Web" %>
<%@ import Namespace="System.Web.Mail" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script runat="server">

'Store database connection
Dim strConn as string ="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("PayPal.mdb") & ";"
Dim conn As New OLEDBConnection(strConn)
Dim cmdinsert As OLEDBCommand

Dim mailObj As New System.Web.Mail.MailMessage()

Dim strToSend, OrderID, Txn_id, Payment_status, Receiver_email, Receiver_id, Item_name, _
Item_number, Quantity, Invoice, Custom, _
Payment_gross, Payer_email, Payer_business_name, Pending_reason, Payment_date, Payment_fee, _
Txn_type, First_name, Last_name, Address_street, Address_city, Address_state, _
Address_zip, Address_country, Address_status, Payer_status, Payment_type, _
Notify_version, Verify_sign, Subscr_date, Period1, Period2, Period3, _
Amount1, Amount2, Amount3, Recurring, Reattempt, Retry_at, Recur_times, _
Username, Password, Subscr_id As String

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
Try
' assign posted variables to local variables
Receiver_email = Request.Params("receiver_email")
Receiver_id = Request.Params("receiver_id")
Item_name = Request.Params("item_name")
Item_number = Request.Params("item_number")
Quantity = Request.Params("quantity")
Invoice = Request.Params("invoice")
Custom = Request.Params("custom")
Payment_status = Request.Params("payment_status")
Pending_reason = Request.Params("pending_reason")
Payment_date = Request.Params("payment_date")
Payment_fee = Request.Params("payment_fee")
Payment_gross = Request.Params("payment_gross")
Txn_id = Request.Params("txn_id")
Txn_type = Request.Params("txn_type")
First_name = Request.Params("first_name")
Last_name = Request.Params("last_name")
Address_street = Request.Params("address_street")
Address_city = Request.Params("address_city")
Address_state = Request.Params("address_state")
Address_zip = Request.Params("address_zip")
Address_country = Request.Params("address_country")
Address_status = Request.Params("address_status")
Payer_email = Request.Params("payer_email")
Payer_business_name = Request.Params("payer_business_name")
Payer_status = Request.Params("payer_status")
Payment_type = Request.Params("payment_type")
Notify_version = Request.Params("notify_version")
Verify_sign = Request.Params("verify_sign")
Subscr_date = Request.Params("subscr_date")
Period1 = Request.Params("period1")
Period2 = Request.Params("period2")
Period3 = Request.Params("period3")
Amount1 = Request.Params("amount1")
Amount2 = Request.Params("amount2")
Amount3 = Request.Params("amount3")
Recurring = Request.Params("recurring")
Reattempt = Request.Params("reattempt")
Retry_at = Request.Params("retry_at")
Recur_times = Request.Params("recur_times")
Username = Request.Params("username")
Password = Request.Params("password")
Subscr_id = Request.Params("subscr_id")

strToSend = Request.Form.ToString()
'Create the string to post back to PayPal system to validate
strToSend &= "&cmd=_notify-validate"

'Initialize the WebRequest.
Dim myRequest As HttpWebRequest = System.Net.HttpWebRequest.Create("https://www.eliteweaver.co.uk/cgi-bin/webscr")

myRequest.AllowAutoRedirect = False
myRequest.Method = "POST"
myRequest.ContentType = "application/x-www-form-urlencoded"

'Create post stream
Dim RequestStream As Stream = myRequest.GetRequestStream()
Dim SomeBytes() As Byte = Encoding.UTF8.GetBytes(strToSend)

RequestStream.Write(SomeBytes, 0, SomeBytes.Length)
RequestStream.Close()

'Send request and get response
Dim myResponse As HttpWebResponse = myRequest.GetResponse()

If myResponse.StatusCode = HttpStatusCode.OK Then
'Obtain a 'Stream' object associated with the response object.
Dim ReceiveStream As Stream = myResponse.GetResponseStream()
Dim encode As Encoding = System.Text.Encoding.GetEncoding("utf-8")

'Pipe the stream to a higher level stream reader with the required encoding format.
Dim readStream As StreamReader = New StreamReader(ReceiveStream, encode)

'Read result
Dim Result As String = readStream.ReadLine()

If Result = "INVALID" Then
'Do something!
MailUsTheOrder("PROCESS ME: Houston, we may have a problem!")

if Notify_version > 1.5
mailObj.From = "chris.mottershead@tabtalk.co.uk"
mailObj.To = Receiver_email
mailObj.Subject = "Paypal Version Change"
mailObj.Body = "I see a new version of PayPal Notify IPN Service:" + Notify_version
mailObj.BodyFormat = MailFormat.Html
System.Web.Mail.SmtpMail.SmtpServer = "mail.freeserve.com"
System.Web.Mail.SmtpMail.Send(mailObj)
end if

ElseIf Result = "VERIFIED" Then
' Check strTxn against the previous transaction
'
' Do something!
' check that Txn_id has not been previously processed
' check that Receiver_email is an email address in your PayPal account
' process payment

' check that Payment_status=Completed

if Notify_version > 1.5
mailObj.From = "chris.mottershead@tabtalk.co.uk"
mailObj.To = Receiver_email
mailObj.Subject = "Paypal Version Change"
mailObj.Body = "I see a new version of PayPal Notify IPN Service:" + Notify_version
mailObj.BodyFormat = MailFormat.Html
System.Web.Mail.SmtpMail.SmtpServer = "mail.freeserve.com"
System.Web.Mail.SmtpMail.Send(mailObj)
end if

Select Case (Payment_status)
Case "Completed" 'The payment has been completed and the funds are successfully in your account balance
If Receiver_email = "chris.mottershead@tabtalk.co.uk" Then
Select Case (Txn_type)
Case "web_accept", "cart"
'"web_accept": The payment was sent by your customer via the Web Accept feature.
'"cart": This payment was sent by your customer via the Shopping Cart feature
MailUsTheOrder("PROCESS ME: The order was completed successfully.")
cmdInsert = New OLEDBCommand("Insert into PayPal (txn_id, receiver_email, receiver_id, item_name, item_number, quantity, invoice, custom, payment_status, pending_reason, payment_date, payment_fee, payment_gross, txn_type, first_name, last_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_business_name, payer_status, payment_type, notify_version, verify_sign, process) VALUES ('" & txn_id & "','" & receiver_email & "','" & receiver_id & "','" & item_name & "','" & item_number & "','" & quantity & "','" & invoice & "','" & custom & "','" & payment_status & "','" & pending_reason & "','" & payment_date & "','" & payment_fee & "','" & payment_gross & "','" & txn_type & "','" & first_name & "','" & last_name & "','" & address_street & "','" & address_city & "','" & address_state & "','" & address_zip & "','" & address_country & "','" & address_status & "','" & payer_email & "','" & payer_business_name & "','" & payer_status & "','" & payment_type & "','" & notify_version & "','" & verify_sign & "','1')", conn)

conn.Open()
cmdInsert.ExecuteNonQuery()
conn.close

Case "send_money"
MailUsTheOrder("PROCESS ME: Somebody sent us money!")
cmdInsert = New OLEDBCommand("Insert into PayPal (txn_id, receiver_email, receiver_id, item_name, item_number, quantity, invoice, custom, payment_status, pending_reason, payment_date, payment_fee, payment_gross, txn_type, first_name, last_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_business_name, payer_status, payment_type, notify_version, verify_sign, process) VALUES ('" & txn_id & "','" & receiver_email & "','" & receiver_id & "','" & item_name & "','" & item_number & "','" & quantity & "','" & invoice & "','" & custom & "','" & payment_status & "','" & pending_reason & "','" & payment_date & "','" & payment_fee & "','" & payment_gross & "','" & txn_type & "','" & first_name & "','" & last_name & "','" & address_street & "','" & address_city & "','" & address_state & "','" & address_zip & "','" & address_country & "','" & address_status & "','" & payer_email & "','" & payer_business_name & "','" & payer_status & "','" & payment_type & "','" & notify_version & "','" & verify_sign & "','1')", conn)

conn.Open()
cmdInsert.ExecuteNonQuery()
conn.close

Case "subscr_signup"
MailUsTheOrder("PROCESS ME: Subscription signup.")
cmdInsert = New OLEDBCommand("Insert into PayPal (txn_id, receiver_email, receiver_id, item_name, item_number, quantity, invoice, custom, payment_status, pending_reason, payment_date, payment_fee, payment_gross, txn_type, first_name, last_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_business_name, payer_status, payment_type, notify_version, verify_sign, process) VALUES ( '" & txn_id & "','" & receiver_email & "','" & receiver_id & "','" & item_name & "','" & item_number & "','" & quantity & "','" & invoice & "','" & custom & "','" & payment_status & "','" & pending_reason & "','" & payment_date & "','" & payment_fee & "','" & payment_gross & "','" & txn_type & "','" & first_name & "','" & last_name & "','" & address_street & "','" & address_city & "','" & address_state & "','" & address_zip & "','" & address_country & "','" & address_status & "','" & payer_email & "','" & payer_business_name & "','" & payer_status & "','" & payment_type & "','" & notify_version & "','" & verify_sign & "','1' )", conn)

conn.Open()
cmdInsert.ExecuteNonQuery()
conn.close

Case "subscr_cancel"
MailUsTheOrder("PROCESS ME: Subscription cancellation.")
cmdInsert = New OLEDBCommand("Insert into PayPal (txn_id, receiver_email, receiver_id, item_name, item_number, quantity, invoice, custom, payment_status, pending_reason, payment_date, payment_fee, payment_gross, txn_type, first_name, last_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_business_name, payer_status, payment_type, notify_version, verify_sign, process) VALUES ('" & txn_id & "','" & receiver_email & "','" & receiver_id & "','" & item_name & "','" & item_number & "','" & quantity & "','" & invoice & "','" & custom & "','" & payment_status & "','" & pending_reason & "','" & payment_date & "','" & payment_fee & "','" & payment_gross & "','" & txn_type & "','" & first_name & "','" & last_name & "','" & address_street & "','" & address_city & "','" & address_state & "','" & address_zip & "','" & address_country & "','" & address_status & "','" & payer_email & "','" & payer_business_name & "','" & payer_status & "','" & payment_type & "','" & notify_version & "','" & verify_sign & "','1')", conn)

conn.Open()
cmdInsert.ExecuteNonQuery()
conn.close

Case "subscr_failed"
MailUsTheOrder("FAILURE: Subscription failed.")
cmdInsert = New OLEDBCommand("Insert into PayPal (txn_id, receiver_email, receiver_id, item_name, item_number, quantity, invoice, custom, payment_status, pending_reason, payment_date, payment_fee, payment_gross, txn_type, first_name, last_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_business_name, payer_status, payment_type, notify_version, verify_sign, process) VALUES ('" & txn_id & "','" & receiver_email & "','" & receiver_id & "','" & item_name & "','" & item_number & "','" & quantity & "','" & invoice & "','" & custom & "','" & payment_status & "','" & pending_reason & "','" & payment_date & "','" & payment_fee & "','" & payment_gross & "','" & txn_type & "','" & first_name & "','" & last_name & "','" & address_street & "','" & address_city & "','" & address_state & "','" & address_zip & "','" & address_country & "','" & address_status & "','" & payer_email & "','" & payer_business_name & "','" & payer_status & "','" & payment_type & "','" & notify_version & "','" & verify_sign & "','1')", conn)

conn.Open()
cmdInsert.ExecuteNonQuery()
conn.close

Case "subscr_payment"
MailUsTheOrder("Ka-Ching: We got cash!")
cmdInsert = New OLEDBCommand("Insert into PayPal (txn_id, receiver_email, receiver_id, item_name, item_number, quantity, invoice, custom, payment_status, pending_reason, payment_date, payment_fee, payment_gross, txn_type, first_name, last_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_business_name, payer_status, payment_type, notify_version, verify_sign, process) VALUES ('" & txn_id & "','" & receiver_email & "','" & receiver_id & "','" & item_name & "','" & item_number & "','" & quantity & "','" & invoice & "','" & custom & "','" & payment_status & "','" & pending_reason & "','" & payment_date & "','" & payment_fee & "','" & payment_gross & "','" & txn_type & "','" & first_name & "','" & last_name & "','" & address_street & "','" & address_city & "','" & address_state & "','" & address_zip & "','" & address_country & "','" & address_status & "','" & payer_email & "','" & payer_business_name & "','" & payer_status & "','" & payment_type & "','" & notify_version & "','" & verify_sign & "','1')", conn)

conn.Open()
cmdInsert.ExecuteNonQuery()
conn.close

Case "subscr_eot"
MailUsTheOrder("WHAT IS THIS? Subscription end of term.")
cmdInsert = New OLEDBCommand("Insert into PayPal (txn_id, receiver_email, receiver_id, item_name, item_number, quantity, invoice, custom, payment_status, pending_reason, payment_date, payment_fee, payment_gross, txn_type, first_name, last_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_business_name, payer_status, payment_type, notify_version, verify_sign, process) VALUES ('" & txn_id & "','" & receiver_email & "','" & receiver_id & "','" & item_name & "','" & item_number & "','" & quantity & "','" & invoice & "','" & custom & "','" & payment_status & "','" & pending_reason & "','" & payment_date & "','" & payment_fee & "','" & payment_gross & "','" & txn_type & "','" & first_name & "','" & last_name & "','" & address_street & "','" & address_city & "','" & address_state & "','" & address_zip & "','" & address_country & "','" & address_status & "','" & payer_email & "','" & payer_business_name & "','" & payer_status & "','" & payment_type & "','" & notify_version & "','" & verify_sign & "','1')", conn)

conn.Open()
cmdInsert.ExecuteNonQuery()
conn.close

End Select

Select Case (Address_status)
Case "confirmed"
Case "unconfirmed"

End Select

Select Case (Payer_status)
Case "verified"
Case "unverified"
Case "intl_verified"
Case "intl_unverified"
End Select

Select Case (Payment_type)
Case "echeck"
Case "instant"
End Select
Else
MailUsTheOrder("Someone is notifying us that payments were received by someone else!")
End If

Case "Pending"
Select Case (Pending_reason)
Case "echeck"
Case "intl"
Case "verify"
Case "address"
Case "upgrade"
Case "unilateral"
Case "other"
End Select
MailUsTheOrder("PENDING: Order is waiting to be processed.")

Case "Failed"
MailUsTheOrder("FAILED: This only happens if the payment was made from our customer's bank account.")

Case "Denied"
MailUsTheOrder("DENIED: We denied this payment.")
End Select
End If
End If

'Close the response to free resources.
myResponse.Close() 'If it is "OK"

Catch ee As Exception
'do error handling

End Try

End Sub

Public Sub MailUsTheOrder(ByVal TagMsg As String)
Dim mailObj As New MailMessage

SmtpMail.SmtpServer = "mail.freeserve.com"

mailObj.BodyFormat = Mail.MailFormat.Html
mailObj.From = "chris.mottershead@tabtalk.co.uk"
mailObj.Subject = TagMsg
mailObj.Body = TagMsg & " " & vbCrLf & " " & vbCrLf
mailObj.To = "chris.mottershead@tabtalk.co.uk"

mailObj.Body = mailObj.Body & "Order ID: " & OrderID & " " & vbCrLf _
& "Transaction ID: " & Txn_id & " " & vbCrLf _
& "Transaction Type:" & Txn_type & " " & vbCrLf _
& "Payment Type: " & Payment_type & " " & vbCrLf _
& "Payment Status: " & Payment_status & " " & vbCrLf _
& "Pending Reason: " & Pending_reason & " " & vbCrLf _
& "Payment Date: " & Payment_date & " " & vbCrLf _
& "Receiver Email: " & Receiver_email & " " & vbCrLf _
& "Invoice: " & Invoice & " " & vbCrLf _
& "Item Number: " & Item_number & " " & vbCrLf _
& "Item Name: " & Item_name & " " & vbCrLf _
& "Quantity: " & Quantity & " " & vbCrLf _
& "Custom: " & Custom & " " & vbCrLf _
& "Payment Gross: " & Payment_gross & " " & vbCrLf _
& "Payment Fee: " & Payment_fee & " " & vbCrLf _
& "Payer Email: " & Payer_email & " " & vbCrLf _
& "First Name: " & First_name & " " & vbCrLf _
& "Last Name: " & Last_name & " " & vbCrLf _
& "Street Address: " & Address_street & " " & vbCrLf _
& "City: " & Address_city & " " & vbCrLf _
& "State: " & Address_state & " " & vbCrLf _
& "Zip Code: " & Address_zip & " " & vbCrLf _
& "Country: " & Address_country & " " & vbCrLf _
& "Address Status: " & Address_status & " " & vbCrLf _
& "Payer Status: " & Payer_status & " " & vbCrLf _
& "Verify Sign: " & Verify_sign & " " & vbCrLf _
& "Subscriber Date: " & Subscr_date & " " & vbCrLf _
& "Period 1: " & Period1 & " " & vbCrLf _
& "Period 2: " & Period2 & " " & vbCrLf _
& "Period 3: " & Period3 & " " & vbCrLf _
& "Amount 1: " & Amount1 & " " & vbCrLf _
& "Amount 2: " & Amount2 & " " & vbCrLf _
& "Amount 3: " & Amount3 & " " & vbCrLf _
& "Recurring: " & Recurring & " " & vbCrLf _
& "Reattempt: " & Reattempt & " " & vbCrLf _
& "Retry At: " & Retry_at & " " & vbCrLf _
& "Recur Times: " & Recur_times & " " & vbCrLf _
& "UserName: " & Username & " " & vbCrLf _
& "Password: " & Password & " " & vbCrLf _
& "Subscriber ID: " & Subscr_id & " " & vbCrLf _
& "Notify Version: " & Notify_version & " " & vbCrLf
System.Web.Mail.SmtpMail.SmtpServer = "mail.freeserve.com"
System.Web.Mail.SmtpMail.Send(mailObj)

End Sub
</script>

Shane · #2 (**permalink**) 05-20-04, 01:47 PM

What do you mean by "it doesn't write to the database"? Are any errors displayed?

cmottershead · #3 (**permalink**) 05-20-04, 07:24 PM

Quote:

Originally Posted by Shane

What do you mean by "it doesn't write to the database"? Are any errors displayed?

the part of the script that adds the information that paypal post to the script isn't being written to the database and no errors are displayed.

thuenb · #4 (**permalink**) 11-15-04, 01:10 AM

Is the paypal.mdb located in the root of the website? That is the way the connection string is setup... Plus if you want asp.net to be able to write to the DB file you will need to give the asp.net worker process read/write access to the folder that holds the DB.