Posts

SendGrid Inbound Parse

I’ve spent most of a weekend trying to figure out how to get attachments from SendGrid’s Inbound Parse service. If you haven’t heard of it – it accepts an incoming email, parses out the various parts and sends them to you by posting to a web form you have set up for this.

The “text” parts of the message (from, to, subject, etc.) were pretty straightforward, but trying to figure out how to get files that had been attached to the message took me a long time to figure out. (Much longer than it should have, in hindsight.)

I followed the recommendations of the tutorial video and configured Inbound Parse to send the information to RequestBin, a service that will accept and show you posted form values without your having to write any code.

The values that I sent to RequestBin agreed with the documentation. There was an “attachments” value that would tell how many attachments were included and “attachmentX” value(s) for each attachment. (If attachments = 2, there would be an attachment1 value and an attachment2 value.)

I reconfigured SendGrid to post the values to an Azure website I have. The code that I wrote based on the SendGrid documentation (and what I saw on RequestBin) kept throwing exceptions, so I took advantage of Azure’s remote debugging feature (which is awesome, by the way).

The attachment-related values weren’t showing up. SendGrid has posted 13 form values to RequestBin, but I was consistently only getting 9 in my ASP.Net application. For 2 days, I assumed that something was going on with ASP.Net, thinking that it was related to request validation, or something like that.

After hours of fighting with this, I finally realized that when I had re-configured SendGrid to post to my Azure site instead of RequestBin, I had checked the “SendRaw” checkbox. The explanation next to the checkbox says “This will post the full mime message”. What it doesn’t tell you is – it changes what SendGrid posts.

If you check the SendRaw checkbox – you won’t get these form values:

  • attachments
  • attachment-info
  • attachmentX
  • text
  • html

Instead you will get an “email” value that has the original email message.

Since the whole point of using the Inbound Parse is to not have to parse the email, I’d recommend not checking the SendRaw checkbox.

DATALENGTH vs. LEN in T-SQL (or detecting trailing spaces gotchas)

A client reported an issue with a SQL Server Reporting Services (SSRS) report that had recently been upgraded from 2008R2 to 2012.  The report was splitting a particular area into 2 pages (breaking) where it used to print all of that area on the same page, which is how the client still wanted it to print.

After looking at layout issues, I finally realized that the actual issue was that a group definition was evaluating some string values as different when they appeared to be the same.

The report was designed to look like this:

Group Header for value AA

Details for value AA

Group Footer for value AA

Group Header for value AB

Details for value AB

Group Footer for value AB

When the report ran – I got two sections (complete with header and footer) for value “AA”. Some of the details showed up in one section and some in the other.  When I ran the query – it looked like all the rows had the same value (“AA”).  I suspected trailing spaces or some other non-visible character, so I ran a select distinct … query and only got 1 occurrence of “AA”. I added a “where Field = ‘AA'” clause and got all of the rows. I added a LEN(Field) column and all of them showed up as “2”.

DATALENGTH to the rescue.  If you read this – you can see that SQL Server basically ignores trailing spaces when doing a string comparison. If you look at the documention for the LEN function, it says “… excluding trailing blanks”.

Turns out that LEN returns the number of characters and DATALENGTH returns the number of bytes. Once I added a DATALENGTH(field) column – I could see that some returned 2 and some returned 10.  My query was UNIONing several data sources together and some of the “AA” values were apparently defined in a CHAR(10) field.

I added some RTrim statements to the part of the query that was showing DATALENGTH of 10 and the report now ran as expected.

I’m not sure if it’s SQL or SSRS, but something has changed regarding this between 2008R2 and 2012, because this report ran for years the old way.

Posted in SQL

Hotmail handles attachments differently at SMTP level

I discovered a problem in some email processing code recently.  Circlebox has an email forwarding service where an end user can send an email to an address like groupname@cbcircle.com and that message is then sent to each group member’s email address according to their subscription preferences.

A user was sending a message with an attachment from a Hotmail address but the attachment wasn’t being forwarded to the end users. It turned out to be a difference in the headers.

Here are the relevant headers from a message sent from an Exchange Server.

