另外对你的语句有一点意见.(关系不大) 1.判断用户名与密码处 If rs.EOF And rs.BOF Then 只要写 If rs.EOF Then 就可以了 2.对msgbox的选择判断 zhen = MsgBox("确认修改密码吗?", vbOKCancel, "修改密码框") if zhen = vbOK Then 可以写成 If MsgBox("确认修改密码吗?", vbOKCancel, "修改密码框")= vbOK Then 这样可以少声明一个变量
Private Sub Command1_Click() Set db = New ADODB.Connection Set rs = New ADODB.Recordset path = App.path & "\jxcun.mdb" connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Persist Security Info=False" db.ConnectionString = connstr db.Open rs.Open "select name from usera where name='" & Combo1.Text & _ "' and password='" & Text1.Text & "'", db, adOpenStatic, adLockReadOnly If rs.EOF And rs.BOF Then MsgBox "用户名与密码不符,请重新输入", , "修改密码框" Else If MsgBox("确认修改密码吗?", vbOKCancel, "修改密码框") = vbOK Then rs.Close db.Close db.Open sql = "UPDATE usera SET password='" & Text2.Text & "' WHERE name='" & Combo1.Text & "'" 'rs.Open sql, db, adOpenDynamic, adLockOptimistic Set rs = db.Execute(sql) End If End If rs.Close db.Close End Sub 这两种方法都用了,还是不行呀,数据库是ACSSES2000转的老版本
把更新语句改成存储过程: 变量用参数传递 再就是更新数据不需要用到记录集 CREATE PROCEDURE UpdateUsera @password as varchar, @name as varchar AS UPDATE usera SET password = @password WHERE name = @name GOSet db = New ADODB.Connection Set cmd = New ADODB.Command path = App.path & "\jxcun.mdb" connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Persist Security Info=False" db.ConnectionString = connstr db.OpenSet cmd.ActiveConnection = db cmd.CommandText = "UpdateUsera" '存储过程名 cmd.CommandType = adCmdStoredProc '命令类型 cmd.Parameters.Refresh '传递参数值 cmd.Parameters("@password").Value = Trim(Text1.Text) cmd.Parameters("@name").Value = Trim(Combo1.Text) cmd.Execute
我还没有找到哪里有问题,不过你可以换一下:If MsgBox("确认修改密码吗?", vbOKCancel, "修改密码框") = vbOK Then rs.Fields("password").Value = Text3.Text rs.Update end if
Private Sub Command1_Click() Set db = New ADODB.Connection Set rs = New ADODB.Recordset path = App.path & "\jxcun.mdb" with db .provider="Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source=" & path .Open end with rs.Open "select name from usera where name='" & Combo1.Text & "' and password='" & Text1.Text & "'", db, adOpendynamic, adLockoptimistic If rs.EOF Then MsgBox "用户名与密码不符,请重新输入", , "修改密码框" Elseif MsgBox("确认修改密码吗?", vbOKCancel, "修改密码框") = vbOK Then sql = "UPDATE usera SET password='" & Text2.Text & "' WHERE name='" & Combo1.Text & "'" db.Execute(sql) End If rs.Close db.Close End Sub 试试看
1.判断用户名与密码处 If rs.EOF And rs.BOF Then 只要写 If rs.EOF Then 就可以了
2.对msgbox的选择判断
zhen = MsgBox("确认修改密码吗?", vbOKCancel, "修改密码框")
if zhen = vbOK Then
可以写成
If MsgBox("确认修改密码吗?", vbOKCancel, "修改密码框")= vbOK Then
这样可以少声明一个变量
另外更新时不用打开记录集.用db.execute(strsql)也可以的
Set db = New ADODB.Connection
Set rs = New ADODB.Recordset
path = App.path & "\jxcun.mdb"
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Persist Security Info=False"
db.ConnectionString = connstr
db.Open
rs.Open "select name from usera where name='" & Combo1.Text & _
"' and password='" & Text1.Text & "'", db, adOpenStatic, adLockReadOnly
If rs.EOF And rs.BOF Then
MsgBox "用户名与密码不符,请重新输入", , "修改密码框"
Else
If MsgBox("确认修改密码吗?", vbOKCancel, "修改密码框") = vbOK Then
rs.Close
db.Close
db.Open
sql = "UPDATE usera SET password='" & Text2.Text & "' WHERE name='" & Combo1.Text & "'"
'rs.Open sql, db, adOpenDynamic, adLockOptimistic
Set rs = db.Execute(sql)
End If
End If
rs.Close
db.Close
End Sub
这两种方法都用了,还是不行呀,数据库是ACSSES2000转的老版本
变量用参数传递
再就是更新数据不需要用到记录集
CREATE PROCEDURE UpdateUsera
@password as varchar,
@name as varchar
AS
UPDATE usera
SET password = @password
WHERE name = @name
GOSet db = New ADODB.Connection
Set cmd = New ADODB.Command
path = App.path & "\jxcun.mdb"
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Persist Security Info=False"
db.ConnectionString = connstr
db.OpenSet cmd.ActiveConnection = db
cmd.CommandText = "UpdateUsera" '存储过程名
cmd.CommandType = adCmdStoredProc '命令类型
cmd.Parameters.Refresh
'传递参数值
cmd.Parameters("@password").Value = Trim(Text1.Text)
cmd.Parameters("@name").Value = Trim(Combo1.Text)
cmd.Execute
rs.Fields("password").Value = Text3.Text
rs.Update
end if
Set db = New ADODB.Connection
Set rs = New ADODB.Recordset
path = App.path & "\jxcun.mdb"
with db
.provider="Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & path
.Open
end with
rs.Open "select name from usera where name='" & Combo1.Text & "' and password='" & Text1.Text & "'", db, adOpendynamic, adLockoptimistic
If rs.EOF Then
MsgBox "用户名与密码不符,请重新输入", , "修改密码框"
Elseif MsgBox("确认修改密码吗?", vbOKCancel, "修改密码框") = vbOK Then
sql = "UPDATE usera SET password='" & Text2.Text & "' WHERE name='" & Combo1.Text & "'"
db.Execute(sql)
End If
rs.Close
db.Close
End Sub
试试看
错误提示:实时错误‘-2147217900(80040e14)
update 语句的语法错误?
你需要改结构,不要使用这种容易冲突的字段名。
另外,建议你用command对象来执行命令。