模板
教程
環境
性能
功能
管理
自動生成插入，修改的SQL
發表于：2007-06-30來源：作者：點擊數：
標簽：
數據庫 設計的小網站，表單多而操作簡單，一般就只有插入刪除修改等操作。每次都要重復寫插入修改的SQL， 是不是覺得很麻煩呢，我是這么覺得，所以，自己寫了一個自動產生插入，修改的SQL，就方便多了，一下是ASP代碼， 對ACCESS，SQL Server都適用。如果對
數據庫設計的小網站，表單多而操作簡單，一般就只有插入刪除修改等操作。每次都要重復寫插入修改的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")
@#下面以Aclearcase/" target="_blank" >ccess數據庫為例
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>
