Retrieving Record Key while creating the record.

G

Guest

Using SQL server and VS.NET with ASP.NET/C# how can I retrieve the Key ID of
a record right after creating that record in the same event? Do I need to
close and reopen the connection and requery?

Thanks, Justin.
 
G

Guest

Hi Justin,

You'll want to use ExecuteScalar() as shown in this article about one third
of the way down:

http://dotnetadvisor.net/doc/13279

Here's the relevant VB code:

<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>

<html>

<script language="VB" runat="server">

Sub Page_Load(Source as Object, E as EventArgs)

Dim dbConn As SqlConnection

' Create a new connection object pointing to
the database
dbConn = New SqlConnection("server=.;uid=sa;pwd=
;database=Northwind")

Dim dbComm As New SqlCommand()
dbComm.Connection = dbConn
dbComm.CommandType = CommandType.Text
dbComm.CommandText = "INSERT INTO Categories
(CategoryName, Description) " & _
"VALUES ('Guitars','Here you can find just the " & _
"guitar you looked for'); SELECT @@IDENTITY As 'Identity'"
Dim iID As Integer

Try
dbConn.Open()
iID = dbComm.ExecuteScalar()
Catch ex As Exception
Response.Write(ex.Message)
Response.End
Finally
If dbConn.State = ConnectionState.Open Then
dbConn.Close()
End If
End Try

Response.Write("The ID of the new record is: "
& iID.ToString())

End Sub

</script>
</html>
 
K

Karl Seguin

"INSERT INTO BLAH VALUES(1,2,3,4); SELECT @@IDENTITY;"

int key = Convert.ToInt32(command.ExecuteScalar());

Karl
 
K

Karl Seguin

Ken ur all over me like a fat kid on a smarties today!

'tis best to cint dbComm.ExecuteScalar() and turn option strict on though :p

Karl
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top