Great Ideas. Always Flowing.

We are not happy until you are happy. Client satisfaction guaranteed. Whatever your needs and requirements, we have the skills and resources for the job!

Popular Posts

Tags

2008 2008 Express Add AFTER alert Alias Aliases Alter Alternating And ASC Assist BETA Blog Cancel Check ON Checkbox Checkbox Group Color Column Columns Combo Box Composite Confirm Confirm Message Control CREATE CREATE TABLE Data Data Springs Database DDL DELETE Delete Confirm Demo Demonstration DESC Direct Access DNN DotNetNuke Drop Drop Constratint Drop Down Drop Down List DS Dynamic Dynamic Blog Dynamic Forms Dynamic Registration Dynamic Views Edit Event Example Express Fade In Fade Out Field Filters FK Forms Full-Table Scan Generating Generator Generators Great Ideas Group Help HTML Increase Index Indexes Input Integrity jQuery Label Listbox Listener Message Modify My Account MySQL No NULL Often Ok ON Optimization Optimize Option Options Oracle Order By Perform Performance PK Popup PostreSQL Queries Query QuestionOptionValue Radio Button Radio Button Group References referential Round Script Search Search Filters SearchOption SearchOptionValue Select Server Snippet Sort Springs SQL SQL Driven SQL Driven Query SQL Express SQL Server SQL Server 2005 SQL Server 2008 SQL Server 2008 Express SQL Server 2008 R2 SQL Server Express SSMS Stored Stored Procedure TABLE Tables Temporary Tool Tools Tooltip Tooltips Transact-SQL Trials Triggers TSQL T-SQL Unique UPDATE Use Useful Views Widget Widgets Window Yes [userimage] 10 64 bit Ability ABS Action Active Forums Alleviate Alternating Colors Alternating Rows Analytics ARB asp.net asp.net validation AuthARB Authorize.NET Avoid Back Button Basecamp Blog Blog Posts Blogging Browser Browser Back Browser Close Browser History Browser Script Button cascading style sheet Case Char Character CharAt CharAt() Check chip levinson Click client side validation Close Browser Close Window Collection 5.0 Comment Compatibility Compatible Completion completion event Confirm Message Conform Constraints content localization Count Timer Countdown CRM css csv Cursor Custom Custom HTML Custom JavaScript Custom JavaScript File customer feedback Customize Data Data Integrity Data Springs Data Springs Blog Data Springs Collection Data Springs Development Data Springs Planning Data Springs Training Databases DataSprings Date Time JavaScript Debug Info default value Delete Demonstration DF DNN DNN Authentication DNN Blog DNN Core Profile Property dnn html module dnn modules dnn schedule error dnn schedule multiple DNN Store document document.getElementById DotNetNuke dotnetnuke 5.4.4 DotNetNuke Analytics DotNetNuke Forums DotNetNuke JavaScript DotNetNuke Modules dotnetnuke reporting dotnetnuke scheduler dotnetnuke user image Double Double Quotes DR DROP Drop Constraint DropDown Login DS dynamic Dynamic Data dynamic fields Dynamic Form Dynamic Forms dynamic forms silent post Dynamic Forms Tutorial dynamic login Dynamic PDF Form Completion Event dynamic registration dynamic registration silent post dynamic registration user image dynamic user directory dynamic views DynamicRegistration_Question DynamicRegistration_QuestionResponse email email issues Encapsulated Encapsulation Even Event Event Viewer Example Excel Execute Export Export to Excel Facebook Facebook Connect Field FieldID First FirstName Fix Foreign Key Form Form Post Formatting Forms Forum Flow Full Table Scan Fully Function Google Analytics Google Analytics Ecommerce Great Great ideas Grid guides hidden field Hide Show Rows Highrise Highrise API Hourly Services HTML HTTP Post iDeal IE IE 10 Image Image Button Include Inconsistancy Info Information Injection INSERT Integration Interactive User Import Interface internet Internet Explorer iPAD iPAD App for Highrise iPAD Highrise App Items JavaScript JavaScript hide show JS Function Layout Lead Generation Learn Left Left Quote Link LinkedIn list import Live Blog localization Log Logic Login login module login skin object Loop Mandeeps Match Message Microsoft Live Writer module configuration Modules Monthly Services Name Netherlands New News Blog NL NOT NULL oAuth2 Odd OnClick Open Web Studio opt in email OWS Part 1 Part 2 Passed payment gateway paypal Phone Number Placement Post Postback Posts Premium Integration Premium Services Premium Support Primary Key Procedure Products profile Quarterly Services Question question fields Question Loop Question Value QuestionOption Quick Quotes Recommend Recommendation Recurring Billing Redirect Referential Integrity registration Rendered Replace replace html text report views reports Resolution Resource resource files resx Retrieve Retrieving Right Right Quote Rows Ryan Bakerink Sales Force SalesForce Script Scripting Scripts Sequential Access server side validation Silent Post Single Single Quotes Single Sign On skin object Snowcovered Solution sp Springs SQL sql 2005 pivot sql default value SQL Example sql import SQL Injection sql query sql replace statement sql reports SQL Server sql server 2005 SSL SSO stored stored procedure String style sheet stylesheet Submit Submit Button Submit Image Submit Link success story Suggest Suggestion Support Syntax Table technical techwise research Temp test credit card numbers testimonial Text/HTML thumbnail image Time Timer Tip Token Topic Transaction Trial Trigger TSQL T-SQL Tutorial Twitter Types of Constraints Unique Update Highrise user user directory user profile image users online Variable View Web Based Work Around writer writing xls xlsx XML

