publicfunction.asp

功能齐全的oa系统

				Do While Not rstChgOrd.EOF 
					If rstChgOrd(OrderName) = 1 Then
						Sql = Sql +"Update "&TableName&" Set "&OrderName&"="&m_OldTatal&" Where "&PrimaryKeyName&"="&cint(PrimaryValue)
					Else
						Sql = Sql + " ; Update "&TableName&" Set "&OrderName&"="&OrderName&"-1 Where "&PrimaryKeyName&_
								"="&rstChgOrd(PrimaryKeyName)
					End If
					rstChgOrd.MoveNext 
				Loop						
			End If
		Case "DOWN"
			If	m_OldOrder <> m_OldTatal Then       '要移动的试题次序不是最后一个
				Do While Not rstChgOrd.EOF 
					If rstChgOrd(OrderName) = m_OldOrder+1 Then
						Sql = "Update "&TableName&" Set "&OrderName&"="&m_OldOrder+1&" Where "&PrimaryKeyName&"="&cint(PrimaryValue)&_
								" ; Update "&TableName&" Set "&OrderName&"="&m_OldOrder&" Where "&PrimaryKeyName&"="&rstChgOrd(PrimaryKeyName)
					End If
					rstChgOrd.MoveNext 
				Loop									
			Else
				Do While Not rstChgOrd.EOF 
					If rstChgOrd(OrderName) = m_OldOrder Then
						Sql = Sql + "Update "&TableName&" Set "&OrderName&"=1 Where "&PrimaryKeyName&"="&cint(PrimaryValue)
					Else
						Sql = Sql + "Update "&TableName&" Set "&OrderName&"="&OrderName&"+1 Where "&PrimaryKeyName&_
								"="&rstChgOrd(PrimaryKeyName)&" ; "
					End If
					rstChgOrd.MoveNext 
				Loop						
			End If
	End Select
	rstChgOrd.Close 
	rstChgOrd.Open Sql,conn,3,3
	
End Function


'*************************************************************
'功能：添加一个新的纪录
'参数：
'  TableName；String；表名称
'  FieldList；String；字段列表（逗号","分割，不含ID字段）
'  ValueList；String；字段值列表（特殊符号"и"分割）
'  IDFieldName；String；ID字段的名称
'返回值：Integer
'*************************************************************
Function AddRecord(TableName,FieldList,ValueList,IDFieldName)
	Dim rstAddRecord
	Dim strSelect
	Dim CurrentField
	Dim CurrentValue
	Dim OtherFieldList
	Dim OtherValueList
	
	On Error Resume Next
	
	Set rstAddRecord=Server.CreateObject("adodb.recordset")
	
	With rstAddRecord
		.ActiveConnection =conn
		.CursorType =3 'adOpenStatic
		.LockType =3 'adLockOptimistic
		.Source ="select * from " & TableName & " where " & IDFieldName & "=-1" '建立一个空纪录集
		.Open 
		.AddNew

		'.Fields(IDFieldName).Value =AutoGetNo(TableName) '通过公用函数取得唯一ID
		
		OtherFieldList = FieldList
		OtherValueList = ValueList

		Do Until Instr(OtherFieldList,",") = 0
			CurrentField = Left(OtherFieldList,Instr(OtherFieldList,",")-1)
			OtherFieldList = mid(OtherFieldList,Instr(OtherFieldList,",")+1)
			
			CurrentValue = Left(OtherValueList,Instr(OtherValueList,"и")-1)		'解析字符и
			OtherValueList = mid(OtherValueList,Instr(OtherValueList,"и")+1)
			Select Case .Fields(CurrentField).Type
				'日期时间
				Case 7,133,134,135 'adDate adDBDate adDBTime adDBTimeStamp 
					If IsDate(CurrentValue) = false Then
						Response.Write "无效的日期值！" & chr(10)
	                    conn.Execute "ROLLBACK TRAN Tran_Insert"                   '如果存在添加事务（事务滚回）
						Response.End
						Exit Function
					End If
					.Fields(CurrentField).Value = CurrentValue
				'字符
				Case 8,129,200,201,202,203 'adBSTR adChar adVarChar adLongVarChar adVarWChar adLongVarWChar 
					.Fields(CurrentField).Value = CurrentValue
				'布尔
  				Case 11  'adBoolean
  					If UCase(cstr(Trim(CurrentValue)))="TRUE" Then
						.Fields(CurrentField).Value = 1
  					Else 
						.Fields(CurrentField).Value = 0
  					End If						
				'数值
				Case Else
					If IsNumeric(CurrentValue) = false Then
						Response.Write "无效的数值！" & chr(10)
						conn.Execute "ROLLBACK TRAN Tran_Insert"
						Response.End 
						Exit Function
					End If
					.Fields(CurrentField).Value = CurrentValue
			End Select
			
		Loop
		
		If len(OtherFieldList) > 0 Then
			CurrentField = OtherFieldList
			CurrentValue = OtherValueList
			Select Case .Fields(CurrentField).Type
				'日期时间
				Case 7,133,134,135 'adDate adDBDate adDBTime adDBTimeStamp 
					If IsDate(CurrentValue) = false Then
						Response.Write "无效的日期值！" & chr(10)
						conn.Execute "ROLLBACK TRAN Tran_Insert"
						Response.End
						Exit Function
					End If
					.Fields(CurrentField).Value = CurrentValue
				'字符
				Case 8,129,200,201,202,203 'adBSTR adChar adVarChar adLongVarChar adVarWChar adLongVarWChar 
					.Fields(CurrentField).Value = CurrentValue
				'布尔
  				Case 11  'adBoolean
  					If UCase(cstr(Trim(CurrentValue)))="TRUE" Then
						.Fields(CurrentField).Value = 1
  					Else 
						.Fields(CurrentField).Value = 0
  					End If						
				'数值
				Case Else
					If IsNumeric(CurrentValue) = false Then
						Response.Write "无效的数值！" & chr(10)
						conn.Execute "ROLLBACK TRAN Tran_Insert"
						Response.End 
						Exit Function
					End If
					.Fields(CurrentField).Value = CurrentValue
			End Select
			
		End If

		.Update 
		If Err.number <> 0 Then
			Response.Write "Error"
			conn.Execute "ROLLBACK TRAN Tran_Insert"
			AddRecord = -1
			Exit Function
		End If
		
	End With
	
End Function	


'*************************************************************
'功能：修改某一纪录
'参数：
'  TableName；String；表名称
'  FieldList；String；字段列表（逗号","分割，不含ID字段）
'  ValueList；String；字段值列表（特殊符号"и"分割）
'  IDFieldName；String；ID字段的名称
'  IDFieldValue；String；ID字段的值
'返回值：Integer
'*************************************************************
Function ModifyRecord(TableName,FieldList,ValueList,IDFieldName,IDFieldValue)
	Dim rstModifyRecord
	Dim strSelect
	Dim CurrentField
	Dim CurrentValue
	Dim OtherFieldList
	Dim OtherValueList
	
	On Error Resume Next
	
	Set rstModifyRecord=Server.CreateObject("adodb.recordset")
	
	With rstModifyRecord
		.ActiveConnection =conn
		.CursorType =3 'adOpenStatic
		.LockType =3 'adLockOptimistic
		.Source ="select * from " & TableName & " where " & IDFieldName & "=" & IDFieldValue '定位指定的纪录
		.Open 

		OtherFieldList = FieldList
		OtherValueList = ValueList
		
		Do Until Instr(OtherFieldList,",") = 0
			CurrentField = Left(OtherFieldList,Instr(OtherFieldList,",")-1)
			OtherFieldList = mid(OtherFieldList,Instr(OtherFieldList,",")+1)

			CurrentValue = Left(OtherValueList,Instr(OtherValueList,"и")-1)
			OtherValueList = mid(OtherValueList,Instr(OtherValueList,"и")+1)

			Select Case .Fields(CurrentField).Type
				'日期时间
				Case 7,133,134,135 'adDate adDBDate adDBTime adDBTimeStamp 
					If IsDate(CurrentValue) = false Then
						Response.Write "无效的日期值！" & chr(10)
						conn.Execute "ROLLBACK TRAN Tran_Update"                   '如果存在修改事务（事务滚回）
						Response.End
						Exit Function
					End If
					.Fields(CurrentField).Value = CurrentValue
				'字符
				Case 8,129,200,201,202,203 'adBSTR adChar adVarChar adLongVarChar adVarWChar adLongVarWChar 
					.Fields(CurrentField).Value = CurrentValue
				'布尔
  				Case 11  'adBoolean
  					If UCase(cstr(Trim(CurrentValue)))="TRUE" Then
						.Fields(CurrentField).Value = 1
  					Else 
						.Fields(CurrentField).Value = 0
  					End If						
				'数值
				Case Else
					If  UCase(cstr(CurrentValue))="NULL" Then	'如果数据库中的整型字段允许为空，则将‘NULL’写入数据库
						.Fields(CurrentField).Value = null
					Else
						If IsNumeric(CurrentValue) = false  Then
							Response.Write "无效的数值！" & chr(10)
							conn.Execute "ROLLBACK TRAN Tran_Update"                   '如果存在修改事务（事务滚回）
							Response.End 
							Exit Function
						End If
						.Fields(CurrentField).Value = CurrentValue
					End If
			End Select
			
		Loop

		If len(OtherFieldList) > 0 Then
			CurrentField = OtherFieldList
			CurrentValue = OtherValueList
			
			Select Case .Fields(CurrentField).Type
				'日期时间
				Case 7,133,134,135 'adDate adDBDate adDBTime adDBTimeStamp 
					If IsDate(CurrentValue) = false Then
						Response.Write "无效的日期值！" & chr(10)
						conn.Execute "ROLLBACK TRAN Tran_Update"                   '如果存在修改事务（事务滚回）
						Response.End
						Exit Function
					End If
					.Fields(CurrentField).Value = CurrentValue
				'字符
				Case 8,129,200,201,202,203 'adBSTR adChar adVarChar adLongVarChar adVarWChar adLongVarWChar 
					.Fields(CurrentField).Value = CurrentValue
				'布尔
  				Case 11  'adBoolean
  					If UCase(cstr(Trim(CurrentValue)))="TRUE" Then
						.Fields(CurrentField).Value = 1
  					Else 
						.Fields(CurrentField).Value = 0
  					End If						
				'数值
				Case Else
					If  UCase(cstr(CurrentValue))="NULL" Then
						.Fields(CurrentField).Value = null
					Else
						If IsNumeric(CurrentValue) = false  Then
							Response.Write "无效的数值！" & chr(10)
							conn.Execute "ROLLBACK TRAN Tran_Update"                   '如果存在修改事务（事务滚回）
							Response.End 
							Exit Function
						End If
						.Fields(CurrentField).Value = CurrentValue
					End If
			End Select
			
		End If
		
		.Update 
		If Err.number <> 0 Then
			ModifyRecord = -1
			conn.Execute "ROLLBACK TRAN Tran_Update"                   '如果存在修改事务（事务滚回）
			Exit Function
		End If
		
	End With
	
