Javascript连接Access数据库完整实例
本文实例讲述了Javascript连接Access数据库的方法。分享给大家供大家参考。具体实现方法如下:
varroc=roc||{}; roc.db=roc.db||{}; //创建一个连接 roc.db.createDb=function(){ varconn=newActiveXObject("ADODB.Connection"), fso=newActiveXObject("Scripting.FileSystemObject"), connstr="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+fso.GetFile("./db/Sigma.mdb"); conn.Open(connstr);//打开数据库 roc.db.conn=conn; returnroc.db.conn; }; //获取连接 roc.db.getDb=function(){ if(roc.db.conn){ returnroc.db.conn; }else{ returnroc.db.createDb(); } }; //关闭连接 roc.db.closeConn=function(){ if(roc.db.conn){ roc.db.conn.close(); roc.db.conn=null; } }; //获取结果集 roc.db.getRs=function(sqlStr){ varmysql=roc.dom.trim(sqlStr); if(mysql==''){return;} varrs=newActiveXObject("ADODB.Recordset"), myConn=roc.db.getDb(); rs.open(sqlStr,myConn); returnrs; }; //关闭结果集 roc.db.closeRs=function(rs){ rs.close(); rs=null; }; //更新、插入 roc.db.execute=function(sqlStr){ varmyConn=roc.db.getDb(); myConn.execute(sqlStr); roc.db.closeConn(); }; /*---------Sigma:“我任你践踏我的尊严而毫不生气,是因为我爱你。”---------*/ roc.dom=roc.dom||{}; roc.dom.id=function(id){ if(typeofid=='string'||idinstanceofString){ returndocument.getElementById(id); }elseif(id&&id.nodeName&&(id.nodeType==1||id.nodeType==9)){ returnid; } returnnull; }; /** *@methodtagName根据标签获取指定dom元素 *@param{String}tagName元素标签名称 *@param{HTMLElement}el元素所属的文档对象默认为当前文档 *@return{HTMLElement}返回HTMLElement元素 */ roc.dom.tagName=function(tagName,el){ varel=el||document; returnel.getElementsByTagName(tagName); }; //删除左右两端的空格 roc.dom.trim=function(str){ return(str+'').replace(/(^\s*)|(\s*$)/g,""); } /** *@methodshow显示目标元素 *@param{Element}element目标元素或目标元素的id *@param{String}element目标元素 */ roc.dom.show=function(element){ element=roc.dom.id(element); element.style.display=''; returnelement; }; /** *@methodhide隐藏目标元素 *@param{Element}element目标元素或目标元素的id *@param{String}element目标元素 */ roc.dom.hide=function(element){ element=roc.dom.id(element); element.style.display='none'; returnelement; }; /** *@methodhasClass判断元素是否含有class *@param{Element}el元素 *@param{String}classNameclass名称 */ roc.dom.hasClass=function(el,className){ varre=newRegExp('(^|\\s)'+className+'(\\s|$)'); returnre.test(el.className); }; /** *@methodaddClass给元素添加class *@param{Element}el元素 *@param{String}classNameclass名称 */ roc.dom.addClass=function(el,className){ if(!roc.dom.hasClass(el,className)){ el.className=el.className+''+className; } }; /** *@methodremoveClass给元素移除class *@param{Element}el元素 *@param{String}classNameclass名称 */ roc.dom.removeClass=function(el,className){ varre=newRegExp('(^|\\s)'+className+'(?:\\s|$)') el.className=el.className.replace(re,'$1'); }; /** *date对象命名空间 * *@namespace *@namedata */ roc.date=roc.date||{}; /** *@methodformat对目标日期对象进行格式化 *@param{Object}timestamp目标日期对象 *@return{String}str格式化后的时间 */ roc.date.format=function(timestamp){ if(timestamp=='')return''; varstr='', temptime=newDate(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()); returnstr; }; /** *cookie对象命名空间 * *@namespace *@namecookie */ roc.cookie=roc.cookie||{}; /** *@methodset *@param{String}namecookie的键 *@param{String}valuecookie的值 *@param{String}expires失效时间(小时) *@param{String}domaindomain域 *@param{String}path路径 *@param{String}secure是否支持https */ roc.cookie.set=function(name,value,expires,domain,path,secure){ vartext=encodeURIComponent(value),date=expires; if(date&&typeofdate==='number'){ date=newDate(); date.setTime(date.getTime()+(expires*3600000)); } if(dateinstanceofDate){ text+=';expires='+date.toUTCString(); } if(domain){ text+=';domain='+domain; } if(path){ text+=';path=/'+path; }else{ text+=';path=/'; } if(secure){ text+=';secure'; } document.cookie=name+'='+text; }; /** *@methodget *@param{String}namecookie的键 */ roc.cookie.get=function(name){ varret, m; if(name){ if((m=document.cookie.match('(?:^|)'+name+'(?:(?:=([^;]*))|;|$)'))){ ret=m[1]?decodeURIComponent(m[1]):''; } } returnret; }; 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){ vare=e||window.event; if(e.keyCode>=48&&e.keyCode<=57){alert() returntrue; } returnfalse; }); }; roc.util.addEvent=function(elem,type,fn,useCapture){ if(elem.addEventListener){//DOM2.0 elem.addEventListener(type,fn,useCapture); returntrue; }elseif(elem.attachEvent){//IE5+ elem.attachEvent('on'+type,fn); returntrue; }else{//DOM0 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(variine[0]){ varv=$.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){ returnfalse; } returnvals.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*frominslistwhere1=1', datetypeName=datetype==0?'selltime':'addtime'; if(addr!=''){ mysql+='anduaddrlike"%'+addr+'%"'; } if(phone!=''){ mysql+='anduphone="'+phone+'"'; } if(style!=''){ mysql+='andtypeid='+style+''; } if(year!=''){ mysql+='andyear('+datetypeName+')='+year+''; } if(month!=''){ mysql+='andmonth('+datetypeName+')='+month+''; } if(date!=''){ mysql+='anddate('+datetypeName+')='+date+''; } returnmysql; }; //搜索 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="<tableclass='list'id='memoryDetails'>" +"<colgroup>" +"<colclass='pid'/>" +"<colclass='uaddr'/>" +"<colclass='phone'/>" +"<colclass='number'/>" +"<colclass='money'span='3'/>" +"<colclass='number'/>" +"<colclass='phone'/>" +"<colclass='number'/>" +"<colclass='date'/>" +"<colclass='date'/>" +"</colgroup>" +"<trclass='secondRowdoNotFilter'>" +"<thclass='pid'>序号</th>" +"<thclass='uaddr'>用户地址</th>" +"<thclass='phone'>用户电话</th>" +"<thclass='number'>型号</th>" +"<thclass='money'>代收款</th>" +"<thclass='money'>货款</th>" +"<thclass='money'>余额</th>" +"<thclass='number'>安装人</th>" +"<thclass='phone'>销售电话</th>" +"<thclass='number'>备注</th>" +"<thclass='date'>销售日期</th>" +"<thclass='date'>记录时间</th>" +"</tr>"; while(!rs.EOF) { varid=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+="<trjsselect='browzr_data'>" +"<tdclass='pid'>"+id+"</td>" +"<tdclass='uaddr'>"+uaddr+"</td>" +"<tdclass='phone'>"+uphone+"</td>" +"<tdclass='number'>"+typeid+"</td>" +"<tdclass='money'>"+received+"</td>" +"<tdclass='money'>"+prize+"</td>" +"<tdclass='money'>"+unreceived+"</td>" +"<tdclass='number'>"+installerid+"</td>" +"<tdclass='phone'>"+sellerid+"</td>" +"<tdclass='number'>"+remark+"</td>" +"<tdclass='date'>"+selltime+"</td>" +"<tdclass='date'>"+addtime+"</td>" +"</tr>"; //统计项 total_receive+=received, total_prize+=prize, num++; rs.moveNext(); } html=html +"<trclass='totaldoNotFilter'>" +"<tdclass='pid'></td>" +"<tdclass='uaddr'>Σ</td>" +"<tdclass='number'></td>" +"<tdclass='number'></td>" +"<tdclass='number'>"+total_receive+"</td>" +"<tdclass='number'>"+total_prize+"</td>" +"<tdclass='number'>"+(total_prize-total_receive)+"</td>" +"<tdclass='number'></td>" +"<tdclass='number'></td>" +"<tdclass='number'></td>" +"<tdclass='date'></td>" +"<tdclass='date'></td>" +"</table>"; $.db.closeRs(rs); $.db.closeConn(); returnhtml; }; roc.search.getLock=function(){ //查询锁 if(typeofroc.search.searchLock=='undefined'){ roc.search.setLock(false); } returnroc.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; } varsqlStr='insertintoinslist(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; } varsqlStr='insertintotype(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(vari=0;i<els.length;i++){ vare=$.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*fromtypewhereisdel=0', rs=$.db.getRs(mySql), filtRs=function(str){//处理字段 return(str+'')=='null'?'':str; }, myOpt=$.dom.id(optId), optIndex=1; while(!rs.EOF){ varid=filtRs(rs.Fields('id')), prize=filtRs(rs.Fields('tprize')), name=filtRs(rs.Fields('tname')); desc=filtRs(rs.Fields('tdesc')); myOpt.options[optIndex]=newOption(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(vari=0;i<=10;i++){ $.dom.id("s_year").options[i]=newOption(2010+i,2010+i); if(2010+i+''==(newDate()).getYear()){ $.dom.id("s_year").options[i].selected=true; } } //初始化查询安装单月份 for(vari=1;i<=12;i++){ $.dom.id("s_month").options[i]=newOption(i,i); } //提检条件字段id修改触发查询 $.dom.s_fields=["s_uaddr","s_uphone","s_style","s_datetype",'s_year','s_month','s_date']; for(vari=0;i<$.dom.s_fields.length;i++){ varf=$.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(vari=0;i<$.search.addEls.length;i++){ varcurObj=$.dom.id($.search.addEls[0][i]+''); curObj.onfocus=function(){ $.dom.addClass(this,'focusit'); } curObj.onblur=function(){ $.dom.removeClass(this,'focusit'); } } //添加导航点击事件 varlis=$.dom.tagName('li',$.dom.id("ulNav")); for(vari=0;i<lis.length;i++){ $.dom.hide($.dom.id(lis[i].id+'Div')); lis[i].onclick=function(){ for(varn=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); } } //默认的载入显示页面 varshowWhichDiv=$.cookie.get("showWhichDiv")||"searchList"; $.dom.addClass($.dom.id(showWhichDiv),"click"); $.dom.show($.dom.id(showWhichDiv+'Div')); //日期控件,感谢此控件开发者的分享,祝你有个好女朋友! J('#selltime').calendar({format:'yyyy-MM-ddHH:mm:ss'}); varnumFields=['s_uphone','s_date','uphone','received','prize','unreceived','installerid']; for(vari=0;i<numFields.length;i++){ $.util.onlyInputNumber(numFields[i]); } })();
希望本文所述对大家的javascript程序设计有所帮助。