Shawn Romine Posts:73
|
04-12-2010 11:27 AM |
|
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 |
|
|
|
|
David To
river guide Posts:2719
|
04-13-2010 07:57 AM |
|
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 |
|
|
|
|
Shawn Romine Posts:73
|
04-13-2010 08:17 AM |
|
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" |
|
|
|
|
David To
river guide Posts:2719
|
04-14-2010 11:29 AM |
|
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 |
|
|
|
|
Shawn Romine Posts:73
|
04-14-2010 01:51 PM |
|
Sure. I created a Dynamic Form called FILP Application on your test website. Thanks for taking a look! |
|
|
|
|
David To
river guide Posts:2719
|
04-15-2010 06:53 AM |
|
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 |
|
|
|
|
Shawn Romine Posts:73
|
04-16-2010 10:36 AM |
|
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 |
|
|
|
|
David To
river guide Posts:2719
|
04-16-2010 12:59 PM |
|
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 |
|
|
|
|
Shawn Romine Posts:73
|
04-16-2010 01:13 PM |
|
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 |
|
|
|
|
Shawn Romine Posts:73
|
04-19-2010 08:23 AM |
|
Any movement on this at all? Maybe a version upgrade whereby it doesnt care what the length of the question might be? |
|
|
|
|
David To
river guide Posts:2719
|
04-19-2010 08:39 AM |
|
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 |
|
|
|
|
Shawn Romine Posts:73
|
04-19-2010 10:55 AM |
|
Thank you for taking a look at this issue. I have sent the info you requested through the contact page. |
|
|
|
|
David To
river guide Posts:2719
|
04-20-2010 10:33 AM |
|
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 |
|
|
|
|
Shawn Romine Posts:73
|
04-22-2010 05:14 AM |
|
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? |
|
|
|
|
Shawn Romine Posts:73
|
04-22-2010 05:19 AM |
|
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. |
|
|
|
|
David To
river guide Posts:2719
|
04-22-2010 07:46 AM |
|
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 |
|
|
|
|
Shawn Romine Posts:73
|
04-22-2010 07:50 AM |
|
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. |
|
|
|
|
Shawn Romine Posts:73
|
04-23-2010 06:26 AM |
|
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. |
|
|
|
|
David To
river guide Posts:2719
|
04-24-2010 03:27 PM |
|
HI Shawn, I was off Friday but I will look into this on Monday morning. -- David |
|
|
|
|
Shawn Romine Posts:73
|
04-26-2010 12:02 PM |
|
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... |
|
|
|
|
David To
river guide Posts:2719
|
04-26-2010 12:46 PM |
|
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 |
|
|
|
|
Shawn Romine Posts:73
|
04-26-2010 01:06 PM |
|
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. |
|
|
|
|
David To
river guide Posts:2719
|
04-26-2010 01:08 PM |
|
I tried module 854 and it works fine for me. Did you try to delete all the columns and then regenerate the columns? -- David |
|
|
|
|
Shawn Romine Posts:73
|
04-26-2010 01:27 PM |
|
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. |
|
|
|
|
David To
river guide Posts:2719
|
|
Shawn Romine Posts:73
|
05-04-2010 08:19 AM |
|
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? |
|
|
|
|
Shawn Romine Posts:73
|
05-04-2010 08:43 AM |
|
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. |
|
|
|
|
David To
river guide Posts:2719
|
05-04-2010 08:43 AM |
|
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 |
|
|
|
|