CODEWITHSUNDEEP
asp.netsql-serverinsertidentity
Adam
Adam

Reputation: 6132

Return new identity value from table adapter Insert command

I have an insert statement in a table adapter, but it keeps returning 1, instead of the id of the newly inserted row. Why?

Dim TA As New pTableAdapters.householdsTableAdapter
lblResult.Text = TA.Insert(New Guid("32dd20eb-1691-457b-9ff5-fc41d687e578"), Trim(Title), 0, "add", "addr2", _
            "4544", "london", "ld", 0, "", Date.Now)

The insert statement in the table adapter: 

INSERT INTO households (userid, title, side, address, address2, zipcode, city, state, country, notes, createdate) VALUES (@userid,@title,@side,@address,@address2,@zipcode,@city,@state,@country,@notes,@createdate);SELECT CAST(scope_identity() AS int)

Upvotes: 0

Views: 1252

Answers (1)

lheria
lheria

Reputation: 601

From the following link: http://blogs.msdn.com/b/smartclientdata/archive/2005/10/31/returnidentityvaluequery.aspx

... There's one last important piece: select the new query in the DataSet Designer and change the ExecuteMode property from NonQuery to Scalar. ...

NonQuery will return the affected row count (hence why you always get a 1), while Scalar mode returns the selected value itself.

Upvotes: 1

Related Questions