Aceess database Field Type collection and sorting passed the test

Drop-down box in Form

<Select name = "rstype <% = I %>" size = "1" id = "rstype <% = I %>">
<Option value = "autoincrement" <% if I = 0 then response. Write ("selected") %> auto number </option>
<Option value = "varchar" <% if I> 0 then response. Write ("selected") %> text </option>
<Option value = "text"> text </option>
<Option value = "nchar"> nchar </option>
<Option value = "nvarchar"> nvarchar </option>
<Option value = "string"> string </option>
<Option value = "memo"> remarks </option>
<Option value = "bit"> bit </option>
<Option value = "smallint"> smallint2 bytes </option>
<Option value = "tinyint"> tinyint </option>
<Option value = "int"> int4 bytes </option>
<Option value = "guid"> synchronous replication id </option>
<Option value = "uniqueidentifier"> uniqueidentifier </option>
<Option value = "integer"> long integer </option>
<Option value = "long"> long </option>
<Option value = "short"> integer </option>
<Option value = "real"> Single-precision Integer </option>
<Option value = "single"> single </option>
<Option value = "double"> double integer </option>
<Option value = "float"> float </option>
<Option value = "double"> byte integer </option>
<Option value = "numeric"> fractional numeric </option>
<Option value = "datetime"> date/time </option>
<Option value = "smalldatetime"> smalldatetime </option>
<Option value = "date"> date </option>
<Option value = "time"> time </option>
<Option value = "currency"> currency </option>
<Option value = "money"> money </option>
<Option value = "smallmoney"> smallmoney </option>
<Option value = "yesno"> yes/no </option>
<Option value = "image"> image </option>
<Option value = "HYPERLINK"> hyperlink </option>
<Option value = "oleobject"> OLE object </option>
</SELECT>

ASP processing code

SQL = ""
Rsname = CSTR (TRIM (Request ("rsname" & I )))
Rstype = CSTR (TRIM (Request ("rstype" & I )))
Rslenth = trim (Request ("rslenth" & I ))
Rsnull = CSTR (TRIM (Request ("rsnull" & I )))
Rsdefault = CSTR (TRIM (Request ("rsdefault" & I )))

Select case rstype
'Boolean, integer, long, currency, single, double, date, string, and variant (default )).

Case "autoincrement"
SQL = SQL & rsname & "autoincrement" & rsnull &","

Case "bit" 'failed
SQL = SQL & rsname & "bit not null"
If rsdefault <> "" And rsdefault = "1" then
SQL = SQL & "Default 1 ,"
Else
SQL = SQL & "Default 0 ,"
End if
 
Case "Boolean" 'failed
SQL = SQL & rsname & "Boolean not null"
If rsdefault <> "" And rsdefault = "yes" then
SQL = SQL & "Default 1 ,"
Elseif rsdefault <> "" And rsdefault = "true" then
SQL = SQL & "Default 1 ,"
Elseif rsdefault <> "" And rsdefault <> "0" then
SQL = SQL & "Default 1 ,"
Else
SQL = SQL & "Default 0 ,"
End if

Case "string"
If rslenth = "" then
SQL = SQL & rsname & "string (50)" & rsnull
Else
SQL = SQL & rsname & "string (" & CINT (rslenth) & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &"',"
Else
SQL = SQL &","
End if
 
Case "varchar"
If rslenth = "" then
SQL = SQL & rsname & "varchar (50)" & rsnull
Else
SQL = SQL & rsname & "varchar (" & CINT (rslenth) & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &"',"
Else
SQL = SQL &","
End if

Case "nvarchar"
If rslenth = "" then
SQL = SQL & rsname & "nvarchar (50)" & rsnull
Else
SQL = SQL & rsname & "nvarchar (" & CINT (rslenth) & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &"',"
Else
SQL = SQL &","
End if

Case "nchar"
If rslenth = "" then
SQL = SQL & rsname & "nchar (50)" & rsnull
Else
SQL = SQL & rsname & "nchar (" & CINT (rslenth) & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &"',"
Else
SQL = SQL &","
End if

Case "text"
If rslenth = "" then
SQL = SQL & rsname & "text (50)" & rsnull
Else
SQL = SQL & rsname & "text (" & CINT (rslenth) & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &"',"
Else
SQL = SQL &","
End if

Case "memo"
If rsdefault <> "" then
SQL = SQL & rsname & "memo" & "default" & rsdefault &"',"
Else
SQL = SQL & rsname & "memo" & rsnull &","
End if

Case "integer"
If rslenth = "" then
SQL = SQL & rsname & "integer" & rsnull
Else
SQL = SQL & rsname & "INTEGER (" & rslenth & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if
Case "float"
If rslenth = "" then
SQL = SQL & rsname & "float" & rsnull
Else
SQL = SQL & rsname & "float (" & rslenth & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if

