1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Oracle sequences and VB problems

Discussion in 'Oracle' started by josemacall, Sep 5, 2008.

  1. josemacall

    josemacall New Member

    Joined:
    Sep 5, 2008
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    Hi guys:

    I've met the following issue. We've created some sequences with this code:

    CREATE SEQUENCE SQ_TR_HQPROJECT MINVALUE 13 MAXVALUE
    START WITH 13 INCREMENT BY 1

    Right, while using TOAD or SQLPlus the sequence seems to work properly:

    SQL> select SQ_TR_HQPROJECT.nextval from dual;

    NEXTVAL
    ----------
    21

    SQL> r
    1* select SQ_TR_HQPROJECT.nextval from dual

    NEXTVAL
    ----------
    22​
    But when we implement in our program the increment is by 2 not by 1:

    Const SqlCodFiltro As String = "SELECT SQ_TR_HQPROJECT.NEXTVAL FROM dual"

    On Error GoTo Error

    Dim rsServicios As Object

    Set rsServicios = conexOracleSIGEL.CreateDynaset(SqlCodFiltro, 0&)

    Do While Not rsServicios.EOF

    IdFiltro = rsServicios(0)
    rsServicios.MoveNext
    gCodFiltroEjecutado = IdFiltro
    Loop​

    The first time we run the procedure returns 23 and the second one 25.

    Can anyone hepl us with this problem.

    Thanks in advance.
     
  2. xpi0t0s

    xpi0t0s Mentor

    Joined:
    Aug 6, 2004
    Messages:
    3,012
    Likes Received:
    203
    Trophy Points:
    0
    Occupation:
    Senior Support Engineer
    Location:
    England
    This will be because you're running the query twice. Enable SQL*Net tracing, then step through the code in the debugger and watch the trace file in an editor (you may need to reopen the trace file each step to see what's new, depending on which editor you use). Then you will be able to see where the extra run occurs.

    I checked with Oracle 10.2.0.3, using OLE DB 10.2.0.2.20 and the following code:

    Code:
           Dim oCon As ADODB.Connection
           Text = "Provider=ORAOLEDB.ORACLE;Data Source=V102_host;User ID=scott;Password=tiger"
           oCon = New ADODB.Connection
           oCon.ConnectionString = Text
           oCon.Open()
    
           Dim oCmd As ADODB.Command
           oCmd = New ADODB.Command
           oCmd.ActiveConnection = oCon
           oCmd.CommandType = ADODB.CommandTypeEnum.adCmdText
           oCmd.CommandText = "select hqproj.nextval from dual"
           'oCmd.Execute()
    
           Dim oRst As ADODB.Recordset
           oRst = New ADODB.Recordset
           oRst.Open(oCmd)
           Do While Not oRst.EOF
               MsgBox("<" & oRst(0).Value & ">")
               oRst.MoveNext()
           Loop
    
    With oCmd.Execute() not commented out I also got the same behaviour, because oRst.Open also executes the query.
     
  3. josemacall

    josemacall New Member

    Joined:
    Sep 5, 2008
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    Thank you very much.

    As soon as I test your solution I'll let you know
     

Share This Page