locked
Retrieving generated key value after an insert RRS feed

  • Question

  • I am inserting a record in a SQL Server Compact table, using ExecuteNonQuery.  The table generates a unique key, which I need to use as a foreign key elsewhere.  Is there a simple way of getting at the key field value after the insert?

     

    Thanks

    Friday, July 25, 2008 4:15 PM

Answers

  • How about an example to get you in the right direction

     

    Code Snippet

    Dim conn, sql As String

    conn = "Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;"

    sql = "SELECT @UnitPrice = UnitPrice, @UnitsINStock = UnitsInStock FROM Products WHERE ProductName = @ProductName"

    Dim cn As New SqlConnection(conn)

    cn.Open()

    Dim cmd As New SqlCommand(sql, cn)

     

    Dim pUnitPrice, pInStock, pProductName As SqlParameter

    pUnitPrice = cmd.Parameters.Add("@UnitPrice", SqlDbType.Money)

    pUnitPrice.Direction = ParameterDirection.Output

     

    pInStock = cmd.Parameters.Add("@UnitsInStock", SqlDbType.NVarChar, 20)

    pInStock.Direction = ParameterDirection.Output

     

    pProductName = cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar, 40)

    pProductName.Value = "Chai"

     

    cmd.ExecuteNonQuery()

     

    Console.WriteLine("Unit Price:  {0}", pUnitPrice.Value)

    Console.WriteLine("In Stock:    {0}", pInStock.Value)

     

     

    Friday, July 25, 2008 6:05 PM
  • you can use @@IDENTITYto return the id back from your insert.

     

    "SELECT @@IDENTITY"

     

    i believe you can also add it on to the end of your sql seperated by a comma.

    How it returns i am not sure

     

    or you can select max(youridcolumn) from your table

    since the number will be higher each time you can get the last value by select max(youridcolumn)

    and yourcommand.executescalar

     

    Friday, July 25, 2008 9:36 PM
  •  

    I thought there might be (or should be!) a way of doing this other than with a Select.  I can use a SELECT with a logical key, because this is a Stocks table, so the ticker is unique and I can WHERE it.

     

    OK.  I added a SELECT and an ExecuteScalar which gave me the value of the key.  It works like a champ, even if it is a bit long-winded!!

     

     

    Thanks

     

    EnglishFletch

    Friday, July 25, 2008 9:48 PM

All replies

  • You can use the ParameterDirection, here is an example

    Friday, July 25, 2008 4:22 PM
  • I did that and got an "Invalid direction 'Output' for '@StockID' Papameter" on the underscored line.   The Help on the error wasn't helpful

     

    Here is my code.

     

    oDBConnection.Open()

    With oStockDA

    Dim ParamID As New SqlCeParameter("@StockID", m_StockID)

    ParamID.Direction = ParameterDirection.Output

    .InsertCommand.Parameters.Clear()

    .InsertCommand.Parameters.Add(ParamID)

    .InsertCommand.Parameters.AddWithValue("@Ticker", m_Ticker)

    .InsertCommand.Parameters.AddWithValue("@StockName", m_Name)

    .InsertCommand.Parameters.AddWithValue("@IndustryID", m_Industry.IndustryID)

    .InsertCommand.ExecuteNonQuery()

    End With

     

     

    Thanks

    Friday, July 25, 2008 5:26 PM
  • How about an example to get you in the right direction

     

    Code Snippet

    Dim conn, sql As String

    conn = "Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;"

    sql = "SELECT @UnitPrice = UnitPrice, @UnitsINStock = UnitsInStock FROM Products WHERE ProductName = @ProductName"

    Dim cn As New SqlConnection(conn)

    cn.Open()

    Dim cmd As New SqlCommand(sql, cn)

     

    Dim pUnitPrice, pInStock, pProductName As SqlParameter

    pUnitPrice = cmd.Parameters.Add("@UnitPrice", SqlDbType.Money)

    pUnitPrice.Direction = ParameterDirection.Output

     

    pInStock = cmd.Parameters.Add("@UnitsInStock", SqlDbType.NVarChar, 20)

    pInStock.Direction = ParameterDirection.Output

     

    pProductName = cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar, 40)

    pProductName.Value = "Chai"

     

    cmd.ExecuteNonQuery()

     

    Console.WriteLine("Unit Price:  {0}", pUnitPrice.Value)

    Console.WriteLine("In Stock:    {0}", pInStock.Value)

     

     

    Friday, July 25, 2008 6:05 PM
  • Thanks for all your work, but ...

     

    I am INSERTING a record.  SQL Server creates a key when I add a record.  I need that key. 

     

    To keep things simple, I used your basic structure, but changed to an insert.  Here is the whole thing.  It still gives me the  Invalid Direction diagnostic.  I tried replacing the INSERT with a SELECT, and I got the same error.

     

    The only other thing I can see that looks logicall different is I am using SQLCE.

     

    Imports System.Data.SqlServerCe

    Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Dim conn, Sql As String

    conn = "Data Source=C:\CoveredCallsDev\CoveredCalls3.0\CoveredCalls\CoveredCalls.sdf"

    Sql = "Insert Stocks (StockID, Ticker) VALUES (@StockID, @Ticker)"

    Dim cn As New SqlCeConnection(conn)

    cn.Open()

    Dim cmd As New SqlCeCommand(Sql, cn)

    Dim pStockID, pTicker As SqlCeParameter

    pStockID = cmd.Parameters.Add("@StockID", SqlDbType.BigInt)

    pStockID.Direction = ParameterDirection.Output   '  ERROR on THIS LINE

    pTicker = cmd.Parameters.AddWithValue("@Ticker", "IBM")

    pTicker.Direction = ParameterDirection.Input

    cmd.ExecuteNonQuery()

    Console.WriteLine("Ticker: {0}", pTicker.Value)

    Console.WriteLine("Stock ID {0}", pStockID.Value)

    cn.Close()

    End Sub

    End Class

     

    Friday, July 25, 2008 9:21 PM
  • you can use @@IDENTITYto return the id back from your insert.

     

    "SELECT @@IDENTITY"

     

    i believe you can also add it on to the end of your sql seperated by a comma.

    How it returns i am not sure

     

    or you can select max(youridcolumn) from your table

    since the number will be higher each time you can get the last value by select max(youridcolumn)

    and yourcommand.executescalar

     

    Friday, July 25, 2008 9:36 PM
  • For this, I would create a Stored Procedure which would encapsulate the Insert, and as far as the return value I would include what js06 indicated in the same procedure.

     

    This is one of those times when a stored procedure is much better then a dynamic query.

    Friday, July 25, 2008 9:47 PM
  •  

    I thought there might be (or should be!) a way of doing this other than with a Select.  I can use a SELECT with a logical key, because this is a Stocks table, so the ticker is unique and I can WHERE it.

     

    OK.  I added a SELECT and an ExecuteScalar which gave me the value of the key.  It works like a champ, even if it is a bit long-winded!!

     

     

    Thanks

     

    EnglishFletch

    Friday, July 25, 2008 9:48 PM