ddl语言自定义数据库字段

【文件属性】：
文件名称：ddl语言自定义数据库字段
文件大小：4KB
文件格式：RAR
更新时间：2010-11-03 14:44:19
ddl语言自定义数据库字段

<%
sFileName = "alter_manage.asp"
tableName = "C_alter"
tableName2 = "product"
action = Trim(Request.QueryString("action"))
%>

<%
Select Case action
case ""
Call ShowContent()
case "add"
Call add()
case "addsave"
Call addsave()
case "modify"
Call modify()
case "modifysave"
Call modifysave()
case "delall"
Call delall()
End Select
%>

<%
Sub ShowContent()
Call gRsClose()
sSql = "select * from view_alter"

oRs.Open sSql,oConn,1,1

%>














<%
do while not oRs.eof and not oRs.bof
select case oRs("a_type")
case "nvarchar(255)"
a_type = "文本类型"
case "int"
a_type = "整数类型"
case "float"
a_type = "浮点类型"
case else
a_type = "未知类型"
end select
%>










<%
oRs.movenext
loop
%>

字 段 信 息 管 理					添加
选择	产品类别	字段类型	字段名称	字段意义	操作
<%Response.Write("" & vbCrlf)%>	<%Response.Write(oRs("c_name"))%>	<%Response.Write(a_type)%>	<%Response.Write(oRs("a_name"))%>	<%Response.Write(oRs("a_detail"))%>	">删除　">修改
<% Response.Write("全/反选" & vbCrlf) Response.Write("" & vbCrlf) %>

<%
Call gRsClose()
End Sub
%>


<%
Sub add()
%>

字 段 信 息 添 加
选择类别：	<%=gMakeClassSelect("a_classid","p_class",0)%>
字段类型：	请您选择类型 整数型 文本型 浮点型
字段名称：
字段意义：

<%
End Sub
%>


<%
Sub addsave()
a_classid = GetSafeStr(Trim(Request.Form("a_classid")))
a_name = GetSafeStr(Trim(Request.Form("a_name")))
a_detail = GetSafeStr(Trim(Request.Form("a_detail")))
a_type = GetSafeStr(Trim(Request.Form("a_type")))
a_regtime = now()
'判断主表中要添加的字段是否存在
eSql = "select * from syscolumns where id=object_id('"& tableName2 &"') and name='"& a_name &"'"
Call gRsClose()
oRs.Open eSql,oConn,1,3
if not oRs.EOF then
Call ErrorMsg(sFileName,"主表中字段已经存在在，三秒后返回",1)
end if
Call gRsClose()

'将字段相关信息添加到从表
sSql = "select * from "& tableName &" where 1=1"
Call gRsClose()
oRs.Open sSql,oConn,1,3
oRs.AddNew()
oRs("a_classid") = a_classid
oRs("a_name") = a_name
oRs("a_detail") = a_detail
oRs("a_type") = a_type
oRs("a_regtime") = a_regtime
oRs.Update()

'添加字段

oConn.Execute("ALTER TABLE "& tableName2 &" ADD "& a_name &" "& a_type &"")
Call ErrorMsg(sFileName,"添加字段成功，三秒后返回",1)
Call gRsClose()
End Sub
%>



<%
Sub modify()
%>

<%
Call gRsClose()
a_id = Trim(Request.QueryString("a_id"))
sSql = "select * from view_alter where a_id = "& a_id
oRs.Open sSql,oConn,1,1
If not oRs.eof and not oRs.bof Then
a_classid = oRs("a_classid")
a_name = oRs("a_name")
a_detail = oRs("a_detail")
a_type = oRs("a_type")
End If
Call gRsClose()
%>

字 段 信 息 添 加
选择类别：	<%=gMakeClassSelect("a_classid","p_class",a_classid)%>
字段类型：	>请您选择类型 >整数型 >文本型 >浮点型
字段名称：
字段意义：

<%
End Sub
%>

<%
Sub modifysave()
a_classid = GetSafeStr(Trim(Request.Form("a_classid")))
a_name = GetSafeStr(Trim(Request.Form("a_name")))
a_detail = GetSafeStr(Trim(Request.Form("a_detail")))
a_type = GetSafeStr(Trim(Request.Form("a_type")))
a_regtime = now()
a_id = Trim(Request.QueryString("a_id"))


'将字段相关信息添加到从表
sSql = "select * from "& tableName &" where a_id = "& a_id
Call gRsClose()
oRs.Open sSql,oConn,1,3

aname = oRs("a_name")
atype = oRs("a_type")
on error resume next
oRs("a_classid") = a_classid
'判断主表中是否有相同的字段(保持主从表中的数据一致)
set rs1=oConn.Execute("select 1 from syscolumns where id=object_id('"& tableName2 &"') and name='"& a_name &"'")
If rs1.eof and rs1.bof Then
oRs("a_name") = a_name
End If
rs1.close()
oRs("a_detail") = a_detail
oRs("a_type") = a_type
oRs.Update()

If a_name<>aname Then
'修改字段名称(判断主表中是否有相同的字段)
oConn.Execute("if not exists(select 1 from syscolumns where id=object_id('"& tableName2 &"') and name='"& a_name &"') EXEC sp_rename '"& tableName2 &".["& aname &"]', '"& a_name &"', 'COLUMN'")
End If
If a_type<>atype Then
'修改字段类型
oConn.Execute("ALTER TABLE "& tableName2 &" ALTER COLUMN "& a_name &" "& a_type &"")
End If

Call ErrorMsg(sFileName,"修改字段成功，三秒后返回",1)
Call gRsClose()
End Sub
%>

<%
Sub delall()
checkzi = Trim(Request("checkzi"))
If checkzi <> "" Then

Dim z1
z1 = tableName
Dim temp_id,last_id
last_id="("
temp_id = Split(Request("checkzi"),",")

For i=0 To UBound(temp_id)
last_id = last_id & Trim(temp_id(i)) & ","
Next
last_id = Left(last_id,Len(last_id)-1)
last_id = last_id & ")"
'last_id为最终要删除的记录ID

'取出要删除的对应字段
sSql = "select a_name from "& z1 &" where a_id in " & last_id
Call gRsClose()
oRs.Open sSql,oConn,1,1
dim a_names
a_names = ""
jj=1
do while not oRs.eof and not oRs.bof
If jj <> oRs.recordcount Then
a_names = a_names & oRs("a_name") &","
Else
a_names = a_names & oRs("a_name")
End If
oRs.movenext
jj = jj + 1
loop
Call gRsClose()

'删除主表对应字段
a_name = Split(a_names,",")
for i=0 to Ubound(a_name)
oConn.Execute("ALTER TABLE "& tableName2 &" DROP COLUMN "& a_name(i) &"")
next

'删除从表中字段的意义信息
sSql = "delete from " & z1 & " where a_id in " & last_id
oConn.Execute(sSql)
Call ErrorMsg(sFileName,"成功删除所选记录,三秒后将返回……",1)
End If
End Sub
%>

【文件预览】：
ddl增加字段
----备用字段.txt(668B)
----alter_manage.asp(13KB)