Error : Conversion failed when converting the nvarchar value 'C2110' to data type int.

[Former Member]

Hi,

Need your help to resolve the following error

I am using  B1 8.81 PL 07 version

I am facing  an error

1). [Microsoft][SQL Server Native Client 10.0][SQLServer]Conversion failed when converting the nvarchar value 'C2110' to datatype int. 'Bank Charges Allocation Codes' (OBCA)

while click on Code to generate new code to add BP

There is query for AutoGenerte code

Thanks

[pvsbprasad]

Hi,

The  error is due to "LEN(((MAX(RIGHT(CARDCODE,5))+1)))" while converting instead use 1

LEN(((MAX(RIGHT(CARDCODE,5)))))+1

at last and

ELSE 'C00001' in nvachar but "LEN(((MAX(RIGHT(CARDCODE,5))+1))) in integer

Regards,

Prasad

[Former Member]

Hi,

Your code may only work if all the right most 5 characters of BP code are numbers. If your example is correct, you need change 5 to 4 to get right result.

Thanks,

Gordon

[Former Member]

Hi

Thanks for your reply

The problem is that somebody entered the code manually.
on that customer the invoice is done.

so what to do now?

[former_member204969]

As I have already written,

you should change in the code

CardCode not like 'C%' to CardCode like 'V[0-9][0-9][0-9][0-9] [0-9]'

and

CardCode not like 'V%' to CardCode like 'C[0-9][0-9][0-9][0-9] [0-9]'

to exclude the manually entered codes.

[pvsbprasad]

Hi Ketaki,

one of the filed datatype is "int ", while converting it throws this error

use

cast(Fieldname as varchar)

or

post the query

Regards,

Prasad

[Former Member]

hi,

the query is

if ($[$40.0.0]='S')  and ($[$1.0.0]='Add')

SELECT CASE WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=1 THEN 'V00000'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=2 THEN 'V0000'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=3 THEN 'V000'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=4 THEN 'V00'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=5 THEN 'V0'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=6 THEN 'V'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
ELSE 'V00001' END AS CardCode FROM ocrd Where CardType='S' and  CardCode not like 'C%'

if ($[$40.0.0]='C')  and ($[$1.0.0]='Add')
SELECT CASE WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=1 THEN 'C00000'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=2 THEN 'C0000'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=3 THEN 'C000'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=4 THEN 'C00'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=5 THEN 'C0'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=6 THEN 'C'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
ELSE 'C00001' END AS CardCode FROM ocrd Where CardType='C' and CardCode not like 'V%'

if ($[$40.0.0]='C') and ($[$1.0.0]='Update')
SELECT CASE WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=1 THEN 'C00000'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=2 THEN 'C0000'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=3 THEN 'C000'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=4 THEN 'C00'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=5 THEN 'C0'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
WHEN LEN(((MAX(RIGHT(CARDCODE,5))+1)))=6 THEN 'C'+CONVERT(VARCHAR,(MAX(RIGHT(CARDCODE,5))+1))
ELSE 'C00001' END AS CardCode FROM ocrd Where CardType='C' and CardCode not like 'V%'

here we added many BP but this type of error had nt come before.

[Former Member]

Dear ketaki,

can u please post the customer code of first BP and last BP

and vendor code of first BP and last bp?

[former_member204969]

Your code generates 6 character partner codes and in the data base there is (at least) one with 5 characters: C2110.  It probably entered manually.

You should change in the code

CardCode not like 'C%' to CardCode like 'V[0-9][0-9][0-9][0-9] [0-9]'

and

CardCode not like 'V%' to CardCode like 'C[0-9][0-9][0-9][0-9] [0-9]' to exclude the manually entered different form codes.