[Original] Automatically generate insert, modified SQL

xiaoxiao2021-03-06 107

Sundy Original 2004-11-02 Small website for database design, simple operation, usually only inserted to delete modifications. Every time you have to write into the modified SQL, don't I feel very trouble, I think so, so I wrote an automatic insertion, modified SQL, which is more convenient, and it is an ASP code, for Access, SQL Server is applicable. If you have help you, you can use it, you can also improve it.

<% Dim tableName, strSQL, rsFields, fieldsCount, totalCountDim insertSQL, iFields, iDataFields, updateSQL, deleteSQLDim keyFieldName, funQuoteDim conndim dbPath'SQL SERVER connection string 'conn.Open ( "Driver = {sql server}; server = sundy; database = TEST; UID = SA; PWD =; ") Set conn = server.createObject (" adodb.connection ") 'below the Access database as an example dbconn =" driver = {Microsoft Access Driver (* .mdb)}; DBQ = "& Server.Mappath (" DATA / DB.MDB ") Conn.open dbconn 'table primary keypad name KeyfieldName = Request.form (" keyfield ")' is used to filter the" '"function name in the submission form" Funquote = Request.form ("Funquote") 'Name Tablename = Request ("Tablename") if Request.form ("Subtable") <> "" "" "" "" SELECT TOP 1 * from "& TableName Set Rsfields = Server.createObject ("AdoDb.Recordset") RSfields.open strsql, conn, 1, 1 fieldscount = rsfields.fields.count insertsql = "" "" "& 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 & "" "" "" "" & rsfields.fields (i) .Name & "") & ""

"Updatesql = updatesql &" "" & rsfields.fields (i) .name & "=" "& request.form (" "" & rsfields.fields (i) .name & "") & "" "" "Case 202 'char IDATAFIELDS = Idatafields & ""' "" "" "" "" "" "") & "" "Updatesql = UpdatesQL & "" "& Rsfields.fields (i) .name &" = '"" "" "" "" "" "" ")) &") & "" '"" Case 203' Text Idatafields = Idatafields & "" '"" & "& funquote &" (Request.form ("" "& rsfields.fields (i) .name &" ") &" " '"Updatesql = Updatesql &" "" & rsfields.fields (i) .name & "='" "&" & funquote "(" "" "" "& rsfields.fields (i) .name &" "")) & "" End Select ifields = ifields & "" "& _" & vbcrf idatafields = iDatafields & "" & _ "& vbcrlf UpdateSQL = Updatesql &", "& _" & VBCRLF End if Next IFI Elds = MID (ifields, 1, Len (Ifields) - 9) iDatafields =

MID (Idatafields, 1, Len (Idatafields) - 9) Insertsql = INSERTSQL & IFIELDS & ") Values (" & _ "& VBCRLF & Idatafields &" "" "" "" "" "" "" "" "" "" Updates QL = MID (Updatesql, 1, Len (Updatesql) - 9) & "" "& _" "WHERE" & keyfieldname & "=" "" "" "" & keyfieldname & "") "end ifconn.close () set conn = Nothing%> Body {Font-Family: "Song", Arial; Font-size: 9pt; Color: # 0000FF; Background-Color: #eeeeee;}

table name:
Keyword field:
Filter "'" function:
INSERT SQL:
<% response.write ("

"& INSERTSQL &" </ textarea> <br> ")%></p></div><div class="text-center mt-3 text-grey">
转载请注明原文地址:https://www.9cbs.com/read-97426.html</div><div class="plugin d-flex justify-content-center mt-3"></div><hr><div class="row"><div class="col-lg-12 text-muted mt-2"><i class="icon-tags mr-2"></i><span class="badge border border-secondary mr-2"><h2 class="h6 mb-0 small"><a class="text-secondary" href="tag-2.html">9cbs</a></h2></span></div></div></div></div><div class="card card-postlist border-white shadow"><div class="card-body"><div class="card-title"><div class="d-flex justify-content-between"><div><b>New Post</b>(<span class="posts">0</span>)
</div><div></div></div></div><ul class="postlist list-unstyled">

</ul></div></div><div class="d-none threadlist"><input type="checkbox" name="modtid" value="97426" checked /></div></div></div></div></div><footer class="text-muted small bg-dark py-4 mt-3" id="footer"><div class="container"><div class="row"><div class="col">CopyRight © 2020 All Rights Reserved

</div><div class="col text-right">Processed:
<b>0.033</b>, SQL:
                <b>9</b></div></div></div></footer><script src="./lang/en-us/lang.js?2.2.0"></script><script src="view/js/jquery.min.js?2.2.0"></script><script src="view/js/popper.min.js?2.2.0"></script><script src="view/js/bootstrap.min.js?2.2.0"></script><script src="view/js/xiuno.js?2.2.0"></script><script src="view/js/bootstrap-plugin.js?2.2.0"></script><script src="view/js/async.min.js?2.2.0"></script><script src="view/js/form.js?2.2.0"></script><script>
var debug = DEBUG = 0;
var url_rewrite_on = 1;
var url_path = './';
var forumarr = {"1":"Tech"};
var fid = 1;
var uid = 0;
var gid = 0;
xn.options.water_image_url = 'view/img/water-small.png';
</script><script src="view/js/wellcms.js?2.2.0"></script><a class="scroll-to-top rounded" href="javascript:void(0);"><i class="icon-angle-up"></i></a><a class="scroll-to-bottom rounded" href="javascript:void(0);" style="display: inline;"><i class="icon-angle-down"></i></a></body></html><script>
var forum_url = 'list-1.html';
var safe_token = 'ePp4A3dujsGofUuc8kOdjWXmzkuUwCQi7TujhZL0TVp_2FlOO7ZN4F7HwuziRbw9XzN3NlNx5SRs2l_2Bp4oYLeYlw_3D_3D';
var body = $('body');
body.on('submit', '#form', function() {
var jthis = $(this);
    var jsubmit = jthis.find('#submit');
jthis.reset();
jsubmit.button('loading');
var postdata = jthis.serializeObject();
$.xpost(jthis.attr('action'), postdata, function(code, message) {
if(code == 0) {
location.reload();
} else {
$.alert(message);
jsubmit.button('reset');
}
});
return false;
});

function resize_image() {
var jmessagelist = $('div.message');
var first_width = jmessagelist.width(); 
jmessagelist.each(function() {
var jdiv = $(this);
var maxwidth = jdiv.attr('isfirst') ? first_width : jdiv.width();
var jmessage_width = Math.min(jdiv.width(), maxwidth);
jdiv.find('img, embed, iframe, video').each(function() {
var jimg = $(this);
var img_width = this.org_width;
var img_height = this.org_height;
if(!img_width) {
var img_width = jimg.attr('width');
var img_height = jimg.attr('height');
this.org_width = img_width;
this.org_height = img_height;
}

if(img_width > jmessage_width) {
if(this.tagName == 'IMG') {
jimg.width(jmessage_width);
jimg.css('height', 'auto');
jimg.css('cursor', 'pointer');
jimg.on('click', function() {

});
} else {
jimg.width(jmessage_width);
var height = (img_height / img_width) * jimg.width();
jimg.height(height);
}
}
});
});
}

function resize_table() {
$('div.message').each(function() {
var jdiv = $(this);
jdiv.find('table').addClass('table').wrap('<div class="table-responsive"></div>'); 
});
}

$(function() {
resize_image();
resize_table();
$(window).on('resize', resize_image);
});

$('#nav li[data-active="fid-1"]').addClass('active');
</script>