Thread: calling nextval function

calling nextval function

From
"Gosling, Oliver"
Date:

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

 

Re: calling nextval function

From
Eric E
Date:
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
>


Re: calling nextval function

From
"Merlin Moncure"
Date:
> 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