Change Primary key in DataTable

Hi,

I have following problem:there's a DataSet with a DataTable (with primary key[int]) that I just got from a DB. Now I need to locally create new DataRow with a valid primary key from DB. How do I best do this
I use 1.1

Answer this question

Change Primary key in DataTable

Sinisa
Thanks, AcceptChanges() is what I forgot to do on my solution

MarkR_OmniVue

I was working at this issue some days earlier. Probebly these lines of code would solve your problem;

using System;
using System.Data;
using System.Data.OleDb;

namespace ConsoleApplication30
{
class Class1
{
      // Create OleDbCommand for SELECT @@IDENTITY statement
      private static OleDbCommand cmdGetIdentity;

      [STAThread]
      static void Main(string[] args)
      {
         // Open Connection
         OleDbConnection cnJetDB = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourAccessDatabase");
         cnJetDB.Open();

         // If the test table does not exist then create the Table
         string strSQL;
         strSQL = "CREATE TABLE AutoIncrementTest " +
            "(ID int identity, Description varchar(40), " +
            "CONSTRAINT AutoIncrementTest_PrimaryKey PRIMARY KEY (ID))";

         // Command for Creating Table
         OleDbCommand cmdJetDB = new OleDbCommand(strSQL, cnJetDB);
         cmdJetDB.ExecuteNonQuery();

         // Create a DataAdaptor With Insert Command For inserting records
         OleDbDataAdapter oleDa = new OleDbDataAdapter("Select * from AutoIncrementTest", cnJetDB);

         // Command to Insert Records
         OleDbCommand cmdInsert = new OleDbCommand();
         cmdInsert.CommandText = "INSERT INTO AutoIncrementTest (Description) VALUES ( )";
         cmdInsert.Connection = cnJetDB;
         cmdInsert.Parameters.Add(new OleDbParameter("Description", OleDbType.VarChar, 40, "Description"));
         oleDa.InsertCommand = cmdInsert;

         // Create a DataTable
         DataTable dtTest = new DataTable();
         oleDa.Fill(dtTest);

         DataRow drTest;

         // Add Rows to the Table
         drTest = dtTest.NewRow();
         drTest["Description"] = "This is a Test Row 1";
         dtTest.Rows.Add(drTest);

         drTest = dtTest.NewRow();
         drTest["Description"] = "This is a Test Row 2";
         dtTest.Rows.Add(drTest);

         // Create another Command to get IDENTITY Value
         cmdGetIdentity = new OleDbCommand();
         cmdGetIdentity.CommandText = "SELECT @@IDENTITY";
         cmdGetIdentity.Connection = cnJetDB;

         // Delegate for Handling RowUpdated event
         oleDa.RowUpdated += new OleDbRowUpdatedEventHandler(HandleRowUpdated);

         // Update the Data
         oleDa.Update(dtTest);

         // Drop the table
         cmdJetDB.CommandText = "DROP TABLE AutoIncrementTest";
         cmdJetDB.ExecuteNonQuery();

         // Release the Resources
         cmdGetIdentity = null;
         cmdInsert = null;
         cmdJetDB = null;
         cnJetDB.Close();
         cnJetDB = null;
      }

      // Event Handler for RowUpdated Event
      private static void HandleRowUpdated(object sender, OleDbRowUpdatedEventArgs e)
      {
         if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert )
         {
            // Get the Identity column value
            e.Row["ID"] = Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString());
            System.Diagnostics.Debug.WriteLine(e.Row["ID"]);
            e.Row.AcceptChanges();
         }
      }
}
}

Change Primary key in DataTable

Answer this question