Content-Type: application/vnd.ms-excel;
name=”Directory.xls”
Content-Description: Directory.xls
Content-Disposition: attachment;
filename=”Directory.xls”; size=64512;
creation-date=”Sun, 23 Feb 2014 13:58:23 GMT”;
modification-date=”Sun, 23 Feb 2014 13:58:23 GMT”
Content-Transfer-Encoding: base64

Here are the relevant headers from an identical message sent from Hotmail:

Content-Type: application/vnd.ms-excel
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename=”Directory.xls”

My code was expecting the Content-Type header to have a Name parameter that contained the attachment filename. As you can see, this is in the first set of headers, but not in the second one.

This seems to be the direction things are heading in. RFC 2183 defines this and it looks like most mail servers are currently using both the Name parameter of the Content-Type header and the Filename parameter of the Content-Disposition header, but Hotmail is only using Content-Disposition.

Don’t host multiple websites with one IP address if one of the sites uses SSL

I just figured this out yesterday and it’s probably obvious to most people, but since I hadn’t thought of it before – maybe this will help someone else.

You can use host headers to publish multiple websites on the same IP address, but this doesn’t work when using SSL because the host header is encrypted so the web server can’t route based on this value. This means you can only bind 1 web site to a given IP address using SSL.

You can publish Site1, Site2, and Site3 on the same IP address using http and then bind SSL to Site1 on the same IP address, so that all of these would work:
http://Site1
http://Site2
http://Site3
https://Site1
(In DNS, Site1, Site2, and Site3 all resolve to the same IP address.)

What just occurred to me yesterday (when a client of mine pointed it out) was that if you browse to https://Site2, you will get the standard certificate error that browsers give when something doesn’t look right with the certificate.  In this case – the certificate is for Site1 but the user is browsing to Site2.

If the user clicks the button to proceed in spite of the error – now you’ve got a problem.  They think they are at Site2, but what they are seeing is Site1.

It had just never occurred to me that someone would specifically type https to get to a site that wasn’t set up for SSL.

Posted in IT

Using iText to generate PDF from Html using HtmlWorker

Someone asked this on Google+ and I had a program that did it so I decided to turn it into a quick blog post.  I stripped away the application specific logic, but this is the gist of the iText code. (Technically this is iTextSharp, but I’m guessing it will work in iText as well.)  The trick is to use the HtmlWorker. (If I remember right, there’s a newer XMLWorkerHelper object that’s newer/better.)

This returns the PDF in a memory stream (useful for sending to the Response object to send to a browser):

using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html.simpleparser;

public static Stream RenderPDF()

{

    Document document = new Document(PageSize.LETTER, 36F, 36F, 36F, 36F);
    MemoryStream memoryStream = new MemoryStream();
    PdfWriter writer = PdfWriter.GetInstance(document, memoryStream);
    document.Open();

    HTMLWorker htmlWorker = new HTMLWorker(document);
    var sectionTitleFont = new Font(Font.FontFamily.TIMES_ROMAN, 24, Font.BOLD, BaseColor.BLACK);
    var sectionTitleParagraph = new Paragraph(“Title text”, sectionTitleFont);

    document.Add(sectionTitleParagraph);

    document.NewPage();

    StringBuilder sb = new StringBuilder();
    string strBody = ”

This is the paragraph body

“;
    sb.AppendLine(strBody);
    sb.AppendLine(“”);

    htmlWorker.Parse(new StringReader(sb.ToString()));
    document.NewPage();

    writer.CloseStream = false;
    document.Close();
    memoryStream.Position = 0;
    return memoryStream;

}

What is Circlebox?

Circlebox is a web startup that I founded a couple of years ago. Hard to say exactly when, because I’ve been thinking about it for several years and have been actually writing code and using it myself for about a year and a half. I’m still honing my elevator pitch – this is the longer version. I’ll start with some examples of the problems it’s designed to solve.

Scenario #1: Headcount and $10

One of the earliest examples I can give is of coordinating a Cub Scout campout.  The pack leaders needed to know who was going.  At the pack meeting, we got a lot of “let me check our schedule with my wife and I’ll email you”.  Because there are multiple leaders, not everyone emails the same person, so the leaders keep emailing revisions of an Excel spreadsheet back and forth.

