Error when trying to add data to datepicker

[Gregory Kohle]

If I try to add data to a datepicker the following error message is displayed. ShortDate is automatically selected when I drag EvalDate into "Column".


Is there some kind of fix for this?

[Umapathy Shanmugam]

Hi Patrick, 

 

Thank you for contacting Syncfusion support. 

 

We are not able to reproduce the issue “Error when trying to add date to date picker” from our side. Kindly provide us the following information to investigate this issue further from our side. 

1.       Type of data source you are using. 

2.       Language you are using in dashboard designer application. (Share resource file if possible which will be available in the below location). 

“C:\Program Files (x86)\Syncfusion\Dashboard Designer\DashboardDesigner\Localization”  

3.       Language preference and date time format you are using in your machine.  

4.       If possible please share us the sample data along with steps to reproduce this issue from our side.  

 

Regards, 

Umapathy S. 

[Gregory Kohle]

Hi Umapathy,

1: Database MS SQL Server
2: English
3: German language and german Time Format : dd/mm/yyyy
4: Here are some example values from our database:
 

Here is the errolog:

Date : 06-03-2017 14:46:22
Message : 'FORMAT' wird nicht als Name einer integrierten Funktion erkannt.
Stack Trace:    bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   bei System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   bei System.Data.SqlClient.SqlDataReader.get_MetaData()
   bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   bei System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   bei System.Data.SqlClient.SqlCommand.ExecuteReader()
   bei Syncfusion.Dashboard.Base.Data.SqlQueryBuilder.ExecuteReaderQuery(String query, String connectionString, ConnectionProviderType type)
   bei Syncfusion.Dashboard.Base.Data.ServerDataProvider.GetTable(ServerDataProviderParameters parameter)
   bei Syncfusion.Dashboard.Base.Engine.RelationalEngine.GetDataFromMainSource(List`1 schemaInfos, List`1 viewerFilterList, Boolean isNotControlFilter, String reportName, Int32 startRowNumber, Int32 endRowNumber, FilterColumn datePagingInfo)
   bei Syncfusion.Dashboard.Base.Engine.RelationalEngine.NotifyOnActiveColumnsChanged(SchemaInfo addedSchemaInfo, MinimalDataSetting minimalDataSetting)
   bei DashboardApplication.DashboardDesigner.<>c__DisplayClass5d2.<NotifyActiveColumnChanged>b__5ce(Object obj, DoWorkEventArgs arg)

Generated Query: SELECT TOP 1000 FORMAT([dbo_MDEDATA].[EvalDate], 'yyyy/MM/dd') AS [DatePicker_Column_0] FROM [dbo].[MDEDATA] AS [dbo_MDEDATA] WHERE (([dbo_MDEDATA].[EvalDate] >= {ts '2017-01-02 00:00:00.000'} AND [dbo_MDEDATA].[EvalDate] <= {ts '2017-02-15 23:59:59.998'} )) GROUP BY FORMAT([dbo_MDEDATA].[EvalDate], 'yyyy/MM/dd')  ORDER BY 1 ASC
--------------------------------------------------

Regards,
Patrick

[Umapathy Shanmugam]

Hi Patrick, 

 

Thanks for sharing required information.  

 

We suspect you are using  SQL server 2008. If yes, we would like to inform you that our Syncfusion Dashboard supports only SQL server 2012 and above. Kindly refer to the below link for Dashboard designer require to run.  

https://help.syncfusion.com/dashboard-platform/dashboard-designer/system-requirements  

 

Regards, 

Umapathy S. 

[Gregory Kohle]

Hi Umapathy,

I have SQL Server 2008 R2 & 2012 installed on my machine.
Do I need to uninstall SQL Server 2008 to get it working?

Regards,
Patrick

[Umapathy Shanmugam]

Hi Patrick, 

 

Thanks for your reply. 

 

No need to uninstall SQL Server 2008, instead of this you need to use SQL Server 2012 server name (instance name) to connect SQL Server 2012 server with our dashboard application. Kindly refer to the following link to find the instance name of your SQL Server 2012 version. 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bb7ce542-be81-436a-bcd0-e6590f7ea003/instance-names-of-the-sql-server-?forum=sqlkjmanageability  

 

Regards, 

Umapathy S. 

[Gregory Kohle]

Hi Umapathy,

thanks for your respone.
Now I get the following error:


Here is the errorlog:

Date : 08-03-2017 15:07:35
Message : Die Zeichenfolge wurde nicht als gültiges DateTime erkannt.
Stack Trace:    bei System.DateTimeParse.ParseExact(String s, String format, DateTimeFormatInfo dtfi, DateTimeStyles style)
   bei System.DateTime.ParseExact(String s, String format, IFormatProvider provider, DateTimeStyles style)
   bei Syncfusion.Dashboard.Base.Engine.RelationalEngine.GetCustomDateTimeFormatTable(Dictionary`2 customDatePositions, DataTable orderedTable, Int32 rowIndex, DataRow row, DateTimeFormat key)
   bei Syncfusion.Dashboard.Base.Engine.RelationalEngine.GetTableWithCustomFormat(DataTable source, Dictionary`2 customDatePositions)
   bei Syncfusion.Dashboard.Base.Engine.RelationalEngine.GetTableWithCustomFormattedDateColumns(DataTable dataTable, List`1 schemaInfoCollection)
   bei Syncfusion.Dashboard.Base.Engine.RelationalEngine.GetDataFromMainSource(List`1 schemaInfos, List`1 viewerFilterList, Boolean isNotControlFilter, String reportName, Int32 startRowNumber, Int32 endRowNumber, FilterColumn datePagingInfo)
   bei Syncfusion.Dashboard.Base.Engine.RelationalEngine.NotifyOnActiveColumnsChanged(SchemaInfo addedSchemaInfo, MinimalDataSetting minimalDataSetting)
   bei DashboardApplication.DashboardDesigner.<>c__DisplayClass5d2.<NotifyActiveColumnChanged>b__5ce(Object obj, DoWorkEventArgs arg)

Generated Query: SELECT TOP 1000 FORMAT([dbo_MDEDATA].[EvalDate], 'yyyy/MM/dd') AS [DatePicker_Column_1] FROM [dbo].[MDEDATA] AS [dbo_MDEDATA] GROUP BY FORMAT([dbo_MDEDATA].[EvalDate], 'yyyy/MM/dd')  ORDER BY 1 ASC
--------------------------------------------------

Regards,
Patrick

[Umapathy Shanmugam]

Hi Patrick,

We unable to reproduce the “String was not recognized as a valid date time” issue at our side. Kindly help us to check the issue by giving some below information.

1. Version of Dashboard Designer which you are using.
2. Please confirm German time format. We unable to find dd/mm/yyyy format in system date time formats for German language.
3. SQL Server language.
4. Please share us the steps to reproduce this issue along with some screenshots which should explain how you have travelled over dashboard while designing a report.

Regards,
Umapathy S.

[Gregory Kohle]

Hi Umapathy,

1: version 2.1.0.2
2:

3: german
4: first I added a datasource which is located on a SQL Server 2012.
Then I added a datepicker.



Here are some EvalDate values:

All values are correctly displayed in the datasource view.

Regards,
Patrick

[Umapathy Shanmugam]

Hi Patrick, 

 

Thank you for sharing information.  

 

We are tried by changing system format as German(Germany), date time format as “dd.MM.yyyy”, SQL Server language as “German” and prepared data like you shared, but still we are not able to reproduce “String was no recognized as a valid date time” issue at our side.  

 

If possible to share your online SQL server credentials with guest account that holds sample database, we will check by connecting that server through our dashboard application and will try to find the root cause of this issue.  

 

Kindly use “yyyy/MM/dd” this date time format in your machine instead of “dd.MM.yyyy” and try to use DatePicker control and let us know the status.  

 

Regards, 

Umapathy S.

[Gregory Kohle]

Hi Umapathy,

I switched the time format to yyyy/MM/dd and still got the same error message.
I will mail you a backup of a test database to [e-mail] so that you can
check for cause of the issue.
Thanks.

Regards,
Patrick

[Ranjani Prabhakaran]

From: Patrick Neubauer
Sent: Wednesday, March 15, 2017 2:32 AM
To: Syncfusion Support <[e-mail]>
Subject: test database for technical support forum 129222 

Dear Sir or Madam, 

I have attached a test database for Umapathy Shanmugam regarding an issue in the datepicker widget. 

To reproduce the issue just add the table dbo.MDEData to the Dashboard Designer and add a datapicker widget  

with the datetime value EvalDate.  

Please forward this mail to him.  

Thanks.  

Regards, 

Patrick Neubauer 

20170315-TestDB

[Umapathy Shanmugam]

Hi Patrick, 

 

Thank you for sharing the sample database.  

 

We have tried to reproduce “String was not recognized as a valid date time format” issue with the sample data you have shared in our dashboard designer application by designing a report with date picker control. Still we are not able to reproduce that issue. We have recorded the same for your reference.  However we are checking further possibilities from our side. Can you please share your database collation details if any.  

 

http://www.syncfusion.com/downloads/support/forum/129222/ze/DatePicker254076689  

 

NOTE: Also please let us know if we are proceeding in the proper way by viewing the above video. If your way of reproducing the issue is different from ours please report/send a video on the same.  

 

Regards, 

Umapathy S. 

[Gregory Kohle]

Hi Umapathy,

thank you for your respone.
The only difference is that we use SQL Server Authentification to connect to our database.
All other steps are the same.

Regards,
Patrick

[Umapathy Shanmugam]

Hi Patrick, 

 

Thanks for your reply. 

 

We have tried the same through SQL server authentication, but still we are not able to reproduce “String was not recognized as a valid date time format” issue at our end. Seems the problem is not with data because we have imported your data into our SQL server and tried to reproduce the issue. On the pther hand, we suspect that the issue may be in your SQL server environment settings that returns the data in system date time format. Hence we suggest you to check the date picker control by connecting some other SQL server environment using our dashboard designer application in a new/fresh machine environment and let us know the outcome.  

 

We have another possible way to check this issue, that is, you need to setup a global environment to connect your SQL server from our side to debug that issue and provide solution further. In short, you need to make your SQL environment globally accessible.  

 

Regards, 

Umapathy S. 

[Gregory Kohle]

Hi Umapathy,

would it be possible to schedule teamviewer session or something like that to look into my environment?
Thanks.

Regards,
Patrick

[Umapathy Shanmugam]

Hi Patrick, 

  

As we created separate incident, we will continue further communication through that incident.  

  

Thanks, 

Umapathy S.