VB.net getting last inserted record

.NET
1

Hi folks,

I know this is a common request, I’ve googled loads but found lots of complicated code for such a simple task.

My basic insert code is below, can someone tell me how/ where I can modify it to get the last ID? Many thanks

 comm = New SqlCommand("INSERT INTO documents(documentName, documentCat, documentDescription,documenttype,documentsiteid)  VALUES (@xx, @xx, @xx, @xx,@xx)", conn)

	
        ' Add command parameters etc

        comm.Parameters.Add("@xx", System.Data.SqlDbType.Nvarchar)
        comm.Parameters("@xx").Value = FileName

'execute reader

catreader = comm.ExecuteReader()

While catreader.Read()

’ populate controls

End While

2

Looking at your code I presume your ID field auto increases, if so the last inserted record is the one with the highest ID value and the tsql for getting it will be

select max(ID) from documents

3

use SCOPE_IDENTITY()

4

Thanks for your reply.

I’m trying to use max id. but can’t seem to output the number in the label. I dont get an error it just doesn’t show, can anyone help?

'get last id query
comm = New SqlCommand(“select MAX(subid) as SUB from subpages”, conn)

’ Execute the subjects command

getid = comm.ExecuteReader()

'output in label

success.Visible = true
success.Text = getid.Item(“sub”)

5

As stated above. You want to be using the following command:

select SCOPE_IDENTITY();

After your insert

6

Thanks for your reply, I;ve used scope identity, but can;t seem to output the result, my code is below, can you please help?

Thanks again

comm = New SqlCommand(“INSERT INTO subpages(subpageid, subbody, subsummary, subtitle, subembed)VALUES (@pageid, @body, @summary,@title, 1)SELECT SCOPE_IDENTITY()”, conn)

’ Add command parameters|
comm.Parameters.Add(“@pageid”, System.Data.SqlDbType.Int)
comm.Parameters(“@pageid”).Value = queryvalue
comm.Parameters.Add(“@title”, System.Data.SqlDbType.NText)
comm.Parameters(“@title”).Value = Trim(title.text)
comm.Parameters.Add(“@summary”, System.Data.SqlDbType.NText)
comm.Parameters(“@summary”).Value = Trim(summary.value)
comm.Parameters.Add(“@body”, System.Data.SqlDbType.NText)
comm.Parameters(“@body”).Value = Trim(body.value)

’ Enclose database code in Try-Catch-Finally
Try

’ Open the connection
conn.Open()
’ Execute the command
comm.ExecuteScalar()

'output link text
success.Visible = true
success.Text = comm.Item(“subid”)

7

Are you showing all the codes or you’re skipping some, cos I can’t see where you’re actually reading the data with the reader

Yeah, like pufa said you can also use scope_identity. In this case you add it to your insert statement

comm = New SqlCommand(“INSERT INTO documents(documentName, documentCat, documentDescription,documenttype,documentsiteid) VALUES (@xx, @xx, @xx, @xx,@xx); SELECT CAST(scope_identity() AS int)”, conn)

when you execute the command, the last id is returned so

conn.Open()
getid = Convert.ToInt32(cmd.ExecuteScalar())

8

Thanks got it, works great.

Thanks alot for everyones help