Thread: calling nextval function

Hi Oliver,
I think the problem with your approach might be in using the nextval
function as a parameter.
I get sequence values by opening a recordset on the sequence, like this:

Dim cnPGODBC as ADODB.Connection
Dim rsSeqVal as ADODB.Recordset
Dim sSeqValSQL as String
Dim iSeqVal as Integer 'The value returned

sSeqValSQL = "SELECT nextval(bed_id_seq);"
cnPGODBC.Open ("Your ODBC connection string")
Set rsSeqVal = cnPGODBC.Execute(sSeqValSQL)

iSeqVal = rsSeqVal.Fields(0)

Set rsSeqVal = Nothing
Set cnPGODBC = Nothing

Hope this helps,

Eric



Gosling, Oliver wrote:

> Does anybody know how to get the next value of a sequence?
>
> I’m presuming that nextval is an SQL procedure in postgres which can
> be called by passing the name of the sequence as a parameter. The code
> below shows what I’ve done in VB. This does not work however - JET
> throws up an error saying that ‘nextval’ table or query cannot be found.
>
> Dim objCmd As New ADODB.Command
>
> Dim objPrm As ADODB.Parameter
>
> objCmd.ActiveConnection = CurrentProject.Connection
>
> objCmd.CommandType = adCmdStoredProc
>
> objCmd.CommandText = "nextval"
>
> Set objPrm = objCmd.CreateParameter("@name", adBSTR, adParamInput, ,
> "bed_id_seq")
>
> objCmd.parameters.Append objPrm
>
> Set sequenceNumber = objCmd.Execute
>
> seqNum = sequenceNumber!nextval
>
> Can anbody help me with this?
>
> Regards,
>
> Oliver Gosling
>

> Hi Oliver,
> sSeqValSQL = "SELECT nextval(bed_id_seq);"
> cnPGODBC.Open ("Your ODBC connection string")
> Set rsSeqVal = cnPGODBC.Execute(sSeqValSQL)


I thought the sequence name had to be single quoted to the sequence
functions...can you skip that in VB?

i.e. select nextval('my_seq');

Merlin