Get last id from SQL Server DB in Visual Basic

Question

I need to make a function that return the last inserted id, and the connection isn't working

Code:

Private Function lastId() As Integer
    Using conn As New SqlConnection("data source=localhost;User Id=root;database=farmacia")
        Dim query = "SELECT max(id_client) FROM clients"
        Dim cmd As New SqlCommand(query, conn)
        conn.Open()
        Return Convert.ToInt32(cmd.ExecuteScalar())
    End Using
End Function

Please do a little research. There is a function like this (indeed there are 3) in SQL-SERVER — Leandro Bardelli, Nov 13 '14 at 00:00
possible duplicate of [How to get last inserted id?](http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id) — Leandro Bardelli, Nov 13 '14 at 00:02
Seems pretty clear to me - your code isn't able to find the server with the provided connection string. Double check your connection string. — Tim, Nov 13 '14 at 00:38

score 0 · Answer 1 · answered Nov 13 '14 at 04:08

The problem is with your connection string you cannot connect to the database as specified so you should change the connection string :

you should specify AttachDbFilename,Initial Catalog for a proper connection

for example:

"Data Source=.\SQLEXPRESS;AttachDbFilename=" + constring + ";Initial        Catalog=mydatabaseName;Integrated Security=True;User Instance=True"

SQL Server Connection String Tags

Initial Catalog= Since there is only one database per file in VistaDB, there is no concept of an initial catalog.
User Id= All database files are fully available to VistaDB connections, there are no per user restrictions.
Trusted_Connection= All connections are trusted.
Integrated Security= No concept since all users have to be logged into a machine in order to reach the file.
Network Library= There is only one managed assembly for VistaDB, libraries are not used.
MultipleActiveResultSets= MARS is not supported at this time.
AttachDbFilename= The filename is part of the DataSource identifier.
Failover Partnering= There are no server components or mirroring in VistaDB. Asynchronous Processing= All connections are synchronous in VistaDB. Asynchronous operation is not supported.

For more details : http://www.gibraltarsoftware.com/vistadb/learn-more/tutorials/connection-strings

score 0 · Answer 2 · answered Nov 13 '14 at 04:20

Here is the connection string that you need to connect to the server. you need to write your SQL password in the connection string.

Private Function lastId() As Integer
    Using conn As New SqlConnection("Server=localhost;UserId=root;Password=myPassword;database=farmacia")
        Dim query = "SELECT max(id_client) FROM clients"
        Dim cmd As New SqlCommand(query, conn)
        conn.Open()
        Return Convert.ToInt32(cmd.ExecuteScalar())
    End Using
End Function

This query is okay for finding the latest inserted id

SELECT max(id_client) FROM clients

You were getting that error coz of the Connection string

Get last id from SQL Server DB in Visual Basic

2 Answers2