Hi,

I'm using DF 03.00.31 on a DNN 495 portal.  I've tried following your tutorials on creating a flat table and then generationg a SQL completion event to insert data into that table using the assistance options.

The table was created from the script generated, however the INSERT SQL Completions event is not working for me.  There is no data being written to the table nor do I have any error messages in the event viewer.

Does anyone have any suggestions on how I can get this to work?

This is a fairly large form with hidden fields that I need written to a flat table.  Any suggestions is very much appreciated.

Thanks,

Jerry

Update: I've tried to run just a simple Insert to three columns with three values and it will still not write to the table.  It appears that the SQL Completion event is not firing.  I have a hidden text field with a default value of INSERT and so the completion event tied to that hidden field, so it should fire everytime.  Basically I want to keep a transaction table of everything that was entered...

I have tried your second suggestion by eliminating all the blank spaces but that didn't work either...I'm a SQL newbie, so how would I go about creating a SQL Procedure?

Jerry

Hi,

I've tried creating the stored procedure as you outlined...I'm assuming that when you typed values above that each value needed to have '@' in front of it...values(@test1,@test2) etc...not values(@test1,test2) etc...

The SP is on the database as [OSC_InsertOnlineTransactions], yet I still can't get it insert any data to that table.

Any ideas?
Jerry.

The following is my SQL Event:

[OSC_InsertOnlineTransactions] '$(UniqueCompletionID)','$(ResponseDateTime)','$(UserID)','$(IPAddress)','$(DSParam1)','$(MailingAddress)','$(City)','$(ITEMCAT)','$(ITEMTITLE)','$(DisplayMemExpDate)','$(ItemDesc)','$(VB_NotifyMeTourn)','$(VB_NewPlyrDesc)','$(VBWhatLeagues)','$(VB_AA)','$(VB_A)','$(VB_BB)','$(VB_B)','$(VB_B1)','$(VB_B2)','$(VB_TeamCaptain)','$(VB_NotAttend)','$(VB_HelpTryouts)','$(VB_TryoutHelpTues)','$(VB_HelpTryoutsThurs)','$(GOLFOUTTYPE)','$(GOLF_PartnerName)','$(GOLF_TeamName)','$(GOLF_TeamMem01)','$(GOLF_TeamMem02)','$(GOLF_TeamMem03)','$(GOLF_NotifySunEvents)','$(PoolPartner)','$(DARTS_SkillLevel)','$(DARTS_TeamCaptain)','$(DARTS_301AVG)','$(DARTS_301Points)','$(DARTS_MPR)','$(ItemPrice)','$(HandFee)','$(TotalDue)','$(ITEMCODE)','$(ItemRole)','$(ItemYear)','$(EMail)','$(IPNStatusField)','$(PhoneNumber)','$(State)','$(ZipCode)','$(CurrentIPA)','$(CurrentDate)','$(FName)','$(LName)'

Yes, thanks for taking a look:

Here is the SQL Stored Procedure:

