Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Easy way to obtain new Autonumber ID Field from newly created row (ADO.Net in C#)

Posted on 2006-06-25
6
Medium Priority
?
320 Views
Last Modified: 2011-04-14
Hi all,

I have the code below which adds a new record to my database and then updates it.  The database is Microsoft Access and i'm using the OleDbAdapters in ADO.Net with a disconnected dataset.  I want to grab the "ProjectID" field which is an Autonumber field that isn't created until you "update" the dataset.  Is there an easy way to get it without reopening the whole database and doing an SQL search for the record I just created using the Project Title?  Maybe some sort of "newid" method or some such?

Here is the code I have now:

OleDbDataAdapter daOurAdapter;
            DataSet dsResults;

            //do a search
            this.mstrSQL = "SELECT * FROM Projects";

            this.instantiateDatabase();

            try
            {
                //create an adapter to interact with the database
                daOurAdapter = new OleDbDataAdapter(this.mstrSQL, this.dbConnect);

                //make our dataset object an actual dataset
                dsResults = new DataSet();

                //now fill our dataset object with data (if any)
                daOurAdapter.Fill(dsResults, "Projects");

                //we now have a DataSet object that contains any results that have been returned by our query
                //create a DataTable object representing the table returned
                DataTable dtProjects = dsResults.Tables["Projects"];

                DataRow drProjectResults = dtProjects.NewRow();                
               
                drProjectResults["CustomerID"] = cboCustomers.SelectedValue;
                drProjectResults["ProjectTitle"] = (string)txtProjectTitle.Text;
                drProjectResults["ProjectNotes"] = (string)txtProjectNotes.Text;
                if (txtRender1Loc.Text != "")
                    drProjectResults["RenderLocation1"] = (string)txtRender1Loc.Text;
                if (txtRender1Desc.Text != "")
                    drProjectResults["RenderDescription1"] = (string)txtRender1Desc.Text;
                if (txtRender2Loc.Text != "")
                    drProjectResults["RenderLocation2"] = (string)txtRender2Loc.Text;
                if (txtRender2Desc.Text != "")
                    drProjectResults["RenderDescription2"] = (string)txtRender2Desc.Text;
                if (txtRender3Loc.Text != "")
                    drProjectResults["RenderLocation3"] = (string)txtRender3Loc.Text;
                if (txtRender3Desc.Text != "")
                    drProjectResults["RenderDescription3"] = (string)txtRender3Desc.Text;
                if (txtRender4Loc.Text != "")
                    drProjectResults["RenderLocation4"] = (string)txtRender4Loc.Text;
                if (txtRender4Desc.Text != "")
                    drProjectResults["RenderDescription4"] = (string)txtRender4Desc.Text;
                if (txtRender5Loc.Text != "")
                    drProjectResults["RenderLocation5"] = (string)txtRender5Loc.Text;
                if (txtRender5Desc.Text != "")
                    drProjectResults["RenderDescription5"] = (string)txtRender5Desc.Text;
                drProjectResults["Status"] = 0;
                drProjectResults["StartDate"] = (string)txtStartDate.Text;
                if (txtDueDate.Text != "")
                    drProjectResults["DueDate"] = (string)txtDueDate.Text;                

                //push all changes to the database
                dtProjects.Rows.Add(drProjectResults);



                //FIGURE OUT THE NEW PROJECT ID HERE AND CREATE THE DIRECTORY WITH THE CUSTOMER ID, THEN
                //CREATE A DIRECTORY WITH THE PROJECT ID

               // MessageBox.Show("New project id = " + drProjectResults["ProjectID"]);  <-- this doesn't work

                if (!dsResults.HasErrors)
                {
                    // Set up insert, update, and delete commands
                    OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(daOurAdapter);

                    //setup escape characters
                    cmdBuilder.QuotePrefix = "[";
                    cmdBuilder.QuoteSuffix = "]";

                    daOurAdapter.Update(dsResults, "Projects");

                    dsResults.AcceptChanges();
                }
            }
            catch (Exception ex)
            {
                string strMsg = "Error Message = " + ex.Message;
            }

You can see in the comments where I want to be able to figure out the new project ID at.  Any help would be appreciated, even if it is to tell me to just subsequently search for the Project based on its title which is what i'm implementing temporarily.

Thanks,
Nathan
0
Comment
Question by:ironwill96
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 2

Author Comment

by:ironwill96
ID: 16979094
That ScopeID stuff didn't work for me and is also using SQL-based instead of OleDB-based solutions.  Here is how I am now getting the ProjectID, I just want maybe a faster way, but perhaps this is the best solution possible.

(Add this code right after what I have in my first post):

 try
            {

                //this.mstrSQL = "SELECT ProjectID FROM Projects WHERE (ProjectID = SCOPE_IDENTITY())";  <-- that doesnt work, OleDB doesnt understand the SCOPE_IDENTITY() operator
                this.mstrSQL = "SELECT * FROM Projects WHERE CustomerID = " + cboCustomers.SelectedValue + " AND ProjectTitle = '" + txtProjectTitle.Text + "'";
                this.openDatabase();
                OleDbCommand myCmd = new OleDbCommand(this.mstrSQL, this.dbConnect);
                OleDbDataReader myReader = myCmd.ExecuteReader();
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        //we found a value, grab the ProjectID
                        string temp = (string)myReader["ProjectTitle"];
                        this.mintProjectID = (int)myReader["ProjectID"];
                        break;
                    }
                }
                else
                {
                    //we didnt find any values, this is an issue, abort
                    MessageBox.Show("Unable to locate ProjectID from newly created record, aborting.", "Data Value Error");
                    myReader.Close();
                    return;
                }
                myReader.Close();
                this.closeDatabase();
            }
            catch (Exception exp)
            {
                //something went wrong, abort                
                this.closeDatabase();
                MessageBox.Show("Exception thrown during attempt to obtain ProjectID, aborting.", "Fatal Data Error");
                return;
            }

