数据库操作函数

<%
‘——————————————-
‘ Dvbbs System Update Software
‘ Inclue File
‘ 动网先锋 [AspSky Software, Inc.]
‘ ScriptEditor Fssunwin
‘ 2005-03-25
‘——————————————-
Call InnerHtml(“UpdateInfo”,”正在更新用户组表结构”)
Checktable(“Dv_Board”,True)

Call DelTable(“[Dv_AdCode]”)
Call DelColumn(“[Dv_UserGroups]”,”IsDisp”)
Call ModColumn(“[Dv_UserGroups]”,”IsSetting”,”varchar(50) null”)

‘JS InnerHtml （对象名，追加信息）
Sub InnerHtml(obj,msg)
ReportTxt = ReportTxt & “<li>”& msg &”</li>”
Response.Write “<script LANGUAGE=””JavaScript””>”&obj&”.innerHTML += “”<li>”&msg&”</li>””;</script>”
Response.Flush
End Sub

‘JS 错误提示信息，并自已返回上一页
Sub ErrMsg(msg)
Response.Write “<script LANGUAGE=””JavaScript””>ErrMsg(“””&msg&”””);</script>”
Response.Flush
End Sub

‘检查数据库链接，（变量名，连接字串）
Sub CheckData(DataConn,ConnStr)
On Error Resume Next
Set DataConn = Server.CreateObject(“ADODB.Connection”)
DataConn.Open ConnStr
If Err Then
Err.Clear
Set DataConn = Nothing
ErrMsg(“数据库连接出错:” & Replace(ConnStr,”\”,”\\”) & “，\n请检查连接字串，确认您输入的数据库信息是否正确。”)
Response.End
End If
End Sub

‘检查数据表是否存在
Function Checktable(TableName,GetReport)
On Error Resume Next
Conn.Execute(“select * From ” & TableName)
If Err.Number <> 0 Then
If GetReport Then
Call ErrMsg(Err.Description &”（请确认您的数据库是否是正确的dvbbs7.0sp2数据库）”)
End If
Err.Clear()
Checktable = False
Else
Checktable = True
End If
End Function

‘检查主键是否存在，返回该表的主键名
Function GetPrimaryKey(TableName,GetReport)
on error Resume Next
Dim RsPrimary
GetPrimaryKey = “”
Set RsPrimary = Conn.OpenSchema(28,Array(Empty,Empty,TableName))
If Not RsPrimary.Eof Then GetPrimaryKey = RsPrimary(“COLUMN_NAME”)
Set RsPrimary = Nothing
If Err.Number <> 0 Then
Call InnerHtml(“UpdateInfo”,”报告信息：数据库不支持检测数据表[“&TableName&”]的主键。程序跳过检测。原因 ：”&Err.Description)
Err.Clear()
Else
If GetReport Then
If GetPrimaryKey<>”” Then
Call InnerHtml(“UpdateInfo” , “报告检测信息：数据表[“&TableName&”]的主键为” & GetPrimaryKey)
Else
Call InnerHtml(“UpdateInfo” , “报告检测信息：数据表[“&TableName&”]没有设置主键。”)
End If
End If
End If
End Function

Sub CloseData()
Set objADOXDatabase = Nothing
Conn.Close
Set Conn=Nothing
End Sub

‘测试更新表名对象
Sub ReNameTableConn()
On Error Resume Next
Set objADOXDatabase = Server.CreateObject(“ADOX.Catalog”)
objADOXDatabase.ActiveConnection = ConnStr
If Err Then
Call InnerHtml(“UpdateInfo”,”建立更改表名对象出错，您所要升级的空间不支持此对象，您很可能需要手动更改表名，原因” & Err.Description)
Err.Clear
End If
End Sub

Sub upColumn(ByVal TableName, ByVal ColumnName, ByVal ValueText)
On Error Resume Next
Conn.Execute(“update ” & TableName & ” set ” & ColumnName & “=” & ValueText)
End Sub

‘更改数据库表名，入口参数：老表名、新表名
Sub RenameTable(oldName, newName)
On Error Resume Next
objADOXDatabase.Tables(oldName).Name = newName
If Err Then
Call InnerHtml(“UpdateInfo”,”更改表名<font color=’blue’>错误</font>，请手动将数据库中 <b>”&oldName&”</b> 表名更改为 <b>”&newName&”</b>，原因” & Err.Description)
Err.Clear
Else
Call InnerHtml(“UpdateInfo”,”更改表名 “&OldName&” To “&newName&” 成功”)
End If
End Sub

‘删除字段通用函数
Sub DelColumn(TableName,ColumnName)
On Error Resume Next
Conn.Execute(“Alter Table “&TableName&” Drop “&ColumnName&””)
If Err Then
Call InnerHtml(“UpdateInfo”,”删除 “&TableName&” 表中字段<font color=’blue’>错误</font>，请手动将数据库中 <b>”&ColumnName&”</b> 字段删除，原因” & Err.Description)
Err.Clear
Else
Call InnerHtml(“UpdateInfo”,”删除 “&TableName&” 表中字段 “&ColumnName&” 成功”)
End If
End Sub

