s
Contact Login Register
h M

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

Wednesday, April 11, 2012/Author: Chad Nash/Number of views (36796)/Comments (-)/ Article rating: No rating
Categories: In The Flow
Tags:

Need CC Test numbers?

Here is a nice cheat sheet of testing CC numbers.

Card Type Number
The table above contains a number of credit card numbers that can be used to test credit card handling software. None of these numbers will work when trying to buy something (if you hadn't guessed).
Master Card (16 Digits) 5105105105105100
Master Card (16 Digits) 5555555555554444
Visa (13 Digits) 4222222222222
Visa (16 Digits) 4111111111111111
Visa (16 Digits) 4012888888881881
American Express (15 Digits) 378282246310005
American Express (15 Digits) 371449635398431
Amex Corporate (15 Digits) 378734493671000
Dinners Club (14 Digits) 38520000023237
Dinners Club (14 Digits) 30569309025904
Discover (16 Digits) 6011111111111117
Discover (16 Digits) 6011000990139424
JCB (16 Digits) 3530111333300000
JCB (16 Digits) 3566002020360505
Thursday, April 5, 2012/Author: Chad Nash/Number of views (35852)/Comments (-)/ Article rating: No rating
Categories: In The Flow
Tags:
RSS
12345

Enter your email below AND grab your spot in our big giveaway!

The winner will receive the entire Data Springs Collection 7.0 - Designed to get your website up and running like a DNN superhero (spandex not included).

  
Subscribe