feed sequence.NEXTVAL into InsertParameters

Question

User-592976280 posted

I want to insert a record into Oracle database. I want to tell FormField that when it performs insertion it should get the value of sequence.NEXTVAL. How can that be done?

There are many posts related to this issue but there's no solution to it. There's a suggestion to make an additional call to the database and retrieve the MAX value of the sequence field and add one. That might not work because by the time the actual insertion is done that MAX value is not MAX any more because other users might already inserted a bunch of records. Another way to fix the problem is to call a stored procedure and let it get the MAX immediately before the actual insertion. That will work but I thought there might be a better way such as letting FormField takes care of it. I might be wrong but doesn't hurt to explore a different option.

<asp:SqlDataSource ID="SqlDataSource3" runat="server"  
                            ...... 
       SelectCommand="SELECT a.primaryKeyId1, a.primaryKeyId2, a.city, a.stateAbreviation, b.stateDescription, FROM table1 a, lookupTable b WHERE a.id = b.id" 
       InsertCommand="INSERT INTO table1 (primaryKeyId1, primaryKeyId2, city, stateAbreviation) VALUES (:fpPkId1, :fpPkId2, :fpCity :fpStateAbr)"> 
                        <InsertParameters> 
                            <asp:FormParameter Name="fpPkId1" FormField="how to get the sequence.NEXTVAL into here?" /> 
                         </InsertParameters> 
</asp:SqlDataSource>

Answer 1

User269602965 posted

AER2 is the data schema

<asp:SqlDataSource ID="dsCONTRACTS" runat="server" 
  ConnectionString="<%$ ConnectionStrings:AuthenticatedOracleConnectionString %>" 
  ProviderName="<%$ ConnectionStrings:AuthenticatedOracleConnectionString.ProviderName %>"
  SelectCommand="SELECT
                   CONTRACT_SEQ,
                   DATE_START_CONTRACT,
                   DATE_END_CONTRACT,
                 FROM AER2.VW_SERVICE_CONTRACTS
                 ORDER BY DATE_END_CONTRACT DESC"
  UpdateCommand="UPDATE AER2.SERVICE_CONTRACTS
                 SET
                   DATE_START_CONTRACT         = :DATE_START_CONTRACT,
                   DATE_END_CONTRACT           = :DATE_END_CONTRACT,
                 WHERE CONTRACT_SEQ            = :CONTRACT_SEQ"
  InsertCommand="INSERT INTO AER2.SERVICE_CONTRACTS
                 (CONTRACT_SEQ,  DATE_START_CONTRACT, DATE_END_CONTRACT)
                  VALUES
                 (AER2.CONTRACT_SEQ.NEXTVAL,  :DATE_START_CONTRACT, :DATE_END_CONTRACT)">
  <SelectParameters>
  </SelectParameters>
  <UpdateParameters>
    <asp:Parameter Name="CONTRACT_SEQ" Type="Decimal" />
    <asp:Parameter Name="DATE_START_CONTRACT" Type="DateTime" />
    <asp:Parameter Name="DATE_END_CONTRACT" Type="DateTime" />
  </UpdateParameters>
  <InsertParameters>
    <asp:Parameter Name="DATE_START_CONTRACT" Type="DateTime" />
    <asp:Parameter Name="DATE_END_CONTRACT" Type="DateTime" />
  </InsertParameters>
</asp:SqlDataSource>

Answer 2

User-592976280 posted

I try that with dbname.instanceName.CONTRACT_SEQ.NEXTVAL and it gives error "Exception Details: System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number".  I also try with instanceName.CONTRACT_SEQ.NEXTVAL and also with CONTRACT_SEQ.NEXTVAL. Nore of them fixed the error.

If I replace dbname.instanceName.CONTRACT_SEQ.NEXTVAL with :pk1 and pk1 to the <InsertParameters> as below then there's no error.

<asp:Parameter Name="pk1" Type="Int32" />

With that I can't insert with the NEXTVAL.

Answer 3

User269602965 posted

Use in connection string

providerName="Oracle.DataAccess.Client"

it is faster and System. Data.OracleClient is deprecated (declining support and innovation) in ASP.NET 4.0.

Check your datatype in the table.

Oracle Sequence should be a NUMBER(12,0) data type (Oracle integer) on the column you are inserting into.

 

CREATE SEQUENCE MYKEY_SEQ
START WITH     1000
INCREMENT BY   1
NOCACHE
NOCYCLE;

CREATE TABLE MySchema.MyTable

(MyKey_SEQ NUMBER(12,0),

MyDataString VARCHAR2(32));

INSERT INTO MySchema.MyTable (MyKey_SEQ, MyDataString) VALUES (MySchema.MYKEY_SEQ.NEXTVAL, 'Mydata string');

COMMIT;

You also need to GRANT SELECT ON MYKEY_SEQ TO app_connection_account_user,

or it will not return a NEXVAL.

 

 

 

 

 

 

Answer 4

User-592976280 posted

The table, schema, sequence... are all working fine. It's just not working when feeding sequence.nextvale into InsertParameters.

Answer 5

