assign oracle sequence number to parameter value

[AzizKamal]

In asp.net, I have an insert sql command. One of the parameter in the insert command is oracle sequence number.

Currently, my sql command is:

sql.CommandText = "insert into gas.transpmtvchr values ("
sql.CommandText = sql.CommandText & ":vchrno,:account,:amount,:narration," & "gas.sno.nextval)"

gas.sno.nextval gives the next sequence number of sequence SNO. I tried to make this field too a parameter field but it did not work.

I changed the sql command to:

sql.CommandText = "insert into gas.transpmtvchr values ("
sql.CommandText = sql.CommandText & ":vchrno,:account,:amount,:narration,:entryid)"

Dim EntryidParam As OracleParameter = New OracleParameter
EntryidParam.ParameterName = ":entryid"
EntryidParam.OracleType = OracleType.Number
EntryidParam.Direction = ParameterDirection.Input
EntryidParam.Value = "gas.sno.nextval"
sql.Parameters.Add(EntryidParam)

When the program executes the following line:

sql.ExecuteNonQuery()

it goes to exception block with exception message:
Failed to convert parameter value from a String to a Decimal.

gas.sno.nextval is a string but I have to write it this way to generate the next sequence number

 

[taree]

why do you want to make this field a parameter. since it is a sequence number, it will increament automatically when you insert a value into the database.

 

[AzizKamal]

Thanks.

I need to make this field a parameter because I need to specify which sequence (out of many defined in oracle) I will be using. nextval statement needs to be used in insert statement prefixed with the name of the sequence. I cannot write:

insert into gas.transpmtvchr (vchrno,account,amount,narration) values (:vchrno,:account,:amount,:narration)

In this case, entryid column will not get the next sequence number automatically.

For example, in oracle, I can define a sequence object called seq1 and another sequence object called seq2.

CREATE SEQUENCE seq1
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    NOCACHE; 

CREATE SEQUENCE seq2
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

Let’s say both seq1 and seq2 start with 1. Last used number for seq1 is 10 and for seq2 is 20. Now if I write gas.seq1.nextval in insert statement, it will insert 11 to entryid column (datatype in oracle is Number) for that row. If I write gas.seq2.nextval in insert statement, it will insert 21 to entryid column for that row.

Also, by default, insert statements assign parameters for each column in asp.net and an autogenerated insert statement would be:

insert into gas.transpmtvchr values (:vchrno,:account,:amount,:narration,:entryid)

and then there needs to be some way to assign value to :entryid to make the code clean.