SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Jun 2006
    Posts
    250
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    VB.net getting last inserted record

    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. #2
    SitePoint Zealot chieftain's Avatar
    Join Date
    Feb 2005
    Location
    Michigan
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Guru pufa's Avatar
    Join Date
    Oct 2004
    Location
    Portugal, Lisboa
    Posts
    947
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    use SCOPE_IDENTITY()
    Ciao, Rui...

  4. #4
    SitePoint Addict
    Join Date
    Jun 2006
    Posts
    250
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Mentor NightStalker-DNS's Avatar
    Join Date
    Jul 2004
    Location
    Cape Town, South Africa
    Posts
    2,868
    Mentioned
    37 Post(s)
    Tagged
    0 Thread(s)
    As stated above. You want to be using the following command:

    select SCOPE_IDENTITY();

    After your insert

  6. #6
    SitePoint Addict
    Join Date
    Jun 2006
    Posts
    250
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SitePoint Zealot chieftain's Avatar
    Join Date
    Feb 2005
    Location
    Michigan
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SitePoint Addict
    Join Date
    Jun 2006
    Posts
    250
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks got it, works great.

    Thanks alot for everyones help


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •