Primary Key and LIKE operator

[adamlovell]

Hi All,

I have been using SQL Server 2000 for some time now and decided to change to SQL Server 2005 Express.

I have a SQL statement as follows

SELECT CAST(ID AS VARCHAR(50)) AS ID, CAST(FILE_ID AS VARCHAR(50)) AS FILE_ID FROM tblTest ORDER BY CAST(ID AS INT)

The reason for this statement is I want to be able to search the recordset using ADO in VB6 using FIND
rs.Find ID LIKE '1%'

So the SELECT CASE converts it to a VARCHAR and the ORDER BY CAST keeps the numbers in a numerical order.

It works fine using SQL Server 2000 but SQL Server 2005 it gives me a "The comparison operator was invalid" error message but only on the ID column. If I modify the FIND statement to rs.Find FILE_ID LIKE '1%' I do not get this error and it all works as expected.

I looked at the datatypes in the table and they were the same, the only difference was the ID column is set to a PRIMARY KEY. I removed the primary key and the problem went away.

Has anyone encountered this issue and do you have any workarounds. Is this a bug with SQL 2005 or ADO??

Any help is appreciated.

[szlamany]

My first guess would be that ID is a reserved keyword - and it is - but that in SQL 2005 it's being more properly rejected...

Pick a different name for the column

[adamlovell]

This is just example I put into the post, The names of the columns are formatted like this
File_ID
Task_ID
rather than just ID.

I have managed to workaround this issue by adding an Identity column to each table and set this column to the primary key. I have named it ID and it seems to be working fine.

I still would like to get this resolved because I have heaps of applications which I would like to update to SQL Server Express 2005, but I do not want to have to modify each table

[szlamany]

I'm going to ask a moderator to move this thread to the database forum - I would also like to know the answer to this...

[szlamany]

btw - welcome to the forum!

[si_the_geek]

Moved to DB forum

Welcome to VBForums

I have seen several issues before with rs.Find, perhaps this is just another one of those oddities?

It may be that the alias of the field as having the same name causes an issue - have you tried with a different alias?

As a workaround, how about loading just the required data, rather that filtering?
eg:

SELECT CAST(ID AS VARCHAR(50)) AS IDVarchar, CAST(FILE_ID AS VARCHAR(50)) AS FILE_ID 
FROM tblTest 
WHERE IDVarchar LIKE '1%'
ORDER BY CAST(ID AS INT)

[adamlovell]

Well done. You hit the nail on the head.

I changed the name of the alias and bingo it now works.

Strange because this worked fine in SQL server 2000 but not 2005. Weird

Thanks again.