IDC资讯中心
数据库设计的小网站，表单多而操作简单，一般就只有插入删除修改等操作。每次都要重复写插入修改的sql，
是不是觉得很麻烦呢，我是这么觉得，所以，自己写了一个自动产生插入，修改的sql，就方便多了，一下是asp代码，
对access，sql server都适用。如果对你有帮助，可以拿去用用，还可以改进。
<%
dim tablename,strsql,rsfields,fieldscount,totalcount
dim insertsql,ifields,idatafields,updatesql,deletesql
dim keyfieldname,funquote
dim conn
dim dbpath
sql server 连接字符串
conn.open("driver={sql server};server=sundy;database=test;uid=sa;pwd=;")
set conn = server.createobject("adodb.connection")
下面以access数据库为例
dbconn = "driver={microsoft access driver (*.mdb)};dbq=" & server.mappath("data/db.mdb")
conn.open dbconn
表的主键字段名
keyfieldname = request.form("keyfield")
用于过滤提交表单中的““的函数名
funquote = request.form("funquote")
表名
tablename = request("tablename")
if request.form("subtable")<> "" then
strsql = "select top 1 * from " & tablename
set rsfields = server.createobject("adodb.recordset")
rsfields.open strsql,conn,1,1
fieldscount = rsfields.fields.count
insertsql = """insert into " & tablename & "("" & _" & vbcrlf
updatesql = """update " & tablename & " set "" & _" & vbcrlf
for i = 0 to fieldscount – 1
if instr(",3,202,203,","," & rsfields.fields(i).type & ",") > 0 and ucase(rsfields.fields(i).name) <> ucase(keyfieldname) then
ifields = ifields & """" & rsfields.fields(i).name
select case rsfields.fields(i).type
case 3 int
idatafields = idatafields & """"" & request.form(""" & rsfields.fields(i).name & """) & """
updatesql = updatesql & """" & rsfields.fields(i).name & "="" & request.form(""" & rsfields.fields(i).name & """) & """
case 202 char
idatafields = idatafields & """"" & " & funquote & "(request.form(""" & rsfields.fields(i).name & """)） & """
updatesql = updatesql & """" & rsfields.fields(i).name & "="" & " & funquote & "(request.form(""" & rsfields.fields(i).name & """)) & """
case 203 text
idatafields = idatafields & """"" & " & funquote & "(request.form(""" & rsfields.fields(i).name & """)） & """
updatesql = updatesql & """" & rsfields.fields(i).name & "="" & " & funquote & "(request.form(""" & rsfields.fields(i).name & """)) & """
end select
ifields = ifields & ", "" & _" & vbcrlf
idatafields = idatafields & ","" & _ " & vbcrlf
updatesql = updatesql & ","" & _ " & vbcrlf
end if
next
ifields = mid(ifields,1,len(ifields) – 9)
idatafields = mid(idatafields,1,len(idatafields) – 9)
insertsql = insertsql & ifields & ") values ("" & _" & vbcrlf & idatafields & ")"""
updatesql = mid(updatesql,1,len(updatesql) – 9) & """ & _ " & vbcrlf & """ where " & keyfieldname & "="" & request.querystring(""" & keyfieldname & """)"
end if
conn.close()
set conn = nothing
%>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=gb2312">
<title>自动生成插入，修改sql</title>
<style type="text/css">
body {
font-family: "宋体",arial;
font-size: 9pt;
color: #0000ff;
background-color: #eeeeee;
}
</style>
</head>
<body>
<form name="tableopreate" action="?" method="post">
表名：<input name="tablename" type="text" id="tablename" value="product_research"><br>
关键字字段：<input name="keyfield" type="text" id="keyfield" value="pf_id"><br>
过滤“”函数：<input name="funquote" type="text" id="funquote" value="fixquote"><input name="subtable" type="submit" id="subtable" value="submit"><br>
insert sql:<br><% response.write("<textarea name=""textarea"" cols=""60"" rows=""10"">" & insertsql & "</textarea><br>")%>
update sql:<br><% response.write("<textarea name=""textarea"" cols=""60"" rows=""10"">" & updatesql & "</textarea><br>")%>
</form>
</body>
</html>
