When trying to create column names within Dynamic views on a Dynamic Forms Instance, I receive the following error:

The identifier that starts with 'RJoin_100 on (A.uniqueresponseID = [RJoin_100.uniqueresponseID and '5129B3D8-5F18-4755-A9E4-7B46AB9D1804' = [RJoin_100#Dy' is too long# Maximum length is 128# Unclosed quotation mark after the character string 'RJoin_100 on (A#uniqueresponseID = [RJoin_100#uniqueresponseID and '5129B3D8-5F18-4755-A9E4-7B46AB9D1804' = [RJoin_100#Dyn'# Incorrect syntax near 'RJoin_100 on (A#uniqueresponseID = [RJoin_100#uniqueresponseID and '5129B3D8-5F18-4755-A9E4-7B46AB9D1804' = [RJoin_100.Dy'.

Any ideas on a direction to troubleshoot?

Any direction at all? I am going to create a short form and see if it is just this particular form that is the issue.

So, trying to enter field names manually, creating template, and then get the following error which is very similiar to the above on a previously created form instance

A critical error has occurred.
The identifier that starts with 'RJoin_100 on (A.uniqueresponseID = [RJoin_100.uniqueresponseID and '5129B3D8-5F18-4755-A9E4-7B46AB9D1804' = [RJoin_100.Dy' is too long. Maximum length is 128. Incorrect syntax near the keyword 'like'.

any ideas on a direction for troubleshoot?

More details for Site View:

More details from Event Log:

Inner Exception
The identifier that starts with 'RJoin_100 on (A.uniqueresponseID = [RJoin_100.uniqueresponseID and '5129B3D8-5F18-4755-A9E4-7B46AB9D1804' = [RJoin_100.Dy' is too long. Maximum length is 128. Unclosed quotation mark after the character string 'RJoin_100 on (A.uniqueresponseID = [RJoin_100.uniqueresponseID and '5129B3D8-5F18-4755-A9E4-7B46AB9D1804' = [RJoin_100.Dyn'. Incorrect syntax near 'RJoin_100 on (A.uniqueresponseID = [RJoin_100.uniqueresponseID and '5129B3D8-5F18-4755-A9E4-7B46AB9D1804' = [RJoin_100.Dy'.

Message:
DotNetNuke.Services.Exceptions.PageLoadException: The identifier that starts with 'RJoin_100 on (A.uniqueresponseID = [RJoin_100.uniqueresponseID and '5129B3D8-5F18-4755-A9E4-7B46AB9D1804' = [RJoin_100.Dy' is too long. Maximum length is 128. Unclosed quotation mark after the character string 'RJoin_100 on (A.uniqueresponseID = [RJoin_100.uniqueresponseID and '5129B3D8-5F18-4755-A9E4-7B46AB9D1804' = [RJoin_100.Dyn'. Incorrect syntax near 'RJoin_100 on (A.uniqueresponseID = [RJoin_100.uniqueresponseID and '5129B3D8-5F18-4755-A9E4-7B46AB9D1804' = [RJoin_100.Dy'. ---> System.Data.SqlClient.SqlException: The identifier that starts with 'RJoin_100 on (A.uniqueresponseID = [RJoin_100.uniqueresponseID and '5129B3D8-5F18-4755-A9E4-7B46AB9D1804' = [RJoin_100.Dy' is too long. Maximum length is 128. Unclosed quotation mark after the character string 'RJoin_100 on (A.uniqueresponseID = [RJoin_100.uniqueresponseID and '5129B3D8-5F18-4755-A9E4-7B46AB9D1804' = [RJoin_100.Dyn'. Incorrect syntax near 'RJoin_100 on (A.uniqueresponseID = [RJoin_100.uniqueresponseID and '5129B3D8-5F18-4755-A9E4-7B46AB9D1804' = [RJoin_100.Dy'. 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.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, String spName, Object[] parameterValues) at DataSprings.DNN.Modules.DynamicViews.Data.SqlDataProvider.DataSprings_RetrieveDynamicView(Int32 PortalID, Int32 intTableorQuery, String strTableOrQuery, String LimitResults, String strUserFilter, Int32 intUserID, String strDetailView, String strAltConnection) at DataSprings.DNN.Modules.DynamicViews.Business.DynamicViewsController.DataSprings_RetrieveDynamicView(Int32 PortalID, Int32 intTableorQuery, String strTableOrQuery, String LimitResults, String strUserFilter, Int32 intUserID, String strDetailView, String strAltConnection) at DataSprings.DNN.Modules.DynamicViews.Common.GetDynamicViewData(Int32 PortalID, Int32 intTableorQuery, String strTableOrQuery, Int32 TabId, Int32 ModuleID, String strUserFilter, Boolean blnTop1, Boolean blnGenerateColumns, String strPrimaryKey, Int32 intUserID, String strDetailView, String strAltConnection) at DataSprings.DNN.Modules.DynamicViews.Settings.cmdGenerateColumns_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.LinkButton.OnClick(EventArgs e) at System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) --- End of inner exception stack trace ---

