Using sequence numbers

[Jerod]

In access or sql server, you can use autonumber which is automatically generated as soon as an insert statement is invoked.
Does anyone know how I can use this in oracle without the benefit of a stored procedure?
For instance, I need to have a sequence number which not only assigns numbers but also increments whenever a record is created.
Example, I need to do an insert like
strSql = "INSERT INTO mytable(id, name,city,state)"
strSql = strSql & " VALUES ("
strSql = strSql & "'" & id_seq.nextval & "',"
strSql = strSql & "'" & name & "',"
strSql = strSql & "'" & city & "',"
strSql = strSql & "'" & state & "'"
strSql = strSql & &quot"
Notice the id_seq.nextval. It is not working.
The only way I know how to use it in asp is to use a stored procedure but I don't want to use a stored proc because I am having problem with it and I have a short time to complete this task.

 

[cfk]

I havn't used Oracle but I'll give you something in SQL to
try.
I'm going to assume Oracle has an equivalent to the MAX
function.
Note that you can insert with a select query:
INSERT INTO Table1 SELECT a,b,c FROM Table2
Also Note that you can select constants in your select list
SELECT Field,ConstantNum,'ConstantString' FROM Table1

Put these two toegether

"INSERT INTO mytable " & _
"SELECT MAX(id) + 1, " & _
"'" & name & "', " & _
"'" & city & "', " & _
"'" & state & "' " & _
"FROM mytable"

 

[FengShui1998]

jerod,

After doing an INSERT, you can do objrs.MOVELAST. That will be the ID of the record.


fengshui_1998

 

[Jerod]

I used cfks and it worked great!
but the two suggestions are great.
Thanks to both of you.