‘添加字段通用函数
Sub AddColumn(TableName,ColumnName,ColumnType)
On Error Resume Next
Conn.Execute(“Alter Table “&TableName&” Add “&ColumnName&” “&ColumnType&””)
If Err Then
Call InnerHtml(“UpdateInfo”,”新建 “&TableName&” 表中字段<font color=’blue’>错误</font>，请手动将数据库中 <b>”&ColumnName&”</b> 字段建立，属性为 <b>”&ColumnType&”</b>，原因” & Err.Description)
Err.Clear
Else
Call InnerHtml(“UpdateInfo”,”新建 “&TableName&” 表中字段 “&ColumnName&” 成功”)
End If
End Sub

‘更改字段通用函数
Sub ModColumn(TableName,ColumnName,ColumnType)
On Error Resume Next
Conn.Execute(“Alter Table “&TableName&” Alter Column “&ColumnName&” “&ColumnType&””)
If Err Then
Call InnerHtml(“UpdateInfo”,”更改 “&TableName&” 表中字段属性<font color=’blue’>错误</font>，请手动将数据库中 <b>”&ColumnName&”</b> 字段更改为 <b>”&ColumnType&”</b> 属性，原因” & Err.Description)
Err.Clear
Else
Call InnerHtml(“UpdateInfo”,”更改 “&TableName&” 表中字段属性 “&ColumnName&” 成功”)
End If
End Sub

‘删除表通用函数
Sub DelTable(TableName)
On Error Resume Next
Conn.Execute(“Drop Table “&TableName&””)
If Err Then
Call InnerHtml(“UpdateInfo”,”删除 “&TableName&” 表<font color=’blue’>错误</font>，请手动将数据库中 <b>”&TableName&”</b> 表删除，原因” & Err.Description)
Err.Clear
Else
Call InnerHtml(“UpdateInfo”,”删除 “&TableName&” 表成功”)
End If
End Sub

‘添加字段索引
Sub AddIndex(ByVal TableName, ByVal IndexName, ByVal ValueText)
On Error Resume Next
Conn.Execute(“CREATE INDEX ” & IndexName & ” ON ” & TableName & “(” & ValueText & “)”)
If Err.Number <> 0 Then
Call InnerHtml(“UpdateInfo”,”在 ” & TableName & ” 表新建” & IndexName & “索引错误，原因” & Err.Description & “操作:跳过.”)
Err.Clear()
Else
Call InnerHtml(“UpdateInfo”,”在 ” & TableName & ” 表添加 ” & IndexName & ” 索引成功。”)
End If
End Sub

‘DROP INDEX TableName.IndexName
‘删除表索引
Sub DelIndex(ByVal TableName, ByVal IndexName)
On Error Resume Next
Conn.Execute(“DROP INDEX “& TableName &”.”& IndexName)
If Err.Number <> 0 Then
Call InnerHtml(“UpdateInfo”,”在 ” & TableName & ” 表删除” & IndexName & “索引错误，原因” & Err.Description & “操作:跳过.”)
Err.Clear()
Else
Call InnerHtml(“UpdateInfo”,”在 ” & TableName & ” 表删除 ” & IndexName & ” 索引成功。”)
End If
End Sub

‘更改表TableName的定义把字段ColumnName设为主键
Sub AddPRIMARYKEY(ByVal TableName, ByVal ColumnName)
On Error Resume Next
TableName = Replace(Replace(TableName,”[“,””),”]”,””)
Conn.Execute(“ALTER TABLE “& TableName & ” ADD CONSTRAINT PK_”&TableName&” PRIMARY KEY (” & ColumnName & “)”)
If Err.Number <> 0 Then
Call InnerHtml(“UpdateInfo”,”在 ” & TableName & ” 将字段” & ColumnName & ” 添加为主键时出错，原因” & Err.Description & “操作:跳过.请手工修改该字段属性。”)
Err.Clear()
Else
Call InnerHtml(“UpdateInfo”,”在 ” & TableName & ” 将字段” & ColumnName & ” 添加为主键成功。”)
End If
End Sub

‘更改表TableName的定义把字段ColumnName主键的定义删除
Sub DelPRIMARYKEY(ByVal TableName, ByVal ColumnName)
On Error Resume Next
Response.Write “ALTER TABLE “& TableName & ” DROP PRIMARY KEY (” & ColumnName & “)”
Conn.Execute(“ALTER TABLE “& TableName & ” DROP PRIMARY KEY (” & ColumnName & “)”)
If Err.Number <> 0 Then
Call InnerHtml(“UpdateInfo”,”在 ” & TableName & ” 将字段” & ColumnName & ” 主键的定义删除时出错，原因” & Err.Description & “操作:跳过.请手工修改该字段属性。”)
Err.Clear()
Else
Call InnerHtml(“UpdateInfo”,”在 ” & TableName & ” 将字段” & ColumnName & ” 主键的定义删除成功。”)
End If
End Sub
%>