Trouble w/ inserting into ORACLE table with autonumber field

olmos · Nov 10, 2000

hi all,

How do I insert a name into a ORACLE table with an autonumber field. My error is cannot insert null into ( " &quot

, error code 1400 . fn_id is the autonumber field and also the sequence name.

I tried using the sequence name in the query like this
VALUES ( fn_id.nextval, '#Form.NAME#' ) , and putting NotNull also in there, and that didn't work either . Can anyone help me with this, it is probably very simple but I haven't figured it out. The code is below.

Thanks,
olmos
--------------------------------------------
Data entry form code for name to be inserted ---
----------------------------------------------
<form name="data_entryform1.cfm" action="action2.cfm" method="POST">

<INPUT TYPE=hidden NAME="fn_id" VALUE= "fn_id>
<input type=text size=50 maxlength=70 name="name" value = "name " >

<input type=SUBMIT>

---------------------------------------
2nd file code for action-
action2.cfm
-----------------------------------
<CFQUERY NAME="AddFN" DATASOURCE="db" dbtype="Oracle80">

INSERT INTO scott.tablename

(NAME)

VALUES ('#Form.NAME#' )

</CFQUERY>

<cfoutput query = "AddFN">

<html>

<body>

The following has been submitted.

#Form.NAME#

</body>

</html>

</cfoutput>

Jami · Nov 13, 2000

Hi olmos,
Your ColdFusion insert is correct. You should only have to list your fields names that are being inserted and will not have to reference your sequence.

You might then want to be sure your autonumbering in Oracle is correct. In addition to needing a sequence, you will also need a trigger. Here is an example of the SQL syntax I use to create these. The trigger lets the database know that anytime an insert is made to this table, Oracle must reference your sequence to grab the next number and then populate the autonumber field. Because you are getting a "null" error, it seems a trigger is not being used here. Try this:

SQL> create sequence seq_name start with 1;

Sequence created.

SQL> create or replace trigger trigger_name
2 before insert on table_name
3 for each row
4 begin
5 select seq_name.nextval
6 into :new.autonumber_field
7 from dual;
8 end;
9 /

Trigger created.

Any insert into the table will prompt this trigger. This trigger will call your sequence, get the next value and isert it into the appropriate field (here the field is called autonumber_field). Hope this helps!

olmos · Nov 14, 2000

Thanks so much for your help Jami. I created the trigger for the sequence and also did it for my other tables that were using sequences and needed inserting. Thanks for your help, It worked!

olmos

jkellow · Nov 15, 2000

I too am using auto number from a sequence. I am including
my code, see if it help you.
Jim Kellow (jkellow@stemco.bfg.com)
<CFif findcustheader.recordcount LT "1">
<CFQUERY name="Getorderseq" DATASOURCE="#DSN#">
select #DBowner#.webseq.nextval as webseq1 from dual
</cfquery>

<CFLOCK TIMEOUT="60" SCOPE="APPLICATION" TYPE="Exclusive">
<cfset temp = toString(#NumberFormat(Getorderseq.webseq1,'L______')# )>
<cfset webp['orderseq'] = LTrim(RTrim(temp))>
</CFLOCK>
<CFQUERY name="Insertcustheader" DATASOURCE="#DSN#">
insert into #DBowner#.CustHeader
values
('#StructFind(webp,'orderseq')#','#StructFind(webp,'sessionid')#',sysdate,'I',
'#StructFind(webp,'userid')#','','#StructFind(webp,'email')#','','','','','','','','#StructFind(webp,'custpo')#','')
</CFQUERY>
<cfelse>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Trouble w/ inserting into ORACLE table with autonumber field

olmos

Technical User

Jami

Programmer

olmos

Technical User

jkellow

Programmer

Similar threads

Part and Inventory Search

Sponsor