capture and store a returned output param

  • Thread starter Thread starter BobMcClellan
  • Start date Start date
B

BobMcClellan

Hi....
Hopefully I will be explain this well enough to interpret....

I have an .adp that interacts with an SQL backend.
When a new order is created, I need to allow the user to save the order....
and continue editing if they want to.. and then click save again....

The first click adds the customer to the orderRequest Table
which creates a new OrderID.

I then retrieve this OrderID and place it in a control on the form....

Then all updates happen via a stored Proc which updates that OrderID.

My problem is this.
I'd like to have the New OrderID returned to the .adp as an output param
but I can not get the syntax to work.

I can make it work fine from the QA but when I run it from the .adp it
says.... must declare output param @maxOid....

THIS IS HOW IT WORKS NOW...........................................

'This is in the save button OnClick Event

sqlString = "OrderAdder_Save '" & cust & "'"
CurrentProject.Connection.Execute sqlString

This is the stored proc that fires.
CREATE Proc OrderAdder_Save
(
@cust char(50)
)
as

Insert into OrderRequest (Customer) Select @cust

..................................................THIS IS HOW IT WORKS NOW

this works fine, but I then need to retrieve the ID that was just created
with....

'Go to the New Order Request Just Created.
sqlText = "Select max(orderid) as oid from OrderRequest"
Dim rs As New ADODB.Recordset
rs.Open sqlText, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs.MoveFirst

'Populate the field with OrderID number.
Me.txt_OrderID = rs.Fields("OID")
rs.Close
Set rs = Nothing

This is what I'd like to do... or some variation of it.....

sqlString = "OrderAdder_Save '" & cust & "', @maxOid OUTPUT"

Me.txt_OrderID = CurrentProject.Connection.Execute sqlString

.....where the returned output param is the max(OrderID)

I can modify the stored proc to

CREATE Proc OrderAdder_Save
(
@cust char(50),
@maxOid int
)
as

Insert into OrderRequest (Customer) Select @cust

Select @maxOid = max(OrderID) from OrderRequest


AGAIN... this works fine from the QA but not from the .adp


Thanks much for any help on this.

Respectfully,
bob mcclellan
 
This is what I'd like to do... or some variation of it.....

sqlString = "OrderAdder_Save '" & cust & "', @maxOid OUTPUT"

Me.txt_OrderID = CurrentProject.Connection.Execute sqlString

....where the returned output param is the max(OrderID)
Click to expand...

One way is to make your stored procedure return a recordset:

---- stored procedure ----
CREATE Proc OrderAdder_Save
(
@cust char(50)
)
as
Insert into OrderRequest (Customer) Select @cust
Select max(OrderID) 'MaxOid' from OrderRequest

---- ADO code ----
sqlString = "OrderAdder_Save " & cust
set rs = CurrentProject.Connection.Execute(sqlString)
Me.txt_OrderID = rs("MaxOid")



The other way is to use a command object. More ADO code, but supposedly faster. Use the stored
procedure you already designed, and try this ADO code:

sqlString = "OrderAdder_Save"
set oCmd = new ADODB.Command
set oCmd.Connection = CurrentProject.Connection
oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15

set param = new ADODB.Parameter
param.Type = adInteger
param.Size = 3 ' 3 bytes
param.Direction = adParamInput
param.Value = cust
param.name = "Cust"
oCmd.Parameters.Append param

set param = new ADODB.Parameter
param.Type = adInteger
param.Size = 3
param.Direction = adParamOutput
param.Name = "MaxOid"
oCmd.Parameters.Append param

oCmd.Execute ,,adExecuteNoRecords

Me.txt_OrderID = oCmd.Parameters("MaxOid").Value
 
B> My problem is this.
B> I'd like to have the New OrderID returned to the
B> .adp as an output param but I can not get the
B> syntax to work.


see my response in thread "how to pass a return value from a sql 2000 stored
procedure into access vba routine" in this newsgroup, dated 9/9.

Vadim
 
Thank you Thank you Thank you!!!!!!!!!
Had to modify two lines but it works Perfect.

I really appreciate the reply and the help.
Sincerly,
bob mcclellan

Modified the lines shown......

Dim cn As New ADODB.Connection <----added this
sqlString = "OrderAdder_Save"
Set oCmd = New ADODB.Command

Set cn = CurrentProject.Connection
Set oCmd.ActiveConnection = cn
<----changed from
set oCmd.Connection = CurrentProject.Connection

oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15

Set param = New ADODB.Parameter
param.Type = adChar <---- changed this from adInteger
param.Size = 50 ' 3 bytes
param.Direction = adParamInput
param.Value = Cust
param.Name = "Cust"
oCmd.Parameters.Append param

Set param = New ADODB.Parameter
param.Type = adInteger
param.Size = 3
param.Direction = adParamOutput
param.Name = "MaxOid"
oCmd.Parameters.Append param

oCmd.Execute , , adExecuteNoRecords


Me.txt_OrderID = oCmd.Parameters("MaxOid").Value

End If
 
Back
Top