数据库操作相关代码

   日期:2017-06-15

<%
'连接数据库Access
Dim Conn,ConnStr
ConnStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("data/db.mdb")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ConnStr

'---------------------------------------------------------------------------

'连接sql
dim conn,dbuid,dbpwd,dBName,dbip,rs
'修改以下信息以适合你的网站
 dbuid="sa"    '数据库登陆名
 dbpwd="123456"   '数据库密码
 dBName="hx"       '数据库名称
 dbip="(local)"   '数据库所在地址,如果是本地数据库则为:(local)
set conn=Server.CreateObject("adodb.Connection")
Conn.Open  "PROVIDER=SQLOLEDB.1;Data Source="&dbip&";Initial Catalog="&dBName&";Persist Security Info=True;User ID="&dbuid&";Password="&dbpwd&";Connect Timeout=30"
'---------------------------------------------------------------------------
'打开记录集
Set rs=Server.CreateObject("ADODB.RecordSet")
sql="Select * from [user]"
rs.open sql,conn,1,1
'---------------------------------------------------------------------------
'修改记录集
Set rs=Server.CreateObject("ADODB.RecordSet")
sql="Select * from [user] where name='"&incept&"'"
rs.open sql,conn,1,3
'---------------------------------------------------------------------------
'关闭记录集、连接

rs.close
set rs=nothing
conn.close
set conn=nothing
'---------------------------------------------------------------------------

'判断是否为空
Set rs=server.createobject("adodb.recordset")
  sql="Select * from [user] where name='"&incept&"'"
  rs.open sql,conn,1,1
  if rs.eof and rs.bof Then  
   response.Write "无"
  else
      response.Write "有记录"
  End if
  rs.close
  Set rs=Nothing
  
'---------------------------------------------------------------------------
'插入记录sql 
sql="insert into message (incept,sender,title,content) values ('"&incept&"','"&membername&"','"&title&"','"&message&"')"
conn.execute(sql)

'删除记录sql
strsql="delete from [a] where id='"& a &"'"
Conn.Execute(strsql)

sql1="delete from [user] where username='"&request("del")&"'"
 username=request("del")
 Conn.Execute sql1
'更新记录sql
sql="Update [user] set state=2,类别='计算机' where username='"&request("lock")&"'"
 Conn.Execute sql
'---------------------------------------------------------------------------
strsql="select * from a where id = '" & a & "'"   '如果a是字符型的
strsql="select * from a where id = " & a          '如果a是数字型的
'---------------------------------------------------------------------------
rs("name")="ttt"
rs.open sql,conn,1,3
rs(1).value="newname"
rs.update
rs.movefirst
response.write rs.RecordCount

for i=0 to rs.recordcount-1
response.write rs.Fields(0).Name
response.write  rs.Fields(0).Value 
rs.movenext
next


do while not rs.eof
if rs("name") = "a" then
rs.delete
rs.update
exit do
end if
rs.movenext
loop


'---------------------------------------------------------------------------
'判断用户名是否重复
If not conn.Execute("Select userid From [user] where username='"&username&"'" ).eof Then
response.redirect("error.asp?error=rname")
response.end
end if
'---------------------------------------------------------------------------

'通过大类ID找大类名

dlid=Request("type_dl")
xlid=Request("type_xl")
set rsi=conn.execute("select * from xclass where id="&xlid)
xlname=rsi("name")
rsi.close
set rsi=conn.execute("select * from dclass where id="&dlid)
dlname=rsi("name")
rsi.close
set rsi=nothing

'---------------------------------------------------------------------------
'添加大类代码,首先看大类是否存在
  Set rs=Server.CreateObject("Adodb.RecordSet")
  sql="Select * From BigClass Where BigClassName='" & BigClassName & "'"
  rs.open sql,conn,1,3
  if not (rs.bof and rs.EOF) then
   rs.close
   set rs=nothing
   response.Write "<script language=javascript>alert( '大类名称不能为空!'  );location.href = 'javascript:history.back()'</script>"
   response.End
  else
       rs.addnew
       rs("BigClassName")=BigClassName
       rs.update
       rs.Close
       set rs=Nothing
       conn.close
   set conn=nothing
   Response.Redirect "ClassManage.asp"  
  end if
'---------------------------------------------------------------------------


if   IsObject(conn)   then   
response.Write  conn.State 
end if


sql="select * from moban where (id=null)"
rs.open sql,conn,1,3
rs.addnew


总计信息数量:
set rs=conn.execute("select count(id) from msg")
response.write rs(0)

批量删除
for each a in request("de")
conn.execute "update msg set show=true where id="& a
next
创建rs
set rs=conn.execute("select*from msg where show=false ORDER BY id DESC")

%>