我一直使用数据库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