| ASP创建ACCESS表或添加字段 |
创建一张空表：
Iyj7Ut http://blog.numino.net/
Sql="Create TABLE [表名]"
10yJb8 http://blog.numino.net/
创建一张有字段的表：
Bm2Vws 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))
7F8ifC http://blog.numino.net/
字段类型：
qaGmRx http://blog.numino.net/
2 : "SmallInt", // 整型
A2R2R8 http://blog.numino.net/
3 : "Int", // 长整型
0NyO58 http://blog.numino.net/
4 : "Real", // 单精度型
K0P6QF http://blog.numino.net/
5 : "Float", // 双精度型
z3S7rD http://blog.numino.net/
6 : "Money", // 货币
dReF7A http://blog.numino.net/
7 : "DateTime", // 日期时间
U12kp2 http://blog.numino.net/
11 : "Bit", // 是否
a51L60 http://blog.numino.net/
13 : "TimeStamp",
ZHR5Jg http://blog.numino.net/
17 : "TinyInt", // 字节
PWrnom http://blog.numino.net/
72 : "UniqueIdentifier", // 同步复制 ID
27Fep3 http://blog.numino.net/
128 : "Binary",
wm144A http://blog.numino.net/
129 : "Char",
M5rGDU http://blog.numino.net/
130 : "NChar",
IRsO23 http://blog.numino.net/
131 : "Decimal", // 小数
ZdK7LL http://blog.numino.net/
133 : "DateTime",
fRC8te http://blog.numino.net/
135 : "SmallDateTime",
6fVk49 http://blog.numino.net/
200 : "VarChar",
4XRysk http://blog.numino.net/
201 : "Text",
aWzm3L http://blog.numino.net/
202 : "VarChar", // 文本
igomkz http://blog.numino.net/
203 : "Text", // 备注
l7pxA2 http://blog.numino.net/
204 : "Binary", // 二进制
qz5mxm http://blog.numino.net/
205 : "Image" // OLE 对象
keRyN0 http://blog.numino.net/
以下字段为无编码字段（NChar、NVarchar、NText型）
0I1URj http://blog.numino.net/
8,128,130,202,203,204,205
31vP2z http://blog.numino.net/
以下字段为按当前系统内码编码的字段（Asp中可用CodePage=936纠正为gb2312内码）
6TH8IN http://blog.numino.net/
129,200,201
dO7R5S http://blog.numino.net/
在现有的表中增加字段：
lpR2R2 http://blog.numino.net/
Sql="alter table [表名] add column [字段名] varchar(200)"
Qh8K0e http://blog.numino.net/
修改字段类型：
9uKm00 http://blog.numino.net/
Sql="alter table [表名] Alter COLUMN 字段名] varchar(50)"
BBSxFu http://blog.numino.net/
删除表：
b36bGR http://blog.numino.net/
Sql="Drop table [表名]"
1WS2c5 http://blog.numino.net/
删除字段：
5b3y59 http://blog.numino.net/
sql="alter table [表名] drop [字段名]"
01nmE0 http://blog.numino.net/
修改字段：Alter TABLE [表名] Alter COLUMN [字段名] 类型(大小) NULL
7GX129 http://blog.numino.net/
新建约束：Alter TABLE [表名] ADD CONSTRAINT 约束名 CHECK ([约束字段] <= '2007-1-1')
5lwkj5 http://blog.numino.net/
删除约束：Alter TABLE [表名] Drop CONSTRAINT 约束名
7gMpD5 http://blog.numino.net/
新建默认值：Alter TABLE [表名] ADD CONSTRAINT 默认值名 DEFAULT 'Gziu.CoM' FOR [字段名]
4Q3eT7 http://blog.numino.net/
删除默认值：Alter TABLE [表名] Drop CONSTRAINT 默认值名
57Rsqn http://blog.numino.net/
=======================================
JxY0QN http://blog.numino.net/
conn.open connstr
e39G77 http://blog.numino.net/
sql="alter table [tablename] add hehe char(20)"
PRlJtl http://blog.numino.net/
conn.execute(sql)
Wsa08v http://blog.numino.net/
response.write("添加成功")
V2862H http://blog.numino.net/
ACCESS新建数据库和表还不简单，有了表字段初始化就更简单
pP8TGL http://blog.numino.net/
=======================================
1uRq0b http://blog.numino.net/
<%
hjMoOU http://blog.numino.net/
session("tablen")="news"
7M191o http://blog.numino.net/
'news是已存在的表名
94KdGM http://blog.numino.net/
session("fieldsn")="c"
0v185p http://blog.numino.net/
'要添加的字段名
b2M0W8 http://blog.numino.net/
connectionstring="provider=microsoft.jet.oledb.4.0;data source="&server.MapPath("data/qq.mdb")
pPzzc5 http://blog.numino.net/
set conn=server.createobject("adodb.connection")
W00rhd http://blog.numino.net/
conn.OPEN connectionstring
2hDGk3 http://blog.numino.net/
jhsql = " Alter Table "&session("tablen")&" add column "&session("fieldsn")&" real "
ZdVM0X http://blog.numino.net/
conn.execute(jhsql)
1uWf4S http://blog.numino.net/
%>
zL47Qc http://blog.numino.net/
================================
o5HA75 http://blog.numino.net/
生成数据表，添加字段，其中id字段为自动增加，测试通过~~
mhHf3d http://blog.numino.net/
Sub GenAutoIncrementFld()
PxRqoF http://blog.numino.net/
set cn=server.CreateObject("ADODB.Connection")
hi8OUi http://blog.numino.net/
set clx=server.CreateObject("ADOX.Column")
zl0Yse http://blog.numino.net/
set cat=server.CreateObject("ADOX.Catalog")
5imBpY http://blog.numino.net/
set tblnam=server.CreateObject("ADOX.Table")
HmsmxU http://blog.numino.net/
D4xAfp http://blog.numino.net/
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\网站制作\asp操作mdb\db\test.mdb"
5miQSn http://blog.numino.net/
Set cat.ActiveConnection = cn
XhV9ZR http://blog.numino.net/
tblnam.Name = "Test"
r3nMUo http://blog.numino.net/
clx.ParentCatalog = cat
cDCp6U http://blog.numino.net/
Sp6D0x http://blog.numino.net/
clx.Type = 3
zHH4je http://blog.numino.net/
clx.Name = "Id"
H3Ij9J http://blog.numino.net/
clx.Properties("AutoIncrement") = true
EptPx5 http://blog.numino.net/
tblnam.Columns.Append clx
1tDRDC http://blog.numino.net/
tblnam.Columns.Append "DataField",130,20
Rz8Na6 http://blog.numino.net/
cat.Tables.Append tblnam
72b9Sx http://blog.numino.net/
Set clx = Nothing
H0me9f http://blog.numino.net/
Set cat = Nothing
Fvvdnl http://blog.numino.net/
cn.Close
kjI9me http://blog.numino.net/
Set cn = Nothing
xGRft3 http://blog.numino.net/
End Sub
3U350x http://blog.numino.net/
call GenAutoIncrementFld
Y01P53 http://blog.numino.net/
===================================
5E6LsS http://blog.numino.net/
字段类型对应数值
Dvl07c http://blog.numino.net/
''---- DataTypeEnum Values ----
W9ju8D http://blog.numino.net/
Const adEmpty = 0
B8m54i http://blog.numino.net/
Const adTinyInt = 16
RhzrbJ http://blog.numino.net/
Const adSmallInt = 2
5C6xZ7 http://blog.numino.net/
Const adInteger = 3
C5mzt8 http://blog.numino.net/
Const adBigInt = 20
03dI5y http://blog.numino.net/
Const adUnsignedTinyInt = 17
Wa96ui http://blog.numino.net/
Const adUnsignedSmallInt = 18
VZ6ekx http://blog.numino.net/
Const adUnsignedInt = 19
x9X6lZ http://blog.numino.net/
Const adUnsignedBigInt = 21
B02SuU http://blog.numino.net/
Const adSingle = 4
sB6Sqk http://blog.numino.net/
Const adDouble = 5
U695S5 http://blog.numino.net/
Const adCurrency = 6
e4k27k http://blog.numino.net/
Const adDecimal = 14
91i2os http://blog.numino.net/
Const adNumeric = 131
dCtysq http://blog.numino.net/
Const adBoolean = 11
udsqFn http://blog.numino.net/
Const adError = 10
4ewiOQ http://blog.numino.net/
Const adUserDefined = 132
i328Bm http://blog.numino.net/
Const adVariant = 12
I4i3eU http://blog.numino.net/
Const adIDispatch = 9
u1Rk6h http://blog.numino.net/
Const adIUnknown = 13
uT3NX5 http://blog.numino.net/
Const adGUID = 72
LyIQfn http://blog.numino.net/
Const adDate = 7
W4jbh8 http://blog.numino.net/
Const adDBDate = 133
0IGM2U http://blog.numino.net/
Const adDBTime = 134
i5gKuZ http://blog.numino.net/
Const adDBTimeStamp = 135
ISuzA1 http://blog.numino.net/
Const adBSTR = 8
FZ5Cs9 http://blog.numino.net/
Const adChar = 129
pBW5aB http://blog.numino.net/
Const adVarChar = 200
ACXp6e http://blog.numino.net/
Const adLongVarChar = 201
96p05J http://blog.numino.net/
Const adWChar = 130
W37Qh9 http://blog.numino.net/
Const adVarWChar = 202
9vSXs1 http://blog.numino.net/
Const adLongVarWChar = 203
umrIPv http://blog.numino.net/
Const adBinary = 128
4X9MNC http://blog.numino.net/
Const adVarBinary = 204
JnHtJC http://blog.numino.net/
Const adLongVarBinary = 205
5iBZ3l http://blog.numino.net/
Const adChapter = 136
F1p47r http://blog.numino.net/
Const adFileTime = 64
oF2S62 http://blog.numino.net/
Const adPropVariant = 138
iCsb7v http://blog.numino.net/
Const adVarNumeric = 139
AQyXud http://blog.numino.net/
Const adArray = &H2000
|
|