AutoNumber

[MNovy]

Hi,

I would like to add a new column in my MDB file with auto number, but I could not figure out
how to do that. Google was not helpful.


Currently I have a method which adds a new column to an existing MDB file and a given
table. Maybe my concept is wrong? Please have a look in my code:

                string strDatabase = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + fileName;
                string strStatement = "ALTER TABLE "+tableName+" ADD COLUMN "+ columnName + " " +columnType +";";

                OleDbConnection conn = new OleDbConnection(strDatabase);
                OleDbCommand cmd = new OleDbCommand(strStatement);
                cmd.Connection = conn;

                try
                {
                    conn.Open();
                    
                    cmd = new OleDbCommand(strStatement);
                    cmd.Connection = conn;
                    cmd.ExecuteReader();
                }
                catch { }
                finally
                {
                    conn.Close();
                }

[Thread1]

try to add IDENTITY to field's description:

ALTER TABLE [<table name>] ADD COLUMN [<column name>] INT IDENTITY;

string strDatabase = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + fileName;
            string strStatement = string.Format("ALTER TABLE [{0}] ADD COLUMN {1} INT IDENTITY", tableName, columnName);

            using (OleDbConnection conn = new OleDbConnection(strDatabase))
            {
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = strStatement;
                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        conn.Close();
                    }
                    catch (Exception ex)
                    {
                    }                    
                }                
            }

hth

[MNovy]

hth

unfortunately not :-(
Something is happening with the file since the edit date changes,
but there is no column added with your additional option.

[Thread1]

there must be an error, it just doesn't show up because there is nothing in the catch block. try to put something in there, a message box i guess..

[MNovy]

you're right. There is only one AutoNum column allowed per table.
I deleted a manually inserted AutoNum column and run the code again - with success,.


Thanks four your quick help.