Freddy,
No, the part that follows IN isn't the problem, it's that the PermissionID
column being used to compare against the "<> 1" is a string. SQL 2000 is
basically telling me that I can't compare a string with an integer. I've
worked around it by making the right side of the equation a string
evaluation, but that's not a very good solution me thinks.
In other words it's the "PermissionID" side of the equation that's the
problem. WHERE PermissionID <> 1
There was another thread here that answered a very similar question (Stored
Procedures and HTML forms) and maybe in the morning I can extract the logic
from that and solve this thing, it's been a looonngg day!
Thanks for the response.
-----Original Message-----
From: Freddy [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 02, 2001 9:24 PM
To: CF-Talk
Subject: Re: SQL Server data type question...
Couldn't you use Permissions.PermissionID IN " '4','5' "
Tim Fields wrote:
> I have a client that has an existing SQL Server database which includes a
> table used as part of a permissions framework. The problem is that whoever
> created the table was clever enough to create a column for permissions
id's
> as a varchar rather than creating a join table. So I may a situation where
> Permissions.PermissionID =
>
> They have been using a statement in SQL Server7 that is something like:
>
> Select p.FName, p.LName
> >From Permissions p
> Where p.PermissionID <> 1
>
> That worked in 7, but not in 2000 which correctly sees it as a data type
> error ( cannot convert...)
>
> They won't let me rebuild that part of the database properly right now for
> reasons that are debatable, but anyway I have to work with this structure.
> Their internal person came up with:
>
> Where p.PermissionID NOT LIKE '%1%'
>
> but I pointed out that it had some serious holes in it such as it would
fail
> 11 or 21. I've fixed it temporarily by using a series of AND NOT LIKE
> statements with wildcards to fail '1', '1,' or ',1' but pass everything
> else. It's a pretty damned ugly statement though if I do say so myself.
>
> Does anyone know of a more elegant solution? I can't quite get my arms
> around sp_executesql to see if that could help.
>
> Any help would be appreciated.
>
> Tim
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
