The data types ntext and varchar are incompatible in the equal to operator.

[Paul]

Hi, 
    I have an application that can switch between using MySQL and SQL and am trying to just have one data access layer as ORM states it can support.

When using MySQL the below statement works

 

 

  Query<Resourcelibrary> query = scope.GetOqlQuery<Resourcelibrary>("SELECT * FROM ResourcelibraryExtent WHERE resourceName ='" +
                    ResourceName + "' AND resourceType = '" + ResourceType + "' AND metaData = '" + MetaData + "'");

 

On sql it errors with the title name above.
The sql output is

SELECT [ResourceID] AS COL1, [CreatedBy] AS COL2, [CreatedOn] AS COL3, [LastAccessedBy] AS COL4, [LastAccessedOn] AS COL5, [MetaData] AS COL6,
 [ResourceAccess] AS COL7, [ResourceData] AS COL8, [ResourceDescription] AS COL9, [ResourceName] AS COL10, [ResourceType] AS COL11
 FROM [resourcelibrary] WHERE [ResourceName] = 'Movie Maker' AND [ResourceType] = 'Icon' AND
 [MetaData] = '<SourcePath=C:\Program Files\Movie Maker\MOVIEMK.exe><EmbededIconName=MOVIEMK_128.ico>'

MetaData column is the column in question with the datatype of ntext. If I was just using the Sql statement instead of oql then I could do a convert on the types.

How can I change this statement to work regardless of the db engine and without changing the DB.

  Query<Resourcelibrary> query = scope.GetOqlQuery<Resourcelibrary>("SELECT * FROM ResourcelibraryExtent WHERE resourceName ='" +
                    ResourceName + "' AND resourceType = '" + ResourceType + "' AND metaData = '" + MetaData + "'");

I would have thought the ORM would have sorted this out for me or have an option to set that could be something like.
AutoConvert on The data types ntext and varchar are incompatible in the equal to operator error.

[Paul]

it's ok I have now sorted it. I changed the statement to

Query<Resourcelibrary> query = scope.GetOqlQuery<Resourcelibrary>("SELECT * FROM ResourcelibraryExtent WHERE resourceName ='" +
                    ResourceName + "' AND resourceType = '" + ResourceType + "' AND metaData like '" + MetaData + "'");

replaced = with like.

[Zoran]

Hello Paul,

Good to know that you have solved your problem. We will nevertheless have in mind what you previously reported. If you have any other concerns with our product, please give us your feedback.

Regards,
Zoran
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.