📄 tables_design.aspx
字号:
<!--#include file="includes/topnav.aspx"-->
<script language="Javascript" src="tables_design.js"></script>
<%
Dim table as String = request("Table")
Dim InsertPoint as String = Request("Insert")
if request("Save") <> "" and Request("Insert") = "" and Request("AddFields") = "" then
Dim sqlstmt as String
Dim X as Integer
Dim NeedsToBeAltered as Boolean = false
Dim Precision as Integer
Dim Scale as Integer
Dim Found as Boolean = false
Dim TableName as String
Dim FieldName as String
Dim pKeys as String = ""
if table <> "" then ' The table is being altered...look for changes to the table definition
For X = 1 to Request("FieldCount")
Try
if Request("Type_" & X).ToString <> Request("cType_" & X).ToString or _
Request("Length_" & X) <> Request("cLength_" & X) or Request("Precision_" & X) <> Request("cPrecision_" & X) or _
Request("Scale_" & X) <> Request("cScale_" & X) or Request("Nulls_" & X) <> Request("cNulls_" & X) or _
Request("Default_" & X) <> Request("cDefault_" & X) or Request("Identity_" & X) <> Request("cIdentity_" & X) or _
Request("Seed_" & X) <> Request("cSeed_" & X) or Request("Increment_" & X) <> REquest("cIncrement_" & X) then
NeedsToBeAltered = true
end if
Catch
End Try
Next
end if
if table="" then
NeedsToBeAltered = true
TableName = request("NewTable")
else
TableName = table & "_Temp"
end if
if NeedsToBeAltered then
sqlStmt = "CREATE TABLE [" & TableName & "]" & vbCRLF
sqlstmt = sqlstmt & " (" & vbCRLF
For X = 1 to Request("FieldCount")
if request("Name_" & X) <> "" or Request("cName_" & X) <> "" then
Found = true
if request("Name_" & X) <> "" then
FieldName = request("Name_" & X)
else
FieldName = request("cName_" & X)
end if
if request("Type_" & X) <> "real" then
sqlstmt = sqlstmt & " " & FieldName & " " & request("Type_" & X)
else
sqlstmt = sqlstmt & " " & FieldName & " float"
end if
Select Case lcase(request("Type_" & X))
case "float" :
sqlstmt = sqlstmt & " (53) "
case "real" :
sqlstmt = sqlstmt & " (24) "
case "numeric", "decimal" :
if request("Precision_" & X) = "" then
Precision = 18
else
Precision = request("Precision_" & X)
end if
if request("Scale_" & X) = "" then
Scale = 0
else
Scale = request("Scale_" & X)
end if
sqlstmt = sqlstmt & " (" & Precision & "," & Scale & ") "
case "text", "image", "ntext", "int", "bigint", "smallint", "tinyint", "bit", "datetime", "smalldatetime", "money", "smallmoney", "timestamp", "uniqueidentifier":
sqlstmt = sqlstmt & " "
case "binary", "char", "varchar", "nchar", "nvarchar", "varbinary" :
sqlstmt = sqlstmt & " (" & request("Length_" & x) & ") "
End Select
if request("Identity_" & X) <> "" then _
sqlstmt = sqlstmt & " IDENTITY(" & request("Seed_" & X) & ", " & request("Increment_" & X) & ") "
if request("Nulls_" & X) = "" then
sqlstmt = sqlstmt & " NOT NULL "
else
sqlstmt = sqlstmt & " NULL "
end if
if request("Default_" & X) <> "" then _
sqlstmt = sqlstmt & " DEFAULT " & request("Default_" & X) & " "
if request("Key_" & X) <> "" then _
pKeys = pKeys & request("Name_" & X) & ","
sqlstmt = sqlstmt & ","
end if
Next
sqlstmt = Left(sqlstmt, Len(sqlstmt) - 1)
if pKeys <> "" then pKeys = Left(pKeys, Len(pKeys) - 1)
sqlstmt = sqlstmt & vbCRLF & " )"
if found then
Try
d.execute(sqlstmt) ' SQL Statement to Build the new table
Catch e as Exception
d = Nothing
DisplayError (e)
End Try
else
DisplayMessage ("<h2>No fields entered in the table</h2><br>Table not created.")
end if
end if
if Table <> "" then
if NeedsToBeAltered then
Dim sqlstmt2 as String = "Select "
Dim FoundIdentity as Boolean = False
sqlstmt = "Insert Into [" & TableName & "] ("
For X = 1 to Request("FieldCount")
if request("cName_" & X) <> "" then
sqlstmt = sqlstmt & request("Name_" & X) & ","
sqlstmt2 = sqlstmt2 & Conversion(request("cName_" & X), request("Type_" & X), request("cType_" & X), request("Length_" & X), request("Precision_" & X), request("Scale_" & X)) & ","
end if
if request("Identity_" & X) <> "" then _
FoundIdentity = True
Next
sqlstmt = Left(sqlstmt, Len(sqlstmt) - 1) & ")"
sqlstmt2 = Left(sqlstmt2, Len(sqlstmt2) - 1) & " from [" & Table & "] "
sqlstmt = sqlstmt & " " & sqlstmt2
if FoundIdentity then _
sqlstmt = "SET IDENTITY_INSERT [" & TableName & "] ON " & sqlstmt & " SET IDENTITY_INSERT [" & TableName & "] OFF"
Try ' Try to insert the data from the original table into the new table and delete the old table
d.execute (sqlstmt)
d.execute ("DROP TABLE [" & table & "]")
d.getDataSPA("sp_rename '" & tableName & "', '" & table & "'")
Catch e as Exception ' Couldn't get data From old table to new so drop new table and keep old
d.execute ("DROP TABLE [" & tableName & "]")
d = nothing
DisplayError(e)
End try
if request("NewTable") <> table then
d.getDataSPA("sp_rename '" & table & "', '" & request("NewTable") & "'")
end if
else
'For X = 1 to Request("FieldCount")
' if request("Name_" & X) <> request("cName_" & x) and request("cName_" & X) <> "" then
' d.getDataSPA("sp_rename '" & table & "." & request("cName_" & X) & "', '" & request("Name_" & X) & "', 'COLUMN'")
' end if
'Next
end if
end if
table = request("NewTable")
dr = d.GetDataSPA("sp_pkeys '" & table & "'")
if dr.read()
d.Execute("DROP INDEX " & table & "." & dr("PK_NAME"))
end if
if pKeys <> "" then
d.execute("ALTER TABLE [" & table & "] ADD CONSTRAINT PK_" & Replace(table, " ", "_") & " PRIMARY KEY (" & pKeys & ")")
end if
%>
<script language="Javascript">
parent.frames["LeftFrame"].location.href='databases.aspx';
parent.frames["MainFrame"].location.href='tables_properties.aspx?table=<%= table %>';
</script>
<%
Response.end ' End here so the javascript above will work
end if
%>
<script language="VB" runat="Server">
' The Following Function makes the Explicit Conversions Required on the field to go from Type2 to Type1
Function Conversion (Field as String, Type1 as String, Type2 as String, Length as String, Precision as String, Scale as String) as String
Dim PrScale as String
Type1 = replace(Type1, "(", "")
Type1 = replace(Type1, ")", "")
if instr(Type1, " ") then
Type1 = Trim(Left(Type1, Instr(Type1, " ")))
end if
Type2 = replace(Type2, "(", "")
Type2 = replace(Type2, ")", "")
if instr(Type2, " ") then
Type2 = Trim(Left(Type2, Instr(Type2, " ")))
end if
Select Case lcase(Type1)
case "float" :
prScale = " (53) "
case "real" :
prScale = " (24) "
case "numeric", "decimal" :
if Precision = "" then
Precision = 18
else
Precision = Precision
end if
if Scale = "" then
Scale = 0
else
Scale = Scale
end if
prScale = " (" & Precision & "," & Scale & ") "
End Select
Select Case lcase(Type2)
Case "char", "varchar", "nchar", "nvarchar":
Select Case lcase(Type1)
case "binary": return " CONVERT(binary(" & Length & "), " & Field & ")"
case "varbinary": return " CONVERT(varbinary(" & Length & "), " & Field & ")"
case "money": return " CONVERT(money, " & Field & ")"
case "smallmoney": return " CONVERT(smallmoney, " & Field & ")"
case "timestamp": return " CONVERT(timestamp, " & Field & ")"
case else: return Field
End Select
case "datetime", "smalldatetime":
Select Case lcase(Type1)
case "binary": return " CONVERT(binary(" & Length & "), " & Field & ")"
case "varbinary": return " CONVERT(varbinary(" & Length & "), " & Field & ")"
case "decimal": return " CONVERT(decimal" & prScale & ", " & Field & ")"
case "numeric": return " CONVERT(numeric" & prScale & ", " & Field & ")"
case "float": return " CONVERT(float" & prScale & ", " & Field & ")"
case "real": return " CONVERT(float" & prScale & ", " & Field & ")"
case "bigint": return " CONVERT(bigint, " & Field & ")"
case "int": return " CONVERT(int, " & Field & ")"
case "smallint": return " CONVERT(smallint, " & Field & ")"
case "tinyint": return " CONVERT(tinyint, " & Field & ")"
case "money": return " CONVERT(money, " & Field & ")"
case "smallmoney": return " CONVERT(smallmoney, " & Field & ")"
case "bit": return " CONVERT(bit, " & Field & ")"
case "timestamp": return " CONVERT(timestamp, " & Field & ")"
case else: return Field
End Select
case "money", "smallmoney":
Select Case lcase(Type1)
case "char": return " CONVERT(char(" & Length & "), " & Field & ")"
case "varchar": return " CONVERT(varchar(" & Length & "), " & Field & ")"
case "nchar": return " CONVERT(nchar(" & Length & "), " & Field & ")"
case "nvarchar": return " CONVERT(nvarchar(" & Length & "), " & Field & ")"
case else: return Field
End Select
case "ntext":
Select Case lcase(Type1)
case "char": return " CONVERT(char(" & Length & "), " & Field & ")"
case "varchar": return " CONVERT(varchar(" & Length & "), " & Field & ")"
case else: return Field
End Select
case "text":
Select Case lcase(Type1)
case "nchar": return " CONVERT(nchar(" & Length & "), " & Field & ")"
case "nvarchar": return " CONVERT(nvarchar(" & Length & "), " & Field & ")"
case else: return Field
End Select
case else: return Field
End Select
End Function
Sub DrawRow (Count, Column_Name, Type_Name, Length, Precision, Scale, Is_Nullable, Column_Def, IdentSeed, IdentIncr, cColumn_Name, _
cType_Name, cLength, cPrecision, cScale, cIs_Nullable, cColumn_Def, cIdentSeed, cIdentIncr, Key, cKey, LoadType)
Dim iLength as Integer
Dim iPrecision as Integer
Dim ciLength as Integer
Dim ciPrecision as Integer
With Response
.write("<tr>")
.write("<td class=""TableGrid"">")
.write("<input type=""checkbox"" name=""Key_" & Count & """" & iif(Key = "on", " CHECKED " , "") & ">" & vbCRLF)
.write("<input type=""hidden"" size=""5"" name=""cKey_" & Count & """ value=""" & iif(cKey = "on", "on" , "") & """>" & vbCRLF)
.write("</td><td class=""TableGrid"">")
.write("<input type=""text"" size=""40"" name=""Name_" & Count & """ value=""" & Column_Name & """ class=""TableInput"">" & vbCRLF)
.write("<input type=""hidden"" size=""40"" name=""cName_" & Count & """ value=""" & cColumn_Name & """ class=""TableInput"">" & vbCRLF)
.write("</td>")
.write("<td class=""TableGrid"">")
DrawTypeBox (Type_Name, Count)
.write ("</td>")
.write("<td class=""TableGrid"">")
if LoadType = 1 then
if lcase(Type_Name) = "text" or lcase(Type_Name) = "ntext" or lcase(Type_Name) = "image" then
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -