vb.net get the last ID and display it

sql-server vb.net scope-identity
user3105451 picture user3105451 · Jan 10, 2014 · Viewed 9.5k times · Source

i need to get the last ID from a table and display it. I've tried Scope_Identity() but somehow i could not manage to get it. the message box shows nothing.. it's empty. here is my current code:

Try
  myConn.ConnectionString = "Data Source=192.168.2.222;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=sa;"
  myConn.Open()

  myCmd = New System.Data.SqlClient.SqlCommand("SELECT SCOPE_IDENTITY AS LastId FROM customers", myConn)
  oResult = myCmd.ExecuteScalar()

  If oResult IsNot Nothing Then
    MsgBox(oResult.ToString)
  Else
    MsgBox("No Record Found")
  End If

Catch ex As Exception
  MsgBox(ex.Message)
Finally
  myConn.Close()
End Try

Answer

Al-3sli picture Al-3sli · Jan 10, 2014

If your I'd column is numeric you can use max :

 SELECT MAX(ID) AS LastId FROM customers

Related questions

Set a DateTime database field to "Now"

In VB.net code, I create requests with SQL parameters. It I set a DateTime parameter to the value DateTime.Now, what will my request look like ? UPDATE table SET date = "2010/12/20 10:25:00"; or UPDATE table SET date = GETDATE(); In the first …

Read More
How to execute an SSIS package from .NET?

I have a SSIS package that eventually I would like to pass parameters too, these parameters will come from a .NET application (VB or C#) so I was curious if anyone knows of how to do this, or better yet …

Read More
SqlDataAdapter.Fill method slow

Why would a stored procedure that returns a table with 9 columns, 89 rows using this code take 60 seconds to execute (.NET 1.1) when it takes < 1 second to run in SQL Server Management Studio? It's being run on the local machine so …

Read More