以下是access数据库一直用着的,现想修改成SQL SERVER 请各高手帮帮忙,,谢谢
Set Rs = conn.Execute("SELECT count(*) as newnum FROM [user] WHERE first_ip='" & [ip] & "' and datediff('n',log_time,'" & Now() & "')<10")
newuser = False
If Rs("newnum") - Application(StConstStr & "_newusernum") >= 0 Then newuser = True
Set Rs = conn.Execute("SELECT * FROM [ip_lock] WHERE '" & [ip] & "' like '%'+ip+'%'")
ip_lock = False
ip_lock_level = 0
If Not (Rs.BOF And Rs.EOF) Then
If Rs("end_time") > Now() Then
[ip_lock] = True
ip_lock_level = Rs("act_level")
End If
End Ifsql = "SELECT * FROM [user] WHERE username='" & sUserName & "'"
Set Rs = conn.Execute(sql)
If (Rs.BOF Or Rs.EOF) Then
If [ip_lock] Then
LoginCheck = "IP被封锁了"
Exit Function
ElseIf newuser Then
LoginCheck = "请先注册用户"
Exit Function
ElseIf Application(StConstStr & "_disablenewuser") = "1" Then
LoginCheck = "请先注册用户"
Exit Function
Else
If Request("bSex") <> "1" Then
bSex = 0
Else
bSex = 1
End If
face_Color = Split(Application(StConstStr & "_facecolorstr"), " ")
Bg_Color = Split(Application(StConstStr & "_backgroudcolorstr"), " ")
BgColor = Bg_Color(0) & ":" & face_Color(0) & ":" & face_Color(1)
insert_item = "username,passwd,headface,sex,log_time,user_level,first_ip,last_ip,last_ip2,visit_num," & _
"in_time,out_info,exp_num,out_time,f_size,l_height,n_color,s_color,bgcolor"
sz = "'" & sUserName & "','" & sPass & "',1010," & bSex & ",'" & Now() & "',1,'" & [ip] & "','" & [ip] & "','" & [ip2] & "',1,'" & _
Now() & "',0,0,'" & Now() & "',4,6," & Int(Rnd * 18) & "," & Int(Rnd * 18) & ",'" & BgColor & "'"
conn.Execute ("INSERT INTO [user] (" & insert_item & ") VALUES (" & sz & ")")
Set Rs = conn.Execute("SELECT count(*) as newnum FROM [user] WHERE first_ip='" & [ip] & "' and datediff('n',log_time,'" & Now() & "')<10")
newuser = False
If Rs("newnum") - Application(StConstStr & "_newusernum") >= 0 Then newuser = True
Set Rs = conn.Execute("SELECT * FROM [ip_lock] WHERE '" & [ip] & "' like '%'+ip+'%'")
ip_lock = False
ip_lock_level = 0
If Not (Rs.BOF And Rs.EOF) Then
If Rs("end_time") > Now() Then
[ip_lock] = True
ip_lock_level = Rs("act_level")
End If
End Ifsql = "SELECT * FROM [user] WHERE username='" & sUserName & "'"
Set Rs = conn.Execute(sql)
If (Rs.BOF Or Rs.EOF) Then
If [ip_lock] Then
LoginCheck = "IP被封锁了"
Exit Function
ElseIf newuser Then
LoginCheck = "请先注册用户"
Exit Function
ElseIf Application(StConstStr & "_disablenewuser") = "1" Then
LoginCheck = "请先注册用户"
Exit Function
Else
If Request("bSex") <> "1" Then
bSex = 0
Else
bSex = 1
End If
face_Color = Split(Application(StConstStr & "_facecolorstr"), " ")
Bg_Color = Split(Application(StConstStr & "_backgroudcolorstr"), " ")
BgColor = Bg_Color(0) & ":" & face_Color(0) & ":" & face_Color(1)
insert_item = "username,passwd,headface,sex,log_time,user_level,first_ip,last_ip,last_ip2,visit_num," & _
"in_time,out_info,exp_num,out_time,f_size,l_height,n_color,s_color,bgcolor"
sz = "'" & sUserName & "','" & sPass & "',1010," & bSex & ",'" & Now() & "',1,'" & [ip] & "','" & [ip] & "','" & [ip2] & "',1,'" & _
Now() & "',0,0,'" & Now() & "',4,6," & Int(Rnd * 18) & "," & Int(Rnd * 18) & ",'" & BgColor & "'"
conn.Execute ("INSERT INTO [user] (" & insert_item & ") VALUES (" & sz & ")")
SET @IP='XXXXX'--在这里填写ip地址
SELECT count(*) as newnum
FROM [user] WHERE first_ip=@ip and datediff('s',log_time, getdate())<10 --参考datediff函数,现在用了秒来算--下面这句本身就是sqlserver的,access用*而tsql用%
SELECT * FROM [ip_lock] WHERE [ip] like '%'+@ip+'%'
最后一句给个例子算了
declare @insert_item varchar(1000)
declare @inse_val varchar(2000)
declare @sql varchar(8000)
set @insert_item='tbl(f1,f2,dt3)'
set @inse_val='(''a'',''b'','''+str(getdate())+''')'--这里有时间的转化
set @sql='insert into '+@insert_item+' values'+@inse_val
exec(@sql)
<%Response.CacheControl = "Private"
response.expires=0
application.lock
if application(StConstStr & "_init")<>"true" then
Db = "Global.asa" '数据库路径
Connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath(db)
On Error Resume Next
Set Conn=Server.CreateObject("ADODB.Connection")
Conn.open ConnStr
If Err Then
err.Clear
Set Conn = Nothing
Response.Write "数据库连接出错,请检查连接字串。"
Response.End
End If Application(StConstStr & "_connstr") = ConnStr
call InitGlobal()
end if
Public Function LoginCheck()
Dim room_Owner, room_auto, ip, ip2, insert_item, sz, sUserName, sPass, slNM, regjm1, regjm, sql, Roomlist
Dim All_user_Array, All_user_Num, room_Array, Room_Array2, hour_Array, friend_array
Dim say_mess, says, current, all_User_Str, friendstr, face_Color, Bg_Color, BgColor, InitStr, user_Str, Room_Name
Dim i, ip_lock_level, bSex
Dim newuser, ip_lock
Dim visit_Num, old_Hour, myHour, u_Level, t_Level, maxPeople
Dim headFace
Dim out_Time
Dim say_Who
Dim regUser, regSerial
Dim say_id
Dim ipNum, portNum, Pathname, loginUrl
Dim permit_level, door_open
Dim Rs, rs2, rs1ip = Trim(Request.ServerVariables("REMOTE_ADDR"))regUser = ""
maxPeople = CInt(Application(StConstStr & "_maxpeople"))
ipNum = Replace(Trim(Request.ServerVariables("SERVER_NAME")), "http://", "")
loginUrl = "http://" & ipNum
sUserName = Left(replace(replace(Trim(Request("sUserName"))," ",""),"",""), 10)
sPass = Request("sPass")
slNM = LCase(sUserName)
FilterStr = Application(StConstStr & "_usernamefilter")
Pathname = Trim(Request.ServerVariables("SCRIPT_NAME"))
regSerial = ""
If sPass = sUserName Then
LoginCheck = "用户名与密码相同"
Exit Function
ElseIf (slNM = "") Then
LoginCheck = "用户名为空!"
Exit Function
ElseIf len_ch(sUserName) - Application(StConstStr & "_namelength") < 0 Then
LoginCheck = "用户名长度不能太短至少" & Application(StConstStr & "_namelength") & "个字符!"
Exit Function
ElseIf len_ch(sUserName) - Application(StConstStr & "_maxnamelength") * 2 > 0 Then
LoginCheck = "用户名长度不能长于" & Application(StConstStr & "_maxnamelength") & "个汉字!"
Exit Function
ElseIf Chuser(slNM, Application(StConstStr & "_usernameenable")) Then
LoginCheck = "用户名含有非法字符!"
Exit Function
End IfportNum = Trim(Request.ServerVariables("SERVER_PORT"))conn.Open ConnStripNum = Replace(Replace(Replace(ipNum, "www.", ""), ".com", ""), ".net", "")Pathname = Left(Pathname, Len(Pathname) - 9)If portNum <> "80" Then loginUrl = loginUrl & ":" & portNumloginUrl = loginUrl & Pathname & ";"Pathname = Pathname & regUserDim serialArray, IsReg
regUser = Application(StConstStr & "_user")
IsReg = True
All_user_Num = (UBound(All_user_Array) + 1) \ 2
If All_user_Num - maxPeople > 0 Then
LoginCheck = "对不起:聊天室已经满员了!"
Exit Function
End If
sPass = Stpass(Left(sPass, 15))
Application(StConstStr & "_visitor") = Application(StConstStr & "_visitor") + 1
Randomize
myHour = Hour(Now)
If myHour > 23 Then myHour = 0
hour_Array = Split(Application(StConstStr & "_hour24"), ",")
old_Hour = CInt(hour_Array(24))
If old_Hour - myHour = 0 Then
hour_Array(myHour) = hour_Array(myHour) + 1
Application(StConstStr & "_hour24") = Join(hour_Array, ",")
Else
If myHour = 14 Then
conn.Execute ("DELETE FROM [calls] WHERE datediff('d',calltime,'" & Now() & "')>" & Application(StConstStr & "_friendtimeout"))
conn.Execute ("DELETE FROM [friendrq] WHERE datediff('d',sub_time,'" & Now() & "')>" & Application(StConstStr & "_calltimeout")) If Application(StConstStr & "_autoclearmanagelog") = "1" Then
conn.Execute ("DELETE FROM [record] WHERE DATEDIFF('d',occ_time,now())>" & Application(StConstStr & "_managelogtimeout"))
End If If Application(StConstStr & "_autocleargb") = "1" Then
conn.Execute ("DELETE FROM [guestbook] WHERE DATEDIFF('d',post_time,now())>" & Application(StConstStr & "_gbtimeout"))
End If If Application(StConstStr & "_autoclearusername") = "1" Then
autoclearuser 1, Application(StConstStr & "_usernameoncetimeout"), Application(StConstStr & "_usernametimeout")
End If If Application(StConstStr & "_roomautoclear") = "1" Then
Roomlist = Application(StConstStr & "_room_list")
autoupdateroom 1, Application(StConstStr & "_roomkeepexp"), Roomlist
autoclearroom 1, Application(StConstStr & "_roomtimeout"), Application(StConstStr & "_roomexpperday"), Roomlist
Application(StConstStr & "_room_list") = Roomlist
End If End If
If myHour > old_Hour + 1 Then
For i = old_Hour + 1 To myHour - 1
hour_Array(i) = 0
Next
ElseIf myHour < old_Hour Then
If old_Hour <> 23 Then
For i = old_Hour + 1 To 23
hour_Array(i) = 0
Next
End If
If myHour <> 0 Then
For i = 0 To myHour - 1
hour_Array(i) = 0
Next
End If
End If
hour_Array(24) = myHour
hour_Array(myHour) = 1
Application(StConstStr & "_hour24") = Join(hour_Array, ",")
conn.Execute ("update [sysparam] set body='" & Application(StConstStr & "_hour24") & "' where alias='hour24'")
conn.Execute ("update [sysparam] set body='" & Application(StConstStr & "_visitor") & "' where alias='visitor'")
End IfIf IsNumeric(Request("room_id")) Then
room_id = CLng(Request("room_id"))
Else
room_id = 1
End IfIf InStr(Application(StConstStr & "_room_list"), "<$" & room_id & "$>") = 0 Then
room_Array = Split(Application(StConstStr & "_room_list"), "$><$")
Room_Array2 = Split(room_Array(0), "$>")
room_id = Replace(Room_Array2(0), "<$", "")
i = 2
Else
room_Array = Split(Application(StConstStr & "_room_list"), "<$" & room_id & "$>")
Room_Array2 = Split(room_Array(1), "$>")
i = 1
End If
room_Owner = Room_Array2(i)
room_auto = Room_Array2(i + 1)
permit_level = CInt(Room_Array2(i + 13))
door_open = CInt(Room_Array2(i + 14))If LCase(room_Owner) <> slNM Then
If slNM = LCase(room_auto) Then
LoginCheck = "同名被拒绝进入"
Exit Function
ElseIf permit_level - 1 > 0 Then
LoginCheck = "房间有级别限制"
Exit Function
ElseIf door_open = "0" Then
LoginCheck = "房间门已经关"
Exit Function
End If
End IfSet Rs = conn.Execute("SELECT count(*) as newnum FROM [user] WHERE first_ip='" & [ip] & "' and datediff('n',log_time,'" & Now() & "')<10")
newuser = False
If Rs("newnum") - Application(StConstStr & "_newusernum") >= 0 Then newuser = True
Set Rs = conn.Execute("SELECT * FROM [ip_lock] WHERE '" & [ip] & "' like '%'+ip+'%'")
ip_lock = False
ip_lock_level = 0
If Not (Rs.BOF And Rs.EOF) Then
If Rs("end_time") > Now() Then
[ip_lock] = True
ip_lock_level = Rs("act_level")
End If
End Ifsql = "SELECT * FROM [user] WHERE username='" & sUserName & "'"
Set Rs = conn.Execute(sql)
If (Rs.BOF Or Rs.EOF) Then
If [ip_lock] Then
LoginCheck = "IP被封锁了"
Exit Function
ElseIf newuser Then
LoginCheck = "请先注册用户"
Exit Function
ElseIf Application(StConstStr & "_disablenewuser") = "1" Then
LoginCheck = "请先注册用户"
Exit Function
Else
If Request("bSex") <> "1" Then
bSex = 0
Else
bSex = 1
End If
face_Color = Split(Application(StConstStr & "_facecolorstr"), " ")
Bg_Color = Split(Application(StConstStr & "_backgroudcolorstr"), " ")
BgColor = Bg_Color(0) & ":" & face_Color(0) & ":" & face_Color(1)
insert_item = "username,passwd,headface,sex,log_time,user_level,first_ip,last_ip,last_ip2,visit_num," & _
"in_time,out_info,exp_num,out_time,f_size,l_height,n_color,s_color,bgcolor"
sz = "'" & sUserName & "','" & sPass & "',1010," & bSex & ",'" & Now() & "',1,'" & [ip] & "','" & [ip] & "','" & [ip2] & "',1,'" & _
Now() & "',0,0,'" & Now() & "',4,6," & Int(Rnd * 18) & "," & Int(Rnd * 18) & ",'" & BgColor & "'"
conn.Execute ("INSERT INTO [user] (" & insert_item & ") VALUES (" & sz & ")")
u_Level = 1
t_Level = 1
InitStr = ""
headFace = 1010
InitStr = "【系统】" & sUserName & ":欢迎光临!请记牢您的密码[" & Request("sPass") & "],点[<a href=userset.asp target=list onclick=\'parent.listflag=0\'>设置</a>]可以修改和设置您的个人信息!<br>"
End If
Else
If Rs("passwd") <> sPass Then
LoginCheck = "密码错误!"
Exit Function
ElseIf (Rs("out_info") - 5) = 0 And Rs("out_time") > Now() Then
LoginCheck = "目前聊天室还不允许你进去!"
Exit Function
ElseIf (Rs("out_info") - 6) = 0 And Rs("out_time") > Now() Then
LoginCheck = "聊天室还不允许你进去"
Exit Function
ElseIf (Rs("out_info") - 7) = 0 Then
LoginCheck = "帐号被永久冻结了"
Exit Function
ElseIf Rs("user_level") - ip_lock_level < 0 And [ip_lock] Then
LoginCheck = "IP被封锁了"
Exit Function
u_Level = Rs("user_level")
out_Time = Now()
t_Level = u_Level
Set rs1 = conn.Execute("select temp_level from [vrecord] where username='" & sUserName & "' and temp_level>5")
If Not (rs1.EOF Or rs1.BOF) Then If rs1("temp_level") > t_Level Then t_Level = rs1("temp_level")
rs1.Close
Set rs1 = Nothing
visit_Num = Rs("visit_num") + 1 InitStr = ""
sql = "select * from [calls] where (towho='大家' and datediff('s',calltime,'" & Rs("out_time") & "')<0) or (towho='" & sUserName & "' and readdx=0) order by ID desc"
Set rs2 = conn.Execute(sql)
If Not (rs2.EOF Or rs2.BOF) Then InitStr = InitStr & "<\script>window.open(\'getcall.asp\',\'\',\'width=550,height=300,scrollbars=1\');</\script>"
Set rs2 = conn.Execute("select * from [guestbook] where towho='" & sUserName & "' and datediff('s',post_time,'" & Rs("out_time") & "')<0")
If Not (rs2.EOF Or rs2.BOF) Then InitStr = InitStr & "【系统】" & sUserName & ":有聊友给你留言哟![<a href=guest.asp target=guestbook>观看</a>]<br>"
Set rs2 = conn.Execute("select * from [friendrq] where towho='" & sUserName & "'")
Do While Not rs2.EOF
InitStr = InitStr & "【系统】" & Trim(rs2("who")) & "向你发出好友申请,你是[<a href=\'agreefriend.asp?ID=" & rs2("ID") & "&answer=yes\' onclick=\'parent.input.document.forms[0].saystemp.focus()\' target=newsay>同意</a>]还是[<a href=\'agreefriend.asp?ID=" & rs2("ID") & "&answer=no\' onclick=\'parent.input.document.forms[0].saystemp.focus()\' target=newsay>不同意</a>]?<br>"
rs2.MoveNext
Loop
rs2.Close
Set rs2 = Nothing friendstr = ""
If Trim(Rs("friends") <> "") And Application(StConstStr & "_user_list") <> "" Then
friend_array = Split(Trim(Rs("friends")), " ")
For i = 0 To UBound(friend_array)
If InStr(Application(StConstStr & "_user_list"), "<$" & friend_array(i) & "$>") <> 0 Then
friendstr = friendstr & "<font class=a color=#880088 onclick=javascript:parent.chuser(\'" & friend_array(i) & "\') >" & friend_array(i) & "</font>,"
End If
Next
End If
If friendstr <> "" Then InitStr = InitStr & "【系统】在线好友:" & friendstr & "快快与好友联系吧!<br>" conn.Execute ("UPDATE [user] SET temppass=' ',visit_num=" & visit_Num & ",last_ip='" & [ip] & "',last_ip2='" & [ip2] & "',in_time='" & Now() & "',out_time='" & out_Time & "' ,passwd='" & sPass & "' WHERE username='" & sUserName & "'")
bSex = Rs("sex")
If bSex <> 0 Then bSex = 1
sUserName = Rs("username")
headFace = Rs("headface")
End If If InStr(Application(StConstStr & "_user_list"), "<$" & sUserName & "$>") <> 0 Then
user_array = Split(Application(StConstStr & "_user_list"), "<$" & sUserName & "$>")
user_array2 = Split(user_array(1), "$>")
If room_id - user_array2(4) <> 0 Then room_id = CLng(user_array2(4))
say_mess = Split(Application(StConstStr & "_reentershow"), "::")
all_User_Str = user_array(0) & user_array(2)
Else
all_User_Str = Application(StConstStr & "_user_list")
End If
Session("Stname") = sUserName
Session("mytemp") = GetTimeStr("") says = Application(StConstStr & "_says")
current = Application(StConstStr & "_current")
say_id = says(current, 0) room_Array = Split(Application(StConstStr & "_room_list"), "<$" & room_id & "$>")
Room_Array2 = Split(room_Array(1), "$>")
Room_Name = Room_Array2(0)
If InStr(" " & Room_Array2(12) & " ", " " & sUserName & " ") = 0 Then Room_Array2(12) = Trim(Room_Array2(12)) & " " & sUserName
Room_Array2(7) = Now()
room_Array(1) = Join(Room_Array2, "$>")
Application(StConstStr & "_room_list") = Join(room_Array, "<$" & room_id & "$>")
If Not IsArray(say_mess) Then say_mess = Split(Application(StConstStr & "_enterchatshow"), "::")
If bSex = 1 Then
say_Who = "<small>girl</small>"
Else
say_Who = "<small>boy</small>"
End If say_mess(0) = Replace(say_mess(0), "##", say_Who & "##")
say_mess(0) = say_mess(0) & "<\script>parent.addl(\'" & sUserName & "\'," & bSex & ",\'" & headFace & "\',\'\')</\script>" Newsay sUserName, Room_Name, 0, room_id, say_mess(0), say_mess(1), say_mess(2), -3, says, current
If InitStr <> "" Then Newsay sUserName, "", 1, room_id, InitStr, "", "", -5, says, current
Application(StConstStr & "_says") = says
Application(StConstStr & "_current") = current user_Str = "<$" & sUserName & "$>" & Session("mytemp") & "$>" & [ip] & "$>" & bSex & _
"$>" & u_Level & "$>" & room_id & "$>" & Now() & "$>" & headFace & "$>0$>" & t_Level & _
"$>" & (say_id - 10) & "$>" & Now() & "$>" & Now() & "$>$>$>$><$" & sUserName & "$>"
Application(StConstStr & "_user_list") = all_User_Str & user_Str
LoginCheck = ""
End Function
errText=LoginCheck()
application.unlock
if errText="" then
Session.Timeout=3 '用户3分钟不请求掉线
Response.Redirect("chat.asp")
else
response.redirect("showerr.asp?errtext="&ErrText)
end if%>这是全文有方法能修改成sql server吗,分加多少都行只要能转成sql server就行