none
Would like to get information about a row I just inserted RRS feed

  • Question

  • I am inserting a record into a table with an "identity" column, and would like to get the value of that column to use elsewhere in my program.

    Here's the way I'm doing it now .. which is horribly inefficient:

        Dim connection As New SqlConnection("blah blah blah")

        connection.Open()

        Dim cmd As New SqlCommand("INSERT dataLoad (loadDescription) VALUES ('Data Loaded: " & Date.Now.ToString() & "')", connection)

        cmd.ExecuteNonQuery()
        cmd.Dispose()

        cmd = New SqlCommand("SELECT dataLoadId FROM dataLoad ORDER BY timeStamp DESC", connection)

        Dim reader As SqlDataReader = cmd.ExecuteReader()
        reader.Read()

        dataLoadId = reader(0)

    I know there has to be a better way to do this .. I'm just having some difficulty finding it.

    Thanks in advance,
    Evan
    Tuesday, May 4, 2010 6:15 PM

Answers

  • Hi Dictor, Try something like this

    Dim connection As New SqlConnection("blah blah blah")
    connection.Open()
    Dim cmd As New SqlCommand("INSERT dataLoad (loadDescription) VALUES (@DateValue); Select @Return_Value = @@IDENTITY;", connection)
    cmd.CommandType = Text
    
    '' Add the Parameter Value 1
    cmd.parameters("@DateValue").Direction = Input
    cmd.parameters("@DateValue").Value = "Data Loaded: " & Date.Now.ToString()
    
    '' Set the Parameter for Reading the Output Value
    cmd.parameters("@Return_Value").Direction = Output
    Date.Now.ToString()
    
    cmd.ExecuteNonQuery()
    
    '' After Executing the Query read the Identity value from the Output Parameter like this
    
    Dim OutputValue as Integer 
    OutputValue = cmd.parameters("@Return_Value").Value
    
    cmd.Dispose()
    

     

     


    Best Regards,
    Gopi V

    If you have found this post helpful, please click the Vote as Helpful link (the green triangle and number on the top-left).

    If this post answers your question, click the Mark As Answered link below. It helps others who experience the same issue in future to find the solution.

    • Marked as answer by ESDictor Wednesday, May 5, 2010 5:07 PM
    Wednesday, May 5, 2010 4:45 AM

All replies

  • you could try this.

    dim cmd ad new sqlcommand("insert into table values(.....) select @@identity",connection)

    int id = Convert.ToInt32(  cmd.ExecuteScalar() );

     

     


    http://www.sdtslmn.com
    Tuesday, May 4, 2010 8:54 PM
  • Hi Dictor, Try something like this

    Dim connection As New SqlConnection("blah blah blah")
    connection.Open()
    Dim cmd As New SqlCommand("INSERT dataLoad (loadDescription) VALUES (@DateValue); Select @Return_Value = @@IDENTITY;", connection)
    cmd.CommandType = Text
    
    '' Add the Parameter Value 1
    cmd.parameters("@DateValue").Direction = Input
    cmd.parameters("@DateValue").Value = "Data Loaded: " & Date.Now.ToString()
    
    '' Set the Parameter for Reading the Output Value
    cmd.parameters("@Return_Value").Direction = Output
    Date.Now.ToString()
    
    cmd.ExecuteNonQuery()
    
    '' After Executing the Query read the Identity value from the Output Parameter like this
    
    Dim OutputValue as Integer 
    OutputValue = cmd.parameters("@Return_Value").Value
    
    cmd.Dispose()
    

     

     


    Best Regards,
    Gopi V

    If you have found this post helpful, please click the Vote as Helpful link (the green triangle and number on the top-left).

    If this post answers your question, click the Mark As Answered link below. It helps others who experience the same issue in future to find the solution.

    • Marked as answer by ESDictor Wednesday, May 5, 2010 5:07 PM
    Wednesday, May 5, 2010 4:45 AM
  • Both replies actually had the answer I needed, but the one from Gopi V perfectly explained what I needed to do.

    Thank you both very much!

    Evan

    Wednesday, May 5, 2010 5:09 PM