Using Oracle sequences in recordset addNew method?

Results 1 to 3 of 3

Thread: Using Oracle sequences in recordset addNew method?

  1. #1
    Jon-Eric Eliker Guest

    Default Using Oracle sequences in recordset addNew method?

    I&#039m trying to write new records using addNew and one field is based on a sequence value. Here&#039s the table:<BR><BR> MYTABLE<BR> -------<BR> ID NUMBER(10)<BR> FNAME VARCHAR2(25)<BR><BR>Here&#039s the SQL I would use:<BR><BR> INSERT INTO MYTABLE(ID, LNAME) VALUES(IDSEQ.NEXTVAL, &#039Smith&#039);<BR><BR>I would like to do this with a recordset addNew method:<BR><BR> set rs = server.createobject("adodb.recordset")<BR> rs.Open "mytable", strConnect, adOpenKeyset, adLockOptimistic<BR> &#039<BR> &#039this line works fine<BR> &#039<BR> rs.AddNew array("id","name"), array("1","Smith")<BR> &#039<BR> &#039this fails with "Type Mismatch"<BR> &#039<BR> rs.AddNew array("id","name"), array("idseq.nextval","Smith")<BR><BR>Any suggestions?

  2. #2
    awaim Guest

    Default RE: Using Oracle sequences in recordset addNew met

    I don&#039t know of any way that you can tell AddNew that a <BR>field value is a database expression instead of a literal. <BR>Of course, you could pass the insert statement (minus the <BR>&#039;&#039) to the Command.Execute or Connection.Execute methods. <BR><BR>However, this workaround will allow you to use AddNew:<BR><BR>&#039The main rs opened as before:<BR>Set rs = Server.CreateObject("ADODB.Recordset")<BR>rs.Open "mytable", strConnect, adOpenKeyset, adLockOptimistic<BR><BR>&#039Another recordset, used to get nextval<BR>Dim rsSeq<BR>Dim sSeqSQL<BR>sSeqSQL = "select idseq.nextval from dual"<BR><BR>&#039Open the recordset once you know you need the sequence.<BR>&#039Once you execute this, you own the sequence number - no<BR>&#039other user can generate it by calling idseq.nextval.<BR>Set rsSeq = Server.CreateObject("ADODB.Recordset")<BR>rsSeq.Op en sSeqSQL, rs.ActiveConnection, adOpenStatic, adLockReadOnly<BR><BR>&#039AddNew using the sequence number<BR>rs.AddNew Array("id", "fname"), Array(rsSeq.Fields(0).Value, "Smith")<BR><BR>&#039If you need to add more rows, you can do this:<BR>rsSeq.Requery &#039Gets a new sequence number<BR>rs.AddNew Array("id", "fname"), Array(rsSeq.Fields(0).Value, "Green")<BR><BR><BR>&#039Hope this helps<BR>

  3. #3
    generic Guest

    Default RE: Using Oracle sequences in recordset addNew met

    Works like a champ. Great idea! Thx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •