CODEWITHSUNDEEP
vb.net
Arie
Arie

Reputation: 3563

Take auto incremented id after insert mssql

I am using below code to insert records I am not inserting "Id" because it was set up as auto incremenet. Anyway i would like to get what id was assigned to that record after insert. Could you help me out? I tried with executescalar as below but its not working. Thanks.

Public Function Add() As Integer
        Dim rowId As Integer
        Dim strcon = New AppSettingsReader().GetValue("ConnectionString", GetType(System.String)).ToString()
        Using con As New SqlConnection(strcon)
            Using cmd As New SqlCommand("INSERT INTO tbProjekt (Nazwa, Autor, Datautworzenia, Visible) VALUES (@Nazwa, @Autor, @Datautworzenia, @Visible)", con)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@Nazwa", Nazwa)
                cmd.Parameters.AddWithValue("@Autor", Autor)
                cmd.Parameters.AddWithValue("@DataUtworzenia", DataUtworzenia)
                cmd.Parameters.AddWithValue("@Visible", Visible)
                con.Open()
                rowId = Convert.ToInt32(cmd.ExecuteScalar())
                con.Close()
            End Using
        End Using
        Return rowId
    End Function

Upvotes: 0

Views: 97

Answers (1)

Panos B.
Panos B.

Reputation: 49

Use the SCOPE_IDENTITY()

Replace this line

 Using cmd As New SqlCommand("INSERT INTO tbProjekt (Nazwa, Autor, Datautworzenia, Visible) VALUES (@Nazwa, @Autor, @Datautworzenia, @Visible)", con)

With this line

 Using cmd As New SqlCommand("INSERT INTO tbProjekt (Nazwa, Autor, Datautworzenia, Visible) VALUES (@Nazwa, @Autor, @Datautworzenia, @Visible); SELECT NEWID = SCOPE_IDENTITY()", con)

Also you can change this

 rowId = Convert.ToInt32(cmd.ExecuteScalar())

To this

 rowId = cmd.ExecuteScalar()

Upvotes: 1

Related Questions