SQL Server Data Conversion Error 2

[busterkarmul]

Hello,

Here is the code in an Access 2003 module which is converting a value on a SQL Server 2005 table (tbl_ProjectSteps):

x = DCount("[Key]", "tbl_ProjectSteps", "[numProjID]=" & recCurrentTask.numProjID & " AND " & _
"CLng(Left([Key],InStr(1,[Key],'.')))=" & CLng(str_TaskID))

Here is the query in the SQL Profiler Trace:

SELECT COUNT("Key" )
FROM "dbo"."tbl_ProjectSteps" --count = 1927
WHERE (("numProjID" = 1910 ) --count = 87
AND ({fn convert({fn left("Key",{fn locate('.' ,"Key" ,1 )})},SQL_INTEGER)}= 1) )

I get this error:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value '1.' to data type int

The [key] field in the SQL Server 2005 database table is nvarchar(255) and contains values like 1.0. 1.0.1, etc.

I've been researching this online and cannot seem to find a definitive answer. Any ideas?

Thanks,
Buster

 

[markros]

This simple test shows the exact same problem

declare @t table([key] nvarchar(10))
insert into @t 
select '1.'
union all 
select '1.0'

select * from @t where [KEY] =1

The solution will be to use decimal instead of integer, like this:

declare @t table([key] nvarchar(10))
insert into @t 
select '1.'
union all 
select '1.0'

select * from @t where [KEY] =1.0

PluralSight Learning Library

 

[bborissov]

SELECT COUNT("Key" )
       FROM dbo.tbl_ProjectSteps
WHERE numProjID = 1910 AND
      CASE WHEN CHARINDEX('.', Key) = 0
                THEN Key
           ELSE LEFT(Key, CHARINDEX('.', Key)-1) 
      END = '1'

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[busterkarmul]

Thank you for your help.