We also needed to collect $10/person to cover hot dogs, hamburger patties, buns, mustard, etc.  At the meeting, nobody has their checkbook with them.  At the campout, a few people remember to bring checks, but most people (of the people that pay) bring 20 dollar bills straight from the ATM so making change is a problem.

We looked at some existing websites, but couldn’t find anything that was just right.

What about Evite?

Sites like Evite would handle the head count, but not collecting the money.  In addition, Evite is really targeted at one user coordinating an event.  If you have multiple coordinators – you could share your password, but only one email address will get the notifications.

If you are a group leader for multiple groups, it might be OK to share your password with John, since John helps you run the Scout pack, but what if you are also the baseball coach and want to coordinate events for that. John isn’t affiliated with the baseball team and really shouldn’t see the contact info for the people in that group.

What about PayPal?

Sites that would collect money mostly used PayPal.  Not only is PayPal kind of expensive to use – I couldn’t even get our Pack set up with PayPal.I’ve had a personal PayPal account for years. When you are setting up a PayPal account for an organization, you have to say what kind of Business Type you are.  “Non-profit” was the closest match.  Apparently to PayPal, that means “charity” because now they wanted our 501c3 paperwork to prove that we were OK to accept donations. But I didn’t want to accept donations, I wanted to collect $8 for hot dog buns.

In my day job, I worked with accounting software and was familiar with ACH processing (how the gym automatically drafts your monthly membership fee from your checking account).  Since we wanted to collect money from people that we already knew in the real world (and would take a check from) – this seemed OK (and the transaction fees are significantly cheaper).

PayPal’s sweet spot is paying for a guitar amp that you bought from a guy in Seattle on eBay.  Since you don’ t know the guy in Seattle, you might need some recourse from PayPal if doesn’t actually ship the amp. Since we’re only exchanging money between people that already know each other in the real world, we don’t need that level of protection and the less expensive ACH funds transfer works perfectly for us.

Circlebox:

  • Designed for multiple user accounts to administer the same Circles (groups) and for one user account to be able to administer multiple Circles without sharing passwords and emailing spreadsheets back and forth.
  • Will handle electronic invitations and responses to events, including allowing Circle members to pay online via ACH.

Scenario #2:

In other group that I help run, I remember the treasurer saying “I’ve got a good list of who hasn’t paid, but I don’t really have a good way of emailing them individually. BCC won’t work because different people owe different amounts. I really need to be able to email individual statements.”

Circlebox:

  • Will let you email subsets of Circle members like “only those who haven’t paid” or “only those who haven’t replied to the invitation”.

Scenario #3:

Several years ago, I served on the governing board for our local youth baseball league.  I had been on the board for a couple of years and I knew that this was when we normally started meeting, but I hadn’t heard anything. When I finally talked to the president, she said “we’ve had 2 meetings and wondered where you were.”

Turns out, someone had entered my email address incorrectly on the first email and all communication from that point on had been “Reply to All” from the original email. I never got any of them.

There’s also a flip side to this issue. My kids are involved in Boy Scouts and the local council asked for volunteers to come to a meeting to discuss how they could better use technology. I thought “there’s something I can help with” so I went. When I got there, there were about 50 other guys there and the conversations I heard there were of the “my hard drive is bigger than yours” type. I quickly decided that they could make it without me. Unfortunately, I had put my email on a sign-in sheet which the leader then forwarded to everyone who attended.

Because these were technical types, just about everyone of them made their own personal distribution group from the list and the conversations began. There were many back and forth emails to the group and I had no way of getting my email address off of everyone’s list.

Circlebox:

  • Will let you subscribe, unsubscribe and change the email address where you receive notifications.
  • Is “self-serve” so the group leader doesn’t have to make these updates for the group members

More to come, but this should give you the general idea of Circlebox.

Turn off Shutdown Event Tracker in Windows Server via Group Policy

If you’re like me, it annoys me that Windows Server forces me to tell it why I’m rebooting my server.  For years, I’ve thought “I bet there’s a way to turn that off” but just typed “a” in the comment textbox so the OK button would be enabled and kept going.

