Finding the autoid number of a sql insert query (.net frameowork)

is it possible to find the autoincrement id of a record that has been inserted without doing another call to the database. I need to be able to do this in

Here is an example:
The table consists of three columns; id (autoincrement), username, password

Dim oConn As sqlConnection
Dim oCmd As sqlCommand
Dim SQLQuery As String

SQLQuery = "INSERT INTO TB_Names "
SQLQuery = " (username, password) " & _
                   " Values('kevin','password')

oCmd = new SqlCommand(SQLQuery)
oCmd.Connection = oConn

surely there should be something like oCmd.max(id)
Who is Participating?
Programming_GalConnect With a Mentor Commented:

This one is in C#, should be able to convert easily. ;p

private int WriteToDB(string strName, string strType, ref byte[] Buffer)
      int nFileID = 0;

      // Create connection
      OleDbConnection dbConn = new OleDbConnection(GetConnectionString());

      // Create Adapter
      OleDbDataAdapter dbAdapt = new OleDbDataAdapter("SELECT * FROM tblFile", dbConn);
      // We need this to get an ID back from the database
      dbAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey;
      // Create and initialize CommandBuilder
      OleDbCommandBuilder dbCB = new OleDbCommandBuilder(dbAdapt);

      // Open Connection
      // New DataSet
      DataSet dbSet = new DataSet();
      // Populate DataSet with data
      dbAdapt.Fill(dbSet, "tblFile");

      // Get reference to our table
      DataTable dbTable = dbSet.Tables["tblFile"];

      // Create new row
      DataRow dbRow = dbTable.NewRow();

      // Store data in the row
      dbRow["FileName"] = strName;
      dbRow["FileSize"] = Buffer.Length;
      dbRow["ContentType"] = strType;
      dbRow["FileData"] = Buffer;

      // Add row back to table

      // Update data source
      dbAdapt.Update(dbSet, "tblFile");

      // Get newFileID
      if( !dbRow.IsNull("FileID") )
            nFileID = (int)dbRow["FileID"];    //<<<<<<<<This is the auto ID
      // Close connection

      // Return FileID
      return nFileID;
sybeConnect With a Mentor Commented:
Right after the insert do this:

sSQL = "Select @@IDENTITY"
oRS = oConn.Execute(sSQL)
iNewId = oRS(0).Value
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.