access更新不成功，不知道问题出哪了

fenglin1978 · 发表于 2012-4-12 18:01:48

access更新不成功，不知道问题出哪了?
哪位高人帮忙看下.
还有多条件查询怎么做判断？难道要22 23 33 这样组合？

#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <GUIListView.au3>
#include <Excel.au3>
#include <GUIListView.au3>
#include <TabConstants.au3>
#include <ProgressConstants.au3>

Dim $Input[30]
Dim $Radio[6]
Dim $Checkbox[6]
Dim $read1
Dim $read2
Dim $read3
Dim $read4
Dim $read5
Dim $read6
Dim $read7
Dim $read8
Dim $read9
Dim $read10
Dim $read11
Dim $read12
Dim $output0
Dim $output1
Dim $output2
Dim $output3
Dim $output4
Dim $output5
Dim $Strnspin[1]
Dim $Strnspin2
Dim $sQuery

$mdb_data_path = @ScriptDir & "\fenglin.mdb"
$adTable = 'Receipt'
$t='*'
$Form1_1 = GUICreate("对公付款项目明细V1.0 宁波无极限IT专用 BY:风林", 820, 680, -1, -1)
$MenuItem1 = GUICtrlCreateMenu("文件(&X)")
$MenuItem2 = GUICtrlCreateMenu("编辑(&Y)")
$MenuItem3 = GUICtrlCreateMenu("帮助(&Z)")
$ListView1 = GUICtrlCreateListView("ID|申请地区|    申请单号    |费用合计|供应商|拨款状态|发票状态", 10, 120, 800, 360)
GUICtrlSendMsg($ListView1, $LVM_SETEXTENDEDLISTVIEWSTYLE, $LVS_EX_GRIDLINES, $LVS_EX_GRIDLINES)
$Button_cd = GUICtrlCreateButton("创建数据库", 700, 10, 70, 30)
$Button_bd= GUICtrlCreateButton("备份数据库", 700, 45, 70, 30)
$Button_rd= GUICtrlCreateButton("还原数据库", 700, 80, 70, 30)

$Tab1 = GUICtrlCreateTab(10, 485, 800, 170)
$TabSheet1 = GUICtrlCreateTabItem("查询系统")
$Group1 = GUICtrlCreateGroup("快速查询", 20, 510, 370, 130)
$Radio[0] = GUICtrlCreateRadio("申请地区:", 30, 535, 80, 20)
$Input[12] = GUICtrlCreateInput("", 110, 538, 110, 16)
$Radio[1] = GUICtrlCreateRadio("申请单号:", 30, 560, 80, 20)
$Input[13] = GUICtrlCreateInput("", 110, 563, 110, 16)
$Radio[2] = GUICtrlCreateRadio("费用合计:", 30, 585, 80, 20)
$Input[14] = GUICtrlCreateInput("", 110, 588, 110, 16)
$Radio[3] = GUICtrlCreateRadio("供应商:", 230, 535, 80, 20)
$Input[15] = GUICtrlCreateInput("", 310, 538, 60, 16)
$Radio[4] = GUICtrlCreateRadio("拨款状态:", 230, 560, 80, 20)
$Input[16] = GUICtrlCreateInput("", 310, 563, 60, 16)
$Radio[5] = GUICtrlCreateRadio("发票状态:", 230, 585, 80, 20)
$Input[17] = GUICtrlCreateInput("", 310, 588, 60, 16)
GUICtrlSetState($Radio[0], $GUI_CHECKED);;;;;;;默认选中 $radio0
$Button_clear2 = GUICtrlCreateButton("重选", 40, 610, 70, 25)
$Button_find1 = GUICtrlCreateButton("查找", 125, 610, 70, 25)

$Group1 = GUICtrlCreateGroup("组合查询", 395, 510, 385, 130)
$Checkbox[0] = GUICtrlCreateCheckbox("申请地区:", 405, 535, 80, 20)
$Input[18] = GUICtrlCreateInput("", 485, 538, 110, 16)
$Checkbox[1] = GUICtrlCreateCheckbox("申请单号:", 405, 560, 80, 20)
$Input[19] = GUICtrlCreateInput("", 485, 563, 110, 16)
$Checkbox[2] = GUICtrlCreateCheckbox("费用合计:", 405, 585, 80, 20)
$Input[20] = GUICtrlCreateInput("", 485, 588, 110, 16)
$Checkbox[3] = GUICtrlCreateCheckbox("供应商:", 605, 535, 80, 20)
$Input[21] = GUICtrlCreateInput("", 685, 538, 60, 16)
$Checkbox[4] = GUICtrlCreateCheckbox("拨款状态:", 605, 560, 80, 20)
$Input[22] = GUICtrlCreateInput("", 685, 563, 60, 16)
$Checkbox[5] = GUICtrlCreateCheckbox("发票状态:", 605, 585, 80, 20)
$Input[23] = GUICtrlCreateInput("", 685, 588, 60, 16)
$Button_clear3 = GUICtrlCreateButton("重选", 415, 610, 70, 25)
$Button_find2 = GUICtrlCreateButton("查找", 500, 610, 70, 25)

