Failed Transaction

09-16-2010, 06:01 AM

Hi,

is there any way we get a notice when a customer try to place an order and fails due to any reason.

please advise
thanks

09-16-2010, 06:26 AM

Code:

CREATE TRIGGER [dbo].[tr_FailedTransaction] 
   ON  [dbo].[FailedTransaction] 
   AFTER INSERT
AS 
    DECLARE @MailID INT
    DECLARE @hr INT
    DECLARE @Body VARCHAR(4000)
    DECLARE @CustomerID INT
    DECLARE @OrderNumber INT
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT 
        @CustomerID = ft.CustomerID, 
        @OrderNumber = ft.OrderNumber,
        @Body = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(4000),ft.TransactionCommand),'&',char(13)),'%40','@'),'+',' ') + char(13)+char(13) + CONVERT(VARCHAR(4000),ft.TransactionResult)
    FROM INSERTED AS i JOIN FailedTransaction as ft ON i.DBRecNo = ft.DBRecNo

    EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
    EXEC @hr = sp_OASetProperty @MailID, 'From','no-reply@yourdomain.com'
    EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
    EXEC @hr = sp_OASetProperty @MailID, 'BCC','webmaster@yourdomain.com'
    EXEC @hr = sp_OASetProperty @MailID, 'CC', 'webmaster@yourdomain.com'
    EXEC @hr = sp_OASetProperty @MailID, 'Subject', 'Failed credit card transaction'
    EXEC @hr = sp_OASetProperty @MailID, 'To', 'whoever@yourdomain.com'
    EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
    EXEC @hr = sp_OADestroy @MailID
END

You will need to ensure that the SQL Server Surface Area Configuration utility has OLE Automation enabled for this to work (for SQL Express).

09-16-2010, 06:26 AM

I made a small little application that runs on my desktop which checks the database every 5 minutes. It keeps the last failed transaction in a text file. The only problem with this is its only good for one person to use, otherwise the overload would start to be problematic.

All it is a simple C# application that shows time of fail, data sent and data received.

I believe there is also a WSI solution to this out there too, check the WSI forum I am sure someone posted the code to what you need.

EDIT: esedirect was faster than me and posted code!

09-16-2010, 06:28 AM

I revel in my own AWSOMENESS. I am MORE AWESOME than Barney Stinson!

09-16-2010, 06:40 AM

Not one to be out done.

C# Form1.Designer.cs

C#/VB.NET Code:


namespace Failed_Transactions

{

    partial class Form1

    {

        /// <summary>

        /// Required designer variable.

        /// </summary>

        private System.ComponentModel.IContainer components = null;



        /// <summary>

        /// Clean up any resources being used.

        /// </summary>

        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>

        protected override void Dispose(bool disposing)

        {

            if (disposing && (components != null))

            {

                components.Dispose();

            }

            base.Dispose(disposing);

        }



        #region Windows Form Designer generated code



        /// <summary>

        /// Required method for Designer support - do not modify

        /// the contents of this method with the code editor.

        /// </summary>

        private void InitializeComponent()

        {

            this.components = new System.ComponentModel.Container();

            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(Form1));

            this.timer1 = new System.Windows.Forms.Timer(this.components);

            this.MainBoxTxt = new System.Windows.Forms.TextBox();

            this.notifyIcon1 = new System.Windows.Forms.NotifyIcon(this.components);

            this.contextMenuStrip1 = new System.Windows.Forms.ContextMenuStrip(this.components);

            this.showApplicationToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem();

            this.hideApplicationToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem();

            this.shutDownToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem();

            this.button1 = new System.Windows.Forms.Button();

            this.button2 = new System.Windows.Forms.Button();

            this.button3 = new System.Windows.Forms.Button();

            this.contextMenuStrip1.SuspendLayout();

            this.SuspendLayout();

            // 

            // timer1

            // 

            this.timer1.Enabled = true;

            this.timer1.Interval = 300000;

            this.timer1.Tick += new System.EventHandler(this.timer1_Tick);

            // 

            // MainBoxTxt

            // 

            this.MainBoxTxt.BackColor = System.Drawing.SystemColors.GradientInactiveCaption;

            this.MainBoxTxt.Cursor = System.Windows.Forms.Cursors.Arrow;

            this.MainBoxTxt.Location = new System.Drawing.Point(17, 12);

            this.MainBoxTxt.Multiline = true;

            this.MainBoxTxt.Name = "MainBoxTxt";

            this.MainBoxTxt.ReadOnly = true;

            this.MainBoxTxt.RightToLeft = System.Windows.Forms.RightToLeft.No;

            this.MainBoxTxt.ScrollBars = System.Windows.Forms.ScrollBars.Vertical;

            this.MainBoxTxt.Size = new System.Drawing.Size(891, 516);

