The conversion of the varchar value '1111224080294' overflowed an int column 1

[bharons]

Dear Expert..
I don't know if I post this question in the VFP forum or the SQl Server forum.
because I use vfp 8 with Sql Database back end, so I asked in this forum...
I have code like this below:

local lcValue, lcValue1
lcValue  = thisform.text1.value               && No Pengajuan
lcValue1 = trim(thisform.text3.value)        && No. Rekening
TEXT TO m.lcSQL TEXTMERGE noshow
    UPDATE dbo.jaminan
    SET norekening = ?m.lcValue1
    WHERE trxid = ?m.lcValue1
ENDTEXT
lnResult = SQLExec(Thisform.nHandle, m.lcSQL) 
If m.lnResult < 0
    Aerror(aWhy)
    Messagebox(aWhy[2])
    SQLDisconnect(0)
    Return .F.
Else
messagebox("Data Has Been Updated..!!")
endif

and I got an error message namely, "The conversion of the varchar value '1111224080294' overflowed an int column".
Is there something wrong with the coding or the wrong datatype in my SQL Server database?
As for the SQL Server database (dbo.jaminan) consisting of many columns, I updated the "norekening" column with a decimal datatype, maximum length 9.
Here I ask for advice and how to solve my problem.

 

[Chris Miller]

How do you initialize your textboxes text1 and text3? I'll guess not at all, you have empty textboxes, right?
Then no matter if you enter letter or only digits like 1111224080294.

It will still be a character value. SQL Server is so kind to interpret the string '1111224080294' to a number, when you compare it to a numeric table column, but as you say you defined it as maximum 9 length decimal, how could it contain the number 1111224080294? That's 13 digits, 4 too many. Of course you get an overflow error. You tried to compare a column of the SQL Server table that can store up to 9 digits with a 13 digit number. That'll never be equal, but even more so the large number you tried to compare overflows.

The data types of SQL parameters are important and should at best match the SQL Server tables column data types. So you'll have to ensure that whatever variable is used for a parameter of a numeric field is numeric, not text.

 

[Mike Lewis]

The error message comes from SQL Server, so it is clearly a SQL Server issue rather than a VFP one.

That said, it is easily solved. In SQL Server, the maximum value of an int column is approx. 2 billion. The value 1111224080294 is clearly more than that. The solution is to change the data type of the relevant field (norekening) to a bigint, which can go up to 9 x 10^18. That should be plenty big enough.

Mike

 

[mJindrova]

Dear Expert..
I don't know if I post this question in the VFP forum or the SQl Server forum.
because I use vfp 8 with Sql Database back end, so I asked in this forum...
I have code like this below:

local lcValue, lcValue1
lcValue  = thisform.text1.value               && No Pengajuan
lcValue1 = trim(thisform.text3.value)        && No. Rekening
TEXT TO m.lcSQL TEXTMERGE noshow
    UPDATE dbo.jaminan
    SET norekening = ?m.lcValue1
    WHERE trxid = ?m.lcValue1
ENDTEXT
lnResult = SQLExec(Thisform.nHandle, m.lcSQL)
If m.lnResult < 0
    Aerror(aWhy)
    Messagebox(aWhy[2])
    SQLDisconnect(0)
    Return .F.
Else
messagebox("Data Has Been Updated..!!")
endif

and I got an error message namely, "The conversion of the varchar value '1111224080294' overflowed an int column".
Is there something wrong with the coding or the wrong datatype in my SQL Server database?
As for the SQL Server database (dbo.jaminan) consisting of many columns, I updated the "norekening" column with a decimal datatype, maximum length 9.
Here I ask for advice and how to solve my problem.

https://learn.microsoft.com/en-us/s...int-and-tinyint-transact-sql?view=sql-server-
Data type Range Range expression Storage
bigint -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 -2^63 to 2^63-1 8 bytes
int -2,147,483,648 to 2,147,483,647 -2^31 to 2^31-1 4 bytes
smallint -32,768 to 32,767 -2^15 to 2^15-1 2 bytes
tinyint 0 to 255 2^0-1 to 2^8-1 1 byte

 

[gbaughma]