$TabSheet1 = GUICtrlCreateTabItem("录入系统")
$Group1 = GUICtrlCreateGroup("原始录入", 20, 510, 315, 130)
$Label = GUICtrlCreateLabel("申请地区:", 30, 540, 60, 20)
$Label = GUICtrlCreateLabel("申请单号:", 30, 565, 60, 20)
$Label = GUICtrlCreateLabel("费用合计:", 30, 590, 60, 20)
$Label = GUICtrlCreateLabel("供应商:", 210, 540, 60, 20)
$Label = GUICtrlCreateLabel("拨款状态:", 210, 565, 60, 20)
$Label = GUICtrlCreateLabel("发票状态:", 210, 590, 60, 20)
$Input[0] = GUICtrlCreateInput("", 90, 538, 110, 16)
$Input[1] = GUICtrlCreateInput("", 90, 563, 110, 16)
$Input[2] = GUICtrlCreateInput("", 90, 588, 110, 16)
$Input[3] = GUICtrlCreateInput("", 270, 538, 60, 16)
$Input[4] = GUICtrlCreateInput("", 270, 563, 60, 16)
$Input[5] = GUICtrlCreateInput("", 270, 588, 60, 16)
$Button_clear1 = GUICtrlCreateButton("重填", 40, 610, 70, 25)
$Button_write1 = GUICtrlCreateButton("添加", 125, 610, 70, 25)

$Group1 = GUICtrlCreateGroup("快速录入", 340, 510, 460, 120)
$Group1 = GUICtrlCreateGroup("第一步", 345, 525, 60, 90)
$Label = GUICtrlCreateLabel("选中记录后点读取", 352, 545, 50, 40)
$Button_read1 = GUICtrlCreateButton("读取", 352, 580, 50, 30)
$Group1 = GUICtrlCreateGroup("第二步", 412, 525, 312, 90)
$Label = GUICtrlCreateLabel("申请地区:", 422, 545, 60, 20)
$Label = GUICtrlCreateLabel("申请单号:", 422, 570, 60, 20)
$Label = GUICtrlCreateLabel("费用合计:", 422, 595, 60, 20)
$Label = GUICtrlCreateLabel("供应商:", 598, 545, 60, 20)
$Label = GUICtrlCreateLabel("拨款状态:", 598, 570, 60, 20)
$Label = GUICtrlCreateLabel("发票状态:", 598, 595, 60, 20)
$Input[6] = GUICtrlCreateInput("", 478, 543, 110, 16)
$Input[7] = GUICtrlCreateInput("", 478, 568, 110, 16)
$Input[8] = GUICtrlCreateInput("", 478, 593, 110, 16)
$Input[9] = GUICtrlCreateInput("", 658, 543, 60, 16)
$Input[10] = GUICtrlCreateInput("", 658, 568, 60, 16)
$Input[11] = GUICtrlCreateInput("", 658, 593, 60, 16)
$Group1 = GUICtrlCreateGroup("第三步", 730, 525, 60, 90)
$Label = GUICtrlCreateLabel("修改数据后点添加", 735, 545, 50, 40)
$Button_write2 = GUICtrlCreateButton("添加", 735, 580, 50, 30)

