Get output value in ADO.Net without stored procedure

Question

I am converting an ADODB application to ADO.Net, and it includes in it a command that inserts a new row and returns the autoincrement value like this:-

INSERT INTO [MyDB].[dbo].[MyTable] (COLUMN1, COLUMN2) OUTPUT inserted.ID_PRIMARY
                      VALUES ('This', 'That')

One can then grab the OUTPUT value and away you go. But when I try that using ADO.Net, like this:-

command_string = (as above)
Dim insert_command   As SqlCommand     = New SqlCommand(command_string, database_connection)
Dim output_parameter As SqlParameter   = New SqlParameter("@inserted.ID_PRIMARY", SqlDbType.Int)
Dim transaction      As SqlTransaction = database_connection.BeginTransaction(System.Data.IsolationLevel.Serializable)
insert_command.Transaction = transaction
output_parameter.Direction = ParameterDirection.Output
insert_command.Parameters.Add(output_parameter)
insert_command.ExecuteNonQuery()
transaction.Commit

an error is returned:-

SqlException (0x80131904) Incorrect Syntax Near '.'

I appreciate that I could use a stored procedure to perform the insert and retrieve a return value, as explained here, but I'd like to know if there is any way this can be done without a stored procedure? Or am I overlooking some trivial error?

score 1 · Accepted Answer · answered Apr 11 '14 at 09:12

1

The newly inserted ID from your OUTPUT Inserted.ID_PRIMARY will be a data set being returned from the statement. You need to use a ExecuteReader approach to read those values returned:

' setup as above - except you don't need the "output_parameter"
Dim reader As SqlDataReader = insert_command.ExecuteReader()

While reader.Read()
    ' get your newly inserted ID's here as returned dataset
    Dim newlyInsertedID As Integer = reader.GetInt32(0)

    ' if you insert multiple rows at once, you might need store those ID's 
    ' that you fetch back one by one into a list or something
End While

answered Apr 11 '14 at 09:12

marc_s

732,580
175
1,330
1,459

Thanks for your help; I have added the data reader, but there is still the syntax error in the SQL statement; do you know what to do here? – Brian Hooper Apr 11 '14 at 09:24
@BrianHooper: the SQL you posted seems just fine - not sure... what exactly is the error? Where does it point to? Are you concatenating together the SQL in VB.NET? Maybe you're missing a space somewhere or something like that .... or are you running against SQL Server **2000** (or a database in "level 80" compatibility mode)?? The `OUTPUT` clause is a new feature in SQL Server **2005** ... – marc_s Apr 11 '14 at 09:25

Steve · Answer 2 · 2014-04-11T11:07:33.050

You can take advantage of the fact that with SQL Server you could emit two sql commands in a single batch. And use the T-SQL function SELECT SCOPE_IDENTITY to find the latest identity value added to the current scope

Dim sqlText = "INSERT INTO [MyDB].[dbo].[MyTable] (COLUMN1, COLUMN2) " & _
              "VALUES ('This', 'That'); SELECT SCOPE_IDENTITY()"
Dim insert_command = New SqlCommand(sqlText, database_connection)
insert_command.Transaction = transaction
Dim result = Convert.ToInt32(insert_command.ExecuteScalar())
transaction.Commit

otherwise, if you want to use your syntax, then remove the output paramenter

Dim sqlText = "INSERT INTO [MyDB].[dbo].[MyTable] (COLUMN1, COLUMN2) " & _ 
              "OUTPUT inserted.ID_PRIMARY  VALUES ('This', 'That')"
Dim insert_command  = New SqlCommand(sqlText, database_connection)
insert_command.Transaction = transaction
Dim result = Convert.ToInt32(insert_command.ExecuteScalar())
transaction.Commit

But as before, use ExecuteScalar to retrieve the value of the first row/first column returned by the query

score 1 · Answer 3 · answered Apr 11 '14 at 13:36

The syntax error was corrected by changing the SQL to:-

INSERT INTO [MyDB].[dbo].[MyTable] (COLUMN1, COLUMN2)
    OUTPUT inserted.ID_PRIMARY As ID_PRIMARY
        VALUES ('This', 'That')

and setting up the parameter like this:-

Dim output_parameter As SqlParameter = New SqlParameter("@ID_PRIMARY", SqlDbType.Int)

score 1 · Answer 4 · edited Mar 01 '15 at 20:11

Try the following code:

/****** Object:  Table [dbo].[Pedidos]    Script Date: 03/01/2015 16:48:17 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Pedidos](
[PedidoID] [int] IDENTITY(1,1) NOT NULL,
[Data] [date] NOT NULL,
[clienteID] [int] NOT NULL,
 CONSTRAINT [PK_Pedidos] PRIMARY KEY CLUSTERED 
(
[PedidoID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =     OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Pedidos]  WITH CHECK ADD  CONSTRAINT     [FK_Pedidos_Clientes] FOREIGN KEY([clienteID])
REFERENCES [dbo].[Clientes] ([clienteID])
GO

ALTER TABLE [dbo].[Pedidos] CHECK CONSTRAINT [FK_Pedidos_Clientes]
GO

Use Dim Identity As Integer or declare PUBLIC Identity which returns the insert record the value of auto increment field PedidoID.

    Dim connection As SqlConnection = databaseDB.GetConnection
    Dim insertStatement As String _
        = "INSERT [dbo].[Pedidos] " _
        & "([Data], [clienteID]) " _
        & "VALUES (@Data, @clienteID) SELECT @PedidoID = SCOPE_IDENTITY()" 'SELECT SCOPE_IDENTITY() AS 'Identity
    Dim insertCommand As New SqlCommand(insertStatement, connection)
    insertCommand.CommandType = CommandType.Text
    insertCommand.Parameters.AddWithValue("@Data", dbo_pedidos.Data)
    insertCommand.Parameters.AddWithValue("@clienteID", dbo_pedidos.clienteID)
    ' Inclui o SqlParameter para retornar o novo valor identity
    ' Define o ParameterDirection como Output.(Saida)
    insertCommand.Parameters.Add("@PedidoID", SqlDbType.Int, 0, "PedidoID")
    insertCommand.Parameters("@PedidoID").Direction = ParameterDirection.Output

    Try
        connection.Open()
        Dim count As Integer = insertCommand.ExecuteNonQuery()
        If count > 0 Then
            Identity = insertCommand.Parameters("@PedidoID").Value
            Return True
        Else
            Return False
        End If
    Catch ex As SqlException
        Throw ex
    Finally
        connection.Close()
    End Try

Get output value in ADO.Net without stored procedure

4 Answers4