Solved

c# msaccess return newly inserted rows id

Posted on 2014-03-14
4
240 Views
Last Modified: 2014-03-25
Hi

I have a networked database, with a number of users


I have a situation where I need to insert a record into a table, and return the autonumber ID generated by access, so that the user can add records to another table using the id from the first table as the foreign key.

I have used the following sql statement

"Select @@Identity from " + table;

which works most times, however on the odd occasion it appears to be returning either nothing or the id from the previous record.

Is there any way to ensure that the query returns the correct value each time?

here is my code to insert data

  public int InsertData(string table, string[] fields, string[] values)
        {
            int id = 0;
            OleDbConnection con = new OleDbConnection(connectionstring);
            string query2 = "Select @@Identity from " + table;
            con.Open();
            string myFields = "";
            string myValues = "";

            foreach (string f in fields)
            {
                myFields += f + ", ";
                myValues += "?,";

            }

            if (myFields.Length > 0)
            {
                myFields = myFields.Substring(0, myFields.Length - 2);
                myValues = myValues.Substring(0, myValues.Length - 1);
            }

            OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("INSERT INTO " + table + " (" + myFields + ") Values (" + myValues + ") ", con);

            foreach (string v in values)
            {
                cmd.Parameters.AddWithValue("?",v);
            }

            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            cmd.CommandText = query2;
            id = Convert.ToInt32(cmd.ExecuteScalar());
            
            con.Close();


            return id; 
        }

Open in new window

0
Comment
Question by:cycledude
  • 3
4 Comments
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 500 total points
ID: 39930296
Hi cycledude;

According to this Microsoft article you will need two OleDbCommand connections. The article shows how to do this.

HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual C# .NET
0
 

Author Comment

by:cycledude
ID: 39930406
thanks Fernando

Any ideas how I can get that to work with my code?
0
 

Accepted Solution

by:
cycledude earned 0 total points
ID: 39941813
in the end I had to use a timestamp when entering the row, then use a while loop to repeat the process of acquiring the id every 1 second until the correct id was found.
0
 

Author Closing Comment

by:cycledude
ID: 39952707
thanks for the assist
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

920 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

11 Experts available now in Live!

Get 1:1 Help Now