            this.MainBoxTxt.TabIndex = 0;

            // 

            // notifyIcon1

            // 

            this.notifyIcon1.ContextMenuStrip = this.contextMenuStrip1;

            this.notifyIcon1.Icon = ((System.Drawing.Icon)(resources.GetObject("notifyIcon1.Icon")));

            this.notifyIcon1.Text = "Failed Transactions";

            this.notifyIcon1.Visible = true;

            this.notifyIcon1.BalloonTipClosed += new System.EventHandler(this.notifyIcon1_BalloonTipClosed);

            this.notifyIcon1.BalloonTipClicked += new System.EventHandler(this.notifyIcon1_BalloonTipClicked);

            this.notifyIcon1.MouseDoubleClick += new System.Windows.Forms.MouseEventHandler(this.notifyIcon1_MouseDoubleClick);

            // 

            // contextMenuStrip1

            // 

            this.contextMenuStrip1.Items.AddRange(new System.Windows.Forms.ToolStripItem[] {

            this.showApplicationToolStripMenuItem,

            this.hideApplicationToolStripMenuItem,

            this.shutDownToolStripMenuItem});

            this.contextMenuStrip1.Name = "contextMenuStrip1";

            this.contextMenuStrip1.ShowImageMargin = false;

            this.contextMenuStrip1.Size = new System.Drawing.Size(143, 70);

            // 

            // showApplicationToolStripMenuItem

            // 

            this.showApplicationToolStripMenuItem.Name = "showApplicationToolStripMenuItem";

            this.showApplicationToolStripMenuItem.Size = new System.Drawing.Size(142, 22);

            this.showApplicationToolStripMenuItem.Text = "Show Application";

            this.showApplicationToolStripMenuItem.Click += new System.EventHandler(this.showApplicationToolStripMenuItem_Click);

            // 

            // hideApplicationToolStripMenuItem

            // 

            this.hideApplicationToolStripMenuItem.Name = "hideApplicationToolStripMenuItem";

            this.hideApplicationToolStripMenuItem.Size = new System.Drawing.Size(142, 22);

            this.hideApplicationToolStripMenuItem.Text = "Hide Application";

            this.hideApplicationToolStripMenuItem.Click += new System.EventHandler(this.hideApplicationToolStripMenuItem_Click);

            // 

            // shutDownToolStripMenuItem

            // 

            this.shutDownToolStripMenuItem.Name = "shutDownToolStripMenuItem";

            this.shutDownToolStripMenuItem.Size = new System.Drawing.Size(142, 22);

            this.shutDownToolStripMenuItem.Text = "Shut Down";

            this.shutDownToolStripMenuItem.Click += new System.EventHandler(this.shutDownToolStripMenuItem_Click);

            // 

            // button1

            // 

            this.button1.Location = new System.Drawing.Point(209, 534);

            this.button1.Name = "button1";

            this.button1.Size = new System.Drawing.Size(158, 23);

            this.button1.TabIndex = 1;

            this.button1.Text = "Clear Failed Transactions Log";

            this.button1.UseVisualStyleBackColor = true;

            this.button1.Click += new System.EventHandler(this.button1_Click);

            // 

            // button2

            // 

            this.button2.Location = new System.Drawing.Point(813, 534);

            this.button2.Name = "button2";

            this.button2.Size = new System.Drawing.Size(95, 23);

            this.button2.TabIndex = 2;

            this.button2.Text = "Minimise To Tray";

            this.button2.UseVisualStyleBackColor = true;

            this.button2.Click += new System.EventHandler(this.button2_Click);

            // 

            // button3

            // 

            this.button3.Location = new System.Drawing.Point(17, 534);

            this.button3.Name = "button3";

            this.button3.Size = new System.Drawing.Size(186, 23);

            this.button3.TabIndex = 3;

            this.button3.Text = "Check For Failed Transactions Now";

            this.button3.UseVisualStyleBackColor = true;

            this.button3.Click += new System.EventHandler(this.button3_Click);

            // 

            // Form1

            // 

            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);

            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;

            this.BackColor = System.Drawing.SystemColors.ButtonShadow;

            this.ClientSize = new System.Drawing.Size(920, 562);

            this.Controls.Add(this.button3);

            this.Controls.Add(this.button2);

            this.Controls.Add(this.button1);

            this.Controls.Add(this.MainBoxTxt);

            this.ForeColor = System.Drawing.SystemColors.ControlText;