End Function


'*************************************************************
'功能：删除指定的纪录,并进行出错提示
'参数：
'  TableName；String；表名称
'  IDFieldName；String；ID字段的名称
'  IDValues；String；ID字段的值串（该值串由逗号","分割）
'  FalseInfo ;String 删除失败时的提示信息
'返回值：Integer
'*************************************************************
Function DeleteRecord(TableName,IDFieldName,IDValues,FalseInfo)

	Dim ArrayIDs,intNumIDs
	Dim rstDelRecord,Sql
	
	Set rstDelRecord = Server.CreateObject("ADODB.Recordset")
	
	ArrayIDs = split(IDValues,",",-1)
	intNumIDs=UBound(ArrayIDs)-LBound(ArrayIDs)

	On Error Resume Next
	
		If IDValues<>"" Then
			For i=0 To intNumIDs-1
				Sql = "Delete From "&TableName&" Where "&IDFieldName&"="&clng(ArrayIDs(i))
				rstDelRecord.Open Sql,conn,3,3	
			Next	
		End If
		
		If Err.number <> 0 Then
			Response.Write "<Script language=JavaScript>"
			Response.Write "alert('删除失败！\n\n所要删除的【"&FalseInfo&"】在系统中已有其他相关信息，\n不能进行删除操作。\n\n若要删除，请先删除该【"&FalseInfo&"】的所有相关信息！');"
			Response.Write "window.history.back(1);"
			Response.Write "</Script>"
			DeleteRecord = -1 
			Exit Function
		End If
		
End Function 


'*************************************************************
'功能：取得某一指定纪录的详细资料
'参数：
'  TableName；String；表名称
'  IDFieldName；String；ID字段的名称
'  IDValue；String；ID字段的值
'返回值：Recordset
'*************************************************************
Function GetRecordDetail(TableName,IDFieldName,IDValue)
	Dim rstRecordDetail
	Dim strSelect
	
	On Error Resume Next
	
	Set rstRecordDetail=Server.CreateObject("adodb.recordset")
	
	With rstRecordDetail
		.ActiveConnection =conn
		strSelect="select * from " & TableName & " where " & IDFieldName & "=" & IDValue
		.CursorType =3 'adOpenStatic
		.LockType =3 'adLockOptimistic
		.Source =strSelect
		.Open 
		If Err.number <> 0 Then
			Response.Write "无效的查询条件！" & chr(10)
			Response.End
			Exit Function
		End If

	End With
	
	Set GetRecordDetail=rstRecordDetail
	
End Function



'*************************************************************
'功能：取得符合条件的纪录列表
'参数：
'  TableName；String；表名称
'  SearchCondition；String；搜索条件
'  OrderField；String；排序字段
'  OrderMode；String；排序方式，1为升序，0为降序
'  ShowN；Integer；仅显示前N条纪录
'返回值：Recordset
'*************************************************************
Function GetRecordList(TableName,SearchCondition,OrderField,OrderMode,ShowN)
	Dim rstRecordList
	Dim strSelect
	
	On Error Resume Next

	Set rstRecordList=Server.CreateObject("adodb.recordset")
	
	With rstRecordList
		.ActiveConnection =conn

		If ShowN > 0 Then
			strSelect="select top " & ShowN & " * from " & TableName
		Else
			strSelect="select * from " & TableName
		End If

		If SearchCondition <> "" Then
			strSelect = strSelect + " where " & SearchCondition
		End If
		
		If OrderField <> "" Then
			strSelect = strSelect + " order by " & OrderField
			Select Case OrderMode
				Case 1 '升序
					strSelect = strSelect & " asc"
				Case 0 '降序
					strSelect = strSelect & " desc"
			End Select
		End If
		
		.CursorType =3 'adOpenStatic
		.LockType =3 'adLockOptimistic
		.Source =strSelect
		.Open 
		If Err.number <> 0 Then
			Response.Write Err.Description & "<br>"
			Response.Write "无效的查询条件！" & chr(10)
			Response.End
			Exit Function
		End If
			
	End With
	
	Set GetRecordList=rstRecordList
	
End Function