Update DB with PayPal data (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

TrudyD1474 -> Update DB with PayPal data (8/8/2006 18:15:47)

I can successfully receive information back from PayPal and get it to display on a confirmation page.

I am getting the following fields back:
Credit Card Infor: firstName, lastName
My database id: itemNumber
Payment amount: mcGross

What I want to do is put the Credit Card Information into my registration record. I want this to automatically happen; I do NOT want to have to have the person select a button to get this to happen.

The page is created entirely in ASP Script, so I would like to update the database using ASP Script.

Here is what I've tried to do.

quote:


...
Response.Write("<p><h3>Your order has been received.</h3></p>")
Response.Write("<b>Details</b><br>")
Response.Write("<li>Credit Card Owner: " & firstName & " " & lastName & "</li>")
Response.Write("<li>Description: " & itemName & "</li>")
Response.Write("<li>Registration No: " & itemNumber & "</li>")
Response.Write("<li>Amount: " & mcCurrency & " " & mcGross & "</li>")
Response.Write("<hr>")

<!--#include file="_fpclass/fpdblib.inc"-->
<!--#include file="_fpclass/fpdbrgn1.inc"-->
Dim Connect
set connect = server.createObject("ADOB.Connection")
connect.open "Registration"


fp_sQry="SELECT * FROM Registration WHERE (Registratio_ID = ::itemNumber::)"
Registration("R_Credit_Card_Payor") = "lastName"
Registration.Update
...


I've looked in a couple of books on ASP but not sure which way to go, since for the rest of the pages I'm using FrontPage to generate the DB information.

Can anyone help.
Trudy



BeTheBall -> RE: Update DB with PayPal data (8/8/2006 19:33:13)

Post the full page code and I may be able to take a stab at it.



Spooky -> RE: Update DB with PayPal data (8/8/2006 20:44:44)

What you need is an insert, something like this : 

<%
sSQL = "INSERT INTO Registration (R_Credit_Card_Payor) VALUES ('"&lastName&"') WHERE Registratio_ID ="& cLng(itemNumber)
Set oADO = Server.CreateObject("ADODB.Connection")
oADO.Open DSN
oADO.Execute(sSQL),,129
oADO.Close
Set oADO = Nothing
%>


Note - you would need to specify the connection as sDSN



BeTheBall -> RE: Update DB with PayPal data (8/9/2006 11:56:49)


quote:

ORIGINAL: TrudyD1474

What I want to do is put the Credit Card Information into my registration record. I want this to automatically happen; I do NOT want to have to have the person select a button to get this to happen.



Can you clarify a bit? When you say put the Credit Card Information into the registration record, are you talking about updating a record that is already there or adding a new record? If you are going to update or even add, I would assume you would need the unique ID for the user so as to be able to associate the data. Is that info available on the page?



TrudyD1474 -> RE: Update DB with PayPal data (8/9/2006 12:48:49)

Here's my error:
quote:


Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/registration/payment_confirmation.asp, line 84



Here's the coding in global.asa
quote:


'--Project Data Connection
Application("Registration_ConnectionString") = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=fpdb/Registratio.mdb"
FrontPage_UrlVars(0) = "Registration_ConnectionString"
Application("Registration_ConnectionTimeout") = 15
Application("Registration_CommandTimeout") = 30
Application("Registration_CursorLocation") = 3
Application("Registration_RuntimeUserName") = ""
Application("Registration_RuntimePassword") = ""
'--
Application("FrontPage_UrlVars") = FrontPage_UrlVars
'==FrontPage Generated - endspan==



And here is what I changed the coding to based on Spooky's input:
quote:


sSQL = "INSERT INTO Registration (R_Credit_Card_Payor) VALUES ('"&lastName&"') WHERE Registratio_ID ="& cLng(itemNumber)
Set Registration = Server.CreateObject("ADODB.Connection")
Registration.Open DSN=Registration
Registration.Execute(sSQL),,129
Registration.Close
Set Registration = Nothing



Trudy



TrudyD1474 -> RE: Update DB with PayPal data (8/9/2006 14:25:30)

Spooky,

I don't seem to see balanced " quotes in the above statement you gave me. Is the punctuation correct?
Trudy



TrudyD1474 -> RE: Update DB with PayPal data (8/9/2006 15:17:20)

Spooky,

On the line

Registration.Execute (sSQL),,129

what is the 129 and why is it there?
Trudy



Spooky -> RE: Update DB with PayPal data (8/9/2006 15:41:52)

The last line is numeric so doesnt need quoting
Imagine it like so :
Registratio_ID ="& cLng(itemNumber) &""
The last 2 would cancel out.

129 stands for adexecutenorecords - it is more efficient as it doesnt return a record, just performs the action

Use :
Registration.Open Application("Registration_ConnectionString")



TrudyD1474 -> RE: Update DB with PayPal data (8/9/2006 17:16:36)

Well, now I'm getting a different message:

Microsoft JET Database Engine error '80040e14'

Missing semicolon (;) at end of SQL statement.

/registration/payment_confirmation.asp, line 85


Here is my code now:
quote:


sSQL = "INSERT INTO Registration (R_Credit_Card_Payor) VALUES ('"&lastName&"') WHERE Registratio_ID ="& cLng(itemNumber)
Set Registration = Server.CreateObject("ADODB.Connection")
Registration.Open Application("Registration_ConnectionString")
Registration.Execute(sSQL),,129
Registration.Close
Set Registration = Nothing




The red line is line 85. Now what do I do?



BeTheBall -> RE: Update DB with PayPal data (8/9/2006 18:40:16)

Does this work? 

sSQL = "UPDATE Registration SET R_Credit_Card_Payor = '"&lastName&"' WHERE Registratio_ID ="& cLng(itemNumber)
Set Registration = Server.CreateObject("ADODB.Connection")
Registration.Open Application("Registration_ConnectionString")
Registration.Execute(sSQL),,129
Registration.Close
Set Registration = Nothing


An INSERT wouldn't have a WHERE clause. [;)]



TrudyD1474 -> RE: Update DB with PayPal data (8/9/2006 19:09:39)

I made the change, and that is very logical. However, I'm still getting the following error:

Microsoft JET Database Engine error '80040e14'

Missing semicolon (;) at end of SQL statement.

/registration/payment_confirmation.asp, line 85

mycode:
quote:


sSQL = "UPDATE Registration (R_Credit_Card_Payor) VALUES ('"&lastName&"') WHERE Registratio_ID ="& cLng(itemNumber)
Set Registration = Server.CreateObject("ADODB.Connection")
Registration.Open Application("Registration_ConnectionString")
Registration.Execute(sSQL),,129
Registration.Close
Set Registration = Nothing




BeTheBall -> RE: Update DB with PayPal data (8/9/2006 19:22:43)

You aren't putting the SQL on two lines like it shows in your post are you?



TrudyD1474 -> RE: Update DB with PayPal data (8/9/2006 19:33:56)

no........
all one line.......

Trudy



BeTheBall -> RE: Update DB with PayPal data (8/9/2006 19:46:13)

The semicolon error is a telltale sign of using a WHERE clause with an INSERT. If you are sure you replaced the INSERT SQL with the UPDATE SQL, I can only suggest trying to include the semicolon, like this: 

sSQL = "UPDATE Registration (R_Credit_Card_Payor) VALUES ('"&lastName&"') WHERE Registratio_ID ="& cLng(itemNumber) &";"



TrudyD1474 -> RE: Update DB with PayPal data (8/9/2006 19:50:54)

Already tried that...........

here is the line
quote:


sSQL = "UPDATE Registration (R_Credit_Card_Payor) VALUES ('"&lastName&"') WHERE Registratio_ID ="& cLng(itemNumber)




TrudyD1474 -> RE: Update DB with PayPal data (8/9/2006 19:55:20)

tried it again....

here is the message:

Microsoft JET Database Engine error '80040e14'

Syntax error in UPDATE statement.

/registration/payment_confirmation.asp, line 85


so there does seem to be a problem with the UPDATE



BeTheBall -> RE: Update DB with PayPal data (8/9/2006 19:56:07)

This is the correct SQL, that I posted in post #12:

sSQL = "UPDATE Registration SET R_Credit_Card_Payor = '"&lastName&"' WHERE Registratio_ID ="& cLng(itemNumber)

see the difference?

UPDATE statements have a different syntax that INSERT statements.

UPDATE tableName SET (dbfield=formfield)

INSERT INTO tableName (dbfield) VALUES (formfield)




TrudyD1474 -> RE: Update DB with PayPal data (8/9/2006 20:11:54)

sSQL = "UPDATE Registration SET (R_Credit_Card_Payor) = ('"&lastName&"') WHERE Registratio_ID ="& cLng(itemNumber)


Do we still use WHERE?
I think I still have a problem with format.



BeTheBall -> RE: Update DB with PayPal data (8/9/2006 20:17:07)

Yes. The WHERE specifies which record to update. You added () around the db field and the variable. Did you try it exactly as I posted, without the ()?

sSQL = "UPDATE Registration SET R_Credit_Card_Payor = '"&lastName&"' WHERE Registratio_ID ="& cLng(itemNumber)



TrudyD1474 -> RE: Update DB with PayPal data (8/9/2006 20:30:48)

OK, here is the coding.....but I think I have quotes in the wrong place.......

sSQL = "UPDATE Registration SET (Registration_id=" & itemNumber ") INSERT INTO Registration (R_Credit_Car_Payor) VALUES (" & lastName ")"
Set Registration = Server.CreateObject("ADODB.Connection")
Registration.Open Application("Registration_ConnectionString")
Registration.Execute(sSQL),,129
Registration.Close
Set Registration = Nothing


my message is this:

Expected end of statement

/registration/payment_confirmation.asp, line 81

sSQL = "UPDATE Registration SET (Registration_id=" & itemNumber ") INSERT INTO Registration (R_Credit_Car_Payor) VALUES (" & lastName ")"
----------------------------------------------------------------^
Trudy



TrudyD1474 -> RE: Update DB with PayPal data (8/9/2006 20:35:54)

Duane,

I copied and pasted your line......

here is the message......the underline goes as far as SET

Expected end of statement

/registration/payment_confirmation.asp, line 81

sSQL = "UPDATE Registration SET R_Credit_Card_Payor = '"&lastName&"' WHERE Registratio_ID ="& cLng(itemNumber) Set Registration = Server.CreateObject("ADODB.Connection")
---------------------------------------------------------------------------------------------------------------^



TrudyD1474 -> RE: Update DB with PayPal data (8/9/2006 20:40:43)

Object required: ''

/registration/payment_confirmation.asp, line 82


recopied and repasted.......


quote:


sSQL = "UPDATE Registration SET R_Credit_Card_Payor = '"&lastName&"' WHERE Registratio_ID ="& cLng(itemNumber)
Registration.Open Application("Registration_ConnectionString")
Registration.Execute(sSQL),,129
Registration.Close
Set Registration = Nothing



BeTheBall -> RE: Update DB with PayPal data (8/9/2006 21:02:39)

What is on line 82? Might want to post the whole code here. Do that by putting the code between these tags:

[ code ] and [ /code ]

Except remove the spaces before and after the brackets.



TrudyD1474 -> RE: Update DB with PayPal data (8/9/2006 21:10:25)



error message
quote:


Object required: ''

/registration/payment_confirmation.asp, line 82



code line 82 is in red.......
quote:


<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Payment Confirmation</title>
</head>

<body>


<p><b><font size="4">Thank you for your payment.</font></b> </p>
<p>Your transaction has been completed, and a receipt for your purchase has been
emailed to you. You may log into your account at
<a href="https://www.sandbox.paypal.com/us/">www.sandbox.paypal.com/us</a> to
view details of this transaction. .</p>

<p><img border="0" src="2_workers.gif" width="600" height="32"></p>


<%@LANGUAGE="VBScript"%>
<%

Dim authToken, txToken
Dim query
Dim objHttp
Dim sQuerystring
Dim sParts, iParts, aParts
Dim sResults, sKey, sValue
Dim i, result
Dim itemNumber, firstName, lastName, itemName, mcGross, mcCurrency

authToken ="LObybRyuF1ioO7uufP7IhdZwV-bt1mblFLi7X21pr4GAQZYhKfQxZNVAh7y"
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 "item_number"
itemNumber = sValue
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>Credit Card Owner: " & firstName & " " & lastName & "</li>")
Response.Write("<li>Description: " & itemName & "</li>")
Response.Write("<li>Registration No: " & itemNumber & "</li>")
Response.Write("<li>Amount: " & mcCurrency & " " & mcGross & "</li>")
Response.Write("<hr>")

sSQL = "UPDATE Registration SET R_Credit_Card_Payor = '"&lastName&"' WHERE Registratio_ID ="& cLng(itemNumber)
Registration.Open Application("Registration_ConnectionString")
Registration.Execute(sSQL),,129
Registration.Close
Set Registration = Nothing


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


</body>

</html>



BeTheBall -> RE: Update DB with PayPal data (8/9/2006 22:20:18)

You removed this line:

Set Registration = Server.CreateObject("ADODB.Connection")

Put it back in right after or before the sSQL line.



TrudyD1474 -> RE: Update DB with PayPal data (8/9/2006 22:28:44)

[sm=banana.gif]

It works.

I owe so much to Duane for his persistance, patience and help.

Thanks again Duane.

Trudy
[sm=yupi3ti.gif]



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.1401367