            this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle;

            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));

            this.MaximizeBox = false;

            this.MinimizeBox = false;

            this.Name = "Form1";

            this.ShowIcon = false;

            this.Text = "Failed Transactions";

            this.contextMenuStrip1.ResumeLayout(false);

            this.ResumeLayout(false);

            this.PerformLayout();



        }



        #endregion



        private System.Windows.Forms.Timer timer1;

        private System.Windows.Forms.TextBox MainBoxTxt;

        private System.Windows.Forms.NotifyIcon notifyIcon1;

        private System.Windows.Forms.ContextMenuStrip contextMenuStrip1;

        private System.Windows.Forms.ToolStripMenuItem hideApplicationToolStripMenuItem;

        private System.Windows.Forms.ToolStripMenuItem shutDownToolStripMenuItem;

        private System.Windows.Forms.ToolStripMenuItem showApplicationToolStripMenuItem;

        private System.Windows.Forms.Button button1;

        private System.Windows.Forms.Button button2;

        private System.Windows.Forms.Button button3;

    }

}

C# Form1.cs

C#/VB.NET Code:


using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.IO;

using System.Data.SqlClient;



namespace Failed_Transactions

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

            MainBoxTxt.Text = "The application will check for Failed Transactions every 5 minutes." + Environment.NewLine;

        }



        private void timer1_Tick(object sender, EventArgs e)

        {

            CheckForNewFailedTransactions();

        }



        private void CheckForNewFailedTransactions()

        {

            //Loading basic data from file and database

            //Build connection string

            string Server = "YOUR IP";

            string Username = "USERNAME";

            string Password = "PASSWORD";

            string Database = "DATABASE";



            string ConnectionString = "Data Source=" + Server + ";";

            ConnectionString += "User ID=" + Username + ";";

            ConnectionString += "Password=" + Password + ";";

            ConnectionString += "Initial Catalog=" + Database;



            // Loaded the last failed transaction

            StreamReader streamReader = new StreamReader(@"I:\\LastFailedTransaction.txt");

            int lastFailed = int.Parse(streamReader.ReadToEnd().ToString());

            streamReader.Close();



            // Check for more from the database from the last one saved

            SqlConnection dbconn = new SqlConnection(ConnectionString);

            SqlConnection dbconn2 = new SqlConnection(ConnectionString);



            //Check the connection is live

            try

            {

                dbconn.ConnectionString = ConnectionString;

                dbconn.Open();



                //If the connection is dead the program would of closed, so we know all is good

                //Execute the SQL query and check it

                SqlCommand SQLQuery = new SqlCommand("SELECT "

                + "OrderNumber, "

                + "OrderDate, "

                + "TransactionCommand, "

                + "TransactionResult "

                + "FROM FailedTransaction "

                + "WHERE OrderNumber > "

                + lastFailed.ToString(), dbconn);



                //WARNING The Database IS Still Open

                using (SqlDataReader rs = SQLQuery.ExecuteReader())

                {

                    //Now loops results

                    while (rs.Read())

                    {

                        int OrderNumber = rs.GetInt32(0);

                        //Open connection for next query

                        dbconn2.Open();

                        //New query to remove false positives

                        SqlCommand CheckQuery = new SqlCommand("SELECT Ordernumber FROM Orders WHERE OrderNumber = " + OrderNumber, dbconn2);

                        using (IDataReader rs2 = CheckQuery.ExecuteReader())

                        {

                            //Check if false positive

                            if (rs2.Read())

                            {

                                // False positive end it here

                            }

                            else

                            {

                                //Carry on this is the real deal

                                DateTime FailDate = rs.GetDateTime(1);

                                string CommandText = rs.GetString(2);

                                string CommandResult = rs.GetString(3);



                                //Place in the text area

                                MainBoxTxt.Text += string.Format("{0} failed at {1}" + Environment.NewLine + Environment.NewLine, OrderNumber.ToString(), FailDate.ToString());

                                MainBoxTxt.Text += string.Format("Transaction details sent" + Environment.NewLine + "{0}" + Environment.NewLine + Environment.NewLine, CommandText);

                                MainBoxTxt.Text += string.Format("Transaction result" + Environment.NewLine + "{0}" + Environment.NewLine, CommandResult);

                                MainBoxTxt.Text += "-------+-----+--------------+-----+--------------+-----+------- -------+-----+--------------+-----+--------------+-----+------- -------+-----+--------------+-----+--------------+-----+-------" + Environment.NewLine + Environment.NewLine;



                                //Update the text file

                                StreamWriter streamWriter = new StreamWriter(@"I:\\LastFailedTransaction.txt");

                                streamWriter.Write(OrderNumber.ToString());

                                streamWriter.Close();



                                //Now create pop up to tell us!

                                notifyIcon1.ShowBalloonTip(20, "New Failed Transaction", "A failed transaction has occured click to view the details.", ToolTipIcon.Warning);

                            }

                        }

                        //Close second connection

                        dbconn2.Close();

                    }

                }

                //Close the connection

                dbconn.Close();

            }

            catch (Exception Ex)

            {

                //Connection Failed; Try to close the connection

                if (dbconn != null)

                    dbconn.Dispose();

                //Show error message

                MainBoxTxt.Text += Ex.Message.ToString();

            }

        }



        private void notifyIcon1_MouseDoubleClick(object sender, MouseEventArgs e)

        {

            if (FormWindowState.Normal == WindowState)

            {

                WindowState = FormWindowState.Minimized;

                Hide();

            }

            else

            {

                Show();

                WindowState = FormWindowState.Normal;

            }

        }



        private void hideApplicationToolStripMenuItem_Click(object sender, EventArgs e)

        {

            WindowState = FormWindowState.Minimized;

            Hide();

        }



        private void shutDownToolStripMenuItem_Click(object sender, EventArgs e)

        {

            Application.Exit();

        }



        private void showApplicationToolStripMenuItem_Click(object sender, EventArgs e)

        {

            Show();

            WindowState = FormWindowState.Normal;

        }



        private void notifyIcon1_BalloonTipClicked(object sender, EventArgs e)

        {

            Show();

            WindowState = FormWindowState.Normal;

        }



        private void notifyIcon1_BalloonTipClosed(object sender, EventArgs e)

        {

            Show();

            WindowState = FormWindowState.Normal;

        }



        private void button1_Click(object sender, EventArgs e)

        {

            MainBoxTxt.Text = "";

        }



        private void button2_Click(object sender, EventArgs e)

        {

            WindowState = FormWindowState.Minimized;

            Hide();

        }



        private void button3_Click(object sender, EventArgs e)

        {

            CheckForNewFailedTransactions();

        }

    }

}

