CODEWITHSUNDEEP
sql-servervb.netado.net
Brian Hooper
Brian Hooper

Reputation: 22044

Why is this code inserting two rows into the database?

I am inserting a row and returning the id number using ADO.Net like this:-

command_string = "INSERT INTO [MyDB].[dbo].[MyTable] (THIS, THAT)" & _
                 " OUTPUT inserted.ID_NUMER AS ID_NUMBER " & _
                 "    VALUES ('This', 'That')"

Dim insert_command   As SqlCommand     =
        New SqlCommand(command_string, database_connection)
Dim output_parameter As SqlParameter   =
        New SqlParameter("@id_number", 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()
Dim reader As SqlDataReader = insert_command.ExecuteReader()
reader.Read()
id_number = reader.GetInt32(0)
reader.Close
transaction.Commit

but this code when executed inserts two rows into the target table. The id_number returned from the reader is the id number of the second row.

Does anyone know why it is inserting two rows, and what I should do about it? (I have checked over this question here and I don't think it is the same problem, but I can confirm that the code is only executed once and the table has no triggers.)

Upvotes: 0

Views: 63

Answers (2)

LarsTech
LarsTech

Reputation: 81610

To get your ID_Number, try using ExecuteScalar instead:

id_number = insert_command.ExecuteScalar()

No need to call ExecuteReader nor ExecuteNonQuery.

Upvotes: 2

meda
meda

Reputation: 45490

Because you execute it twice

insert_command.ExecuteNonQuery()
Dim reader As SqlDataReader = insert_command.ExecuteReader()

so remove the first one and just keep:

Dim reader As SqlDataReader = insert_command.ExecuteReader()

Upvotes: 4

Related Questions