Access 主要的地方

来源：互联网发布：爱乐居李静网络推广编辑：程序博客网时间：2024/05/18 09:20

SQL中的保留关键字及INSERT INTO 语句的语法错误

我一直使用数据库SQL Server开发ASP.net程序，但是今天却被ACCESS数据库折磨了。

使用ASP.net向ACCESS数据库中插入数据，相当简单的代码。

一开始是出现“操作必须使用一个可更新的查询”的错误，将存放ACCESS数据库的目录(需要对ACCESS数据库所在的目录)加上“Network”和“Network Service”的写入和修改的权限，解决！

随后的错误"INSERT INTO 语句的语法错误"就让我摸不着头脑了，Google了一些资料后才发现原来是我的ACCESS数据表的字段名使用了SQL的保留关键字。

将插入数据的SQL语句更改为："insert into users([username],[desc]) values(‘"+username+ "’,'"+desc+"’)"，即解决"INSERT INTO 语句的语法错误"的问题.

将保留关键字加上中括号([]).

Asp.net连接Access并更新数据库的代码

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace User
{
/// <summary>
/// xr 的摘要说明。
/// </summary>
public class CreateUser : System.Web.UI.Page
{
protected System.Web.UI.WebControls.TextBox TBUsername;
protected System.Web.UI.WebControls.TextBox TBDesc;
protected System.Web.UI.WebControls.Label LabelState;
protected System.Web.UI.WebControls.Button BTNCreateUser;
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
}
private void BTNCreateUser_Click(object sender, System.EventArgs e)
{
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" + Server.MapPath("mdb/database.mdb");
string username = this.TBUsername.Text.ToString();
string desc = this.TBDesc.Text.ToString();
string strSql = "insert into users([username],[desc]) values(‘"+username+ "’,'"+desc+"’)";
System.Data.OleDb.OleDbConnection oleDbConnection = new System.Data.OleDb.OleDbConnection(ConnStr);
System.Data.OleDb.OleDbCommand oleDbCommand = new System.Data.OleDb.OleDbCommand(strSql, oleDbConnection);
try
{
oleDbCommand.Connection.Open();
LabelState.Text ="Access数据库连接状态：" + oleDbConnection.State;
oleDbCommand.ExecuteNonQuery();
}
catch(Exception ex)
{
Response.Write(ex.Message.ToString());
}
finally
{
oleDbCommand.Connection.Close();
}
}
#region Web 窗体设计器生成的代码
//代码省略
#endregion
}

以下为SQL中的保留关键字，大家在设计数据表时，尽量不要使用如下单词作为字段名称：

SQL中的保留字

action add aggregate all
alter after and as
asc avg avg_row_length auto_increment
between bigint bit binary
blob bool both by
cascade case char character
change check checksum column
columns comment constraint create
cross current_date current_time current_timestamp
data database databases date
datetime day day_hour day_minute
day_second dayofmonth dayofweek dayofyear
dec decimal default delayed
delay_key_write delete desc describe
distinct distinctrow double drop
end else escape escaped
enclosed enum explain exists
fields file first float
float4 float8 flush foreign
from for full function
global grant grants group
having heap high_priority hour
hour_minute hour_second hosts identified
ignore in index infile
inner insert insert_id int
integer interval int1 int2
int3 int4 int8 into
if is isam join
key keys kill last_insert_id
leading left length like
lines limit load local
lock logs long longblob
longtext low_priority max max_rows
match mediumblob mediumtext mediumint
middleint min_rows minute minute_second
modify month monthname myisam
natural numeric no not
null on optimize option
optionally or order outer
outfile pack_keys partial password
precision primary procedure process
processlist privileges read real
references reload regexp rename
replace restrict returns revoke
rlike row rows second
select set show shutdown
smallint soname sql_big_tables sql_big_selects
sql_low_priority_updates sql_log_off sql_log_update
sql_select_limit
sql_small_result sql_big_result sql_warnings straight_join
starting status string table
tables temporary terminated text
then time timestamp tinyblob
tinytext tinyint trailing to
type use using unique
unlock unsigned update usage
values varchar variables varying
varbinary with write when
where year year_month zerofill