User-592976280 posted

Any suggestions please?

Answer 6

User269602965 posted

The schema.seq.nextval is an SQL function

and does not need to be an INSERT PARAMETER

it needs to be part of the INSERT statement only.

 

INSERT INTO SCHEMA.TABLE (MyKey) VALUES (SCHEMA.MySEQ.NEXTVAL);

Answer 7

User-592976280 posted

It's easy when using INSERT statement in code behind, but that's not the case here. I'm using InsertCommand.

InsertCommand="INSERT INTO table1 (primaryKeyId1, primaryKeyId2, city, stateAbreviation) VALUES (SCHEMA.MySEQ.NEXTVAL, :fpPkId2, :fpCity :fpStateAbr)">


And it doesn't like SCHEMA.MySEQ.NEXTVAL

Answer 8

User269602965 posted

InsertCommand="INSERT INTO AER2.SERVICE_CONTRACTS
                 (CONTRACT_SEQ,  DATE_START_CONTRACT, DATE_END_CONTRACT)
                  VALUES
                 (AER2.CONTRACT_SEQ.NEXTVAL,  :DATE_START_CONTRACT, :DATE_END_CONTRACT)">

Use INSERT INTO {SCHEMANAME}.table1 etc.

and the user doing the INSERT must be granted SELECT on SCHEMA.MySeq sequence object

+++++++++++++++

Sometimes oracle gives error pointing to one thing when it is another in the statement.

So far, I see three :BIND variables , but are there three Insert parameters that are exactly in the same order as they appear in the insert statement,

and of the correct datatype acceptable to Oracle.

 

 

Answer 9

User-592976280 posted

and the user doing the INSERT must be granted SELECT on SCHEMA.MySeq sequence object

There's no problem with privilege because I tested it out other ways and it has no problem with insert. Other ways are: use code behind and AER2.CONTRACT_SEQ.NEXTVAL works fine; or use bind variable in InsertCommand and use default value for nextval.

I strongly believe that the problem has to do with InsertCommand doesn't like AER2.CONTRACT_SEQ.NEXTVAL; it likes bind variable or default value.

Answer 10

User269602965 posted

Try one more thing

sometimes when I am having trouble with ASP.NET page SQL,

I erase the line, retype, and recompile.

I use NEXTVAL within ASP.NET page SQL INSERTCOMMAND often with no problems.

Are you in web.config use ODP.NET  Oracle.DataAccess or the deprecated System.Data.OracleClient?

 

 

Answer 11

User-592976280 posted

I use NEXTVAL within ASP.NET page SQL INSERTCOMMAND often with no problems.

Can you provide your code that works? I guess it works without you having to do anything in code behind right?

Are you in web.config use ODP.NET  Oracle.DataAccess or the deprecated System.Data.OracleClient?

I use Oracle.DataAccess

Answer 12

User269602965 posted

My first reply in this string has code copied directly from a real world working application using ASP.NET page level SQL INSERT COMMAND

with grabbing NEXTVAL from an Oracle SEQUENCE object for populating my Table Primary Key.

OTher than user notification of the successful insert in my notification label, I do not have any SQL in the INSERTCOMMAND code behind page because the inserting is done by the SQL PAge InsertCommand section.

I will say it again.  Sometimes when an SQLSource section of an ASP.NET page is driving me CRAZY.

I erase the whole stinking thing, retype, recompile, and viola, it starts working.

 

Answer 13

User-592976280 posted

still not working. Thank you any way.

Answer 14

User269602965 posted

Alright, let's go back to basics

Show the SQL statement you used to create your SEQUENCE object.

In my example, CONTRACT_SEQ is the name of my Oracle SEQUENCE object... not your SEQUENCE object.

CONTRACT_SEQ is also the name of my Primary Key in the CONTRACTS table.

Do not confuse the two.

I created my SEQUENCE object and granted SELECT privileges to the ORACLE ROLE

controlling the priviledges of my Application Oracle connection user.

Application data schema is AER2

CREATE SEQUENCE AER2.CONTRACT_SEQ
  START WITH 1
  MAXVALUE 1024000
  MINVALUE 0
  NOCYCLE
  NOCACHE
  NOORDER;

GRANT SELECT ON AER2.CONTRACT_SEQ TO AER2_VIEWER;

+++++++++++++++

The table using the Oracle SEQUENCE object called CONTRACT_SEQ is:

CREATE TABLE AER2.SERVICE_CONTRACTS
(
  CONTRACT_SEQ                 NUMBER(12),
  DATE_START_CONTRACT          DATE,
  DATE_END_CONTRACT            DATE,
  AMOUNT_ANNUAL_CONTRACT_THEN  NUMBER(12,2),
  STATUS_CONTRACT              VARCHAR2(16 BYTE),
  DATE_PAID                    DATE
);

++++++++++++++

Then using the INSERT COMMAND in my first response,
when INSERTING a new row,
I can increment the Oracle SEQUENCE object called CONTRACT_SEQ
by 1 unit using VALUES (AER2.CONTRACT_SEQ.NEXTVAL, etc.)