create procedure [OSC_OnlineTransactions]
(@UniqueCompletionID UniqueIdentifier,
@ResponseDateTime DateTime,
@UserID int,
@IPAddress nvarchar(100),
@DSParam1 nvarchar(500),
@MailingAddress nvarchar(500),
@City nvarchar(500),
@ITEMCAT nvarchar(500),
@ITEMTITLE nvarchar(500),
@DisplayMemExpDate nvarchar(500),
@ItemDesc nvarchar(500),
@VB_NotifyMeTourn nvarchar(500),
@VB_NewPlyrDesc nvarchar(500),
@VBWhatLeagues nvarchar(500),
@VB_AA nvarchar(500),
@VB_A nvarchar(500),
@VB_BB nvarchar(500),
@VB_B nvarchar(500),
@VB_B1 nvarchar(500),
@VB_B2 nvarchar(500),
@VB_TeamCaptain nvarchar(500),
@VB_NotAttend nvarchar(500),
@VB_HelpTryouts nvarchar(500),
@VB_TryoutHelpTues nvarchar(500),
@VB_HelpTryoutsThurs nvarchar(500),
@GOLFOUTTYPE nvarchar(500),
@GOLF_PartnerName nvarchar(500),
@GOLF_TeamName nvarchar(500),
@GOLF_TeamMem01 nvarchar(500),
@GOLF_TeamMem02 nvarchar(500),
@GOLF_TeamMem03 nvarchar(500),
@GOLF_NotifySunEvents nvarchar(500),
@PoolPartner nvarchar(500),
@DARTS_SkillLevel nvarchar(500),
@DARTS_TeamCaptain nvarchar(500),
@DARTS_301AVG nvarchar(500),
@DARTS_301Points nvarchar(500),
@DARTS_MPR nvarchar(500),
@ItemPrice nvarchar(500),
@HandFee nvarchar(500),
@TotalDue nvarchar(500),
@ITEMCODE nvarchar(500),
@ItemRole nvarchar(500),
@ItemYear nvarchar(500),
@EMail nvarchar(500),
@IPNStatusField nvarchar(500),
@PhoneNumber nvarchar(500),
@State nvarchar(500),
@ZipCode nvarchar(500),
@CurrentIPA nvarchar(500),
@CurrentDate nvarchar(500),
@FName nvarchar(500),
@LName nvarchar(500))
as
begin
INSERT into [OSC_OnlineTransactions]
(UniqueCompletionID,ResponseDateTime,UserID,IPAddress,DSParam1,MailingAddress,City,ITEMCAT,ITEMTITLE,DisplayMemExpDate,ItemDesc,VB_NotifyMeTourn,VB_NewPlyrDesc,VBWhatLeagues,VB_AA,VB_A,VB_BB,VB_B,VB_B1,VB_B2,VB_TeamCaptain,VB_NotAttend,VB_HelpTryouts,VB_TryoutHelpTues,VB_HelpTryoutsThurs,GOLFOUTTYPE,GOLF_PartnerName,GOLF_TeamName,GOLF_TeamMem01,GOLF_TeamMem02,GOLF_TeamMem03,GOLF_NotifySunEvents,PoolPartner,DARTS_SkillLevel,DARTS_TeamCaptain,DARTS_301AVG,DARTS_301Points,DARTS_MPR,ItemPrice,HandFee,TotalDue,ITEMCODE,ItemRole,ItemYear,EMail,IPNStatusField,PhoneNumber,State,ZipCode,CurrentIPA,CurrentDate,FName,LName)
VALUES (@UniqueCompletionID,@ResponseDateTime,@UserID,@IPAddress,@DSParam1,@MailingAddress,@City,@ITEMCAT,@ITEMTITLE,@DisplayMemExpDate,@ItemDesc,@VB_NotifyMeTourn,@VB_NewPlyrDesc,@VBWhatLeagues,@VB_AA,@VB_A,@VB_BB,@VB_B,@VB_B1,@VB_B2,@VB_TeamCaptain,@VB_NotAttend,@VB_HelpTryouts,@VB_TryoutHelpTues,@VB_HelpTryoutsThurs,@GOLFOUTTYPE,@GOLF_PartnerName,@GOLF_TeamName,@GOLF_TeamMem01,@GOLF_TeamMem02,@GOLF_TeamMem03,@GOLF_NotifySunEvents,@PoolPartner,@DARTS_SkillLevel,@DARTS_TeamCaptain,@DARTS_301AVG,@DARTS_301Points,@DARTS_MPR,@ItemPrice,@HandFee,@TotalDue,@ITEMCODE,@ItemRole,@ItemYear,@EMail,@IPNStatusField,@PhoneNumber,@State,@ZipCode,@CurrentIPA,@CurrentDate,@FName,@LName)
END
 

Yes it is setup for PayPal gateway...and I have other SQL events running updates against other tables such as the UserRoles table which fire off upon a successful completion. However, I haven't tied it to a subscription event, I've only tied SQL Completion events to a Completed Standard or Redirect Successfully.  I have tested it with PayPayl gateway turned off and it still doesn't insert a record. However, my other SQL completion events ran.
 

Hi Jerry,

The Completed (Standard) is associated with a Purchase type of transaction.  If this is a subscription, you will need to tie your event to Completed (subscription payment) and/or Completed (subscription signup).  Otherwise, it will continue to wait for the right IPN and it never will.  Hope that helps! 

Candace

Hi Candace,

This is setup to be a purchase not a subscription...I'm basically using this as a cart...I have an IndooGrid sending an item to this the cart page(DF Module) which then populates the questions based on the DSParam1 sent. When the IPN codes comes back it fires off some of my other SQL events that I have...
I've noticed that if I have smaller SQL Events (Smaller Queries) then they run just fine, but if I try to do multiple queries seperated by GO or a large query to throw everything into one flat transaction table then it doesn't work.

Thanks for the assistance Candace and David, I do appreciate it very much!