'Error Executing Database Query. The conversion of the varchar value '10298866175' overflowed an int column

I'm getting this error:

  Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]The conversion of the varchar value '10298866175' overflowed an int column.

The FileSize field where the error is occurring is actually an INT field in the SQL Server database.

Unlike a lot of people who posted similar issue, for me this is occurring in an INSERT statement.

    INSERT INTO FileTransfer (FileID, Sender, SenderEmail, SenderName, UploadDate, IPAddress, Domain, ClientFileName, ClientFileExt, ContentType, ContentSubType, ServerFile, FileSize, UploadID)

    VALUES (656, 18182, '[email protected]', 'Jack Martini', CONVERT(DATETIME, '2021-11-06 12:50:13', 102), '126.14.24.43, 172.17', '', 'General', 'zip', 'application', 'octet-stream', '18182_91f1518a-caca-4bbb-91f9-5edcc1526d0c.dat', '10298866175', 366)

There is some CFSCRIPT that gets the variables, but this is the actual query pre execution in the Coldfusion code:

    <cfquery datasource="#Application.Datasource#" name="AddFile">
    INSERT INTO FileTransfer
    (FileID, Sender, SenderEmail, SenderName, UploadDate, IPAddress, Domain, ClientFileName, ClientFileExt, ContentType, ContentSubType, ServerFile, FileSize, UploadID)
    VALUES (#Variables.FileID#, <cfif IsDefined("Session.UserNUM") and Session.UserNUM gt 0>#Session.UserNUM#<cfelse>NULL</cfif>,
            <cfif Form.SenderEmail is "">NULL<cfelse>'#Form.SenderEmail#'</cfif>,
            <cfif Form.Name is "">NULL<cfelse>'#Form.Name#'</cfif>,
            #UploadDate#,
            '#Trim(Left(session.userIP,20))#',
            '#session.userDomain#',
            <cfif variables.ClientFileName is "">NULL<cfelse>'#variables.ClientFileName#'</cfif>,
            <cfif variables.ClientFileExt is "">NULL<cfelse>'#variables.ClientFileExt#'</cfif>,
            <cfif variables.ContentType is "">NULL<cfelse>'#variables.ContentType#'</cfif>,
            <cfif variables.ContentSubType is "">NULL<cfelse>'#variables.ContentSubType#'</cfif>,
            <cfif variables.ServerFile is "">NULL<cfelse>'#variables.ServerFile#'</cfif>,
            <cfif variables.FileSize is "">NULL<cfelse>'#variables.FileSize#'</cfif>,
            #getUploadID.ThisUploadID#);
</cfquery>

Changing the data type in the database is not an option. So how would I fix this in Coldfusion or SQL?

sqlsql-servercoldfusion


Solution 1:[1]

It's naturally not possible to store a value greater than the memory it has. INT is a 4 byte signed value. If you insist on having an int as datatype you are restricting and applying a constraint for the value to fall in a range from -2,147,483,647 to 2,147,483,647

Now to avoid the exception:

  1. You can change the type of the field from int to bigint
  2. You can apply validation to pass value that fall in the given range

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Bilal Bin Zia

Related Questions