I am starting to test using dynamic views with our dynamic forms and ran into some issues early on.  The first issue that arose was the question lengths were too long.  I was able to reword the questions (didnt like having to do this though), but then got the following error which I could not interpret nor track down.  Can you give me some guidance?

http://www.tfff.org/Default.aspx?ta...ter+string

HI, to narrow the issue even further, can you specify if you are using an SQL query or choosing a moduleID from dynamic forms? Based on the error, it seems to be coming from the Question as being too long in some of the fields. What type of questions do you have? Are they more than 128 characters long?

-- David

We are using the moduleID.....

I was initially getting quite a few "question too long" type errors, and was going through and resolving this.....then at some point I got the above error message....

I am finding it hard to interpret which question this is choking on, and what does the following mean "Unclosed+quotation+mark+after+the+character+string"

Well, can you export that dynamic forms module to our test website and state the URL/TABID so we can take a look at the questions you have on the form?
www.betasprings.com
username: host
password: dnnhost

-- David

Sure. I created a Dynamic Form called FILP Application on your test website. Thanks for taking a look!

HI, can you go to Host / SQL and execute the following two stored procedures? I found out from Chad that the stored procedures previously weren't set to nvarchar(max) so too many questions would get cut off and not work properly.
=======================================================

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}DataSprings_DynamicViews_GetDynamicFormsResults') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure {databaseOwner}{objectQualifier}DataSprings_DynamicViews_GetDynamicFormsResults
GO


Create Procedure {databaseOwner}[{objectQualifier}DataSprings_DynamicViews_GetDynamicFormsResults]

@ModuleID int,
@UserID int,
@MySQL nvarchar(MAX) OUTPUT

AS
Declare curBubba Cursor
FOR
SELECT Question, DynamicQuestionID
FROM {databaseOwner}[{objectQualifier}dynamicforms_question]
where QuestionType <> 'HTML'
AND QuestionType <> 'HR'
AND Question NOT LIKE '%('
AND Question NOT LIKE ')%'
AND InActive=0
AND ModuleID = @ModuleID


ORDER BY SortOrder
FOR READ ONLY;

Declare @Question VarChar(500);
Declare @DynamicQuestionID uniqueidentifier;
Declare @SQLToUse VarChar(MAX);
Declare @SQLToUseJoin VarChar(MAX);
Declare @SQLToExecute Varchar(MAX);
Declare @Alias VarChar(10);

Declare @intCounter Int;

set @Question = '';
set @SQLToUse = '';
set @intCounter = 0;
set @Alias = '';
set @SQLToUseJoin = '';

OPEN curBubba;
FETCH NEXT FROM curBubba INTO @Question, @DynamicQuestionID;

WHILE @@FETCH_STATUS = 0
BEGIN

set @intCounter = @intCounter + 1;

set @Alias = '[RJoin_' + CAST(@intCounter as varchar(20)) + ']';

if DataLength(@SQLToUse) = 0
BEGIN
set @SQLToUse = 'SELECT A.UniqueResponseID as UniqueID, A.UserID, IsNull(' + @Alias + '.response,''N/A'') as [' + lower(@Question) + '] '
set @SQLToUseJoin = '
From (
SELECT distinct Y.uniqueresponseID, Y.UserID, B.UserName
from {databaseOwner}[{objectQualifier}DynamicForms_QuestionResponse] As Y
inner join {databaseOwner}[{objectQualifier}DynamicForms_Question] as Z on (Y.DynamicQuestionID = Z.DynamicQuestionID)
Left Outer Join {databaseOwner}[{objectQualifier}Users] B on B.UserID = Y.UserID
where ((Y.UserID = ' + Cast(@UserID as varchar(10)) + ') OR (' + Cast(@UserID as varchar(10)) + ' = -2)) AND Z.ModuleID = ' + Cast(@ModuleID as varchar(20)) + '
) As A
Left Outer join {databaseOwner}[{objectQualifier}DynamicForms_QuestionResponse] as ' + @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias + '.DynamicQuestionID)
'
END
ELSE
BEGIN
set @SQLToUse = @SQLToUse + ', ' + @Alias + '.response as [' + lower(@Question) + '] '
set @SQLToUseJoin = @SQLToUseJoin + 'Left Outer join {databaseOwner}[{objectQualifier}DynamicForms_QuestionResponse] as ' + @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias + '.DynamicQuestionID) '
END;

