菜鸟请教：SQL server2000问题，急……
数据库中有表group 和userinfo等，在ASP中可分别对他们进行删除操作，但不能对userinfo进行插入操作，却可以对group插入。最初怀疑是代码问题，但相同的代码换到另外一台电脑上测试却无问题。
（说明：两台测试机的SQL server 2000不是同一个人所安装，另外数据库均是还原以前备份的，出问题的是Windows2000 perfessional，另外一台是2000server）
问题点数：80、回复次数：9Top
1 楼zjcxc（邹建）回复于 2006-07-04 20:04:20 得分 20
是还原得来的数据库?
怀疑:
1. 孤立用户导致, 查询分析器连接服务器, 转到你的数据库, 执行下面的语句看看:
exec sp_change_users_login 'Report'
如果上述查询返回了结果集, 则返回的就是孤立用户
孤立用户用下面的语句来修复:
EXEC sp_change_users_login 'Auto_Fix', '孤立用户名', NULL, '密码';
-- 此密码为要修复的用户对应的登录不存在时, sql自动登录, 为该登录指定的密码
Top
2 楼zjcxc（邹建）回复于 2006-07-04 20:04:36 得分 0
2. 如果不是孤立用户, 则检查用户的权限设置是否正确.Top
3 楼horsy（）回复于 2006-07-05 08:31:52 得分 0
谢谢楼上解答，但执行：
exec sp_change_users_login 'Report'
没返回任何结果~！Top
4 楼wangdehao（找找找(现在很幸福)）回复于 2006-07-05 08:36:27 得分 20
没返回说明不是孤立用户的问题，在查询分析器里直接运行那个sql，看报的什么错误。Top
5 楼wangdehao（找找找(现在很幸福)）回复于 2006-07-05 08:37:04 得分 0
用你asp程序所用的帐户执行sqlTop
6 楼zlp321002（Life Is Good,Let's Shine）回复于 2006-07-05 08:37:21 得分 20
1: 在不能对userinfo进行插入操作的服务器,连接数据库服务器,手工做插入操作,是否报错.
是不是权限问题?
2: 对报告错误的服务器,把错误信息贴出来看看.
是不是两台服务器环境不一样?Top
7 楼horsy（）回复于 2006-07-05 09:46:27 得分 0
ASP所用帐户是sa。不因该有权限的问题啊~！
在查询分析器中可以对该数据库插入。
在ASP中试图插入时也没有报错误。日志文件里也没有。
在错误服务器上没有对SQL进行什么设置~！安装好后配置了系统DNS和建立user数据库并从备份恢复。就做了这些工作~！Top
8 楼fcuandy（了此残生.）回复于 2006-07-05 09:50:46 得分 20
贴上你的代码看看。 有没有 On Error 之类的语句.
还有插表时，把SQL语句打出来，到查询分析器执行看看能否成功。
Top
9 楼horsy（）回复于 2006-07-05 10:13:02 得分 0
Dim userid, username, loginname, passwd, usertype, groupid, qxid, addr, dw, tel, regdate, bz, czr, czdate
Dim sqlstr
Dim opr, gid, uname
Dim ret, oprtype
Private Sub Class_Initialize()
On Error Resume Next
Set objContext = GetObjectContext
Set Application = objContext.Item("Application")
Set Server = objContext.Item("Server")
Set Session = objContext.Item("Session")
Set Request = objContext.Item("Request")
Set Response = objContext.Item("Response")
End Sub
Private Sub Class_Terminate()
On Error Resume Next
Set Application = Nothing
Set Server = Nothing
Set Session = Nothing
Set Request = Nothing
Set Response = Nothing
Set objContext = Nothing
End Sub
Public Sub AspClassInit()
On Error GoTo Err
Set AspTransBuilderObject = Server.CreateObject("JFAsp.Class9")
Set conn = Server.CreateObject("ADODB.Connection")
conn.open AspTransBuilderObject.xConn
If Err = 0 Then
Else
If conn.Errors.Count > 0 Then
Response.Redirect ("regerror.asp?opr=0&error=连接数据库失败!")
End If
End If
Set AspTransBuilderObject = Nothing
ret = 0
oprtype = 9
opr = Request.Form("oprtype")
gid = Request.Form("gid")
uid = Request.Form("uid")
uname = Request.Form("uname")
userid = gid + Request.Form("userid")
username = Request.Form("username")
loginname = Request.Form("loginname")
passwd = Request.Form("passwd")
usertype = Request.Form("usertype")
groupid = gid
qxid = ""
addr = Request.Form("addr")
dw = Request.Form("dw")
tel = Request.Form("tel")
If opr = "A" Then
regdate = CStr(Now)
ElseIf opr = "E" Then
regdate = Request.Form("regdate")
End If
bz = Request.Form("bz")
czr = Session("userid")
czdate = CStr(Now)
If opr = "A" Then
oprtype = 0
sqlstr = "insert into userinfo(userid,username,loginname,passwd,usertype,groupid,qxid,addr,dw,tel,regdate,bz,czr,czdate) values('" + userid + "','" + username + "','" + loginname + "','" + passwd + "','" + usertype + "','" + groupid + "','" + qxid + "','" + addr + "','" + dw + "','" + tel + "','" + regdate + "','" + bz + "','" + czr + "','" + czdate + "')"
ElseIf opr = "E" Then
oprtype = 1
sqlstr = "update userinfo set userid='" + userid + "',username='" + username + "',loginname='" + loginname + "',passwd='" + passwd + "',usertype='" + usertype + "',groupid='" + groupid + "',qxid='',addr='" + addr + "',dw='" + dw + "',tel='" + tel + "',regdate='" + regdate + "',bz='" + bz + "',czr='" + czr + "',czdate='" + czdate + "' where userid='" + uid + "' and groupid='" + gid + "'"
ElseIf opr = "D" Then
oprtype = 2
sqlstr = "delete from userinfo where userid='" + uid + "'"
End If
conn.Execute (sqlstr)
If Err = 0 Then
tt = 1
Else
tt = 0
Response.Redirect ("../error.asp?error=" + sqlstr + "数据库操作失败!")
End If
Response.Write "<script language=""javascript"">" & vbCrLf
Response.Write "var ret;" & vbCrLf
Response.Write "var oprtmp; " & vbCrLf
Response.Write "ret="
Response.Write tt
Response.Write ";" & vbCrLf
Response.Write "oprtmp="
Response.Write oprtype
Response.Write ";" & vbCrLf
Response.Write "if (ret==1)" & vbCrLf
Response.Write "{ " & vbCrLf
Response.Write " var obj=parent.parent.frmleft.tree.getSelected();" & vbCrLf
Response.Write " if(oprtmp==0)" & vbCrLf
Response.Write " {" & vbCrLf
Response.Write " "
Response.Write "obj.folder=true;"
Response.Write "obj.doExpand();"
Response.Write "var U" + userid + "=new parent.parent.frmleft.wTreeI('<font color=#7b2e00>" + username + "</font>','javascript:setval(\'" + userid + "\',\'" + username + "\',\'" + usertype + "\',\'" + groupid + "\')',obj);"
Response.Write " }" & vbCrLf
Response.Write " if(oprtmp==1)" & vbCrLf
Response.Write " {" & vbCrLf
Response.Write " "
Response.Write "parent.parent.frmleft.setSubNodeText('" + uname + "','" + username + "');"
Response.Write " }" & vbCrLf
Response.Write " if(oprtmp==2)" & vbCrLf
Response.Write " {" & vbCrLf
Response.Write " parent.parent.frmleft.delsubnode();" & vbCrLf
Response.Write " parent.clearform();" & vbCrLf
Response.Write " }" & vbCrLf
Response.Write "}" & vbCrLf
Response.Write "parent.msg(ret);" & vbCrLf
Response.Write "</script>" & vbCrLf
Set conn = Nothing
Set AspTransBuilderObject = Nothing
Exit Sub
Err:
If Err.Number = -13572468 Then Exit Sub
Resume Next
End Sub
在ASP中执行插入操作后通过： Response.Write "parent.msg(ret);得到数据保存成功的信息。但SELECT出为NULL。好象并没有插进去。但相同的代码在别的服务器上却成功~！
Top
