CODEWITHSUNDEEP
c#database
user3479219
user3479219

Reputation: 349

Can I re-use AutoNumber value during INSERT to populate another field? C# & Access Database

I have a table with componentID(AutoNumber) and componentCode(string) which is a combination of prefix letter and componendID. I want to get the componentID and add prefix and write it to componentCode.

ie;
ComponentID: 123, ComponentCode: 10PT0123.
ComponentID: 124, ComponentCode: 10PT0124.
etc.

What will be the simplest way to do this with c#/access database? Thanks.

Upvotes: 1

Views: 78

Answers (1)

user3479219
user3479219

Reputation: 349

Eureka! I'm not sure if it's the simplest or the most elegant solution but works for me :)

        OleDbConnection cnJetDB = new OleDbConnection(strDB);

        OleDbCommand cmd = new OleDbCommand();
        cmd.CommandText = "INSERT INTO tableComponents (ComponentDesc) VALUES (@ComponentDesc)";
        cmd.Parameters.AddWithValue("@ComponentDesc", partDesc);

        cnJetDB.Open();

        cmd.Connection = cnJetDB;
        cmd.ExecuteNonQuery();

        cmd.CommandText = "SELECT @@IDENTITY";
        int newID = (int)cmd.ExecuteScalar();
        MessageBox.Show(newID.ToString());

        OleDbCommand cmd2 = new OleDbCommand();
        cmd2.CommandText = "UPDATE tableComponents SET ComponentCode=@CompCode WHERE ComponentID="+newID;
        cmd2.Parameters.AddWithValue("@CompCode", "PREFIX" + newID.ToString());

        cmd2.Connection = cnJetDB;
        cmd2.ExecuteNonQuery();

        cnJetDB.Close();

Upvotes: 1

Related Questions