$TabSheet1 = GUICtrlCreateTabItem("修改系统")
$Group1 = GUICtrlCreateGroup("数据修改", 20, 510, 760, 120)
$Group1 = GUICtrlCreateGroup("第一步", 30, 525, 70, 100)
$Label = GUICtrlCreateLabel("选中记录后点读取", 40, 545, 50, 40)
$Button_read2 = GUICtrlCreateButton("读取",40, 580, 50, 30)
$Group1 = GUICtrlCreateGroup("第二步", 110, 525, 370, 100)
$Label = GUICtrlCreateLabel("申请地区:", 120, 545, 60, 20)
$Label = GUICtrlCreateLabel("申请单号:", 120, 570, 60, 20)
$Label = GUICtrlCreateLabel("费用合计:", 120, 595, 60, 20)
$Label = GUICtrlCreateLabel("供应商:", 300, 545, 60, 20)
$Label = GUICtrlCreateLabel("拨款状态:", 300, 570, 60, 20)
$Label = GUICtrlCreateLabel("发票状态:", 300, 595, 60, 20)
$Input[24] = GUICtrlCreateInput("", 180, 543, 110, 16)
$Input[25] = GUICtrlCreateInput("", 180, 568, 110, 16)
$Input[26] = GUICtrlCreateInput("", 180, 593, 110, 16)
$Input[27] = GUICtrlCreateInput("", 360, 543, 110, 16)
$Input[28] = GUICtrlCreateInput("", 360, 568, 110, 16)
$Input[29] = GUICtrlCreateInput("", 360, 593, 110, 16)
$Group1 = GUICtrlCreateGroup("第三步", 490, 525, 70, 100)
$Label = GUICtrlCreateLabel("修改数据后点保存", 500, 545, 50, 40)
$Button_save = GUICtrlCreateButton("保存",500, 580, 50, 30)

$TabSheet1 = GUICtrlCreateTabItem("删除系统")
$Group1 = GUICtrlCreateGroup("数据删除", 20, 510, 760, 120)
$Group1 = GUICtrlCreateGroup("第一步", 30, 525, 70, 100)
$Label = GUICtrlCreateLabel("首先选取一条记录", 40, 545, 50, 40)
$Group1 = GUICtrlCreateGroup("第二步", 110, 525, 70, 100)
$Label = GUICtrlCreateLabel("点击删除按钮", 120, 545, 50, 40)
$Button_del1 = GUICtrlCreateButton("删除",120, 580, 50, 30)
GUISetState(@SW_SHOW)

If FileExists(".\fenglin.mdb") Then
list()
Else
MsgBox(4096, "提示:", "没找到数据库文件啊#35点击“创建数据“创建新数据库！")
EndIf

While 1

      $nMsg = GUIGetMsg()
      Switch $nMsg

      Case $GUI_EVENT_CLOSE
                     Exit
Case $Button_cd
creat()
list()
Case $Button_write1
add1()
Case $Button_read2
read2()
Case $Button_save
save()
Case $Button_read1
read1()
Case $Button_write2
add2()
Case $Button_find1
find1()
Case $Button_del1
del()
      EndSwitch
WEnd

Func list()
GUICtrlSendMsg($ListView1, $LVM_DELETEALLITEMS, 0, 0)
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $mdb_data_path)
$RS = ObjCreate("ADODB.Recordset")
$RS.ActiveConnection = $addfld
$RS.Open("Select " & $T & " From " & $adTable)
While Not $RS.eof And Not $RS.bof
If @error = 1 Then ExitLoop
GUICtrlCreateListViewItem($RS.Fields(0).value & "|" & $RS.Fields(1).value & "|" & $RS.Fields(2).value & "|" & $RS.Fields(3).value & "|" & $RS.Fields(4).value & "|" & $RS.Fields(5).value & "|" & $RS.Fields(6).value, $ListView1)
$RS.movenext
WEnd
$RS.close
$addfld.Close
EndFunc ;==>du

Func creat() ;新建数据库和表
If FileExists(".\fenglin.mdb") Then
MsgBox(4096, "提示:", "已经存在数据库，无法创建")
EndIf
      $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 " & $adTable)
      $addtbl.Close;建表
      $addfld = ObjCreate("ADODB.Connection")
      $addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
      $addfld.Execute("ALTER TABLE " & $adTable & " ADD ID identity(1, 1) primary key,darea text(255),dnumber text(255),dcost int,dsupplier text(255),dgrant text(255),dinvoice text(255)")
      $addfld.Close;建列表名
      MsgBox(0,'提示','数据库创建成功!'&@CR&''&@CR&'宁波无极限IT部专用')
EndFunc

Func add1() ;往数据库中添加新的条目
      If GUICtrlRead($Input[0])<>'' And GUICtrlRead($Input[1])<>'' And GUICtrlRead($Input[2])<>'' And GUICtrlRead($Input[3])<>'' And GUICtrlRead($Input[4])<>'' And GUICtrlRead($Input[5])<>''  Then
            $read1=GUICtrlRead($Input[0])
            $read2=GUICtrlRead($Input[1])
            $read3=GUICtrlRead($Input[2])
            $read4=GUICtrlRead($Input[3])
            $read5=GUICtrlRead($Input[4])
            $read6=GUICtrlRead($Input[5])
            $addfld = ObjCreate("ADODB.Connection")
            $addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $mdb_data_path)  ;要写数据，首先要连接到数据库
            $addfld.Execute("insert into "&$adTable&" (darea,dnumber,dcost,dsupplier,dgrant,dinvoice) values('"&$read1&"','"&$read2&"','"&$read3&"','"&$read4&"','"&$read5&"','"&$read6&"')")  ;这里是告诉数据库要写到什么位置。要写什么值，
            $addfld.close  ;写完结束所有的连接
            list()  ;写完数据之后再从新把数据库的数据从新读取一次，实现实时更新显示、
      Else
            MsgBox(48, "提示", "添加失败,请检查项目填写格式是否正确!如无数值请输入:nul或无")
            GUICtrlSetState($Input[0],$GUI_FOCUS) ;设置焦点
      EndIf