Case "tinyint"
If rslenth = "" then
SQL = SQL & rsname & "tinyint" & rsnull
Else
SQL = SQL & rsname & "tinyint (" & rslenth & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if
Case "smallint"
If rslenth = "" then
SQL = SQL & rsname & "smallint" & rsnull
Else
SQL = SQL & rsname & "smallint (" & rslenth & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if
Case "int"
If rslenth = "" then
SQL = SQL & rsname & "int" & rsnull
Else
SQL = SQL & rsname & "int (" & rslenth & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if

Case "long"
If rslenth = "" then
SQL = SQL & rsname & "long" & rsnull
Else
SQL = SQL & rsname & "long (" & rslenth & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if

Case "Number"
If rslenth = "" then
SQL = SQL & rsname & "Number" & rsnull
Else
SQL = SQL & rsname & "number (" & rslenth & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if

Case "short"
If rslenth = "" then
SQL = SQL & rsname & "short" & rsnull
Else
SQL = SQL & rsname & "short (" & rslenth & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if


Case "single"
If rslenth = "" then
SQL = SQL & rsname & "single" & rsnull
Else
SQL = SQL & rsname & "single (" & rslenth & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if

Case "double"
If rslenth = "" then
SQL = SQL & rsname & "double" & rsnull
Else
SQL = SQL & rsname & "Double (" & rslenth & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if

Case "real"
If rslenth = "" then
SQL = SQL & rsname & "real" & rsnull
Else
SQL = SQL & rsname & "real (" & rslenth & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if

Case "numeric"
If rslenth = "" then
SQL = SQL & rsname & "numeric" & rsnull
Else
SQL = SQL & rsname & "numeric (" & rslenth & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if

Case "replication"
SQL = SQL & rsname & "replication ,"

Case "guid"
SQL = SQL & rsname & "guid ,"

Case "uniqueidentifier"
SQL = SQL & rsname & "uniqueidentifier ,"

Case "datetime" 'access97 supported
If rsdefault = "" then
SQL = SQL & rsname & "datetime" & rsnull &","
Else
SQL = SQL & rsname & "datetime" & rsnull & "default" & rsdefault &","
End if

Case "smalldatetime" 'access97 supported
If rsdefault = "" then
SQL = SQL & rsname & "smalldatetime" & rsnull &","
Else
SQL = SQL & rsname & "smalldatetime" & rsnull & "default" & rsdefault &","
End if

Case "date"
If rsdefault = "" then
SQL = SQL & rsname & "date" & rsnull &","
Else
SQL = SQL & rsname & "date" & rsnull & "default" & rsdefault &","
End if

Case "time"
If rsdefault = "" then
SQL = SQL & rsname & "time" & rsnull &","
Else
SQL = SQL & rsname & "time" & rsnull & "default" & rsdefault &","
End if
Case "yesno"
If rsdefault = "" then
SQL = SQL & rsname & "yesno" & rsnull &","
Else
SQL = SQL & rsname & "yesno" & rsnull & "default" & rsdefault &","
End if

Case "currency"
If rslenth = "" then
SQL = SQL & rsname & "currency" & rsnull
Else
SQL = SQL & rsname & "currency (" & rslenth & ")" & rsnull
End if

If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if
Case "money"
If rslenth = "" then
SQL = SQL & rsname & "money" & rsnull
Else
SQL = SQL & rsname & "Money (" & rslenth & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if

Case "smallmoney"
If rslenth = "" then
SQL = SQL & rsname & "smallmoney" & rsnull
Else
SQL = SQL & rsname & "smallmoney (" & rslenth & ")" & rsnull
End if
If rsdefault <> "" then
SQL = SQL & "default" & rsdefault &","
Else
SQL = SQL &","
End if

Case "variant" 'failed
If rsdefault = "" then
SQL = SQL & rsname & "variant" & rsnull &","
Else
SQL = SQL & rsname & "variant" & rsnull & "default" & rsdefault &","
End if

Case "HYPERLINK"
If rsdefault = "" then
SQL = SQL & rsname & "oleobject" & rsnull &","
Else
SQL = SQL & rsname & "oleobject" & rsnull & "default" & rsdefault &","
End if
Case "oleobject"
If rsdefault = "" then
SQL = SQL & rsname & "oleobject" & rsnull &","
Else
SQL = SQL & rsname & "oleobject" & rsnull & "default" & rsdefault &","
End if
Case "image"
If rsdefault = "" then
SQL = SQL & rsname & "image" & rsnull &","
Else
SQL = SQL & rsname & "image" & rsnull & "default" & rsdefault &","
End if

Case else
Response. write ("<SCRIPT> alert ('data category" & rstype & "cannot be identified or the table creation function for this category is not completed yet, and will be returned soon. '); location. href = 'createtable1224. ASP '; </SCRIPT> ")
Response. End ()
End select
Sql1 = sql1 & SQL
Next
Sql1 = left (sql1, Len (sql1)-1 )&")"
Response. Write (sql1)