I'm using Dynamic Forms 4.10.4 on a DotNetNuke 6.2.5 website.

I've got a form that uses a SQL Form Completion Event (InsertData) to save the data in a SQL table. This is all working fine. A user completes the form and I see the data show up in the table as expected.

My problem is that I have set the "SQL Failure Redirect URL" to an error page and the user is always redirected to this page, even when there doesn't appear to be an error.

I have tried setting up a second Email event (ordered after the "InsertData" event) that emails me the error by setting the body set to:

$(SQLResponse_InsertData)

but I never get any data emailed to me. Is this because the first event redirects before the second event is fired?

If I delete the "SQL Failure Redirect URL", then I DO get the mail, but there is nothing in the body (and of course, if there were an error, the user would not know it).
 
Ideally, I would like:

1. The user to only see an error page if there truly is an error.
2. The administrator gets an email if there is an error.

How can I achieve this?

Thanks,

Larry

Hi Larry,


Can you try and do this... Can you remove the URL completely for the failure URL and then let me know what the response token is you are using? If you don't have the failure URL present then it should skip that step. I forget what it is looking for, I think if the actual SQL causes an exception.

Thanks,

Chad

Hi Chad,

Thanks for looking into this. I believe my original post had all the information you requested.

If I remove the URL, I do get the email (with nothing in the body).

The token I'm using in the body is $(SQLResponse_InsertData). The name of the SQL event is "InsertData" (Event Sort Order: 10). The Email event has Event Sort Order: 20.

If I can provide any additional information, please let me know.

Thanks,
Larry

Hi Larry,

That is strange... Here is the code that is going on. This should only be happening (at least with the most current an update to date release) so you might check under host, extensions to see what the release is.

Try

If mblnDebug = True And DotNetNuke.Security.PortalSecurity.HasEditPermissions(ModuleId, TabId) = True Then
lblHeader.Controls.Add(New LiteralControl("
Executing SQL Next Step"))

End If


strEventResponse = objSQLC.GetSQLEventResponse(strMySQL, strAltConnection, CStr(Settings("DBType"))).Rows(0).Item(0).ToString

If mblnDebug = True And DotNetNuke.Security.PortalSecurity.HasEditPermissions(ModuleId, TabId) = True Then
lblHeader.Controls.Add(New LiteralControl("
SQL Response: " & strEventResponse))
End If
'End If


Catch ex As Exception
If mblnDebug = True And DotNetNuke.Security.PortalSecurity.HasEditPermissions(ModuleId, TabId) = True Then
lblHeader.Controls.Add(New LiteralControl("
SQL Error Caught: " & ex.Message.ToString))
End If
strEventResponse = ""

If blnLogAllEvents = True Then
ProcessDynamicEventLog(MyEventInfo.EventName.ToString, MyEventInfo.EventType.ToString, "Query (With an Error):" & strMySQL.ToString)
End If

'Since there was
'Redirect the user if there is a failure
If MyEventInfo.OptionalConfig2.ToString <> "" Then
Response.Redirect(MyEventInfo.OptionalConfig2.ToString, True)
End If

End Try

'Collect a response for this SQL query, in case we need to later use it in another event or email...

'Set the viewstate so that we can later get the response
Try
Session("SQLResponse_" & MyEventInfo.EventName.ToString) = strEventResponse.ToString
Catch
Session("SQLResponse_" & MyEventInfo.EventName.ToString) = ""
End Try

If blnLogAllEvents = True Then
ProcessDynamicEventLog(MyEventInfo.EventName.ToString, MyEventInfo.EventType.ToString, "Query:" & strMySQL.ToString, "Alternate Connection:" & strAltConnection.ToString & " / SQL Event Response:" & strEventResponse.ToString)
End If


A few things you can do... AFter the "Insert" maybe have something like a select statement... Like:

Insert Into YourTable(Stuff) (Values)
GO
Select Scope_Identity() AS ReturnValue


This would then return the ID for the record that was just inserted... Or try and just return "something".

Another thing to try and do would be to log the completion events under module configuration, general settings.


Thanks,

Chad

If I go to "Module Configuration" > "General Settings" and check "Enable Debug Mode", here's the debug information generated:

***DYNAMIC FORMS DEBUG MODE ON***
Dynamic Forms Version:4.10.5.20228
DotNetNuke Version: 6.2.5
Web.Config Trust Level: Unrestricted

SQL Debug (Parse SQL Text For Variables):SELECT '71.192.27.163' as DefaultValue
SQL for hidden field:71.192.27.163

Initial Javascript:

Saving Forms Responses: 4:16:24 PM
Processing Events: 4:16:24 PM

Processing SQL Event: InsertData -4:16:24 PM
DBType: SQL
Executing SQL Event
SQL Query: INSERT INTO [IPS_CapeCodHospital_SurveyResponses]( [DateTime], [IPAddress], [RegistrationSatisfaction], [DoctorSatisfaction], [HospitalRoomQuality], [NursingStaffRating], [OverallHospitalExperience], [HowLikelyToRecommend] ) VALUES ( CURRENT_TIMESTAMP, '71.192.27.163', 1, 1, 1, 1, 1, 1 )
Failure URL Redirect (Optional):
Executing SQL Next Step
SQL Error Caught: There is no row at position 0.
Processing Email Event: EmailSQLResponse -4:16:25 PM
Attempted Email Distribution
From: Larry@CompanionSoftware.com
From Name:iPatientSurvey
To:Larry@CompanionSoftware.com
CC:
BCC:
Priority:0
Subject:iPatientSurvey submission SQL response
Message:


Attachment:
Email Type:Html
*** Ending Debug Mode for Dynamic Forms ***
**** Preparing to process all Affected Field Question Events ****

I've bolded the suspicious "SQL Error Caught", above, on a successful submission (which does insert the desired record into the database). It's my guess that this is the source of the problem that I'm seeing.

OK, I believe I've gotten to the bottom of this problem.

The problem is caused because the Dynamic Forms code seems to require a non-null response from the SQL statement. I don't see this documented in the manual. Nor is it shown in the generated SQL when pressing the "Generate Insert SQL Query" link in the form for this event. So my guess is that this is a bug in the code (but could be a documentation error).

The work-around is to return some value.

So, for example, I changed:

INSERT INTO MyTable ([Foo], [Bar]) VALUES ($(Foo), $(Bar))

into:

INSERT INTO MyTable ([Foo], [Bar]) VALUES ($(Foo), $(Bar));
SELECT SCOPE_IDENTITY() AS ReturnValue;

This operates as expected. Note that adding a "GO" in between these statements did not work.

Please update the DF code or documentation so others don't have to struggle with this. Thanks!

Larry

It seems that one adverse side-effect of my work-around is that if the first statement fails, the second doesn't, so Dynamic Forms doesn't seem to catch the error in the first statement.

For example, I did an INSERT to a non-existent table and no error was reported and the user was redirected to the "success" page, not the SQL Failure URL.