EndFunc

Func read1()
   $Strn1=GUICtrlRead(GUICtrlRead($ListView1))
      $Strnspin1=StringSplit($Strn1,"|")  ;若要读取单个，要先获取你选择的是那一条，把那一条的所有的数据取出来用 | 分割开
   GUICtrlSendMsg($ListView1, $LVM_DELETEALLITEMS, 0, 0)
               $addfld = ObjCreate("ADODB.Connection")
         $addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &$mdb_data_path)
      $RS =ObjCreate("ADODB.Recordset")
      $RS.ActiveConnection = $addfld
         $RS.Open ("Select "&$t & " From " & $adTable &  " WHERE id" & " = " & $Strnspin1[1])    ;在所有的记录中找到刚才你选中的那条数据，并根据刚才分割的数据的第十一个数据作为查找的关键字
         while Not $RS.eof And Not $RS.bof
         if @error =1 Then ExitLoop
         GUICtrlSetData($Input[6], $RS.Fields(1).value)
         GUICtrlSetData($Input[7], $RS.Fields(2).value)
         GUICtrlSetData($Input[8], $RS.Fields(3).value)
         GUICtrlSetData($Input[9], $RS.Fields(4).value)
         GUICtrlSetData($Input[10], $RS.Fields(5).value)
         GUICtrlSetData($Input[11], $RS.Fields(6).value)
         $rs.movenext
            WEnd
            $rs.close
            $addfld.Close
   list()
   EndFunc

Func read2()
   $Strn2=GUICtrlRead(GUICtrlRead($ListView1))
      $Strnspin2=StringSplit($Strn2,"|")  ;若要读取单个，要先获取你选择的是那一条，把那一条的所有的数据取出来用 | 分割开
   GUICtrlSendMsg($ListView1, $LVM_DELETEALLITEMS, 0, 0)
               $addfld = ObjCreate("ADODB.Connection")
         $addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &$mdb_data_path)
      $RS =ObjCreate("ADODB.Recordset")
      $RS.ActiveConnection = $addfld
         $RS.Open ("Select "&$t & " From " & $adTable &  " WHERE id" & " = " & $Strnspin2[1])    ;在所有的记录中找到刚才你选中的那条数据，并根据刚才分割的数据的第十一个数据作为查找的关键字
         while Not $RS.eof And Not $RS.bof
         if @error =1 Then ExitLoop
         GUICtrlSetData($Input[24], $RS.Fields(1).value)
         GUICtrlSetData($Input[25], $RS.Fields(2).value)
         GUICtrlSetData($Input[26], $RS.Fields(3).value)
         GUICtrlSetData($Input[27], $RS.Fields(4).value)
         GUICtrlSetData($Input[28], $RS.Fields(5).value)
         GUICtrlSetData($Input[29], $RS.Fields(6).value)
         $rs.movenext
            WEnd
            $rs.close
            $addfld.Close
   list()
   EndFunc

Func save()
         If GUICtrlRead($Input[24])<>'' And GUICtrlRead($Input[25])<>'' And GUICtrlRead($Input[26])<>'' And GUICtrlRead($Input[27])<>'' And GUICtrlRead($Input[28])<>'' And GUICtrlRead($Input[29])<>'' Then
            $output0=GUICtrlRead($Input[24])
            $output1=GUICtrlRead($Input[25])
            $output2=GUICtrlRead($Input[26])
$output3=GUICtrlRead($Input[27])
$output4=GUICtrlRead($Input[28])
$output5=GUICtrlRead($Input[29])
            $Strn=GUICtrlRead(GUICtrlRead($ListView1))
            $Strnspin=StringSplit($Strn,"|")
            $addfld = ObjCreate("ADODB.Connection")
            $addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &$mdb_data_path)
            $addfld.execute("update " & $adTable & " set  darea='"&$output0&"',dnumber='"&$output1&"',dcost='"&$output2&"',dsupplier='"&$output3&"',dgrant='"&$output4&"',dinvoice='"&$output5&"' WHERE id" & " = " & $Strnspin[1])
            $addfld.close
