problem in inserting values to userfields

[Former Member]

hi

i am facing problem when i am inserting values in to ueser fields . for inserting code and name i am auto incrementing those values . up to 10 records i am getting fine but the value is not incrementing to 11 and i am not getting any error or exception. i am unable to understand the problem . is there any configuration for that can anyone help me regarding this concept .if any configuration how to do that . i am accessing B1 database using DIAPI.

regards

om prakash

[Former Member · Accepted solution]

Hi Om Prakesh,

The problem you are having is that the code is a string field which SQL reads like an alpha. If you have codes from 1 to 10, it reckons the maximum is 9! Because it sorts it as follows: 1,10,2,3,4,5,6,7,8,9. So this query

select max(code) + 1 from [@mytable]

will return 10, even if 10 is already there. you need to convert your code to a numeric when running the query, like:

select max(convert(int, code)) + 1 from [@mytable]

Hope it helps,

Adele