access更新不成功，不知道问题出哪了
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
Dim $Radio
Dim $Checkbox
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
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 = GUICtrlCreateRadio("申请地区:", 30, 535, 80, 20)
$Input = GUICtrlCreateInput("", 110, 538, 110, 16)
$Radio = GUICtrlCreateRadio("申请单号:", 30, 560, 80, 20)
$Input = GUICtrlCreateInput("", 110, 563, 110, 16)
$Radio = GUICtrlCreateRadio("费用合计:", 30, 585, 80, 20)
$Input = GUICtrlCreateInput("", 110, 588, 110, 16)
$Radio = GUICtrlCreateRadio("供应商:", 230, 535, 80, 20)
$Input = GUICtrlCreateInput("", 310, 538, 60, 16)
$Radio = GUICtrlCreateRadio("拨款状态:", 230, 560, 80, 20)
$Input = GUICtrlCreateInput("", 310, 563, 60, 16)
$Radio = GUICtrlCreateRadio("发票状态:", 230, 585, 80, 20)
$Input = GUICtrlCreateInput("", 310, 588, 60, 16)
GUICtrlSetState($Radio, $GUI_CHECKED);;;;;;;默认选中 $radio0
$Button_clear2 = GUICtrlCreateButton("重选", 40, 610, 70, 25)
$Button_find1 = GUICtrlCreateButton("查找", 125, 610, 70, 25)
$Group1 = GUICtrlCreateGroup("组合查询", 395, 510, 385, 130)
$Checkbox = GUICtrlCreateCheckbox("申请地区:", 405, 535, 80, 20)
$Input = GUICtrlCreateInput("", 485, 538, 110, 16)
$Checkbox = GUICtrlCreateCheckbox("申请单号:", 405, 560, 80, 20)
$Input = GUICtrlCreateInput("", 485, 563, 110, 16)
$Checkbox = GUICtrlCreateCheckbox("费用合计:", 405, 585, 80, 20)
$Input = GUICtrlCreateInput("", 485, 588, 110, 16)
$Checkbox = GUICtrlCreateCheckbox("供应商:", 605, 535, 80, 20)
$Input = GUICtrlCreateInput("", 685, 538, 60, 16)
$Checkbox = GUICtrlCreateCheckbox("拨款状态:", 605, 560, 80, 20)
$Input = GUICtrlCreateInput("", 685, 563, 60, 16)
$Checkbox = GUICtrlCreateCheckbox("发票状态:", 605, 585, 80, 20)
$Input = 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 = GUICtrlCreateInput("", 90, 538, 110, 16)
$Input = GUICtrlCreateInput("", 90, 563, 110, 16)
$Input = GUICtrlCreateInput("", 90, 588, 110, 16)
$Input = GUICtrlCreateInput("", 270, 538, 60, 16)
$Input = GUICtrlCreateInput("", 270, 563, 60, 16)
$Input = 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 = GUICtrlCreateInput("", 478, 543, 110, 16)
$Input = GUICtrlCreateInput("", 478, 568, 110, 16)
$Input = GUICtrlCreateInput("", 478, 593, 110, 16)
$Input = GUICtrlCreateInput("", 658, 543, 60, 16)
$Input = GUICtrlCreateInput("", 658, 568, 60, 16)
$Input = 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 = GUICtrlCreateInput("", 180, 543, 110, 16)
$Input = GUICtrlCreateInput("", 180, 568, 110, 16)
$Input = GUICtrlCreateInput("", 180, 593, 110, 16)
$Input = GUICtrlCreateInput("", 360, 543, 110, 16)
$Input = GUICtrlCreateInput("", 360, 568, 110, 16)
$Input = 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)<>'' And GUICtrlRead($Input)<>'' And GUICtrlRead($Input)<>'' And GUICtrlRead($Input)<>'' And GUICtrlRead($Input)<>'' And GUICtrlRead($Input)<>''Then
$read1=GUICtrlRead($Input)
$read2=GUICtrlRead($Input)
$read3=GUICtrlRead($Input)
$read4=GUICtrlRead($Input)
$read5=GUICtrlRead($Input)
$read6=GUICtrlRead($Input)
$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,$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) ;在所有的记录中找到刚才你选中的那条数据，并根据刚才分割的数据的第十一个数据作为查找的关键字
while Not $RS.eof And Not $RS.bof
if @error =1 Then ExitLoop
GUICtrlSetData($Input, $RS.Fields(1).value)
GUICtrlSetData($Input, $RS.Fields(2).value)
GUICtrlSetData($Input, $RS.Fields(3).value)
GUICtrlSetData($Input, $RS.Fields(4).value)
GUICtrlSetData($Input, $RS.Fields(5).value)
GUICtrlSetData($Input, $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) ;在所有的记录中找到刚才你选中的那条数据，并根据刚才分割的数据的第十一个数据作为查找的关键字
while Not $RS.eof And Not $RS.bof
if @error =1 Then ExitLoop
GUICtrlSetData($Input, $RS.Fields(1).value)
GUICtrlSetData($Input, $RS.Fields(2).value)
GUICtrlSetData($Input, $RS.Fields(3).value)
GUICtrlSetData($Input, $RS.Fields(4).value)
GUICtrlSetData($Input, $RS.Fields(5).value)
GUICtrlSetData($Input, $RS.Fields(6).value)
$rs.movenext
WEnd
$rs.close
$addfld.Close
list()
EndFunc
Func save()
If GUICtrlRead($Input)<>'' And GUICtrlRead($Input)<>'' And GUICtrlRead($Input)<>'' And GUICtrlRead($Input)<>'' And GUICtrlRead($Input)<>'' And GUICtrlRead($Input)<>'' Then
$output0=GUICtrlRead($Input)
$output1=GUICtrlRead($Input)
$output2=GUICtrlRead($Input)
$output3=GUICtrlRead($Input)
$output4=GUICtrlRead($Input)
$output5=GUICtrlRead($Input)
$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 & " setdarea='"&$output0&"',dnumber='"&$output1&"',dcost='"&$output2&"',dsupplier='"&$output3&"',dgrant='"&$output4&"',dinvoice='"&$output5&"' WHERE id" & " = " & $Strnspin)
$addfld.close
list()
EndIf
EndFunc
Func add2()
If GUICtrlRead($Input)<>'' And GUICtrlRead($Input)<>'' And GUICtrlRead($Input)<>'' And GUICtrlRead($Input)<>'' And GUICtrlRead($Input)<>'' And GUICtrlRead($Input)<>''Then
$read7=GUICtrlRead($Input)
$read8=GUICtrlRead($Input)
$read9=GUICtrlRead($Input)
$read10=GUICtrlRead($Input)
$read11=GUICtrlRead($Input)
$read12=GUICtrlRead($Input)
$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,$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) = $GUI_CHECKED then
$find1 = GUICtrlRead($Input)
$RS.Open ('Select * FromReceipt where ' & "darea" & ' = '& '"' & $find1 & '"')
EndIf
if GUICtrlRead($Radio) = $GUI_CHECKED then
$find1 = GUICtrlRead($Input)
$RS.Open ('Select * FromReceipt where ' & "dnumber" & ' = '& '"' & $find1 & '"')
EndIf
if GUICtrlRead($Radio) = $GUI_CHECKED then
$find1 = GUICtrlRead($Input)
$RS.Open ('Select * FromReceipt where ' & "dcost" & ' = '& '"' & $find1 & '"')
EndIf
if GUICtrlRead($Radio) = $GUI_CHECKED then
$find1 = GUICtrlRead($Input)
$RS.Open ('Select * FromReceipt where ' & "dsupplier" & ' = '& '"' & $find1 & '"')
EndIf
if GUICtrlRead($Radio) = $GUI_CHECKED then
$find1 = GUICtrlRead($Input)
$RS.Open ('Select * FromReceipt where ' & "dgrant" & ' = '& '"' & $find1 & '"')
EndIf
if GUICtrlRead($Radio) = $GUI_CHECKED then
$find1 = GUICtrlRead($Input)
$RS.Open ('Select * FromReceipt 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
$addfld.execute($sQuery)
$addfld.close
list()
EndFunc
页:
[1]