Today, I finally got around to searching for it.  In Group Policy, go to Computer Configuration, Policies, Administrative Templates, System and look for the Display Shutdown Event Tracker.  If you set it to Disabled, it won’t show the Shutdown Event Tracker.

P.S. – I have to confess – as I was typing this, I clicked Restart on my server to look at the Shutdown Event Tracker to see what the label on the textbox was (where I type “a”).  As soon as I clicked Restart, the server rebooted – because I had just configured this setting.  I had to laugh at myself for being annoyed that the Shutdown Event Tracker didn’t come up, when I had just turned it off and was writing a post about how to turn it off.  🙂

Posted in IT

PayPal IPN integration from .Net

Note: I recently put a C# version of this code on GitHub at https://github.com/jtrotman10/PayPalIpnWebFormsDemo

It’s easy enough to integrate your site with PayPal so that you can send shopping carts to get paid for. Since the communication is via a POST, it did take me a couple of minutes to get an ASP.Net page to post to something other than itself via a postback. I ended up using an ImageButton (using a PayPal logo image) and set the PostBackURL property to https://www.paypal.com/cgi-bin/webscr. The static fields (like “business” to pass the email address that is my PayPal user id) were sent by putting a HiddenField control on the WebForm. The dynamic fields (like shopping cart items) were added to the form in code (in the PageLoad event) like this:
Dim h As HiddenField

h = New HiddenField
h.ID = “shopping_url”
h.Value = “http://www.mysite.com/shopping.aspx”
Form.Controls.Add(h)

It’s a little more complicated to get PayPal to tell your site that you’ve been paid.  PayPal has a feature called Instant Payment Notification, or IPN.  It took me a little while to get it debugged and working so I thought I would post a simple, but working, IPN solution that uses ASP.Net, VB.Net and SQL Server.

First, create a table in SQL Server. Here’s a script generated from SQL 2005 that will create a table with a field for each value that PayPal passes to you via IPN:

/****** Object:  Table [PayPalPayments]    Script Date: 08/06/2008 23:35:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [PayPalPayments](
[PayPalID] [int] IDENTITY(1,1) NOT NULL,
[verify_sign] [varchar](255) NULL,
[address_city] [varchar](40) NULL,
[address_country] [varchar](64) NULL,
[address_name] [varchar](128) NULL,
[address_state] [varchar](40) NULL,
[address_status] [varchar](15) NULL,
[address_street] [varchar](200) NULL,
[address_zip] [varchar](20) NULL,
[first_name] [varchar](64) NULL,
[last_name] [varchar](64) NULL,
[payer_business_name] [varchar](127) NULL,
[payer_email] [varchar](127) NULL,
[payer_id] [varchar](13) NULL,
[payer_status] [varchar](15) NULL,
[residence_country] [char](2) NULL,
[business] [varchar](127) NULL,
[item_name1] [varchar](127) NULL,
[quantity1] [smallint] NULL,
[item_name2] [varchar](127) NULL,
[quantity2] [smallint] NULL,
[item_name3] [varchar](127) NULL,
[quantity3] [smallint] NULL,
[item_name4] [varchar](127) NULL,
[quantity4] [smallint] NULL,
[item_name5] [varchar](127) NULL,
[quantity5] [smallint] NULL,
[item_name6] [varchar](127) NULL,
[quantity6] [smallint] NULL,
[receiver_email] [varchar](127) NULL,
[receiver_id] [varchar](13) NULL,
[custom] [varchar](255) NULL,
[invoice] [varchar](127) NULL,
[memo] [varchar](255) NULL,
[auth_id] [varchar](19) NULL,
[auth_exp] [varchar](28) NULL,
[auth_amount] [smallmoney] NULL,
[auth_status] [varchar](10) NULL,
[num_cart_items] [smallint] NULL,
[parent_txn_id] [varchar](19) NULL,
[payment_date] [varchar](28) NULL,
[payment_status] [varchar](25) NULL,
[payment_type] [varchar](10) NULL,
[pending_reason] [varchar](20) NULL,
[transaction_entity] [varchar](10) NULL,
[txn_id] [varchar](19) NULL,
[txn_type] [varchar](25) NULL,
[mc_fee] [smallmoney] NULL,
[mc_gross] [smallmoney] NULL,
[settle_amount] [smallmoney] NULL,
[settle_currency] [varchar](3) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Here’s a script to create a stored procedure to insert a record into the table:

/****** Object:  StoredProcedure [addPayPalPayment]    Script Date: 08/06/2008 23:39:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE  PROCEDURE [addPayPalPayment]
@verify_sign varchar(255),
@address_city varchar(40),
@address_country varchar(64),
@address_name varchar(128),
@address_state varchar(40),
@address_status varchar(15),
@address_street varchar(200),
@address_zip varchar(20),
@first_name varchar(64),
@last_name varchar(64),
@payer_business_name varchar(127),
@payer_email varchar(127),
@payer_id varchar(13),
@payer_status varchar(15),
@residence_country varchar(2),
@business varchar(127),
@item_name1 varchar(127),
@quantity1 smallint,
@item_name2 varchar(127),
@quantity2 smallint,
@item_name3 varchar(127),
@quantity3 smallint,
@item_name4 varchar(127),
@quantity4 smallint,
@item_name5 varchar(127),
@quantity5 smallint,
@item_name6 varchar(127),
@quantity6 smallint,
@receiver_email varchar(127),
@receiver_id varchar(13),
@custom varchar(255),
@invoice varchar(127),
@memo  varchar(255),
@auth_id varchar(19),
@auth_exp varchar(28),
@auth_amount smallmoney,
@auth_status varchar(10),
@num_cart_items smallint,
@parent_txn_id varchar(19),
@payment_date varchar(28),
@payment_status varchar(25),
@payment_type varchar(10),
@pending_reason varchar(20),
@transaction_entity varchar(10),
@txn_id varchar(19),
@txn_type varchar(25),
@mc_fee smallmoney,
@mc_gross smallmoney,
@settle_amount smallmoney,
@settle_currency varchar(3)
AS
INSERT INTO [PayPalPayments]([verify_sign], [address_city], [address_country], [address_name], [address_state], [address_status], [address_street], [address_zip], [first_name], [last_name], [payer_business_name], [payer_email], [payer_id], [payer_status], [residence_country], [business], [item_name1], [quantity1], [item_name2], [quantity2], [item_name3], [quantity3], [item_name4], [quantity4], [item_name5], [quantity5], [item_name6], [quantity6], [receiver_email], [receiver_id], [custom], [invoice], [memo], [auth_id], [auth_exp], [auth_amount], [auth_status], [num_cart_items], [parent_txn_id], [payment_date], [payment_status], [payment_type], [pending_reason], [transaction_entity], [txn_id], [txn_type], [mc_fee], [mc_gross], [settle_amount], [settle_currency])
VALUES(@verify_sign, @address_city, @address_country, @address_name, @address_state, @address_status, @address_street, @address_zip, @first_name, @last_name, @payer_business_name, @payer_email, @payer_id, @payer_status, @residence_country, @business, @item_name1, @quantity1, @item_name2, @quantity2, @item_name3, @quantity3, @item_name4, @quantity4, @item_name5, @quantity5, @item_name6, @quantity6, @receiver_email, @receiver_id, @custom, @invoice, @memo, @auth_id, @auth_exp, @auth_amount, @auth_status, @num_cart_items, @parent_txn_id, @payment_date, @payment_status, @payment_type, @pending_reason, @transaction_entity, @txn_id, @txn_type, @mc_fee, @mc_gross, @settle_amount, @settle_currency)

Create a new web site (I used VB.Net 2005) and paste this code into the Page_Load event of default.aspx (you’ll need to add a ConnectionString to the SQL Server where you created the table and the stored procedure in Web.Config. Name the ConnectionString “xyz”):
Dim connectionStrings As ConnectionStringSettingsCollection = WebConfigurationManager.ConnectionStrings
Dim connectionString As String = connectionStrings(“xyz”).ConnectionString

Using cn As SqlConnection = New SqlConnection(connectionString)
cn.Open()
Using cmd As SqlCommand = cn.CreateCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = “addPayPalPayment”

Dim strVal As String
Dim decVal As Decimal

If IsNothing(Request.Form(“verify_sign”)) Then
strVal = “”
Else
strVal = Request.Form(“verify_sign”)
End If
cmd.Parameters.AddWithValue(“@verify_sign”, strVal)

If IsNothing(Request.Form(“address_city”)) Then
strVal = “”
Else
strVal = Request.Form(“address_city”)
End If
cmd.Parameters.AddWithValue(“@address_city”, strVal)

If IsNothing(Request.Form(“address_country”)) Then
strVal = “”
Else
strVal = Request.Form(“address_country”)
End If
cmd.Parameters.AddWithValue(“@address_country”, strVal)

If IsNothing(Request.Form(“address_name”)) Then
strVal = “”
Else
strVal = Request.Form(“address_name”)
End If
cmd.Parameters.AddWithValue(“@address_name”, strVal)

If IsNothing(Request.Form(“address_state”)) Then
strVal = “”
Else
strVal = Request.Form(“address_state”)
End If
cmd.Parameters.AddWithValue(“@address_state”, strVal)

If IsNothing(Request.Form(“address_status”)) Then
strVal = “”
Else
strVal = Request.Form(“address_status”)
End If
cmd.Parameters.AddWithValue(“@address_status”, strVal)

If IsNothing(Request.Form(“address_street”)) Then
strVal = “”
Else
strVal = Request.Form(“address_street”)
End If
cmd.Parameters.AddWithValue(“@address_street”, strVal)

If IsNothing(Request.Form(“address_zip”)) Then
strVal = “”
Else
strVal = Request.Form(“address_zip”)
End If
cmd.Parameters.AddWithValue(“@address_zip”, strVal)

If IsNothing(Request.Form(“first_name”)) Then
strVal = “”
Else
strVal = Request.Form(“first_name”)
End If
cmd.Parameters.AddWithValue(“@first_name”, strVal)

If IsNothing(Request.Form(“last_name”)) Then
strVal = “”
Else
strVal = Request.Form(“last_name”)
End If
cmd.Parameters.AddWithValue(“@last_name”, strVal)

If IsNothing(Request.Form(“payer_business_name”)) Then
strVal = “”
Else
strVal = Request.Form(“payer_business_name”)
End If
cmd.Parameters.AddWithValue(“@payer_business_name”, strVal)

If IsNothing(Request.Form(“payer_email”)) Then
strVal = “”
Else
strVal = Request.Form(“payer_email”)
End If
cmd.Parameters.AddWithValue(“@payer_email”, strVal)

If IsNothing(Request.Form(“payer_id”)) Then
strVal = “”
Else
strVal = Request.Form(“payer_id”)
End If
cmd.Parameters.AddWithValue(“@payer_id”, strVal)

If IsNothing(Request.Form(“payer_status”)) Then
strVal = “”
Else
strVal = Request.Form(“payer_status”)
End If
cmd.Parameters.AddWithValue(“@payer_status”, strVal)

If IsNothing(Request.Form(“residence_country”)) Then
strVal = “”
Else
strVal = Request.Form(“residence_country”)
End If
cmd.Parameters.AddWithValue(“@residence_country”, strVal)

If IsNothing(Request.Form(“business”)) Then
strVal = “”
Else
strVal = Request.Form(“business”)
End If
cmd.Parameters.AddWithValue(“@business”, strVal)

If IsNothing(Request.Form(“item_name1”)) Then
strVal = “”
Else
strVal = Request.Form(“item_name1”)
End If
cmd.Parameters.AddWithValue(“@item_name1”, strVal)

If IsNothing(Request.Form(“quantity1”)) Then
strVal = “”
Else
strVal = Request.Form(“quantity1”)
End If
cmd.Parameters.AddWithValue(“@quantity1”, strVal)

If IsNothing(Request.Form(“item_name2”)) Then
strVal = “”
Else
strVal = Request.Form(“item_name2”)
End If
cmd.Parameters.AddWithValue(“@item_name2”, strVal)

If IsNothing(Request.Form(“quantity2”)) Then
strVal = “”
Else
strVal = Request.Form(“quantity2”)
End If
cmd.Parameters.AddWithValue(“@quantity2”, strVal)

If IsNothing(Request.Form(“item_name3”)) Then
strVal = “”
Else
strVal = Request.Form(“item_name3”)
End If
cmd.Parameters.AddWithValue(“@item_name3”, strVal)

If IsNothing(Request.Form(“quantity3”)) Then
strVal = “”
Else
strVal = Request.Form(“quantity3”)
End If
cmd.Parameters.AddWithValue(“@quantity3”, strVal)

If IsNothing(Request.Form(“item_name4”)) Then
strVal = “”
Else
strVal = Request.Form(“item_name4”)
End If
cmd.Parameters.AddWithValue(“@item_name4”, strVal)

If IsNothing(Request.Form(“quantity4”)) Then
strVal = “”
Else
strVal = Request.Form(“quantity4”)
End If
cmd.Parameters.AddWithValue(“@quantity4”, strVal)

If IsNothing(Request.Form(“item_name5”)) Then
strVal = “”
Else
strVal = Request.Form(“item_name5”)
End If
cmd.Parameters.AddWithValue(“@item_name5”, strVal)

If IsNothing(Request.Form(“quantity5”)) Then
strVal = “”
Else
strVal = Request.Form(“quantity5”)
End If
cmd.Parameters.AddWithValue(“@quantity5”, strVal)

If IsNothing(Request.Form(“item_name6”)) Then
strVal = “”
Else
strVal = Request.Form(“item_name6”)
End If
cmd.Parameters.AddWithValue(“@item_name6”, strVal)

If IsNothing(Request.Form(“quantity6”)) Then
strVal = “”
Else
strVal = Request.Form(“quantity6”)
End If
cmd.Parameters.AddWithValue(“@quantity6”, strVal)

If IsNothing(Request.Form(“receiver_email”)) Then
strVal = “”
Else
strVal = Request.Form(“receiver_email”)
End If
cmd.Parameters.AddWithValue(“@receiver_email”, strVal)

If IsNothing(Request.Form(“receiver_id”)) Then
strVal = “”
Else
strVal = Request.Form(“receiver_id”)
End If
cmd.Parameters.AddWithValue(“@receiver_id”, strVal)

If IsNothing(Request.Form(“custom”)) Then
strVal = “”
Else
strVal = Request.Form(“custom”)
End If
cmd.Parameters.AddWithValue(“@custom”, strVal)

If IsNothing(Request.Form(“invoice”)) Then
strVal = “”
Else
strVal = Request.Form(“invoice”)
End If
cmd.Parameters.AddWithValue(“@invoice”, strVal)

If IsNothing(Request.Form(“memo”)) Then
strVal = “”
Else
strVal = Request.Form(“memo”)
End If
cmd.Parameters.AddWithValue(“@memo”, strVal)

If IsNothing(Request.Form(“auth_id”)) Then
strVal = “”
Else
strVal = Request.Form(“auth_id”)
End If
cmd.Parameters.AddWithValue(“@auth_id”, strVal)

If IsNothing(Request.Form(“auth_exp”)) Then
strVal = “”
Else
strVal = Request.Form(“auth_exp”)
End If
cmd.Parameters.AddWithValue(“@auth_exp”, strVal)

If IsNothing(Request.Form(“auth_amount”)) Then
decVal = 0
Else
decVal = CDec(Request.Form(“auth_amount”))
End If
cmd.Parameters.AddWithValue(“@auth_amount”, decVal)

If IsNothing(Request.Form(“auth_status”)) Then
strVal = “”
Else
strVal = Request.Form(“auth_status”)
End If
cmd.Parameters.AddWithValue(“@auth_status”, strVal)

If IsNothing(Request.Form(“num_cart_items”)) Then
strVal = “”
Else
strVal = Request.Form(“num_cart_items”)
End If
cmd.Parameters.AddWithValue(“@num_cart_items”, strVal)

If IsNothing(Request.Form(“parent_txn_id”)) Then
strVal = “”
Else
strVal = Request.Form(“parent_txn_id”)
End If
cmd.Parameters.AddWithValue(“@parent_txn_id”, strVal)

If IsNothing(Request.Form(“payment_date”)) Then
strVal = “”
Else
strVal = Request.Form(“payment_date”)
End If
cmd.Parameters.AddWithValue(“@payment_date”, strVal)

If IsNothing(Request.Form(“payment_status”)) Then
strVal = “”
Else
strVal = Request.Form(“payment_status”)
End If
cmd.Parameters.AddWithValue(“@payment_status”, strVal)

If IsNothing(Request.Form(“payment_type”)) Then
strVal = “”
Else
strVal = Request.Form(“payment_type”)
End If
cmd.Parameters.AddWithValue(“@payment_type”, strVal)

If IsNothing(Request.Form(“pending_reason”)) Then
strVal = “”
Else
strVal = Request.Form(“pending_reason”)
End If
cmd.Parameters.AddWithValue(“@pending_reason”, strVal)

If IsNothing(Request.Form(“transaction_entity”)) Then
strVal = “”
Else
strVal = Request.Form(“transaction_entity”)
End If
cmd.Parameters.AddWithValue(“@transaction_entity”, strVal)

If IsNothing(Request.Form(“txn_id”)) Then
strVal = “”
Else
strVal = Request.Form(“txn_id”)
End If
cmd.Parameters.AddWithValue(“@txn_id”, strVal)

If IsNothing(Request.Form(“txn_type”)) Then
strVal = “”
Else
strVal = Request.Form(“txn_type”)
End If
cmd.Parameters.AddWithValue(“@txn_type”, strVal)

If IsNothing(Request.Form(“mc_fee”)) Then
decVal = 0
Else
decVal = CDec(Request.Form(“mc_fee”))
End If
cmd.Parameters.AddWithValue(“@mc_fee”, decVal)

If IsNothing(Request.Form(“mc_gross”)) Then
decVal = 0
Else
decVal = CDec(Request.Form(“mc_gross”))
End If
cmd.Parameters.AddWithValue(“@mc_gross”, decVal)

If IsNothing(Request.Form(“settle_amount”)) Then
decVal = 0
Else
decVal = CDec(Request.Form(“settle_amount”))
End If
cmd.Parameters.AddWithValue(“@settle_amount”, decVal)

If IsNothing(Request.Form(“settle_currency”)) Then
strVal = “”
Else
strVal = Request.Form(“settle_currency”)
End If
cmd.Parameters.AddWithValue(“@settle_currency”, strVal)

cmd.ExecuteNonQuery()
End Using
cn.Close()
End Using

End Sub

That’s it.  When you post to PayPal, use the “notify_url” variable to point back to the address that you publish this site to.  When you someone completes a payment, PayPal will post back to this page will all of the transaction details and this page will call the addPayPalPayment stored procedure which will create a record in the PayPalPayments table.  The value that you send with the cart in the “custom” variable will be returned via IPN. Use this field to hold an order number or something like that and then you can apply the PayPalPayment (which will have the value stored in the Custom field) to the order.

PayPal IPN integration from .Net

Note: I recently put a C# version of this code on GitHub at https://github.com/jtrotman10/PayPalIpnWebFormsDemo


It’s easy enough to integrate your site with PayPal so that you can send shopping carts to get paid for. Since the communication is via a POST, it did take me a couple of minutes to get an ASP.Net page to post to something other than itself via a postback. I ended up using an ImageButton (using a PayPal logo image) and set the PostBackURL property to https://www.paypal.com/cgi-bin/webscr. The static fields (like “business” to pass the email address that is my PayPal user id) were sent by putting a HiddenField control on the WebForm. The dynamic fields (like shopping cart items) were added to the form in code (in the PageLoad event) like this:
Dim h As HiddenField

h = New HiddenField
h.ID = “shopping_url”
h.Value = “http://www.mysite.com/shopping.aspx”
Form.Controls.Add(h)

It’s a little more complicated to get PayPal to tell your site that you’ve been paid.  PayPal has a feature called Instant Payment Notification, or IPN.  It took me a little while to get it debugged and working so I thought I would post a simple, but working, IPN solution that uses ASP.Net, VB.Net and SQL Server. Continue reading