get last inserted primary key record in database

Question

Please help to get the primary key of the last insert record as the one which i have gives me duplicate rows in the database and return 0

Try
        If Conn.State = ConnectionState.Open Then Conn.Close()
        'insert the new customer data
        Conn.Open()
        cmd = New SqlCommand("insert into Quote values ('" & dateOFCreat & "','" & Emp & "','" & Customer_no & "' )", Conn)

        Dim a As Integer = cmd.ExecuteNonQuery()
        Dim results As Integer
        Dim cmd_results As SqlCommand
        'Get the last created Quote in the Database
        cmd_results = New SqlCommand("Select @@Identity from Quote", Conn)
        results = cmd.ExecuteScalar

        TxtLastQuoteID.Text = results

        If a = 0 Then
            MsgBox("Error")
        End If
        Conn.Close()

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

Steve · Accepted Answer · 2019-02-15T13:41:19.177

You can make use of the batch commands supported by Sql Server. Just put together the two instructions and just use ExecuteScalar. However, before that, you need to fix ASAP your Sql Injection vulnerability. Do not concatenate strings to build an sql command, but use parameters.

Try
    Using con as SqlConnection = new SqlConnection(....constringhere...)
        Conn.Open()
        Dim sqlText = "insert into Quote values (@dat,@emp,@cusno); SELECT SCOPE_IDENTITY()"
        cmd = New SqlCommand(sqlText,Conn)
        cmd.Parameters.Add("@dat", SqlDbType.NVarChar).Value = dateOFCreat 
        cmd.Parameters.Add("@end", SqlDbType.NVarChar).Value = emp 
        cmd.Parameters.Add("@cusno", SqlDbType.NVarChar).Value = Customer_no
        Dim lastID As Integer = cmd.ExecuteScalar()
        TxtLastQuoteID.Text = lastID.ToString()
        Conn.Close()
    End Using
Catch ex As Exception
    MsgBox(ex.Message)
End Try

Notice also that is a very bad thing to keep a global connection object. You don't need that because ADO.NET implements Connection Pooling that makes opening a connection a very fast operation. Instead keeping a connection around requires a lot of effort to work correctly around it

Finally you can look here to better understand the difference between SCOPE_IDENTITY and @@IDENTITY and why is usually better to use the first one.

Doesn't ExecuteScalar just return a 0 indicating the query executed successfully? Isn't an OUTPUT parameter required? — HardCode, Feb 15 '19 at 16:14
ExecuteScalar will return the value of the first column of the first row from the last SELECT statement executed. In this case it is the SELECT SCOPE_IDENTITY(). You don't need an output parameter for this — Steve, Feb 15 '19 at 17:59

get last inserted primary key record in database

1 Answers1