I basically have a form that I have setup where the following events happens:

1.  User clicks on SUBMIT button
2.  User gets and e-mail created from the FORM COMPLETION EVENT
3.  USER gets redirected to another page from the FORM COMPLETION EVENT
4.  Form data gets inserted using a Stored procedure from the FORM COMPLETION EVENT

My question is that if an error happens while on Insert Stored Procedure in the database, how can I get DF not to continue to the E-mail being generated and the USER getting redirected.  Basically, I dont want the Form page to proceed until the records get inserted successfully.

Can you give me an idea on what I need to do?

Thanks,
Chuck

Hello Chuck,

The only uncomplicated nightmare of a way I can come up with doing this is by using SQL Validation under Module Configuration.

So basically you will have a condition and this is only psuedocode:

declare @isValid nvarchar(200)

IF
BEGIN
exec storedProcedure '$(token)', '$(token2)'

--If this inserts correctly return true

set @isValid = 'True'

END

ELSE
BEGIN

--IF your storedProcedure doesn't insert
--correctly return false

set @isValid = 'False'
END


Then if this procedure doesn't pass the SQL validation(if it falls under the procedure failing) then the form is considered valid and your email completion event and any other form completion events can be fired after this validation is passed.


If you have any questions please let me know.

Posted By Ryan Bakerink on 09 Dec 2010 10:02 AM
Hello Chuck,

The only uncomplicated nightmare of a way I can come up with doing this is by using SQL Validation under Module Configuration.

So basically you will have a condition and this is only psuedocode:

declare @isValid nvarchar(200)

IF
BEGIN
exec storedProcedure '$(token)', '$(token2)'

--If this inserts correctly return true

set @isValid = 'True'

END

ELSE
BEGIN

--IF your storedProcedure doesn't insert
--correctly return false

set @isValid = 'False'
END


Then if this procedure doesn't pass the SQL validation(if it falls under the procedure failing) then the form is considered valid and your email completion event and any other form completion events can be fired after this validation is passed.


If you have any questions please let me know.

Hi Ryan,
Thanks for the idea. I see where you are going with this. I know this is a rough mock of the code but I am having trouble coding it correctly.

How is it possible to use an
IF statement without a condition, followed by a BEGIN and a stored procedure execution? Can you be more clear on what I can use for the IF part to proceed to the execution of the stored procedure?

Hello Chuck,

Lets say your SP looks like this:

CREATE PROCEDURE usp_SP
(
@varOne NVARCHAR(200),
@varTwo NVARCHAR(200),
@uniqueID NVARCHAR(200)
)

As
BEGIN
INSERT INTO yourTable(column1, column2, uniqueID)
VALUES(@varOne, @varTwo, @uniqueID)
END


Lets down below is what you're passing to the stored procedure(this code will go in your SQL Validation section under Module Configuration)

Start by running the SP and passing the values to the SP:

-------------------------------------

DECLARE @varOne NVARCHAR(200)
DECLARE @varTwo NVARCHAR(200)
DECLARE @uniqueID NVARCHAR(200)

DECLARE @isValid NVARCHAR(200)

SET @varOne = '$(Token1)'
SET @varTwo = '$(Token2)'
SET @uniqueID = '$(TokenUniqueID)'


EXEC usp_SP @varOne, @varTwo, @varThree

IF (SELECT COUNT(*) FROM yourTable where uniqueID = @uniqueID) = 1
BEGIN
SET @isValid= 'True'
END

ELSE
BEGIN
SET @isValid = 'False'
END

----------------------------------------


This way if one record exists then you know it inserted correctly, else is basically 0 or more than one so you can account for that if you'd like. If else is the condition then the SQL validation will fail and the form won't be submitted and the form completion events won't fire.

Please let me know if this helps and again this is only a structure and none of these tables, stored procedures, or columns exist, so make this syntax and logic fit your needs.

Thanks,

Ryan

Posted By Ryan Bakerink on 09 Dec 2010 03:16 PM
Hello Chuck,

Lets say your SP looks like this:

CREATE PROCEDURE usp_SP
(
@varOne NVARCHAR(200),
@varTwo NVARCHAR(200),
@uniqueID NVARCHAR(200)
)

As
BEGIN
INSERT INTO yourTable(column1, column2, uniqueID)
VALUES(@varOne, @varTwo, @uniqueID)
END


Lets down below is what you're passing to the stored procedure(this code will go in your SQL Validation section under Module Configuration)

Start by running the SP and passing the values to the SP:

-------------------------------------

DECLARE @varOne NVARCHAR(200)
DECLARE @varTwo NVARCHAR(200)
DECLARE @uniqueID NVARCHAR(200)

DECLARE @isValid NVARCHAR(200)

SET @varOne = '$(Token1)'
SET @varTwo = '$(Token2)'
SET @uniqueID = '$(TokenUniqueID)'


EXEC usp_SP @varOne, @varTwo, @varThree

IF (SELECT COUNT(*) FROM yourTable where uniqueID = @uniqueID) = 1
BEGIN
SET @isValid= 'True'
END

ELSE
BEGIN
SET @isValid = 'False'
END

----------------------------------------


This way if one record exists then you know it inserted correctly, else is basically 0 or more than one so you can account for that if you'd like. If else is the condition then the SQL validation will fail and the form won't be submitted and the form completion events won't fire.

Please let me know if this helps and again this is only a structure and none of these tables, stored procedures, or columns exist, so make this syntax and logic fit your needs.

Thanks,

Ryan

Hello Ryan,
Thank you so much. I now understand the logic. This sample has helped out greatly.

Thanks again.

Hello Chuck,

No problem if you ever have any questions please let me know.

Thanks,

Ryan