Share via

SqlDataAdapter.Fill method is throws SqlException when the BUILTIN/Administrators language in Sql server is British English

Rajaiah 1 Reputation point
2020-12-30T02:06:40.067+00:00

Our c# application uses SqlDataAdapter to fetch data from the DB(from an rdlc file) to present in a report. For some reason the SqlDataAdapter.Fill method is throwing below SqlException when the BUILTIN/Administrators language is British English. When it is English, it is working fine. I debugged my C# code but didn't get a clue why this is happening. And it is reverse in one of our production systems. Can some one help me overcome this exception?

{System.Data.SqlClient.SqlException (0x80131904): Error converting data type nvarchar to datetime.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
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.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at ReportViewer.ReportViewer.CreateReportDataSource(ReportRequest reportRequest, String selectString, ReportParameterInfoCollection parameters, DataSet dataSet, SqlConnection connection) in C:\ISSCode\branches\Samra2Freeze\Controls\ReportViewer\ReportViewer.cs:line 367
ClientConnectionId:fc8c8835-3d91-423e-b467-64c6adf6fecc
Error Number:8114,State:5,Class:16}

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,878 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,656 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Poblacion 1,556 Reputation points
    2020-12-30T09:20:11.537+00:00

    I agree with the previous response in that the problem is likely to be in the datetime format. The clue is in this line:

    "Error converting data type nvarchar to datetime"

    This is likely caused because you are concatenating the datetime in your query when loading the DataAdapter. This will cause it to be formatted according to the rules of the CurrentCulture (D/M/Y for British English but M/D/Y for American English). But then it will be sent to the SQL Server, and the server will interpret it (by default) as M/D/Y which will cause a problem is the CurrentCulture was not M/D/Y.

    You could, of course, force a specific format when concatenating the date, but this is not the elegant solution. Instead, you should parameterize the query and assign the date to the parameter. This will always work regardless of the CurrentCulture used by the program. If you show the code that you are using for adding the query to your SQLDataAdapter, we can show the changes that are needed to parameterize it.

    1 person found this answer helpful.
    0 comments
  2. Timon Yang-MSFT 9,586 Reputation points
    2020-12-30T06:48:37.363+00:00

    I think the biggest possibility is the format of datetime.
    The default datetime format of different countries is different, and sometimes conflicts may arise, for example: 

         American English: 12 / 30 / 2020 1:53:16 PM    

         British English: 30 / 12 / 2020 13:53:35

    If you try to convert the first string to a datetime type in a British English environment, you will get an error, because there are no 30 months in a year.
    For this kind of problem, we usually solve it by adding a CultureInfo object: 

                CultureInfo culture = CultureInfo.CreateSpecificCulture("en-US");  
            DateTime date= DateTime.Parse(dateTime, culture);

    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments