dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Thread: AutoNumber

  1. #1
    Join Date
    Jul 2008
    Location
    Germany
    Posts
    210

    AutoNumber

    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:

    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();
                    }

  2. #2
    Join Date
    Jan 2003
    Location
    7,107 Islands
    Posts
    2,487

    Re: AutoNumber

    try to add IDENTITY to field's description:

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


    Code:
    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
    Busy

  3. #3
    Join Date
    Jul 2008
    Location
    Germany
    Posts
    210

    Re: AutoNumber

    Quote Originally Posted by Thread1 View Post
    hth

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

  4. #4
    Join Date
    Jan 2003
    Location
    7,107 Islands
    Posts
    2,487

    Re: AutoNumber

    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..
    Busy

  5. #5
    Join Date
    Jul 2008
    Location
    Germany
    Posts
    210

    Re: AutoNumber

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)