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程序设计有所帮助。