| ASP创建ACCESS表或添加字段 |
创建一张空表：
kL5PnU http://blog.numino.net/
Sql="Create TABLE [表名]"
e6Fu4b http://blog.numino.net/
创建一张有字段的表：
6PWTU4 http://blog.numino.net/
Sql="Create TABLE [表名]([字段名1] MEMO NOT NULL, [字段名2] MEMO, [字段名3] COUNTER NOT NULL, [字段名4] DATETIME, [字段名5] TEXT(200), [字段名6] TEXT(200))
r22xH2 http://blog.numino.net/
字段类型：
EVVu97 http://blog.numino.net/
2 : "SmallInt", // 整型
U8CnSI http://blog.numino.net/
3 : "Int", // 长整型
1gUn09 http://blog.numino.net/
4 : "Real", // 单精度型
D1eHlT http://blog.numino.net/
5 : "Float", // 双精度型
mJ7jg1 http://blog.numino.net/
6 : "Money", // 货币
c1tfIX http://blog.numino.net/
7 : "DateTime", // 日期时间
c9xq99 http://blog.numino.net/
11 : "Bit", // 是否
8e4fzf http://blog.numino.net/
13 : "TimeStamp",
H4iWq8 http://blog.numino.net/
17 : "TinyInt", // 字节
iJ93hw http://blog.numino.net/
72 : "UniqueIdentifier", // 同步复制 ID
21Mv0f http://blog.numino.net/
128 : "Binary",
n7M9nW http://blog.numino.net/
129 : "Char",
AgqMqJ http://blog.numino.net/
130 : "NChar",
QwGYb3 http://blog.numino.net/
131 : "Decimal", // 小数
yiMR09 http://blog.numino.net/
133 : "DateTime",
gFfZ6q http://blog.numino.net/
135 : "SmallDateTime",
8BTIRy http://blog.numino.net/
200 : "VarChar",
tZbWnu http://blog.numino.net/
201 : "Text",
GbtI8o http://blog.numino.net/
202 : "VarChar", // 文本
U7jkP7 http://blog.numino.net/
203 : "Text", // 备注
vio12u http://blog.numino.net/
204 : "Binary", // 二进制
1iYXcJ http://blog.numino.net/
205 : "Image" // OLE 对象
PrnOsf http://blog.numino.net/
以下字段为无编码字段（NChar、NVarchar、NText型）
cZUJ4R http://blog.numino.net/
8,128,130,202,203,204,205
upn6NW http://blog.numino.net/
以下字段为按当前系统内码编码的字段（Asp中可用CodePage=936纠正为gb2312内码）
n1B54n http://blog.numino.net/
129,200,201
T3gB26 http://blog.numino.net/
在现有的表中增加字段：
1jY05Z http://blog.numino.net/
Sql="alter table [表名] add column [字段名] varchar(200)"
45SzkF http://blog.numino.net/
修改字段类型：
m28eYy http://blog.numino.net/
Sql="alter table [表名] Alter COLUMN 字段名] varchar(50)"
9iR1vR http://blog.numino.net/
删除表：
6hi314 http://blog.numino.net/
Sql="Drop table [表名]"
0QZmGc http://blog.numino.net/
删除字段：
knE8Lc http://blog.numino.net/
sql="alter table [表名] drop [字段名]"
7g1Sb5 http://blog.numino.net/
修改字段：Alter TABLE [表名] Alter COLUMN [字段名] 类型(大小) NULL
1l21EU http://blog.numino.net/
新建约束：Alter TABLE [表名] ADD CONSTRAINT 约束名 CHECK ([约束字段] <= '2007-1-1')
5E1sN3 http://blog.numino.net/
删除约束：Alter TABLE [表名] Drop CONSTRAINT 约束名
0chMU7 http://blog.numino.net/
新建默认值：Alter TABLE [表名] ADD CONSTRAINT 默认值名 DEFAULT 'Gziu.CoM' FOR [字段名]
nXKW2V http://blog.numino.net/
删除默认值：Alter TABLE [表名] Drop CONSTRAINT 默认值名
Bzxa6p http://blog.numino.net/
=======================================
ODksnf http://blog.numino.net/
conn.open connstr
dbPi62 http://blog.numino.net/
sql="alter table [tablename] add hehe char(20)"
bELGRN http://blog.numino.net/
conn.execute(sql)
U979To http://blog.numino.net/
response.write("添加成功")
0151Mz http://blog.numino.net/
ACCESS新建数据库和表还不简单，有了表字段初始化就更简单
f65nDZ http://blog.numino.net/
=======================================
anULBE http://blog.numino.net/
<%
EOlL4p http://blog.numino.net/
session("tablen")="news"
egAYbZ http://blog.numino.net/
'news是已存在的表名
LRm7Xq http://blog.numino.net/
session("fieldsn")="c"
MwxHbI http://blog.numino.net/
'要添加的字段名
MDRHXo http://blog.numino.net/
connectionstring="provider=microsoft.jet.oledb.4.0;data source="&server.MapPath("data/qq.mdb")
RZu54U http://blog.numino.net/
set conn=server.createobject("adodb.connection")
b2EGzx http://blog.numino.net/
conn.OPEN connectionstring
AC99j0 http://blog.numino.net/
jhsql = " Alter Table "&session("tablen")&" add column "&session("fieldsn")&" real "
3peU4y http://blog.numino.net/
conn.execute(jhsql)
Fe8i8R http://blog.numino.net/
%>
H9z5JP http://blog.numino.net/
================================
1Dw9Uf http://blog.numino.net/
生成数据表，添加字段，其中id字段为自动增加，测试通过~~
Fj2ArX http://blog.numino.net/
Sub GenAutoIncrementFld()
8BEEj5 http://blog.numino.net/
set cn=server.CreateObject("ADODB.Connection")
RIV44k http://blog.numino.net/
set clx=server.CreateObject("ADOX.Column")
06DMCM http://blog.numino.net/
set cat=server.CreateObject("ADOX.Catalog")
XMI9Ru http://blog.numino.net/
set tblnam=server.CreateObject("ADOX.Table")
mNkimP http://blog.numino.net/
a20xYp http://blog.numino.net/
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\网站制作\asp操作mdb\db\test.mdb"
61Z9Q5 http://blog.numino.net/
Set cat.ActiveConnection = cn
0L5Ygu http://blog.numino.net/
tblnam.Name = "Test"
JlMU4c http://blog.numino.net/
clx.ParentCatalog = cat
iqlVi9 http://blog.numino.net/
8ZWxqd http://blog.numino.net/
clx.Type = 3
9uKGGa http://blog.numino.net/
clx.Name = "Id"
cEqSiL http://blog.numino.net/
clx.Properties("AutoIncrement") = true
OV7Y0Q http://blog.numino.net/
tblnam.Columns.Append clx
YU1w3s http://blog.numino.net/
tblnam.Columns.Append "DataField",130,20
TkJAjl http://blog.numino.net/
cat.Tables.Append tblnam
Kr3x1x http://blog.numino.net/
Set clx = Nothing
zjzWhf http://blog.numino.net/
Set cat = Nothing
1iOVw9 http://blog.numino.net/
cn.Close
lmLe8Y http://blog.numino.net/
Set cn = Nothing
k6Ufc7 http://blog.numino.net/
End Sub
AItM8X http://blog.numino.net/
call GenAutoIncrementFld
53c4kx http://blog.numino.net/
===================================
0r23u5 http://blog.numino.net/
字段类型对应数值
GIIAL6 http://blog.numino.net/
''---- DataTypeEnum Values ----
U3Pv92 http://blog.numino.net/
Const adEmpty = 0
VNBsvP http://blog.numino.net/
Const adTinyInt = 16
M4xc7X http://blog.numino.net/
Const adSmallInt = 2
4gqkEx http://blog.numino.net/
Const adInteger = 3
8H6FKg http://blog.numino.net/
Const adBigInt = 20
3U4jEw http://blog.numino.net/
Const adUnsignedTinyInt = 17
Cuw6k3 http://blog.numino.net/
Const adUnsignedSmallInt = 18
I5fWZf http://blog.numino.net/
Const adUnsignedInt = 19
elXPC8 http://blog.numino.net/
Const adUnsignedBigInt = 21
FzvyVy http://blog.numino.net/
Const adSingle = 4
m6Eyxo http://blog.numino.net/
Const adDouble = 5
0QyUzl http://blog.numino.net/
Const adCurrency = 6
3ouft4 http://blog.numino.net/
Const adDecimal = 14
MK9hmL http://blog.numino.net/
Const adNumeric = 131
933MyQ http://blog.numino.net/
Const adBoolean = 11
DsmyEo http://blog.numino.net/
Const adError = 10
tYiq9o http://blog.numino.net/
Const adUserDefined = 132
95GluA http://blog.numino.net/
Const adVariant = 12
MbCdf2 http://blog.numino.net/
Const adIDispatch = 9
WDJmUb http://blog.numino.net/
Const adIUnknown = 13
t9I96J http://blog.numino.net/
Const adGUID = 72
bMx58O http://blog.numino.net/
Const adDate = 7
167BT2 http://blog.numino.net/
Const adDBDate = 133
yn9Whm http://blog.numino.net/
Const adDBTime = 134
e235e7 http://blog.numino.net/
Const adDBTimeStamp = 135
4t9b63 http://blog.numino.net/
Const adBSTR = 8
vfnYT5 http://blog.numino.net/
Const adChar = 129
3v6qYF http://blog.numino.net/
Const adVarChar = 200
hK3BX3 http://blog.numino.net/
Const adLongVarChar = 201
Q8fHBK http://blog.numino.net/
Const adWChar = 130
xzB6b6 http://blog.numino.net/
Const adVarWChar = 202
nQUhfk http://blog.numino.net/
Const adLongVarWChar = 203
NdwSP9 http://blog.numino.net/
Const adBinary = 128
mmqvl2 http://blog.numino.net/
Const adVarBinary = 204
k9S8V5 http://blog.numino.net/
Const adLongVarBinary = 205
QfYL9j http://blog.numino.net/
Const adChapter = 136
KzW2HQ http://blog.numino.net/
Const adFileTime = 64
YM9bTS http://blog.numino.net/
Const adPropVariant = 138
Mfdfnl http://blog.numino.net/
Const adVarNumeric = 139
gWWMis http://blog.numino.net/
Const adArray = &H2000
|
|