Converting the column to a bigint would fix the database issue, as @Mike Lewis said.
I hope that you're running some sort of validation on your input, however, or someone typing a letter into your text box will break your code if it's trying to store it into an INT.
*ALWAYS* validate user input. Twice. With a microscope. And, it's much safer to use Stored Procedures.
Right now your code says "Here's a text variable. Get whatever the user types into that text variable, and put whatever they type (Text, numbers, SQL Injection...) into the database.
Not to be insulting, but very sloppy, and potentially dangerous.

 

[bharons]

Converting the column to a bigint would fix the database issue, as @Mike Lewis said.
I hope that you're running some sort of validation on your input, however, or someone typing a letter into your text box will break your code if it's trying to store it into an INT.
*ALWAYS* validate user input. Twice. With a microscope. And, it's much safer to use Stored Procedures.
Right now your code says "Here's a text variable. Get whatever the user types into that text variable, and put whatever they type (Text, numbers, SQL Injection...) into the database.
Not to be insulting, but very sloppy, and potentially dangerous.

are this coding fine to use :

local lcValue, lcValue1, lcValue2, lcValue3, lcKeterangan
lcValue  = thisform.text1.value           && No Pengajuan
lcValue1 = trim(thisform.text3.value)    && No. Rekening
TEXT TO m.lcSQL TEXTMERGE noshow
    UPDATE     dbo.jaminan
    SET     norekening = cast(?m.lcValue1 as bigint)
    WHERE     trxid = ?m.lcValue
ENDTEXT
set step on
lnResult = SQLExec(Thisform.nHandle, m.lcSQL)
If m.lnResult < 0
    Aerror(aWhy)
    Messagebox(aWhy[2])
    SQLDisconnect(0)
    Return .F.
Else
messagebox("Data Telah Update..!!")
endif

i try this code and give me result as i want ??
maybe i need more explain ??
before i used this code as suggested by Mr. Mike and Mr. Mjindrova, i try this code first to detect what kind data type is

Use In (Select('crsResult6'))
TEXT TO m.lcSQL TEXTMERGE noshow
    SELECT COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'jaminan' AND COLUMN_NAME = 'norekening'
ENDTEXT
lnResult = SQLExec(Thisform.nHandle, m.lcSQL ,'crsResult6')
If m.lnResult < 0
    Aerror(aWhy)
    Messagebox(aWhy[2])
    SQLDisconnect(0)
    Return .F.
Else
select crsResult6
browse
endif

give me information that my column name is norekening, decimal datatype also numeric_precision at 14 ??
really works as I expected, in this chase i wanna change norekening. i.e originally norekening is 1111124080294 became 1111224080294, but i need more suggestion if i use this code are right or wrong

 

[gbaughma]

Someone who is more adept at FoxPro should answer this...
I'm just pointing out that "best practice" is to validate user input before ever writing it to the database.
You can validate in a few ways:
Making sure that you are using the proper field type. If it's a number, use a number field type, not a text field type. That will keep people from typing text into the field.
Check it using code... is it a valid number? A number that you were expecting? The correct amount of digits?
Insert it into your database using a stored procedure on SQL, instead of hard coded in your application. A stored procedure makes sure that if someone enters something nefarious (such as SQL injection), the value of what they entered will be put into the database, and not the pseudo-command they're trying to insert.

 

[Mike Lewis]

No. I didn't say convert the data to bigint. I said change the data type to bigint. This is a one-off design-time action. In other words, you do it within SQL server itself. Once you have done that, your original code should work fine.

Mike

 

[bharons]

thanks, mr. Mike. with out any cast function it work properly.. i mean by my code that i store set value to ?m.lcValue, i store cast function are fine to use or not.

 

[Chris Miller]

i mean by my code that i store set value to ?m.lcValue, i store cast function are fine to use or not.

What? (that English makes no sense to me, be so kind and rephrase it)

Do you refer to what I said?

The data types of SQL parameters are important and should at best match the SQL Server tables column data types.

I say "at best", because VFP data types don't cover all data types SQL Serrver knows and vice versa. But you can't pass in a string and store it into a numeric column. Prameters are passed in paralle to the SQL query code and arrive with the closest possible data type, so for example varchar for string values passed in, that's where the SQL Server error message comes from, so the problem indeed already srarts on the VFP side as you pass in a string instead of a number.

VFP can do 14 digit numbers, but indeed not naturally and not necessarily as precise as you need them. I would actually argue the problem you have is that you can't really provide the correct value and instead should change the SQL Server column to char(14), if it should be able to contain 14 digits, even if it's always and only digits and thus a numeric format.

VFP can have N(14) but will always have numeric variables in data type double float, not N(14). To have lcValue as a double float type, at least, for best dat type fit, the VFP textbox for entering it has to be 0 initially, or any number, not empty. CASTing within the SQL command does only change the variable type after passing it in, might be feasable, but is not what I suggested.

A review number (no rekening means that I guess) is usually just an identifier, isn't it? You don't sum review numbers to some total or anything like that? So effectively they are just identifiers and thus do have the nature of strings, not numbers. Use a char(14) field to store them and you also don't have a problem.

Besides, didn't you at first mention 9 digits? Anyway, let it be 15, 30, 100, no matter, if you store that as a chars you'll not have that problem with strings that you have with a numeric column, So change it to char(N) whatever number of digits N you need would be my final advice.

 

[Chris Miller]

originally norekening is 1111124080294 became 1111224080294

That's 13 places and would neither fit int nor your description:

I updated the "norekening" column with a decimal datatype, maximum length 9

So either it already was bigint and the SQL Server error message still only speaks of int or... I don't know.
You can easily find out what data type a table column is in the SQL Server management studio, you likely don't know better, but it would be good you learn how to look into a tables structure with SSMS: https://learn.microsoft.com/en-us/s...ew-the-table-definition?view=sql-server-ver16

 

[bharons]

That's 13 places and would neither fit int nor your description:

So either it already was bigint and the SQL Server error message still only speaks of int or... I don't know.
You can easily find out what data type a table column is in the SQL Server management studio, you likely don't know better, but it would be good you learn how to look into a tables structure with SSMS: https://learn.microsoft.com/en-us/s...ew-the-table-definition?view=sql-server-ver16

to detect my column at my table i used this coding :

Use In (Select('crsResult13'))
TEXT TO m.lcSQL1 TEXTMERGE noshow
    SELECT COL.name AS ColumnName, TYP.name AS DataTypeName,
    COL.precision, COL.scale,
    COL.is_nullable, COL.is_identity,   
    TYP.max_length AS MaxLength, TAB.name AS TableName,
    TAB.object_id AS ObjectID
    From sys.columns COL
    INNER JOIN sys.tables TAB
    On COL.object_id = TAB.object_id
    INNER JOIN sys.types TYP
    ON TYP.user_type_id = COL.user_type_id
ENDTEXT
lnResult = SQLExec(Thisform.nHandle, m.lcSQL1 ,'crsResult13')

it is give me precision and scale about my decimal datatype..
and finally it work i change te data is, even without cast() to BigInt, my data can be change..
actually i got some wrong statement at my coding before, and give me error cannot convert varchar to int overflowed, at lcValue1.., sorry my mistake..

my first code :

local lcValue, lcValue1
lcValue  = thisform.text1.value               && No Pengajuan
lcValue1 = trim(thisform.text3.value)        && No. Rekening
TEXT TO m.lcSQL TEXTMERGE noshow
    UPDATE dbo.jaminan
    SET norekening = ?m.lcValue1
    WHERE trxid = ?m.lcValue1
ENDTEXT
lnResult = SQLExec(Thisform.nHandle, m.lcSQL)
If m.lnResult < 0
    Aerror(aWhy)
    Messagebox(aWhy[2])
    SQLDisconnect(0)
    Return .F.
Else
messagebox("Data Has Been Updated..!!")
endif

between set properties and where clause are same data.., it is must be set = lcValue where = lcValue1...

 

[Chris Miller]

Fine that you can query sys.columns. I (we) would like to see the outcome of that.
Anyway, you can't fit 13 digits into a decimal(9) column, so itcan't be correct that decimal(9) was the column definition of a column that had stored 1111124080294 and needed to be updated to 1111224080294. It wasn't decimsl(9), was it?

If it was bigint, VFP can't cope with bigints.

I do realize you solved the problem and the bigger one was using the same value variable. Well, that's a general problem of not naming variables, textboxes and any control more properly as you can easily copy&paste where you need different names, and can be too sure to know which textbox contains what value for which assignment or comparison in your statement, besides not knowing what you would need to know to even phrase the question and give enough and correct information to somebody who should answer the question.

This is not just about virtues, this is about self documentation of code making errors much more apparent.