list()
   EndIf
EndFunc

Func add2()
If GUICtrlRead($Input[6])<>'' And GUICtrlRead($Input[7])<>'' And GUICtrlRead($Input[8])<>'' And GUICtrlRead($Input[9])<>'' And GUICtrlRead($Input[10])<>'' And GUICtrlRead($Input[11])<>''  Then
$read7=GUICtrlRead($Input[6])
$read8=GUICtrlRead($Input[7])
$read9=GUICtrlRead($Input[8])
$read10=GUICtrlRead($Input[9])
$read11=GUICtrlRead($Input[10])
$read12=GUICtrlRead($Input[11])
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $mdb_data_path)  ;要写数据，首先要连接到数据库
$addfld.Execute("insert into "&$adTable&" (darea,dnumber,dcost,dsupplier,dgrant,dinvoice) values('"&$read7&"','"&$read8&"','"&$read9&"','"&$read10&"','"&$read11&"','"&$read12&"')")  ;这里是告诉数据库要写到什么位置。要写什么值，
$addfld.close  ;写完结束所有的连接
list()  ;写完数据之后再从新把数据库的数据从新读取一次，实现实时更新显示、
      Else
            MsgBox(48, "提示", "请先选中一条记录后先点读取")
            GUICtrlSetState($Input[6],$GUI_FOCUS) ;设置焦点
      EndIf
EndFunc

Func find1()
Dim $find1
GUICtrlSendMsg($ListView1, $LVM_DELETEALLITEMS, 0, 0)
      $addfld = ObjCreate("ADODB.Connection")
         $addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &$mdb_data_path)
      $RS =ObjCreate("ADODB.Recordset")
      $RS.ActiveConnection = $addfld
         if GUICtrlRead($Radio[0]) = $GUI_CHECKED then
$find1 = GUICtrlRead($Input[12])
$RS.Open ('Select * From  Receipt where ' & "darea" & ' = '& '"' & $find1 & '"')
EndIf
if GUICtrlRead($Radio[1]) = $GUI_CHECKED then
$find1 = GUICtrlRead($Input[13])
$RS.Open ('Select * From  Receipt where ' & "dnumber" & ' = '& '"' & $find1 & '"')
EndIf
if GUICtrlRead($Radio[2]) = $GUI_CHECKED then
$find1 = GUICtrlRead($Input[14])
$RS.Open ('Select * From  Receipt where ' & "dcost" & ' = '& '"' & $find1 & '"')
EndIf
if GUICtrlRead($Radio[3]) = $GUI_CHECKED then
$find1 = GUICtrlRead($Input[15])
$RS.Open ('Select * From  Receipt where ' & "dsupplier" & ' = '& '"' & $find1 & '"')
EndIf
if GUICtrlRead($Radio[4]) = $GUI_CHECKED then
$find1 = GUICtrlRead($Input[16])
$RS.Open ('Select * From  Receipt where ' & "dgrant" & ' = '& '"' & $find1 & '"')
EndIf
if GUICtrlRead($Radio[5]) = $GUI_CHECKED then
$find1 = GUICtrlRead($Input[17])
$RS.Open ('Select * From  Receipt where ' & "dinvoice" & ' = '& '"' & $find1 & '"')
EndIf
         while Not $RS.eof And Not $RS.bof
         if @error =1 Then ExitLoop
         GUICtrlCreateListViewItem ( $RS.Fields (0).value&"|"& $RS.Fields (1).value&"|"& $RS.Fields (2).value&"|"& $RS.Fields (3).value&"|"& $RS.Fields (4).value&"|"& $RS.Fields (5).value&"|"& $RS.Fields (6).value,$ListView1)
         $rs.movenext
         WEnd
            $rs.close
            $addfld.Close
EndFunc

Func find2()

EndFunc

Func del()
$Strn=GUICtrlRead(GUICtrlRead($ListView1))
$Strnspin=StringSplit($Strn,"|")
         $addfld = ObjCreate("ADODB.Connection")
         $addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &$mdb_data_path)
                     $sQuery ="DELETE FROM " & $adTable & " IN '" & $mdb_data_path & "' WHERE id" & " = " & $Strnspin[1]
         $addfld.execute($sQuery)
         $addfld.close
      list()
EndFunc

		自动登录	找回密码
密码			加入

[AU3基础] access更新不成功，不知道问题出哪了

本帖子中包含更多资源

相关帖子