FETCH NEXT FROM curBubba INTO @Question, @DynamicQuestionID;
END

CLOSE curBubba;
DEALLOCATE curBubba;

Set @SQLToExecute = Convert(varchar(MAX), @SQLToUse) + Convert(varchar(MAX), @SQLToUseJoin)


SET @MySQL = @SQLToExecute
RETURN

=============================================================

alter Procedure {databaseOwner}[{objectQualifier}DataSprings_RetrieveDynamicView]
@PortalID integer,
@intTableorQuery integer,
@strTableOrQuery nvarchar(MAX),
@LimitResults nvarchar(4000),
@strUserFilter nvarchar(4000),
@intUserID integer,
@strDetailView nvarchar(2000)

AS

Declare @MySQL nvarchar(MAX)
Declare @SQLToExecute nvarchar(MAX)

if @intTableOrQuery = 0
BEGIN
Set @MySQL = 'Select * from ' + @strTableOrQuery
END
ELSE IF @intTableOrQuery = 1
BEGIN
exec {databaseOwner}[{objectQualifier}DataSprings_DynamicViews_GetDynamicFormsResults] @strTableOrQuery, @intUserID, @MySQL OUTPUT
--Select @MySQL
END
ELSE
BEGIN
Set @MySQL = @strTableOrQuery
END

