Return Record ID of Newly Added Record

[Tony Stoker]

I have a .Net web app that adds a record to a SQL
database. After the user adds their record I want to
have a link that will link them to their new record! The
recordID is a AutoNumber in the SQL server...

How do I return the recordID after I have added the
record?

 

[Matt]

Use a DataSet and SqlDataAdapter to add the record.

If you create a connection to the database with your server explorer you can
simply drag the table onto your designer and it will create it for you.
Then right click on the adapter and choose Generate dataset.

In your code you simply add the record(look at documention for this, its
really simple)

after you add it the dataset will contain the new record with the AutoNumber
in it.

 

[Michael Giagnocavo [MVP]]

In the command that inserts, after the insert, you can do SELECT @@IDENTITY
and that will give you the ID of the last row inserted.

-mike
MVP

 

[Guest]

Thanks but I still have a question..I already had the sql
part that wasn't the issue but when I click on the button
to submit the link how do I assign the newly added record
id to a variable so I can then display a confirm page
with a link to the users newly added record page? I
right now am using the executeNonQuery but it just adds
the record and doesn't return any value.

Thanks

 

[Michael Giagnocavo [MVP]]

Have you added an output parameter and assigned @@IDENTITY to it? I think
that should do it. Alternatively, you could use ExecuteScalar and get the
only SELECTed row, @@IDENTITY.
-mike
MVP

 

[Guest]

Mike-
Thanks for writing back and don't laugh at what I am
about to post I am new to this type of stuff....this is
what I have tried and it isn't working(please note in the
try section I was just trying to see if it was grabbing
my variable or not any suggestions or hints would be
greatly appreciated:

********
Private Sub AddArticle()

Dim sql As String
Dim cmd As SqlCommand
Dim sb As StringBuilder
Dim Values As ArrayList

Dim fNewsDate As String
Dim fNewsSubBy As String
Dim fNewsHdr As String
Dim fNewsDesc As String
Dim NewID


fNewsDate = "'" & txtNewsDate.Text & "',"
fNewsSubBy = "'" & txtNewsSubBy.Text & "',"
fNewsHdr = "'" & txtNewsHdr.Text & "',"
fNewsDesc = "'" & txtNewsDesc.Text & "'"

sql = "SET NOCOUNT ON; Insert INTO [News]
(NewsDate,NewsSubBy,NewsHdr,NewsDesc) Values " & "(" &
fNewsDate & fNewsSubBy & fNewsHdr & fNewsDesc & ");SELECT
@@IDENTITY AS myID FROM News;"



cmd = New SqlCommand(sql, conSCGC)
conSCGC.Open()

Try
cmd.ExecuteScalar()



NewID = cmd.ExecuteScalar("myID")
Response.Write(NewID)
Catch
Response.Write(sql)
Finally
conSCGC.Close()
End Try

End Sub
******

 

[Michael Giagnocavo [MVP]]

Hi there,

fNewsDate = "'" & txtNewsDate.Text & "',"
fNewsSubBy = "'" & txtNewsSubBy.Text & "',"
fNewsHdr = "'" & txtNewsHdr.Text & "',"
fNewsDesc = "'" & txtNewsDesc.Text & "'"

sql = "SET NOCOUNT ON; Insert INTO [News]
(NewsDate,NewsSubBy,NewsHdr,NewsDesc) Values " & "(" &
fNewsDate & fNewsSubBy & fNewsHdr & fNewsDesc & ");SELECT
@@IDENTITY AS myID FROM News;"

Few points. Never concat an SQL string like that. The code below has
security problems. Always use parameters.

Try this:

sql = "SET NOCOUNT ON; INSERT [News] (NewsDate, ETC ) VALUES (@NewsDate,
@ETC); SET @ID = SELECT @@IDENTITY;";
new command, bla bla

myCommand.Add("@NewDate", SqlDbTypes.DateTime).Value = myDateTime;
myCommand.Add("@ETC", SqlDbTypes.Whatever).Value = ETC;
myCommand.Add("@ID", SqlDbTypes.Int).Direction =
ParameterDirection.Output;

conSCGC.Open()
Try
cmd.ExecuteScalar()
NewID = cmd.ExecuteScalar("myID")

Why call ExecuteScalar() twice? Also, shouldn't you have to cast there?
You DO have OPTION STRICT ON, right?

Did this work the way you had it? Try with the output param if not. You
may have to play around a bit (I use stored procedures for inserts, so my
syntax might be off somewhere).

Response.Write(NewID)
Catch
Response.Write(sql)
Finally
conSCGC.Close()

Forgot to dispose your command (SqlCommand implements IDisposable).