By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 1,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

Return value

P: n/a
Hi,

Is it possible to make a stored procedure or function to generate a return
value?

I have a stored procedure which inserts a new row in a table. I would like
that proceudre to return the ID (autogenerated field) of the row after it
has
inserted it.

Gunnar
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
DFS

"Gunnar Liknes" <g_liknes.Tabortunderscores@g_lobal-satcom.com> wrote in
message news:40********@news.broadpark.no...
Hi,

Is it possible to make a stored procedure or function to generate a return
value?

I have a stored procedure which inserts a new row in a table. I would like
that proceudre to return the ID (autogenerated field) of the row after it
has
inserted it.

Gunnar

Gunnar,

Try this:

Public Function (newRecordID) as Long

'place your stored proc code to add new row here

'return latest ID
newRecordID = DMax("ID","Table")

End Function
Note: you need to add some error trapping in the function to ensure your
Insert completes successfully, or the newRecordID might not be the ID of the
newest record.



Nov 12 '05 #2

P: n/a
"DFS" wrote
"Gunnar Liknes" wrote in
Is it possible to make a stored procedure or function to generate a return value?

I have a stored procedure which inserts a new row in a table. I would like that proceudre to return the ID (autogenerated field) of the row after it has
inserted it.

Try this:

Public Function (newRecordID) as Long

'place your stored proc code to add new row here

'return latest ID
newRecordID = DMax("ID","Table")

End Function
Note: you need to add some error trapping in the function to ensure your
Insert completes successfully, or the newRecordID might not be the ID of the newest record.


Thank you:-) But Where do I put in this code? If I try to enter it in SQL
view
I get an error message saying "SQL Batch has to begin with ALTER or
CREATE"...

Gunnar
Nov 12 '05 #3

P: n/a
DFS

"Gunnar Liknes" <g_liknes.Tabortunderscores@g_lobal-satcom.com> wrote in
message news:40********@news.broadpark.no...
"DFS" wrote
"Gunnar Liknes" wrote in

Is it possible to make a stored procedure or function to generate a return value?

I have a stored procedure which inserts a new row in a table. I would like that proceudre to return the ID (autogenerated field) of the row after it has
inserted it.

Try this:

Public Function (newRecordID) as Long

'place your stored proc code to add new row here

'return latest ID
newRecordID = DMax("ID","Table")

End Function
Note: you need to add some error trapping in the function to ensure your
Insert completes successfully, or the newRecordID might not be the ID of

the
newest record.


Thank you:-) But Where do I put in this code? If I try to enter it in SQL
view
I get an error message saying "SQL Batch has to begin with ALTER or
CREATE"...

Add this Function to a Module. Your stored proc code (that adds a new row)
also goes inside this function. Then the new Function is called in place of
your stored procedure.

Something like:

Public Function (newRecordID) as Long
'this function adds a new record and returns the ID

'code to add new row here
db.Execute("INSERT INTO Table (Field1, Field2) VALUES ('Gunnar','test');")

'return latest ID
newRecordID = DMax("ID","Table")

End Function

Public Sub testNewRecordID()
msgbox "Record added. ID = " & newRecordID()
End Sub

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.