Set @SQLToExecute = cast('Select ' as nvarchar(max)) + @LimitResults + cast(' * from
(' as nvarchar(max)) + @MySQL + cast(') AS Main ' as nvarchar(max)) + @strUserFilter + @strDetailView

--Select @SQLToExecute
exec sp_executesql @SQLToExecute

================================================
Then go to HOST / Host Settings and Restart Application. -- David

On the first query, receive an there is an error on your query.....if i run as script I get the following

System.Data.SqlClient.SqlException: Incorrect syntax near 'GO'. 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) if exists (select * from dbo.sysobjects where id = object_id(N'dbo.DataSprings_DynamicViews_GetDynamicFormsResults') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure dbo.DataSprings_DynamicViews_GetDynamicFormsResults GO Create Procedure dbo.[DataSprings_DynamicViews_GetDynamicFormsResults] @ModuleID int, @UserID int, @MySQL nvarchar(MAX) OUTPUT AS Declare curBubba Cursor FOR SELECT Question, DynamicQuestionID FROM dbo.[dynamicforms_question] where QuestionType <> 'HTML' AND QuestionType <> 'HR' AND Question NOT LIKE '%(' AND Question NOT LIKE ')%' AND InActive=0 AND ModuleID = @ModuleID ORDER BY SortOrder FOR READ ONLY; Declare @Question VarChar(500); Declare @DynamicQuestionID uniqueidentifier; Declare @SQLToUse VarChar(MAX); Declare @SQLToUseJoin VarChar(MAX); Declare @SQLToExecute Varchar(MAX); Declare @Alias VarChar(10); Declare @intCounter Int; set @Question = ''; set @SQLToUse = ''; set @intCounter = 0; set @Alias = ''; set @SQLToUseJoin = ''; OPEN curBubba; FETCH NEXT FROM curBubba INTO @Question, @DynamicQuestionID; WHILE @@FETCH_STATUS = 0 BEGIN set @intCounter = @intCounter + 1; set @Alias = '[RJoin_' + CAST(@intCounter as varchar(20)) + ']'; if DataLength(@SQLToUse) = 0 BEGIN set @SQLToUse = 'SELECT A.UniqueResponseID as UniqueID, A.UserID, IsNull(' + @Alias + '.response,''N/A'') as [' + lower(@Question) + '] ' set @SQLToUseJoin = ' From ( SELECT distinct Y.uniqueresponseID, Y.UserID, B.UserName from dbo.[DynamicForms_QuestionResponse] As Y inner join dbo.[DynamicForms_Question] as Z on (Y.DynamicQuestionID = Z.DynamicQuestionID) Left Outer Join dbo.[Users] B on B.UserID = Y.UserID where ((Y.UserID = ' + Cast(@UserID as varchar(10)) + ') OR (' + Cast(@UserID as varchar(10)) + ' = -2)) AND Z.ModuleID = ' + Cast(@ModuleID as varchar(20)) + ' ) As A Left Outer join dbo.[DynamicForms_QuestionResponse] as ' + @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias + '.DynamicQuestionID) ' END ELSE BEGIN set @SQLToUse = @SQLToUse + ', ' + @Alias + '.response as [' + lower(@Question) + '] ' set @SQLToUseJoin = @SQLToUseJoin + 'Left Outer join dbo.[DynamicForms_QuestionResponse] as ' + @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias + '.DynamicQuestionID) ' END; FETCH NEXT FROM curBubba INTO @Question, @DynamicQuestionID; END CLOSE curBubba; DEALLOCATE curBubba; Set @SQLToExecute = Convert(varchar(MAX), @SQLToUse) + Convert(varchar(MAX), @SQLToUseJoin) SET @MySQL = @SQLToExecute RETURN

HI, okay, can you go to Host / SQL and execute the same thing again except take out the GO? on the third line? That should work then. The "GO" is there only for an automated install in an SQL dataprovider file but I don't believe executing it under Host / SQL would work. -- David

Thanks for the quick response. Now I receive this error message after taking out the GO

System.Data.SqlClient.SqlException: 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) if exists (select * from dbo.sysobjects where id = object_id(N'dbo.DataSprings_DynamicViews_GetDynamicFormsResults') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure dbo.DataSprings_DynamicViews_GetDynamicFormsResults Create Procedure dbo.[DataSprings_DynamicViews_GetDynamicFormsResults] @ModuleID int, @UserID int, @MySQL nvarchar(MAX) OUTPUT AS Declare curBubba Cursor FOR SELECT Question, DynamicQuestionID FROM dbo.[dynamicforms_question] where QuestionType <> 'HTML' AND QuestionType <> 'HR' AND Question NOT LIKE '%(' AND Question NOT LIKE ')%' AND InActive=0 AND ModuleID = @ModuleID ORDER BY SortOrder FOR READ ONLY; Declare @Question VarChar(500); Declare @DynamicQuestionID uniqueidentifier; Declare @SQLToUse VarChar(MAX); Declare @SQLToUseJoin VarChar(MAX); Declare @SQLToExecute Varchar(MAX); Declare @Alias VarChar(10); Declare @intCounter Int; set @Question = ''; set @SQLToUse = ''; set @intCounter = 0; set @Alias = ''; set @SQLToUseJoin = ''; OPEN curBubba; FETCH NEXT FROM curBubba INTO @Question, @DynamicQuestionID; WHILE @@FETCH_STATUS = 0 BEGIN set @intCounter = @intCounter + 1; set @Alias = '[RJoin_' + CAST(@intCounter as varchar(20)) + ']'; if DataLength(@SQLToUse) = 0 BEGIN set @SQLToUse = 'SELECT A.UniqueResponseID as UniqueID, A.UserID, IsNull(' + @Alias + '.response,''N/A'') as [' + lower(@Question) + '] ' set @SQLToUseJoin = ' From ( SELECT distinct Y.uniqueresponseID, Y.UserID, B.UserName from dbo.[DynamicForms_QuestionResponse] As Y inner join dbo.[DynamicForms_Question] as Z on (Y.DynamicQuestionID = Z.DynamicQuestionID) Left Outer Join dbo.[Users] B on B.UserID = Y.UserID where ((Y.UserID = ' + Cast(@UserID as varchar(10)) + ') OR (' + Cast(@UserID as varchar(10)) + ' = -2)) AND Z.ModuleID = ' + Cast(@ModuleID as varchar(20)) + ' ) As A Left Outer join dbo.[DynamicForms_QuestionResponse] as ' + @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias + '.DynamicQuestionID) ' END ELSE BEGIN set @SQLToUse = @SQLToUse + ', ' + @Alias + '.response as [' + lower(@Question) + '] ' set @SQLToUseJoin = @SQLToUseJoin + 'Left Outer join dbo.[DynamicForms_QuestionResponse] as ' + @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias + '.DynamicQuestionID) ' END; FETCH NEXT FROM curBubba INTO @Question, @DynamicQuestionID; END CLOSE curBubba; DEALLOCATE curBubba; Set @SQLToExecute = Convert(varchar(MAX), @SQLToUse) + Convert(varchar(MAX), @SQLToUseJoin) SET @MySQL = @SQLToExecute RETURN

Any movement on this at all? Maybe a version upgrade whereby it doesnt care what the length of the question might be?

HI Shawn, can you use the "Contact US" on the left pane and send me host info to your website? I can quickly execute the SQL statements and see if it will fix y our issue. -- David

Thank you for taking a look at this issue. I have sent the info you requested through the contact page.

Okay, we figured out what the problem was. The SQL query we used was based on “question” and you had numerous questions that had identical names, etc. You also had datagrid fields and button fields that weren’t handled in our old stored procedure.

We’ve update a stored procedure to error trap for field types such as buttons/datagrids and also used the shortname fields and made sure that shortnames are selected on uniqueness (distinct) values also. The columns are now generated based on shortnames and I also created a quick template (firstname/lastname) which you can modify to your liking. -- David

First off, thanks for the excellent support your team gives to your product. It is top notch!

It appears that it fixed the initial issue. I could generate columns and setup the template. But now, for some reason (I would love it to be something stupid on my part), if I try to use dynamic view tapping into a specific dynamic forms dataset, it does not show any data, even though I have a template setup. If I use another dynamic forms (a test one I created), it shows the data fine.

Intriguing, because on the initial one you assisted me with, the data shows fine. But I cannot seem to add an additional module to another page with this dynamic form.

Any ideas on how I might be able to troubleshoot? Would you like me to send you the specific page and connect info through contacts?

Let me also add that I believe I was having the problem before, but I installed the full version as I was still on the trial version.

For the other DF modules, do you have any data filled out and submitted? For example, for the DF module that you say isn't generating any results, can you go to that module/page and click on Advance Features / View Form results.
Are there any data saved there? -- David

Yes, I can see data in the view form results. And I have a Dynamic Views page that is showing results from this DF Module. But now trying to create a new view for this DF module, and specifically filter for a community. With or without a filter, I cant seem to get data to show.

You can use the same connect information I sent before if need be. The specific page in question is named something like FILP Trainer Wheeler.

HI Shawn, I was off Friday but I will look into this on Monday morning. -- David

Just tried again. For some reason, I cannot add an additional module to another page that will display results of a specific dynamic form. I do display data no problem on the first page i created, and I can edit that dynamic view template, but cant seem to add it to another page.

hopefully i am missing something silly...

I've looked and it's very bizarre. If I look at the dynamicforms_question SQL table, you have 4 dynamic forms modules (839,854,898,925).
But looking at Dynamic Views, it shows you have for dynamic forms modules (834,854,898,925). Note that moduleID 834 actually doesn't exist in the SQL table which is why if you choose
this form it is bombing out. Is this the module that you were attempting to create? -- David

No, I was trying to add forms module 854, which I am displaying in another location. What do you think a solution might be?

As always, thanks for your excellent tech support and quick responses. I am hoping I am helping refine this module for future releases (my hopes), and not becoming too much of a pain.

I tried module 854 and it works fine for me. Did you try to delete all the columns and then regenerate the columns? -- David

I am actually glad to hear it, because maybe I am doing something silly!

Believe it or not, I still cant do it! Could I impose on you to add a page with the module, and then create a template with maybe [firstname] [lastname]?

Although I have done this succesfully before, cant seem to recreate.

DONE. It's the bottom Dynamic Views module at this URL:

http://www.tfff.org/Institute/FILPS...fault.aspx

-- David

Logged in, saw you had added a module with data.....

Once again, I tried to add a Dynamic View module and link with data and it did not work. Thinking for some reason I needed to use a host account, I tried, still cannot add data.

Can you think of any reason why you seemingly had no problem adding the dynamic view module and listing data but i cant?

Let me tell you a piece of the puzzle that might be interesting. No matter what I put in the template, whether it be a field name [test] or any plain text, it never shows up when viewing?

Think I found it! Under Search it was defaulting to for 300 as the maximum number of records. Even though the display no records until search is unchecked, when I changed the maximum records to zero, viola, the data appeared. I am going to assume this is a bug somewhere, but please let me know if i just wasnt following proper procedures.

Please check your configuration settings under Module Configuration and see how your's is set to mine. This would point you in the right direction why no data shows up for your module versus the one I set up as an example. -- David