As we use SagePay we get a "failed transaction" as they move to 3D Secure! So this checks if it is a real failed transaction or not.

This is old code but I still use the program, so it may be slow, illogical and filled with offence comments in the code.

Hope it helps!

09-16-2010, 06:44 AM

Originally Posted by esedirect

Code:

CREATE TRIGGER [dbo].[tr_FailedTransaction] 
   ON  [dbo].[FailedTransaction] 
   AFTER INSERT
AS 
    DECLARE @MailID INT
    DECLARE @hr INT
    DECLARE @Body VARCHAR(4000)
    DECLARE @CustomerID INT
    DECLARE @OrderNumber INT
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT 
        @CustomerID = ft.CustomerID, 
        @OrderNumber = ft.OrderNumber,
        @Body = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(4000),ft.TransactionCommand),'&',char(13)),'%40','@'),'+',' ') + char(13)+char(13) + CONVERT(VARCHAR(4000),ft.TransactionResult)
    FROM INSERTED AS i JOIN FailedTransaction as ft ON i.DBRecNo = ft.DBRecNo

    EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
    EXEC @hr = sp_OASetProperty @MailID, 'From','no-reply@yourdomain.com'
    EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
    EXEC @hr = sp_OASetProperty @MailID, 'BCC','webmaster@yourdomain.com'
    EXEC @hr = sp_OASetProperty @MailID, 'CC', 'webmaster@yourdomain.com'
    EXEC @hr = sp_OASetProperty @MailID, 'Subject', 'Failed credit card transaction'
    EXEC @hr = sp_OASetProperty @MailID, 'To', 'whoever@yourdomain.com'
    EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
    EXEC @hr = sp_OADestroy @MailID
END

You will need to ensure that the SQL Server Surface Area Configuration utility has OLE Automation enabled for this to work (for SQL Express).

shall i just run this query in MS SQL any precautions to be taking in to consideration.

09-16-2010, 06:52 AM

OK. If you don't know what this is, then maybe you shouldn't do it at all. It's not a query, but a piece of DDL.

This creates a trigger on the [FailedTransaction] table which is fired every time a record is inserted into the table. You would need to run it in any client application that can connect to your database.

I recommend running it in a dev database first to see what feedback you get, before running it on your production server.

09-16-2010, 07:15 AM

so, all you say, is i just need to run it in MS SQL and it creates a DDL which will mail me the failed transaction notification.
can you please confirm if it is correct.

09-16-2010, 07:23 AM

Yes you can use something like MS SQL Server Management Studio, or through the ASPDNSF admin site using Configuration > Advanced > Run SQL. The first option is better because you get feedback to tell you it was successful or not.

09-16-2010, 07:36 AM

just to chime in incase you really dont understand any of that trigger code from esedirect - remember to change the relevant email addresses in there before blindly copying and pasting it :S

09-16-2010, 07:49 AM

It's frightening to see where rgbx resolves to, and what they sell!

09-16-2010, 08:15 AM

Originally Posted by esedirect

It's frightening to see where rgbx resolves to, and what they sell!

not s problem ran with no issue, but waiting to see a unsuccessful transaction to see if i get a notification or not.

Thread: Failed Transaction

Failed Transaction

Shall i just run this query in MS sql

to confirm

not clear