CODEWITHSUNDEEP
c#ms-accessauto-incrementwindows-forms-designerautonumber
Shrenik
Shrenik

Reputation: 1

INSERT statement for AutoNumber field text box (Access)

I have a Windows form which has the following textboxes, and is displayed in gridview. The application is connected to an Access database using C#.

Companyid (autonumber)
CompanyName(shorttext)
TypeofCompany(shorttext)

How can I generate an AutoNumber field to update itself with an INSERT statement?

e.g., C001,C002,C003,C004.....

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Shrenik_Salguna\Desktop\final.accdb;
        Persist Security Info=False;");
        con.Open();

        OleDbCommand cmd = new OleDbCommand(@"INSERT INTO info
                     ([Name of Company], [Type of Company]) VALUES('"+textBox1.Text+"','" + textBox2.Text + ")", con);
        cmd.ExecuteNonQuery();
        con.Close();

Upvotes: 0

Views: 2066

Answers (2)

Jim Lahman
Jim Lahman

Reputation: 2757

Here is how I created a table with an autoNumber field:

        ADOX.Catalog cat = new ADOX.Catalog();
        ADOX.Table table = new ADOX.Table();
        ADOX.Key tableKey = new Key();
        ADOX.Column col = new Column();

        String SecurityDBConnection = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\\{1};", value, SecurityDBName);

        // Define column with AutoIncrement features
        col.Name = "ID";
        col.Type = ADOX.DataTypeEnum.adInteger;


        // Define security table
        table.Name = "Security";
        table.Columns.Append(col);    // default data type is text[255]
        table.Columns.Append("Username", ADOX.DataTypeEnum.adVarWChar, 255);
        table.Columns.Append("Password", ADOX.DataTypeEnum.adVarWChar, 255);
        table.Columns.Append("Engineer", ADOX.DataTypeEnum.adBoolean);
        table.Columns.Append("Default", ADOX.DataTypeEnum.adBoolean);

        tableKey.Name = "Primary Key";
        tableKey.Columns.Append("ID");
        tableKey.Type = KeyTypeEnum.adKeyPrimary;

        // Add security table to database
        cat.Create(SecurityDBConnection);

        // Must create database file before applying autonumber to column
        col.ParentCatalog = cat;
        col.Properties["AutoIncrement"].Value = true;

        cat.Tables.Append(table);

        // Now, try to connect to cfg file to verify that it was created successfully
        ADODB.Connection con = cat.ActiveConnection as ADODB.Connection;
        if (con != null) con.Close();

Here is the code to insert a record into the table with an autoNumber field. Note, the autoNumber field is NOT specified in the insert statement and the field names are bracketed.

        public void WriteRecord(String sUsername, String sPassword, Boolean boEngineerRole, Boolean boDefaultUser)
       {
        String InsertQry = "Insert into Security([Username], [Password], [Engineer], [Default]) "
            + "values(@UserName, @Password, @Engineer, @Default)";
        using (OleDbConnection connection = new OleDbConnection(SecurityDBConnection))
        {
           using (OleDbCommand command = new OleDbCommand(InsertQry, connection))
           {
               command.CommandType = CommandType.Text;
               command.Parameters.AddWithValue("@UserName", sUsername);
               command.Parameters.AddWithValue("@Password", sPassword);
               command.Parameters.AddWithValue("@Engineer", boEngineerRole);
               command.Parameters.AddWithValue("@DefaultUser", boDefaultUser);
               connection.Open();
               command.ExecuteNonQuery();
           }
        }
    }

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123549

If [Companyid] is an AutoNumber field in the Access table then you don't include that field in your INSERT statement because the Access Database Engine takes care of that for you.

You could conceivably create your own "auto-increment" field containing "C001", "C002", etc., but if you already have a true AutoNumber field then why would you bother? You already have a unique column for each row on the table, and if you wanted to derive an identifier like "Cnnn" then you could easily do that in C# by just using something equivalent to this VBA expression:

"C" & Format([Companyid], "000")

Upvotes: 2

Related Questions