It does work with other Dynamic Form Instances, so there is something specific to this one....

Sorry to bring up this old post, but has anyone find a solution for this yet? I am having similar problems. It seems like Dynamic Views has certain requirements for the format of the Dynamic Forms, but it is not completely clear to me what these are. Of course I could try to figure them out myself by trail and error, but I was hoping that this had been done before already.

Thanks, Marc.

Hello Marc,

If you're using a Dynamic Forms instance as the data source for your Dynamic View then there are some restrictions:

* No duplicate ShortFieldNames on your Dynamic Form
* No parenthesis '(' or ')' in the ShortFieldName on your Dynamic Form
* Cannot have the text HTML in the ShortFieldName on your Dynamic Form.

Please let me know if this assists you, more cases then not it's the duplicated short field names.

Or if these don't help you then you may need to refer to our blog post. It sounds like your Generate Columns stored procedure may not exist:

http://www.datasprings.com/news/blo...tial-issue

Thanks,

Ryan

Hi Ryan,

Thanks for your reply. Those three criteria were what I was looking for, so I think I should be safe from now on. But I think you actually mean "Question" when you refer to ShortFieldName.

This strikes me as a bit weird; Dynamic Views seems to be using the descriptions of the fields a columns, which are not required to be unique in Dynamic Forms.

I think it would be a nice feature if Dynamic Views would allow the possibility to select the ShortNames of the fields as columns, since this is in line with the convension of Dynamic Forms to keep ShortNames unique.

I have quite a big collection of large forms with equal descriptions that I need to make different now (using things such as "word1 word2" and "word1 word2" to make descriptions appear equal but unequal in the database).

Hello Marc,

My mistake, I have modified my Dynamic Views SQL Integration file to use the ShortFieldName rather than the QuestionName from the table DynamicForms_Question.

You may want to do the same if you're comfortable with SQL. You can modify the one column that you're referring to in this SQL integration file.

Just copy/paste this procedure alteration into Host - > SQL and execute the query. Then go to Host -> Host Settings and Restart the Application.

This should fix any issues.

Please let me know if you have any questions.

Thanks,

Ryan

Hi Ryan,

I know basic SQL, but I am not familiar with the SQL integration file. Will this make sure that Dynamic Views will always select the ShortFieldName instead of the QuestionName, or do I need to set this for every instance of Dynamic views? In the first case, could you please tell me how to achieve this in a little more details?

Many thanks in advance, Marc

Hi Marc,

Here is the URL that uses the short field names:
http://www.datasprings.com/portals/...dNames.txt

If you run that then this should use short field names instead of the full question. If this gives you an error or the stored procedure does not exist please change the the first word of "ALTER" to be "CREATE". You will run this under Host, SQL.

Thanks!

-Chad

Hello Chad,

Thanks a lot, it works!

Still a sidenote: if I run this script using 'Alter', I get a message that no data has been found, but if I run it using 'Create' instead of 'Alter' I get a message that there is an error, but it works anyway. I hope this is not a problem, and I actually thought that an SQL query was not executed if an error occurs.

Chad:

Just wanted to thank you for this post. I was having the same trouble and found your earlier exchange with chupaul but couldn't get that solution to work. It took me a bit to find this exchange where you mentioned replaceing "Alter" with "Create". It worked fine after I did that.

Thanks, again!

Jim

Hi again,

It seems that Dynamic Views has a limit to the number of fields it can accept form Dynamic Forms as well. I have a form with 138 fields, and importing them all to Dynamic Views gives the error that was described in the first post. When I use SQL to change all the fields to acceptable values, it still gives the error. When I decrease the number of "active" fields (by setting Inactive=0 in SQL) to 121, the error disappears. But when I use different fields, the error disappears only with fewer than 100 fields. When I look at the fields that seemed to cause the error (field 122 and field 100), there is nothing wrong with them and when I place them in a form with very few active fields they work just fine.

So there are happening some weird things with forms with many fields. For now I have set the rule that I will keep the number of fields below 100, which seems acceptable, but I was wondering if you could provide me with some more detailed information on this issue.

Thanks, Marc

Hello Marc,

Hmm... I've never encountered this problem, but then again I never use the default Dynamic Forms storage method.

I would recommend creating SQL Flat tables to contain your form submissions. It's easier to extract data out and format it versus using View Form Results or Dynamic View with a data source of Dynamic Forms instance.

You can learn how to create SQL Flat tables from demonstration 31:

http://www.datasprings.com/formdemos