"Object '%' is not a sequence object" error

Question

I'm trying to generate new number with these codes.

CREATE SEQUENCE seq_fisnumarasi  
    START WITH 1  
    INCREMENT BY 1 ;  
GO

First of all, I 've created a sequence.

CREATE PROCEDURE next_fis_number 
AS 
BEGIN
    SELECT NEXT VALUE FOR seq_fisnumarasi
END

Then created this procedure.

public int GetFisNumber()
    {
        var rawQuery = Database.SqlQuery<int>("SELECT NEXT VALUE FOR next_fis_number;");
        var task = rawQuery.SingleAsync();
        int nextVal = task.Result;

        return nextVal;
    }

Added this to my Entity Framework Context.

txtFisNumber.Text = context.GetFisNumber().ToString();

Finally, called the "GetFisNumber()" to my form.

But, I got an error. Here: "Inner Exception 1: SqlException: Object 'next_fis_number' is not a sequence object."

Answer 1

As per my comments, you don't need an SP, and you're using the wrong data type (as you declared your SEQUENCE as a bigint). Just use SELECT NEXT against the SEQUENCE object, and declare your function as an int64, not an int32:

public int64 GetFisNumber()
    {
        var rawQuery = Database.SqlQuery<int64>("SELECT NEXT VALUE FOR seq_fisnumarasi ;");
        var task = rawQuery.SingleAsync();
        int64 nextVal = task.Result;

        return nextVal;
    }

Note if you have wrongly declared the datatype elsewhere in your c# as an int32, you will need to change those to an int64 as well.

If you want your SEQUENCE to be an int, not a bigint, then define the data type when you CREATE it:

CREATE SEQUENCE seq_fisnumarasi AS int
    START WITH 1  
    INCREMENT BY 1;  

From CREATE SEQUENCE:

A sequence can be defined as any integer type. The following types are allowed.

• tinyint - Range 0 to 255
  
• smallint - Range -32,768 to 32,767
  
• int - Range -2,147,483,648 to 2,147,483,647
  
• bigint - Range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
  
• decimal and numeric with a scale of 0.
  
• Any user-defined data type (alias type) that is based on one of the allowed types.

If no data type is provided, the bigint data type is used as the default.

Emphasis added to the last line. This is why your SEQUENCE is a bigint, as you omitted the data type.