Thanks,
Nathan
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16979411
//this.mstrSQL = "SELECT ProjectID FROM Projects WHERE (ProjectID = SCOPE_IDENTITY())";  <-- that doesnt work, OleDB doesnt understand the SCOPE_IDENTITY() operator

Scope_Identity is purely MSSQL Server. in Access, Mysql and SQL Server the @@IDENTITY should work however:
this.mstrSQL = "SELECT ProjectID FROM Projects WHERE (ProjectID =@@IDENTITY)"
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Author Comment

by:ironwill96
ID: 16979544
Angel,

When I do it that way:

 try
            {

                this.mstrSQL = "SELECT ProjectID FROM Projects WHERE (ProjectID =@@IDENTITY)";
                //this.mstrSQL = "SELECT * FROM Projects WHERE CustomerID = " + cboCustomers.SelectedValue + " AND ProjectTitle = '" + txtProjectTitle.Text + "'";
                this.openDatabase();
                OleDbCommand myCmd = new OleDbCommand(this.mstrSQL, this.dbConnect);
                OleDbDataReader myReader = myCmd.ExecuteReader();
                if (myReader.HasRows)
                {
                    while (myReader.Read())
                    {
                        //we found a value, grab the ProjectID
                        string temp = (string)myReader["ProjectTitle"];
                        this.mintProjectID = (int)myReader["ProjectID"];
                        break;
                    }
                }
                else
                {
                    //we didnt find any values, this is an issue, abort
                    MessageBox.Show("Unable to locate ProjectID from newly created record, aborting.", "Data Value Error");
                    myReader.Close();
                    return;
                }
                myReader.Close();
                this.closeDatabase();
            }
            catch (Exception exp)
            {
                //something went wrong, abort  
                string tempmsg = exp.Message;
                this.closeDatabase();
                MessageBox.Show("Exception thrown during attempt to obtain ProjectID, aborting.", "Fatal Data Error");
                return;
            }

It says "Syntax error (missing operator) in query expression '(ProjectID =@@IDENTITY)'".

Any idea why it doesnt like the syntax of that SQL?

Nathan
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 16979649
I used @@IDENTITY already with access database, however, I used it differently:
http://support.microsoft.com/default.aspx/kb/232144
so, only "SELECT @@IDENTITY", which returns the value, and then use that value to be put into the actual query.
Possibly that it has to be only that way, not directly part of the insert query itself...
0
 
LVL 2

Author Comment

by:ironwill96
ID: 16979869
Thanks, that should work ok for me.

Nathan
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

618 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