|
|
自动生成插入,修改的SQL |
|
|
作者:未知 来源:月光软件站 加入时间:2005-2-28 月光软件站 |
数据库设计的小网站,表单多而操作简单,一般就只有插入删除修改等操作。每次都要重复写插入修改的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>

|
|
相关文章:相关软件: |
|