Retrieving Record Key while creating the record.

Discussion in 'ASP .Net' started by =?Utf-8?B?SnVzdGlu?=, Oct 4, 2004.

  1. 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.
    =?Utf-8?B?SnVzdGlu?=, Oct 4, 2004
    #1
    1. Advertising

  2. 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>

    "Justin" wrote:

    > 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.
    =?Utf-8?B?S2VuIENveCBbTWljcm9zb2Z0IE1WUF0=?=, Oct 4, 2004
    #2
    1. Advertising

  3. =?Utf-8?B?SnVzdGlu?=

    Karl Seguin Guest

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

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

    Karl

    --
    MY ASP.Net tutorials
    http://www.openmymind.net/


    "Justin" <> wrote in message
    news:...
    > 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.
    Karl Seguin, Oct 4, 2004
    #3
  4. =?Utf-8?B?SnVzdGlu?=

    Karl Seguin Guest

    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


    --
    MY ASP.Net tutorials
    http://www.openmymind.net/


    "Ken Cox [Microsoft MVP]" <> wrote in message
    news:...
    > 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>
    >
    > "Justin" wrote:
    >
    > > 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.
    Karl Seguin, Oct 4, 2004
    #4
  5. Thanks for the help!

    "Justin" wrote:

    > 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.
    =?Utf-8?B?SnVzdGlu?=, Oct 5, 2004
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. gbattine
    Replies:
    0
    Views:
    300
    gbattine
    Aug 24, 2006
  2. jayshree
    Replies:
    5
    Views:
    767
    Piet van Oostrum
    Jul 27, 2009
  3. ses
    Replies:
    8
    Views:
    938
    Arved Sandstrom
    Feb 25, 2011
  4. M P
    Replies:
    1
    Views:
    462
  5. Bhavna
    Replies:
    10
    Views:
    243
    Bob Barrows [MVP]
    Jul 21, 2006
Loading...

Share This Page