自动编号字段文本框(Access)的INSERT语句
本文关键字:Access INSERT 语句 编号 字段 文本 | 更新日期: 2023-09-27 18:19:14
我有一个Windows窗体,其中有以下文本框,并显示在gridview。应用程序使用c#连接到Access数据库。
Companyid (autonumber)
公司名称(shorttext)
TypeofCompany (shorttext)
如何使用INSERT语句生成一个自动编号字段来更新它自己?
。, C002 C001 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();
如果[Companyid]是Access表中的AutoNumber
字段,则不要在INSERT语句中包含该字段,因为Access数据库引擎会为您处理该字段。
您可以创建自己的"自动递增"字段,其中包含"C001","C002"等,但如果您已经有一个真正的AutoNumber
字段,那么为什么还要麻烦呢?对于表上的每一行,你已经有了一个唯一的列,如果你想派生一个像"cnn"这样的标识符,那么你可以很容易地在c#中做到这一点,只需使用相当于这个VBA表达式的东西:
"C" & Format([Companyid], "000")
下面是我如何创建一个带有自动编号字段的表:
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();
下面是将一条记录插入到带有自动编号字段的表中的代码。注意,自动编号字段是在插入语句中指定的NOT,并且字段名被括起来。
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();
}
}
}