VB6, ADO resultsets and Oracle 8 sequences

[JMac]

I'm using VB6 and creating resultsets using ADO. It works fine except when I select from a sequence.

My SQL is a straightforward "Select testseq.nextval from testseq" which gives correct results when run in SQL*Plus but from inside VB I get gaps in the sequence. The sequence reads 18-20-22-24 etc.

I create the ADO resultset
populate it using the query
Copy the value from the rsSeqResults(0) into a text field on the screen.
Close the resultset

Seems simple enough - so what am I doing wrong?

John

[dknight]

It probably won't help, but check the properties of the sequence in the DBA_SEQUENCES view.

It is possible that the sequence is not incremented by 1.

Another possibility is that the sequence is shared by more than one column or table.

Hope that helps.

[jmodic]

Originally posted by JMac
My SQL is a straightforward "Select testseq.nextval from testseq" which gives correct results when run in SQL*Plus ...

This can't run from SQL*Plus either. TESTSEQ is the name of the sequence, so it can't also be the name of the table/view you are selecting from. Usualy you select from the table DUAL, so maybe your real select is:

"Select testseq.nextval from DUAL;"?

...but from inside VB I get gaps in the sequence. The sequence reads 18-20-22-24 etc.

Are you sure you are not selecting from the table that contains two rows? If you are selecting from DUAL, check how many rows there is in it:

SELECT COUNT(*) FROM dual;

[JMac]

No,
when I SELECT from the sequence in SQL*Plus the results are perfectly correct.

I'm wondering if there's a bug with ADO/Oracle sequences.

John

[jmodic]

Our developers use ADO with Oracle extensively and we haven't experienced any problems with sequences whatsoever.

BTW, I still claim that the example you provided ("Select testseq.nextval from testseq") is imposible to work even in theory, no matter what tool you are using. Is there a typo in that example or is there some other "mystery" that you can reveal if you assert it is working for you in SQL*Plus?