紧急求教! ASP+access的一个错误.

[zhangxhsj]

我对ACCESS2000数据库执行一个SQL语句,是insert子句,该语句是正确的(已测试),
但用conn.execute sql执行时,出现以下错误:

[Microsoft][ODBC Microsoft Access Driver] 操作必须使用一个可更新的查询。
/bulletin/change.asp, 第 38 行

请问这是怎么回事情?如何解决?
第一个解决者重奖!!

[hfkj]

1.有可能是你的数据库联接语句有问题
2。你把conn.execute sql
改成rs.open sql,conn,1,3

[mygia]

可能有不允许为空的你插空的，或者数据类型不合。

[zhangxhsj]

这样也不行,还是一样的问题.

[zhangxhsj]

mygia(又帅又可爱):
我把生成的sql语句用response.write打出来了
然后拿到access中执行都是成功的!

纳闷死了!

[twtetgso]

rs.open sql,conn,1,3

[baby21st]

看看你的类型还匹配
除了INT型外要用''

[jimohongqilishijin]

代码

[zhangxhsj]

表结构:
name 文本255,
linkurl 文本255,
placard 备注,
addtime 日期,
reveal 数字

SQL语句如下:
insert into data(name,linkurl,placard,addtime,reveal) values ('234132412','null','341234213423',#2003-8-5 17:34:04#,0)

[zhangking]

可能是: 表需要建立一个关键字索引.

[zhangxhsj]

连接代码:
Set conn = Server.CreateObject("ADODB.Connection")
connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(""&db&"")
conn.open connstr

SQL执行代码:
sub add()
'on error resume next
dim name,linkurl,placard,nowtime,reveal,sql
name=cstr(Checkstr(Request.Form("name")))
if name="" then
response.write("<script>alert('标题不能为空！');history.go(-1)</script>")
response.end
end if
linkurl=cstr(Checkstr(Request.Form("linkurl")))
if linkurl="" then linkurl="null"
placard=cstr(Checkstr(Request.Form("placard")))
if placard="" then placard="null"
reveal=Request.Form("reveal")
if reveal="" then reveal=0
nowtime=now()
sql="insert into data(name,linkurl,placard,addtime,reveal) values ('" & name & "','" & linkurl & "','" & placard & "',#" & nowtime & "#," & reveal & ")"
response.write sql
'response.end
conn.execute sql
if err.number<>0 then
err.clear
msg="数据库操作失败，请以后再试！<br>"&err.Description
yn=0
url="add.asp"
else
msg="公告添加成功！"
yn=1
url="mag.asp"
end if
call dbclose()
call info(msg,yn,url)
end sub

[zhangxhsj]

上述代码曾经成功过，可后来怎么也不行了,很奇怪!

请有经验者给予提示.

[heimao111]

将#改成'呢

[sbje981]

可以#改成'因为你取得本机的时间

[zhangxhsj]

将#改成'同样不成功!

[bineon]

你把时间换成‘now（）’试试看？

[mzli]

我的排错方法是
第一只插入name，试试能不能成功，然后依次插入，来排除错误。
看你的错误，可能是数据类型不匹配吧#" & nowtime & "# 换成 now()试试
比较笨的方法，呵呵

[zhangxhsj]

我检查过了,即使每次只插一个字段,也有同样的问题!

应该是数据库写权限的问题,我在IIS管理器中设置了目录和文件的写入权限,
可同样不行!!

我晕了!救我啊。

[rainingboynumber]

试着每个字段分别进行插入操作，strSql="insert into data(name) values('"&name"')"
第2： Access 中是否允许插入null类型字段，请查询一下，我只知道SQL Server 支持null
类型。试试看！

[zhangxhsj]

RESOLUTION
This error is typically encountered when your script attempts to perform an UPDATE or some other action that alters the information in the database. This error occurs because ADO is unable to write to the database for one of the following reasons:


The most common reason is that the Internet Guest account (IUSR_MACHINE), which is by default part of the "Everyone" group, does not have Write permissions on the database file (.mdb). To fix this problem, use the Security tab in Explorer to adjust the properties for this file so that the Internet Guest account has the correct permissions.

NOTE: When using Microsoft Access databases with ADO, it is also necessary to give the Internet Guest account Write permissions on the directory containing the .mdb file. This is because Jet creates an .ldb file to handle database locking. You may also need to give read/write permission on the "Temp" folder because Jet may create temporary files in this directory.
A second cause of this error is that the database was not opened with the correct MODE for writing. If you perform the Open on the Connection object, you use the Mode property to indicate the permissions on the connection as shown here:
SQL = "UPDATE Products Set UnitPrice = 2;"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Mode = 3 '3 = adModeReadWrite
Conn.Open "myDSN"
Conn.Execute(SQL)
Conn.Close


NOTE: By default, the MODE is set to 0(adModeUnknown), which generally allows updates.
Another cause of this error is that the "Read Only" setting may be checked in the Options page for this DSN in the ODBC Manager.
The last issue and work around pertains to any SQL data source. The error can be caused by SQL statements that violate referential integrity of the database. Here are a few of the most common queries that fail:

The simplest group to deal with are those you cannot change: crosstab, SQL pass-through, union, or update (or make-table) action queries that have UniqueValue properties set to Yes.


Another very common cause is when the join includes linked ODBC tables that do not have unique indexes. In this case, there is no way for SQL to guarantee that records are unique in a table that has fields whose value will change with the query.


One cause does have a robust workaround. If you try to update a join field on the "one" side of a "one-to-many" query it will fail unless you turn on cascading updates. This way, you delegate referential integrity to the JET engine.