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.