Type Mismatch in TMSQuery

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
JensFudge
Posts: 35
Joined: Mon 12 Jan 2009 08:37

Type Mismatch in TMSQuery

Post by JensFudge » Fri 20 Jan 2012 10:28

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

Post by AndreyZ » Mon 23 Jan 2012 09:11

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
Posts: 35
Joined: Mon 12 Jan 2009 08:37

Native Client Provider

Post by JensFudge » Tue 24 Jan 2012 08:21

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

Post by AndreyZ » Tue 24 Jan 2012 11:46

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
Posts: 35
Joined: Mon 12 Jan 2009 08:37

Thanks

Post by JensFudge » Mon 30 Jan 2012 11:47

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

Best regards
Jens Fudge

AndreyZ

Post by AndreyZ » Mon 30 Jan 2012 12:02

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

Rycho
Posts: 1
Joined: Mon 04 Jun 2012 17:42

Re: Type Mismatch in TMSQuery

Post by Rycho » Mon 04 Jun 2012 18:00

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

Re: Type Mismatch in TMSQuery

Post by AndreyZ » Tue 05 Jun 2012 08:41

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
Posts: 28
Joined: Wed 18 Jan 2012 20:15

Re: Type Mismatch in TMSQuery

Post by GNiessen » Wed 06 Jun 2012 15:53

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

AndreyZ

Re: Type Mismatch in TMSQuery

Post by AndreyZ » Fri 08 Jun 2012 07:29

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.

Post Reply