操作mdb数据库(建表、查询)

#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
;$mdb_data_path="D:\Program Files\record.mdb"
$mdb_data_path="selcon.mdb"
$mdb_data_pwd=""

$tblname="orisell"
$tblname1="upedsell"
;$encrypt=11111111
data($mdb_data_path, $tblname,$tblname1)
new_id(22,2,123,1,'sadas',3,23,'awa')
new_id1(111,1,1,'adadadsd')
 ;$cardno=99999
; $recid_old=10000000
;  new_id($cardno,$recid_old,'1900-01-01 00:00:01','all have deleted!','21111111')

;new_id('110551783','00000000','1900-00-00 00:00:00',"all have de2222leted ! ",'11111111')
;;new_id($cardid,$createtime,$result,'11111111'')
;new_id('123123','2014-04-04 00:00:00','okkk!','11111111')
;select_id(1888)  ;外部调用接口

Func new_id($iden_id,$coin_num,$money1,$coupon_val,$setcoinno,$num,$balance,$encrypt)
    ;tblname      iden_id,coin_num,money1,  coupon_val,setcoinno,num,balance,encrypt
         $createtime=@YEAR&@mon&@mday
         $time_now = @YEAR & "-" & @MON & "-" & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC

         $addfld = ObjCreate("ADODB.Connection")

       $addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)


      $addfld.Execute("insert into "&$tblname&" (iden_id,coin_num,money1,coupon_val,setcoinno,num,balance,encrypt,usetime) values('"&$iden_id&"','"&$coin_num&"','"&$money1&"','"&$coupon_val&"','"&$setcoinno&"','"&$num&"','"&$balance&"','"&$encrypt&"','"&$time_now&"')")

      ; $conn.Execute("insert into tywb (name,pass,qq,e_mail) values('"&$name&"','"&$pass&"','"&$qq&"','"&$e_mail&"')")
     ;  $RS.close
          $addfld.close
         ;MsgBox(4096, "提示:"," 成功写入数据库!")  
EndFunc


Func new_id1($iden_id,$coin_num,$moneycut,$iden_time)
    ;tblname 1    iden_id,coin_num,moneycut,ident_time
         $createtime=@YEAR&@mon&@mday
         $time_now = @YEAR & "-" & @MON & "-" & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC

         $addfld = ObjCreate("ADODB.Connection")

       $addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)


      $addfld.Execute("insert into "&$tblname1&" (iden_id,coin_num,moneycut,ident_time,usetime) values('"&$iden_id&"','"&$coin_num&"','"&$moneycut&"','"&$iden_time&"','"&$time_now&"')")

      ; $conn.Execute("insert into tywb (name,pass,qq,e_mail) values('"&$name&"','"&$pass&"','"&$qq&"','"&$e_mail&"')")
     ;  $RS.close
          $addfld.close
         ;MsgBox(4096, "提示:"," 成功写入数据库!")  
EndFunc


#cs
Func select_id($cardid) ;判断上次出现该号码的时间,如果没出现则登记入内!
     $addfld = ObjCreate("ADODB.Connection")
      $RS = ObjCreate("ADODB.Recordset")
       $addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
     $RS.ActiveConnection = $addfld ; 设定RS 是 conn 的集合 重要的部份
    ;$conn.close; 关闭数据库连接
    ;$rs.open("Select top 1 '" & $cardid & "',usetime from "&$tblname&" where cardid='"&$cardid&"' and createtime>='"&@YEAR&@mon&@MDAY&"' order by usetime desc")  ;查询当日的领币信息记录

    ;$rs.open("Select top 1 '" & $cardid & "',usetime from "&$tblname&" where cardid='"&$cardid&"'  order by usetime desc")

    $rs.open("Select top 1 cardid,usetime from "&$tblname&" where cardid='"&$cardid&"'  order by usetime desc")
    $recid1 = $RS.Fields(0).value
    $recid2 = $RS.Fields(1).value
    $addfld.Close
    ;MsgBox(0,"",$recid2)
    If StringLen($recid1)>2 Then
        $recid2 = StringMid($recid2, 1, 4) & "-" & StringMid($recid2, 5, 2) & "-" & StringMid($recid2, 7, 2) & " " & StringMid($recid2, 9, 2) & ":" & StringMid($recid2, 11, 2) & ":" & StringMid($recid2, 13, 2)

        MsgBox(16,"错误","该号码: "&$cardid &"      上次登记时间: "&$recid2)
    Else
        If StringLen($cardid)<5 Then
        else

        new_id($cardid,'00000000','11111111','write','11111111')
       MsgBox(64,"OK","登记号码: "&$cardid&"   成功!" &@CRLF&@CRLF& "登记时间:"&@YEAR & "-" & @MON & "-" & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC )    
   EndIf

   EndIf

EndFunc


;;每周一次清

Func select_id_week($cardid) ;判断上次出现该号码的时间,如果没出现则登记入内!
;每周日自动补充全 VVVVVVVV作为一星期开始的标记 或者手动输入issunday 则重新开始计算
If @WDAY =1 Or $cardid='issunday' Then
    new_id1("issunday",'--------','--------','start','--------')
EndIf

$addfld = ObjCreate("ADODB.Connection")
      $RS = ObjCreate("ADODB.Recordset")
       $addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
     $RS.ActiveConnection = $addfld ; 设定RS 是 conn 的集合 重要的部份
    ;$conn.close; 关闭数据库连接
    ;$rs.open("Select top 1 '" & $cardid & "',usetime from "&$tblname&" where cardid='"&$cardid&"' and createtime>='"&@YEAR&@mon&@MDAY&"' order by usetime desc")  ;查询当日的领币信息记录

    $rs.open("Select top 1 id,usetime from "&$tblname1&"  where cardid='issunday' order by usetime desc")
    ;;;;;;;;;;;;;;;;;;;;;;;issunday

    $recid40 = $RS.Fields(0).value ;唯一序号id
    $recid50 = $RS.Fields(1).value ;时间
    ;MsgBox(0,"上次周日id",$recid40)
    If StringLen($recid50)<5 Then
        $recid40=0
    EndIf
    $addfld.Close
;$rs.open("Select top 1 cardid,usetime,id from "&$tblname1&" where cardid='"&$cardid&"' and id>'"&$recid40&"“ order by usetime desc")

$addfld = ObjCreate("ADODB.Connection")
      $RS = ObjCreate("ADODB.Recordset")
       $addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
     $RS.ActiveConnection = $addfld ; 设定RS 是 conn 的集合 重要的部份    
$rs.open("Select top 1 cardid,usetime,id from "&$tblname1&" where cardid='"&$cardid&"' and id>"&$recid40&" order by usetime desc")


    ;MsgBox(0,"cardid11111",$cardid)
    $recid10 = $RS.Fields(0).value ;卡号
    ;MsgBox(0,"recid1",$recid10)
    $recid20 = $RS.Fields(1).value ;时间
    $recid30 = $RS.Fields(2).value ;唯一id
    ;MsgBox(0,"recid2",$recid20)

    $addfld.Close




    ;MsgBox(0,"",$recid2)
    ;If StringLen($recid1)>2 Then
    If  StringLen($recid20)>2  Then 
        $recid20 = StringMid($recid20, 1, 4) & "-" & StringMid($recid20, 5, 2) & "-" & StringMid($recid20, 7, 2) & " " & StringMid($recid20, 9, 2) & ":" & StringMid($recid20, 11, 2) & ":" & StringMid($recid20, 13, 2)

        MsgBox(16,"错误","该号码: "&$cardid &"      上次登记时间: "&$recid20)
    Else
        If StringLen($cardid)<5 Then
        else

        new_id1($cardid,'00000000','11111111','write','11111111')
       MsgBox(64,"OK","登记号码: "&$cardid&"   成功!" &@CRLF&@CRLF& "登记时间:"&@YEAR & "-" & @MON & "-" & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC )    
   EndIf

   EndIf

EndFunc
;;每周一次清

#ce


Func data($mdb_data_path, $tblname,$tblname1)  ;定义判断函数,如果不存在该数据库文件,则自动生成。
if not FileExists($mdb_data_path) Then
$newMdb = ObjCreate("ADOX.Catalog")
$newMdb.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
$newmdb.ActiveConnection.Close;建数据库
$addtbl = ObjCreate("ADODB.Connection")
$addTbl.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
$addTbl.Execute("CREATE TABLE " & $tblname)
$addTbl.Execute("CREATE TABLE " & $tblname1)

$addtbl.Close;建表

$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
$addfld.Execute("ALTER TABLE " & $tblname & " ADD id identity(1, 1) primary key,iden_id int ,coin_num decimal(18,2) ,money1 decimal(18,2) ,coupon_val decimal(18,2) ,setcoinno char(10) ,Num int ,balance decimal(18,2) ,encrypt char(8) ,usetime datetime");id identity(1, 1) primary key为自动编号和主键
$addfld.Execute("ALTER TABLE " & $tblname1 & " ADD id identity(1, 1) primary key,iden_id int ,coin_num decimal(18,2) ,moneycut decimal(18,2) ,ident_time char(20),usetime  datetime ");id identity(1, 1) primary key为自动编号和主键

;tblname      iden_id,coin_num,money1,  coupon_val,setcoinno,num,balance,encrypt
;tblname 1    iden_id,coin_num,moneycut,ident_time

$addfld.Close;建列表名
MsgBox(64,"提示","建立数据库成功",0)
Else
;MsgBox(64,"提示","你已经建立了一个数据库了",5)
EndIf
EndFunc
最后编辑: admin  文档更新时间: 2021-03-16 21:53   作者:admin