Solved

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

Posted on 2003-11-05
2
872 Views
Last Modified: 2007-12-19
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 asp.net.

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
oConn.Open()
oCmd.ExecuteNonQuery()
oCmd.Connection.Close()

surely there should be something like oCmd.max(id)
0
Comment
Question by:ipokkel
2 Comments
 
LVL 28

Assisted Solution

by:sybe
sybe earned 125 total points
ID: 9685277
Right after the insert do this:

sSQL = "Select @@IDENTITY"
oRS = oConn.Execute(sSQL)
iNewId = oRS(0).Value
oRS.Close
0
 
LVL 6

Accepted Solution

by:
Programming_Gal earned 125 total points
ID: 9685369
http://www.codeproject.com/aspnet/fileupload.asp

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
      dbConn.Open();
                              
      // 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
      dbTable.Rows.Add(dbRow);

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

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

      // Return FileID
      return nFileID;
}
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Bypass cross origin issues on development site 3 95
One field not displaying content (ASP Classic) 8 35
Display if field is NOT "" or NOT null 6 22
GitHub 1 10
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question