Gibraltar Support

Start a new topic

Value was either too large or too small for an Int32.

I have a .NET Core 3.1 app. Using the latest release of VistaDB. I'm getting an error and can't figure out what I'm doing wrong. I'm inserting a string into the database via a stored procedure.


 

CREATE PROCEDURE [INSERT_Interval]
@name VarChar,
    @id BigInt OUT
AS
BEGIN
INSERT INTO Intervals ([Name]) VALUES (@name);

SET @id = SCOPE_IDENTITY();

RETURN @id;
END

 The table structure is ID bigint and Name Varchar(100). 


 

        public Interval Create(Interval input)
        {
            using var conn = new VistaDBConnection { ConnectionString = ConnectionString };
            using var comm = conn.CreateCommand();

            comm.CommandText = @"INSERT_Interval";
            comm.CommandType = CommandType.StoredProcedure;

            var nameParameter = comm.CreateParameter();
            var idParameter = comm.CreateParameter();

            idParameter.ParameterName = @"@id";
            idParameter.DbType = DbType.Int64;
            idParameter.Direction = ParameterDirection.Output;

            nameParameter.ParameterName = @"@name";
            nameParameter.DbType = DbType.String;
            nameParameter.Value = input.IntervalName;

            comm.Parameters.Add(idParameter);
            comm.Parameters.Add(nameParameter);

            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            var output = comm.ExecuteNonQuery();

            var objId = comm.Parameters[@"@id"].Value;

            if (conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }

            long.TryParse(objId?.ToString(), out var id);

            input.Id = id;

            return input;
        }

  

        [Test]
        public void InsertIntervalUnitTest()
        {
            var provider = new IntervalProvider(_connectionString);
            var interval = new Interval { IntervalName = @"Test" };

            interval = provider.Create(interval);

            Assert.IsInstanceOf(typeof(Interval), interval);
            Assert.IsTrue(interval.Id != 0L);

        }

  

System.OverflowException
  HResult=0x80131516
  Message=Value was either too large or too small for an Int32.
  Source=System.Private.CoreLib
  StackTrace:
   at System.Convert.ThrowInt32OverflowException()
   at System.Convert.ToInt32(Int64 value)
   at System.Int64.System.IConvertible.ToInt32(IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at System.Convert.ChangeType(Object value, Type conversionType)
   at VistaDB.Engine.Core.IntColumn.set_Value(Object value)
   at VistaDB.Engine.Core.Row.Column.VistaDB.Engine.Internal.IValue.set_Value(Object value)
   at VistaDB.Engine.SQL.Signatures.ScopeIdentityFunction.InternalExecute()
   at VistaDB.Engine.SQL.Signatures.Signature.Execute()
   at VistaDB.Engine.SQL.SetVariableStatement.OnExecuteQuery()
   at VistaDB.Engine.SQL.Statement.ExecuteQuery()
   at VistaDB.Engine.SQL.BatchStatement.NextResult(VistaDBPipe pipe)
   at VistaDB.Provider.VistaDBDataReader.GoNextResult()
   at VistaDB.Provider.VistaDBDataReader..ctor(IQueryStatement statement, VistaDBConnection connection, CommandBehavior commandBehavior)
   at VistaDB.Engine.SQL.BatchStatement.OnExecuteQuery()
   at VistaDB.Engine.SQL.Statement.ExecuteQuery()
   at VistaDB.Engine.SQL.Signatures.StoredProcedure.ExecuteSubProgram()
   at VistaDB.Engine.SQL.Signatures.ProgrammabilitySignature.PrepareExecute(Object& resValue)
   at VistaDB.Engine.SQL.Signatures.ProgrammabilitySignature.InternalExecute()
   at VistaDB.Engine.SQL.Signatures.Signature.Execute()
   at VistaDB.Engine.SQL.ExecStatement.OnExecuteQuery()
   at VistaDB.Engine.SQL.Statement.ExecuteQuery()
   at VistaDB.Engine.SQL.BatchStatement.NextResult(VistaDBPipe pipe)
   at VistaDB.Provider.VistaDBDataReader.ExecQuery(Int64& affectedRows)
   at VistaDB.Provider.VistaDBCommand.ExecQuery(Int64& affectedRows)
   at VistaDB.Provider.VistaDBCommand.ExecuteNonQuery()
   at Budget.Data.IntervalProvider.Create(Interval input) in C:\Users\Ben\source\repos\Budget\Data\IntervalProvider.cs:line 44
   at DataUnitTests.IntervalUnitTests.InsertIntervalUnitTest() in C:\Users\Ben\source\repos\Budget\DataUnitTests\IntervalUnitTests.cs:line 23

  This exception was originally thrown at this call stack:
    [External Code]
    Budget.Data.IntervalProvider.Create(Budget.Dto.Interval) in IntervalProvider.cs
    DataUnitTests.IntervalUnitTests.InsertIntervalUnitTest() in IntervalUnitTests.cs

 


Looking at your PROCEDURE declaration at the top, you have it both using @id as BigInt OUT and returning @id.  Stored Procedures do have a return value (rarely used, I think), but I believe it can only be an Int32 (SQL Int), so returning the declared BigInt parameter may be what is blowing up.  That seems consistent with the exception message, although not necessarily the specific Stack Trace.


It's also possible that Scope_Identity() itself is blowing up expecting only an Int identity.  We'll have to check that code.


Is your identity column declared as BigInt, and is it actually getting to values that exceed the limits of an Int?


    -Rob

We were able to reproduce the error with a unit test extrapolated from this example.  The problem (which also fails with @@IDENTITY) is that the data type of the SCOPE_IDENTITY() function is set in the Prepare() phase, before the row has been inserted, and it is defaulting to Int rather than BigInt when there hasn't been an insertion since the connection was opened.  It then blows up in the Execute() phase when the actual identity value can't convert to an Int.


We'll see about fixing this by changing these to always use BigInt internally, but we'll have to make sure the change doesn't break the more common case of an Int column without anyone needing to change their code.



In the meantime, you can fix your PROCEDURE by using LASTIDENTITY(Id, Intervals)--assuming the column is named Id--instead of SCOPE_IDENTITY().  By specifying the explicit column and table it is able to correctly determine the data type in the Prepare() phase and avoid the internal mismatch.

Login to post a comment