Javascript连接Access数据库完整实例

var roc = roc || {}; roc.db = roc.db ||{}; //创建一个连接 roc.db.createDb = function(){ var conn = new ActiveXObject("ADODB.Connection"), fso = new ActiveXObject("Scripting.FileSystemObject"), connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fso.GetFile("./db/Sigma.mdb"); conn.Open(connstr);//打开数据库 roc.db.conn = conn; return roc.db.conn; }; //获取连接 roc.db.getDb = function(){ if( roc.db.conn ){ return roc.db.conn; }else{ return roc.db.createDb(); } }; //关闭连接 roc.db.closeConn = function(){ if( roc.db.conn ){ roc.db.conn.close(); roc.db.conn = null; } }; //获取结果集 roc.db.getRs = function( sqlStr ){ var mysql = roc.dom.trim( sqlStr ); if(mysql == ''){return;} var rs = new ActiveXObject("ADODB.Recordset"), myConn = roc.db.getDb(); rs.open( sqlStr , myConn ); return rs; }; //关闭结果集 roc.db.closeRs = function( rs ){ rs.close(); rs =null; }; //更新、插入 roc.db.execute = function( sqlStr ){ var myConn = roc.db.getDb(); myConn.execute( sqlStr ); roc.db.closeConn(); }; /*---------Sigma:“我任你践踏我的尊严而毫不生气，是因为我爱你。”---------*/ roc.dom = roc.dom ||{}; roc.dom.id = function( id ){ if(typeof id == 'string' || id instanceof String) { return document.getElementById(id); } else if(id && id.nodeName && (id.nodeType == 1 || id.nodeType == 9)) { return id; } return null; }; /** * @method tagName 根据标签获取指定dom元素 * @param {String} tagName 元素标签名称 * @param {HTMLElement} el 元素所属的文档对象 默认为当前文档 * @return {HTMLElement} 返回HTMLElement元素 */ roc.dom.tagName = function(tagName, el) { var el = el || document; return el.getElementsByTagName(tagName); }; //删除左右两端的空格 roc.dom.trim = function (str) { return (str+'').replace(/(^\s*)|(\s*$)/g, ""); } /** * @method show 显示目标元素 * @param {Element} element 目标元素或目标元素的id * @param {String} element 目标元素 */ roc.dom.show = function (element) { element = roc.dom.id(element); element.style.display = ''; return element; }; /** * @method hide 隐藏目标元素 * @param {Element} element 目标元素或目标元素的id * @param {String} element 目标元素 */ roc.dom.hide = function (element) { element = roc.dom.id(element); element.style.display = 'none'; return element; }; /** * @method hasClass 判断元素是否含有 class * @param {Element} el 元素 * @param {String} className class 名称 */ roc.dom.hasClass = function(el, className){ var re = new RegExp('(^|\\s)' + className + '(\\s|$)'); return re.test(el.className); }; /** * @method addClass 给元素添加 class * @param {Element} el 元素 * @param {String} className class 名称 */ roc.dom.addClass = function(el, className){ if(!roc.dom.hasClass(el, className)){ el.className = el.className + ' ' + className; } }; /** * @method removeClass 给元素移除 class * @param {Element} el 元素 * @param {String} className class 名称 */ roc.dom.removeClass = function(el, className){ var re = new RegExp('(^|\\s)' + className + '(?:\\s|$)') el.className = el.className.replace(re, '$1'); }; /** * date对象命名空间 * * @namespace * @name data */ roc.date = roc.date || {}; /** * @method format 对目标日期对象进行格式化 * @param {Object} timestamp 目标日期对象 * @return {String} str 格式化后的时间 */ roc.date.format = function(timestamp) { if(timestamp =='' )return ''; var str = '', temptime = new Date(Number(timestamp)); str += temptime.getFullYear() + '-'; str += temptime.getMonth() + 1 + '-'; str += temptime.getDate() + ' '; str += String(temptime.getHours()).length > 1 ? (temptime.getHours() + ':') : ('0' + temptime.getHours() + ':'); str += String(temptime.getMinutes()).length > 1 ? (temptime.getMinutes()) : ('0' + temptime.getMinutes()); return str; }; /** * cookie对象命名空间 * * @namespace * @name cookie */ roc.cookie = roc.cookie || {}; /** * @method set * @param {String} name cookie的键 * @param {String} value cookie的值 * @param {String} expires 失效时间(小时) * @param {String} domain domain域 * @param {String} path 路径 * @param {String} secure 是否支持https */ roc.cookie.set = function(name, value, expires, domain, path, secure) { var text = encodeURIComponent(value), date = expires; if(date && typeof date === 'number') { date = new Date(); date.setTime(date.getTime() + (expires * 3600000)); } if(date instanceof Date) { text += '; expires=' + date.toUTCString(); } if(domain) { text += '; domain=' + domain; } if(path) { text += '; path=https://www.jb51.net/' + path; } else { text += '; path=https://www.jb51.net/'; } if(secure) { text += '; secure'; } document.cookie = name + '=' + text; }; /** * @method get * @param {String} name cookie的键 */ roc.cookie.get = function(name) { var ret, m; if(name) { if((m = document.cookie.match('(?:^| )' + name + '(?:(?:=([^;]*))|;|$)'))) { ret = m[1] ? decodeURIComponent(m[1]) : ''; } } return ret; }; roc.util = roc.util || {}; roc.util.loger = function( type ,msg ){ switch(type){ case 'pop': alert(msg); break; case 'float':break; default:break; } }; roc.util.resultBlink = function( msg ){ //操作闪烁提示 var $ = roc , opt = $.dom.id("optTip"); $.util.toogle = $.util.toogle || 0; clearTimeout(roc.util.t);//调试 opt.innerHTML = msg ; $.dom.show(opt); opt.className = "blink" + $.util.toogle%2; $.util.toogle++; roc.util.t = setTimeout(function(){ $.dom.hide(opt); },$.config.BLINK_DELAY); }; roc.util.onlyInputNumber = function( id ){ //限制文本框、文本域只能输入数字 var $ = roc , num = $.dom.id( id ); if( num.tagName.toLowerCase() != 'input' || num.tagName.toLowerCase() != 'textarea' ){ return ; } $.util.addEvent( num , 'keypress' , function( e ){ var e = e || window.event ; if(e.keyCode >= 48 && e.keyCode <= 57){alert() return true; } return false; }); }; roc.util.addEvent = function(elem, type, fn, useCapture) { if(elem.addEventListener) { //DOM2.0 elem.addEventListener(type, fn, useCapture); return true; } else if(elem.attachEvent) { //IE5+ elem.attachEvent('on' + type, fn); return true; } else { //DOM 0 elem['on' + type] = fn; } }; roc.config = roc.config || {}; roc.config = roc.config ||{ BLINK_DELAY:3000, SELECT_DELAY:1000 } roc.search = roc.search || {}; roc.search.getValues = function( e ){ //批量获取表单值,用于插入 var $ = roc, allIsNull = true, wrapStr = function( num ){ return '"'+ num + '"'; }, vals =[]; for(var i in e[0]){ var v =$.dom.trim($.dom.id( e[0][i] ).value + ''); if( v != ''){ allIsNull = false; } switch( e[1][i] ){ case 'date': case 'text': vals.push( wrapStr(v) ); break; case 'num': vals.push( v ); break; default:break; } } if(allIsNull){ return false; } return vals.join(','); }; roc.search.getSelSql = function(){ //组装搜索sql var $ = roc, addr = $.dom.trim($.dom.id("s_uaddr").value), phone = $.dom.trim($.dom.id("s_uphone").value), style = $.dom.trim($.dom.id("s_style").value), year = $.dom.trim($.dom.id("s_year").value ), month = $.dom.trim($.dom.id("s_month").value), date = $.dom.trim($.dom.id("s_date").value), datetype = $.dom.trim($.dom.id("s_datetype").value), mysql = 'select * from inslist where 1=1 ', datetypeName = datetype == 0 ? 'selltime':'addtime'; if( addr != ''){ mysql += ' and uaddr like "%' + addr + '%"'; } if( phone != ''){ mysql += ' and uphone ="' + phone + '"'; } if( style != ''){ mysql += ' and typeid = ' + style + ''; } if( year !=''){ mysql += ' and year(' + datetypeName + ') = ' + year + ''; } if( month !=''){ mysql += ' and month(' + datetypeName + ') = ' + month + ''; } if( date !=''){ mysql += ' and date(' + datetypeName + ') = ' + date + ''; } return mysql; }; //搜索 roc.search.seeking = function(){ if( !roc.search.getLock()){return;} var $ = roc , mySql = $.search.getSelSql(); html = $.search.getSel( mySql); $.search.setLock(false); $.dom.id("searchResult").innerHTML = html; $.util.resultBlink("查询完毕");//闪烁 }; roc.search.getSel = function( sqlStr ){ //查询 var $ = roc, rs = $.db.getRs( sqlStr ), filtRs = function ( str ){//处理字段 return ( str + '' ) == 'null' ? '':str; }, num = 1; total_receive = 0, total_prize = 0, html = "<table>" + "<colgroup>" + "<col />" + "<col />" + "<col />" + "<col/>" + "<col span='3'/>" + "<col />" + "<col />" + "<col />" + "<col />" + "<col />" + "</colgroup>" + "<tr>" + " <th> 序号 </th>" + " <th> 用户地址 </th>" + " <th> 用户电话 </th>" + " <th> 型号 </th>" + " <th> 代收款 </th>" + " <th> 货款 </th>" + " <th> 余额 </th>" + " <th> 安装人 </th>" + " <th> 销售电话 </th>" + " <th> 备注 </th>" + " <th> 销售日期 </th>" + " <th> 记录时间 </th>" + "</tr>"; while(!rs.EOF) { var id = num ,//filtRs(rs.Fields("id") ), uaddr = filtRs(rs.Fields("uaddr") ), uphone = filtRs(rs.Fields("uphone") ), typeid = filtRs(rs.Fields("typeid") ), received = filtRs(rs.Fields("received") ), prize = filtRs(rs.Fields("prize") ), unreceived = filtRs(rs.Fields("unreceived") ), installerid = filtRs(rs.Fields("installerid") ), sellerid = filtRs(rs.Fields("sellerid") ), remark = filtRs(rs.Fields("remark") ), selltime = $.date.format(filtRs(rs.Fields("selltime") )), addtime = $.date.format(filtRs(rs.Fields("addtime") ) ); html += "<tr jsselect='browzr_data'>" +"<td>" + id + "</td>" +"<td>" + uaddr + "</td>" +"<td>" + uphone + "</td>" +"<td>" + typeid + "</td>" +"<td>" + received + "</td>" +"<td>" + prize + "</td>" +"<td>" + unreceived + "</td>" +"<td>" + installerid + "</td>" +"<td>" + sellerid + "</td>" +"<td>" + remark + "</td>" +"<td>" + selltime + "</td>" +"<td>" + addtime + "</td>" +"</tr>"; //统计项 total_receive += received, total_prize += prize, num++; rs.moveNext(); } html = html +"<tr>" +"<td></td>" +"<td>&Sigma; </td>" +"<td></td>" +"<td></td>" +"<td>" + total_receive +"</td>" +"<td>" + total_prize +"</td>" +"<td>" + (total_prize - total_receive ) +"</td>" +"<td></td>" +"<td></td>" +"<td></td>" +"<td></td>" +"<td></td>" +"</table>"; $.db.closeRs(rs); $.db.closeConn(); return html; }; roc.search.getLock = function(){ //查询锁 if( typeof roc.search.searchLock == 'undefined' ){ roc.search.setLock(false); } return roc.search.searchLock; }; roc.search.setLock = function( key ){ roc.search.searchLock = key; }; //[[id],[type]] roc.search.addEls = [[ "uaddr", "uphone", "typeid", "received", "prize", "unreceived", "installerid", "sellerid", "remark", "selltime" ],[ 'text', 'text', 'num', 'num', 'num', 'num', 'num', 'num', 'text', 'date' ]]; roc.search.insert = function(){ //插入安装单记录 var $ = roc, getV = $.search.getValues( $.search.addEls ); if(!getV){ $.util.loger('pop','请填写信息后再保存！'); return; } var sqlStr = 'insert into inslist (uaddr,uphone,typeid,received,prize,unreceived,installerid,sellerid,remark,selltime) values ('+ getV +')'; $.db.execute( sqlStr ); $.util.resultBlink('保存安装单成功'); }; /*显示与隐藏*/ roc.dom.switchDiv = function( objDiv){ var $ = roc , cookieName = objDiv.id + 'cookie'; if( objDiv.style.display =='' || objDiv.style.display =='none' ){ $.dom.show( objDiv ); $.cookie.set(cookieName,0,9999999); }else{ $.dom.hide( objDiv ); $.cookie.set(cookieName,1,9999999); } }; //货物型号操作 roc.tstyle = roc.tstyle || {}; roc.tstyle.els = [ ['tname','tprize','tdesc'], ['text','text','text'] ]; roc.tstyle.insert = function(){ //插入记录 var $ = roc, getV = $.search.getValues( $.tstyle.els ); if( !getV ){ $.util.loger('pop','请填写信息后再保存！'); return; } var sqlStr = 'insert into type ( tname , tprize , tdesc ) values ('+ getV +')'; //$.util.loger('pop',sqlStr); $.db.execute( sqlStr ); $.util.resultBlink('保存成功！'); $.util.flushInput($.tstyle.els); }; roc.util.flushInput = function( els ){ var $ = roc ; for(var i = 0 ; i < els.length ; i ++){ var e = $.dom.id(els[i]+''); /* if(e.tagName == 'input' && e.type =='text'){*/ e.value = ''; /*}*/ } }; //type{id,tname,tprize} roc.tstyle.getStyle = function( optId ){ //获取类型列表 var $ = roc , mySql = 'select * from type where isdel = 0 ', rs = $.db.getRs( mySql ), filtRs = function ( str ){//处理字段 return (str+'')=='null' ? '':str; }, myOpt = $.dom.id( optId ), optIndex = 1; while(! rs.EOF ){ var id = filtRs(rs.Fields('id')), prize = filtRs(rs.Fields('tprize')), name = filtRs(rs.Fields('tname')); desc = filtRs(rs.Fields('tdesc')); myOpt.options[optIndex] = new Option( name , id ); myOpt.options[optIndex].title = '价格：' + prize + ' | 描述:' + desc; optIndex++; rs.moveNext(); } $.db.closeRs(rs); $.db.closeConn(); }; ;(function(){ var $ = roc ; $.dom.id("save").onclick = function(){ //保存 $.search.insert(); } $.dom.id("searchBtn").onclick = function(){ //提检 $.search.seeking(); } //初始化查询安装单 年 for(var i = 0 ; i <= 10 ; i++ ){ $.dom.id("s_year").options[i] = new Option(2010 + i , 2010 + i ); if( 2010+i+'' == (new Date()).getYear() ){ $.dom.id("s_year").options[i].selected = true; } } //初始化查询安装单月份 for(var i = 1 ; i <= 12 ; i++ ){ $.dom.id("s_month").options[i] = new Option(i,i); } //提检条件字段id 修改触发查询 $.dom.s_fields = ["s_uaddr","s_uphone","s_style","s_datetype",'s_year','s_month','s_date']; for(var i = 0 ; i < $.dom.s_fields.length ; i ++){ var f = $.dom.s_fields[i]; $.dom.id(f).onpropertychange = function(){ if(event.propertyName == 'value'){ $.search.setLock(true); if($.search.t){ clearTimeout($.search.t); } $.search.t = setTimeout(function(){ $.search.seeking(); },$.config.SELECT_DELAY); } } $.dom.id(f).onfocus = function(){ $.dom.addClass(this,"focusit"); }; $.dom.id(f).onblur = function(){ $.dom.removeClass(this,"focusit"); }; } $.dom.id('saveType').onclick = function(){ //货物类型 $.tstyle.insert(); }; //取出类型列表 $.tstyle.getStyle('typeid'); $.tstyle.getStyle('s_style'); /* //$.dom.id("s_uaddr").onkeyup = $.dom.id("s_uphone").onkeyup = $.dom.id("s_style").onkeyup = function(){ $.dom.id("s_uaddr").onblur = $.dom.id("s_uphone").onblur = $.dom.id("s_style").onblur = function(){ $.dom.removeClass(this,"focusit"); } $.dom.id("s_uaddr").onfocus = $.dom.id("s_uphone").onfocus = $.dom.id("s_style").onfocus = function(){ $.dom.addClass(this,"focusit"); }*/ //导航样式切换 for(var i = 0 ; i < $.search.addEls.length ; i++ ){ var curObj = $.dom.id($.search.addEls[0][i]+''); curObj.onfocus = function(){ $.dom.addClass(this,'focusit'); } curObj.onblur = function(){ $.dom.removeClass(this,'focusit'); } } //添加导航点击事件 var lis = $.dom.tagName('li',$.dom.id("ulNav")); for(var i = 0 ; i < lis.length ; i ++ ){ $.dom.hide( $.dom.id(lis[i].id + 'Div')); lis[i].onclick = function(){ for(var n = 0 ; n < lis.length ; n ++ ){ $.dom.removeClass(lis[n],'click'); $.dom.hide( $.dom.id(lis[n].id + 'Div')); } $.dom.show( $.dom.id(this.id + "Div")); $.dom.addClass(this,"click"); $.cookie.set('showWhichDiv', this.id); } } //默认的载入显示页面 var showWhichDiv = $.cookie.get("showWhichDiv") || "searchList"; $.dom.addClass($.dom.id(showWhichDiv),"click"); $.dom.show($.dom.id(showWhichDiv + 'Div')); //日期控件,感谢此控件开发者的分享，祝你有个好女朋友！ J('#selltime').calendar({ format:'yyyy-MM-dd HH:mm:ss' }); var numFields = ['s_uphone','s_date','uphone','received','prize','unreceived','installerid']; for( var i = 0 ; i < numFields.length ; i ++ ){ $.util.onlyInputNumber( numFields[i] ); } })();