Solved

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

Posted on 2003-11-05
2
868 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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now