StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POASP function not returning mssql insert id
primarykey
Id
17386833
data
AcceptedAnswerId
0
AnswerCount
1
ClosedDate
CommentCount
4
CommunityOwnedDate
CreationDate
2013-06-30T03:24:10.160
FavoriteCount
0
LastActivityDate
2013-07-02T21:10:53.050
LastEditDate
2013-06-30T12:20:28.230
LastEditorUserId
447356
OwnerUserId
1220143
ParentId
0
PostTypeId
1
Score
0
ViewCount
433
LastEditorDisplayName
text
Body
Having a strange issue with a mssql insert and trying to retrieve the insert id trough a function call. The strange thing is the last insert id is fetched and I can print it out in the function but it returns an empty value via the function if I structure my sql statement in the following way: <pre class="lang-vbs prettyprint-override"><code>function sqlInsert(table, byVal fieldList) sqlInsert = 0 ' default in case of error .... ' removed code that builds sql for simplicity sql = "insert into " & table & " (" & insertFields & ") values (" & insertValues & ")" sql = "SET NOCOUNT ON;" & sql & "; SELECT SCOPE_IDENTITY() AS NewID" set rs = oCn.Execute(sql) response.write rs("NewID") ' 28 for example sqlInsert = rs("NewID") ' empty!! end function </code></pre> The function works and executes the insert statement correctly and rs("NewID") does contain the correct inserted row id. However, nothing is returned to the function call. eg: <pre class="lang-vbs prettyprint-override"><code>result = sqlInsert("tablename", data) response.write result 'empty!! </code></pre> If I seperate the sql as follows: <pre class="lang-vbs prettyprint-override"><code>sql = "insert into " & table & " (" & insertFields & ") values (" & insertValues & ")" set rs = oCn.Execute(sql) set rs2 = oCn.Execute("SELECT SCOPE_IDENTITY() AS NewID") response.write rs2("NewID") '28 for example sqlInsert = rs2("NewID") '28 </code></pre> ....then that works and the result of the function call contains the insert ID. <pre class="lang-vbs prettyprint-override"><code>result = sqlInsert("tablename", data) response.write result '28 </code></pre> in both cases, the recordset contains the correct insert id in the "NewID" but only second method returns the id to the function call. Why? I have tried <code>cInt(rs("NewID"))</code> in the first example but still nothing. I know I could just use the second method but the first is one less call to the database and I just want to know why it doesn't return the value when clearly the value is present in the rs value if I write it out before assigning it to sqlInsert.
Tags
<sql-server><asp-classic><recordset>
Title
ASP function not returning mssql insert id
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USShadow Wizard
UserOwnerUserId
1. USws8
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.