Type Mismatch in TMSQuery

[JensFudge]

I get the following error upon opening a specific query:

qrInvoice Type mismatch for field confirmationDate, expecting: Date
actual: Widestring

Note. The error only occurs on some machines, and I cannot tell the difference on said machines.
I have run the application on Windows 7, and on another Windows 7, with same version number, same level (professional), same updates installed, it doesnt work.

This query is the only one in the entire application with the error.
This query is also the only query where I have done the following:

On the TMSQuery object at designtim add all fields in the fields editor.
I have added a lookup field also to look up something from another dataset the lookupfield is NOT the field in question.
The reason I added the fields in this manner, is because I have hooked it up to a TdbAdvGrid from TMS Software and I want a combobox in the grid.

I have checked the database. The field is defined as DATE, I have checked the generated fields in Delphi XE2, its defined as qrinvoice_confirmationdate : TDateField.

Do you have any suggestions?

oh, I cannot remember how to check which version SDAC we have

Thanks in advance

Kind regards

Jens Fudge

[AndreyZ]

Hello,

This problem is caused by the OLEDB provider. OLEDB provider returns DATE columns in the string format, and this causes the problem with date format. When there is no Native Client provider, SDAC uses OLEDB provider. To solve the problem, you should install Native Client provider on all machines where you encountered the problem with date format.

[JensFudge]

Hi,

Ok, that could make sense.. Fair enough.

I am sorry I have really no clue about drivers..

Can you be a little more specific as to which driver I need to install, and where do I obtain such driver?

Needless to say, I need a driver that I can distribute royalty free.

Thank you very much for your reply, and for these great components.

Kind regards
Jens Fudge

[AndreyZ]

SQL Native Client is provided by Microsoft and is supplied with SQL Server installation. If you are working with SQL Server 2008, you should download SQL Server 2008 Native Client here: http://www.microsoft.com/downloads/deta ... laylang=en . If you are working with SQL Server 2005, you should download SQL Server 2005 Native Client here: http://www.microsoft.com/downloads/deta ... laylang=en . You can find more information abouit SQL Native Client installing at http://msdn.microsoft.com/en-us/library/ms131321.aspx

[JensFudge]

Thank you very much.
After installing the appropriate driver it worked like a charm.

Best regards
Jens Fudge

[AndreyZ]

It's good to see that the problem was solved. If any other questions come up, please contact us.

[Rycho]

I have the same problem, but I have no impact on system components installed by the customer. I'm using MS SQL Server 2008 and any-DAC for Delphi. Is there another solution? Do the same error can be caused by something else? My program works fine on one computer, and the other does not.

[AndreyZ]

To solve the problem, you should install the SQL Server Native Client provider on all computers where you encountered the problem with date format.
Please note that we develop two products for working with SQL Server: SDAC and UniDAC. If you are using another product, you should ask its developers about problems you encountered with it.

[GNiessen]

Does Connection.Option.Provider need to be set a certain way for this the work?

[AndreyZ]

The TMSConnection.Options.Provider property is used to specify a provider from the list of supported providers. If Provider is set to prAuto (the default value), SDAC looks for the available provider with the most recent version. It means that SDAC looks for an available provider in the following sequence: Native Client 2008, Native Client 2005, OLEDB. The first found provider from the sequence is used. If Provider is set to prNativeClient, SDAC looks for an available provider in the following sequence: Native Client 2008, Native Client 2005. If Provider is set to prSQL, SDAC uses the OLEDB provider. If Provider is set to prCompact, SDAC uses the SQL Server Compact Edition provider. If you want SDAC to use only SQL Server Native Client, you should set the TMSConnection.Options.Provider property to prNativeClient.