In the Flow

rss

Data Springs, Inc. - An online diary and web log from staff and customers for premium DotNetNuke resources, Data Springs Modules, and Data Springs Services.


Exporting Dynamic Form Results and Importing to flat table.

 

Hello Everyone,

I have taken the liberty to create SQL Stored Procedures to Export results from the DynamicForms_QuestionResponse table and insert these values into a temp table called “Export_Temptable”. I have also created SQL Stored Procedures to Import the records in the Export_TempTable to a Flat table of my choice.

 

This blog is broken into 2 parts.

Part 1 covers the Export

Part 2 covers the Import

 

Part 1 – Export

Step 1.) Start by creating this table on your data base(You can do so by going to Host –> SQL on your DNN Installation):

----EXPORT TEMP TABLE

CREATE TABLE Export_TempTable
(
ExportRecordID int identity(1,1),
QuestionResponse nvarchar(500),
QuestionName nvarchar(500),
QuestionValue nvarchar(500),
ResponseDate datetime
)

Step 2.) Now you will need to create the Export procedure on your data base(You can do so by going to Host –> SQL on your DNN Installation):

-----EXPORT PROCEDURE

CREATE PROCEDURE Export_DynamicForms_Results 

@ModuleID int 

AS 
BEGIN 
DECLARE Cart_Cursor CURSOR FOR 
select A.UniqueResponseID ,B.ShortFieldName, A.Response, A.ResponseDateTime from DynamicForms_QuestionResponse A inner join 
DynamicForms_Question B on A.DynamicQuestionID = B.DynamicQuestionID where B.ModuleID = @ModuleID and ShortFieldName <> 'HumanCaptcha' order by B.ShortFieldName 
OPEN Cart_Cursor 
DECLARE @V1 nvarchar(400), @V2 nvarchar(400), @V3 nvarchar(400) , @V4 nvarchar(400) 
FETCH NEXT FROM Cart_Cursor INTO @V1, @V2 , @V3, @V4
WHILE @@FETCH_STATUS = 0 
BEGIN 
If @V2 Is Null 
BEGIN 
Set @V2 = '' 
END 
If @V3 Is Null 
BEGIN 
Set @V3 = '' 
END 
INSERT INTO Export_TempTable(QuestionResponse, QuestionName, QuestionValue, ResponseDate) 
VALUES(@V1, @V2, @V3, @V4) 
FETCH NEXT FROM Cart_Cursor INTO @V1, @V2, @V3, @V4
END 
CLOSE Cart_Cursor 
DEALLOCATE Cart_Cursor 
end

Step 3.) Test the Export Procedure

To test the Export procedure, go to a Dynamic Form instance and obtain the ModuleID for that Dynamic Form.

Go to Host –> SQL on your DNN Installation and run this SQL (Example ModuleID = 224):

Exec Export_DynamicForms_Results  224

After executing the Stored Procedure execute the below query:

Select  * from Export_TempTable

You should be able to see all or most of your of your exported records.

 

Part 2 – Import

 

Step 1.) Create Import Procedure 1

 

----IMPORT PROCEDURE 1 OF 2


CREATE PROCEDURE Import_DynamicForms_Results

AS
BEGIN

DECLARE @UNIQUE nvarchar(200)
DECLARE @COUNT int

DECLARE Import_Cursor CURSOR FOR

select QuestionResponse from Export_TempTable order by QuestionResponse

OPEN Import_Cursor
DECLARE @V1 nvarchar(400)
FETCH NEXT FROM Import_Cursor INTO @V1
WHILE @@FETCH_STATUS = 0
BEGIN


If @V1 Is Null
BEGIN
Set @V1 = ''
END

set @COUNT = (Select COUNT(*) from YourFlatTableNameGoesHere where ResponseUNID = @V1)
Set @UNIQUE = (Select NEWID())

IF @COUNT = 0
BEGIN
INSERT INTO YourFlatTableNameGoesHere (UniqueCompletionID)
VALUES(@UNIQUE)

UPDATE YourFlatTableNameGoesHere Set responseUNID = @V1
where UniqueCompletionID = @UNIQUE

END

exec Import_DynamicForms_Results_Nested @V1, @UNIQUE

FETCH NEXT FROM Import_Cursor INTO @V1
END
CLOSE Import_Cursor
DEALLOCATE Import_Cursor

end

 

Step 2 – Create Import Procedure 2:

You will need to modify this procedure the most out of all. For this example I have a flat table that looks like this:

 

Table Name: YourFlatTableNameGoesHere

Columns: ID, UniqueCompletionID, FirstName, LastName, Email

Please note that you must have a UniqueCompletionID column that contains a unique value whether a GUID string or an identity number.

The below procedure has been conformed to the example table. If you’re table has different columns, then you will need to add to or delete from the section color coded “Green”

 

 


----IMPORT PROCEDURE NUMBER 2


CREATE PROCEDURE Import_DynamicForms_Results_Nested
(
@UNID nvarchar(500),
@UNIQUE nvarchar(200)
)
AS
BEGIN

DECLARE Import_Cursor2 CURSOR FOR

select Cast(QuestionName as nvarchar(max)), cast(QuestionValue as nvarchar(max)), ResponseDate from Export_TempTable where QuestionResponse = @UNID

OPEN Import_Cursor2
DECLARE @V1 nvarchar(400), @V2 nvarchar(400), @V3 datetime
FETCH NEXT FROM Import_Cursor2 INTO @V1, @V2, @V3
WHILE @@FETCH_STATUS = 0
BEGIN

If @V2 Is Null
BEGIN
Set @V2 = ''
END

IF @V1 = 'FirstName'
BEGIN
Update YourFlatTableNameGoesHere  set FirstName = @V2  where UniqueCompletionID = @UNIQUE
END

IF @V1 = 'LastName'
BEGIN
Update YourFlatTableNameGoesHere set LastName = @V2  where UniqueCompletionID = @UNIQUE
END

IF @V1 = 'Email'
BEGIN
Update YourFlatTableNameGoesHere set Email = @V2 where UniqueCompletionID = @UNIQUE
END


FETCH NEXT FROM Import_Cursor2 INTO @V1, @V2, @V3
END
CLOSE Import_Cursor2
DEALLOCATE Import_Cursor2

END

This concludes the blog post.

 

Let me know if you have any questions.

 

Thanks,

 

Ryan




Comments are closed.

Recent Comments

 
 

Join our mailing list...

Get current news and events the easy way
 
 
   
Subscribe Me

Recent Blogs...

 
Copyright 2005 - 2011 by Data Springs, Inc.