一个数据库查询的工具~水平有限做不下去了！请大家帮忙
本帖最后由 pizigao 于 2010-4-1 16:02 编辑以查询为主~然后反映在输入框里就行了~希望帮忙~平时工作需要的
数据库密码：GSDKDJV1200Jdls2008
本帖最后由 C.L 于 2010-4-2 15:33 编辑
帮你改了一下,只做了数据读出写入,没有做把数字标志成汉字显示,这个你另处写个函数处理就行了.
代码:
#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
$datename = @ScriptDir & "\data.mdb"
If not FileExists($datename) then Exit (Msgbox(16,'错误','未找到DATA.MDB文件，请检查文件名和路径'))
$datepass = "GSDKDJV1200Jdls2008"
#Region ### START Koda GUI section ### Form=申报信息查看工具.kxf
$Form1_1 = GUICreate("申报信息查看工具", 321, 232, 272, 151)
GUISetCursor (5)
$Label1 = GUICtrlCreateLabel("申报月份", 17, 19, 52, 17)
$Label2 = GUICtrlCreateLabel("扣缴义务人编码", 17, 167, 88, 17)
$Label3 = GUICtrlCreateLabel("企业名称", 17, 198, 52, 17)
$Label4 = GUICtrlCreateLabel("申报用户", 17, 50, 52, 17)
$Label5 = GUICtrlCreateLabel("申报时间", 17, 136, 52, 17)
$Label6 = GUICtrlCreateLabel("申报标志", 17, 79, 52, 17)
$Label7 = GUICtrlCreateLabel("申报反馈", 17, 108, 52, 17)
$Label8 = GUICtrlCreateLabel("扣款反馈", 188, 19, 52, 17)
$Label9 = GUICtrlCreateLabel("申报方式", 188, 50, 52, 17)
$Label10 = GUICtrlCreateLabel("反馈方式", 188, 79, 52, 17)
$Label11 = GUICtrlCreateLabel("申报类型", 188, 108, 52, 17)
$Label12 = GUICtrlCreateLabel("申报次数", 222, 136, 52, 17)
$Button1 = GUICtrlCreateButton("查 询", 140, 13, 43, 25, 0)
$kjywrbm = GUICtrlCreateInput("", 106, 165, 145, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$qymc = GUICtrlCreateInput("", 72, 195, 209, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sbfk = GUICtrlCreateInput("", 71, 104, 89, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sbcs = GUICtrlCreateInput("", 276, 133, 25, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sbsj = GUICtrlCreateInput("", 71, 133, 121, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sbbz = GUICtrlCreateInput("", 71, 75, 57, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sbyh = GUICtrlCreateInput("", 71, 45, 57, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sbfs = GUICtrlCreateInput("", 244, 45, 57, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$kkfk = GUICtrlCreateInput("", 244, 16, 57, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$fkfs = GUICtrlCreateInput("", 244, 75, 57, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sblx = GUICtrlCreateInput("", 244, 104, 57, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sbrq = GUICtrlCreateCombo("", 71, 16, 65, 25)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
rq()
While 1
$nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE
Exit
Case $Button1
cx()
test()
EndSwitch
WEnd
Func rq()
$addfld = ObjCreate("ADODB.Connection")
$RS = ObjCreate("ADODB.Recordset")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $datename & ";Jet Oledb:Database Password=" & $datepass)
$RS.ActiveConnection = $addfld
$jy = "select sbyf from sbzb"
$RS.Open($jy)
$data = ""
While (Not $RS.eof And Not $RS.bof)
$data &= $RS.Fields(0).value & "|"
$RS.movenext
WEnd
$RS.close
$addfld.Close
$temp = StringSplit($data, "|")
GUICtrlSetData($sbrq, $data, $temp)
EndFunc ;==>rq
Func cx()
Global $sbbz
$Strn = GUICtrlRead($sbrq)
$addfld = ObjCreate("ADODB.Connection")
$RS = ObjCreate("ADODB.Recordset")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $datename & ";Jet Oledb:Database Password=" & $datepass)
$RS.ActiveConnection = $addfld
$jy = "select * from sbzb where sbyf = '"& $Strn &"'"
$RS.Open($jy)
While Not $RS.eof And Not $RS.bof
If @error = 1 Then ExitLoop
For $i=0 To $rs.Fields.count-1
Switch $rs.Fields($i).name
Case "KJYWRBM"
GUICtrlSetData ($kjywrbm,$RS.Fields($i).value)
Case "YHM"
GUICtrlSetData ($sbyh,$RS.Fields($i).value)
Case "sbsj"
GUICtrlSetData ($sbsj,$RS.Fields($i).value)
Case "sbbz"
GUICtrlSetData ($sbbz,$RS.Fields($i).value)
Case "SBFK"
If $rs.Fields($i).value == "3" Then;数字翻译成汉字的例子
GUICtrlSetData ($sbfk,"申报成功")
Else
GUICtrlSetData ($sbfk,$RS.Fields($i).value)
EndIf
Case "KKFK"
GUICtrlSetData ($kkfk,$RS.Fields($i).value)
Case "SBFS"
GUICtrlSetData ($sbfs,$RS.Fields($i).value)
Case "FKFS"
GUICtrlSetData ($fkfs,$RS.Fields($i).value)
Case "SBLX"
GUICtrlSetData ($sblx,$RS.Fields($i).value)
Case "SBCS"
GUICtrlSetData ($sbcs,$RS.Fields($i).value)
EndSwitch
Next
$RS.movenext
WEnd
$RS.close
$addfld.Close
EndFunc ;==>cx
Func test ()
$bm = GUICtrlRead ($kjywrbm)
$addfld = ObjCreate("ADODB.Connection")
$RS = ObjCreate("ADODB.Recordset")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $datename & ";Jet Oledb:Database Password=" & $datepass)
$RS.ActiveConnection = $addfld
$jz = "select KJYWRMC from KJYWRXXB where KJYWRBM = '" &$bm &"'"
$RS.Open($jz)
GUICtrlSetData ($qymc,$RS.Fields(0).value)
$RS.movenext
$RS.close
$addfld.Close
EndFunc
#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
$datename = @ScriptDir & "\data.mdb"
If not FileExists($datename) then Exit (Msgbox(16,'错误','未找到DATA.MDB文件，请检查文件名和路径'))
$datepass = "GSDKDJV1200Jdls2008"
#Region ### START Koda GUI section ### Form=申报信息查看工具.kxf
$Form1_1 = GUICreate("申报信息查看工具", 321, 232, 272, 151)
GUISetCursor (5)
$Label1 = GUICtrlCreateLabel("申报月份", 17, 19, 52, 17)
$Label2 = GUICtrlCreateLabel("扣缴义务人编码", 17, 167, 88, 17)
$Label3 = GUICtrlCreateLabel("企业名称", 17, 198, 52, 17)
$Label4 = GUICtrlCreateLabel("申报用户", 17, 50, 52, 17)
$Label5 = GUICtrlCreateLabel("申报时间", 17, 136, 52, 17)
$Label6 = GUICtrlCreateLabel("申报标志", 17, 79, 52, 17)
$Label7 = GUICtrlCreateLabel("申报反馈", 17, 108, 52, 17)
$Label8 = GUICtrlCreateLabel("扣款反馈", 188, 19, 52, 17)
$Label9 = GUICtrlCreateLabel("申报方式", 188, 50, 52, 17)
$Label10 = GUICtrlCreateLabel("反馈方式", 188, 79, 52, 17)
$Label11 = GUICtrlCreateLabel("申报类型", 188, 108, 52, 17)
$Label12 = GUICtrlCreateLabel("申报次数", 222, 136, 52, 17)
$Button1 = GUICtrlCreateButton("查 询", 140, 13, 43, 25, 0)
$kjywrbm = GUICtrlCreateInput("", 106, 165, 145, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$qymc = GUICtrlCreateInput("", 72, 195, 209, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sbfk = GUICtrlCreateInput("", 71, 104, 89, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sbcs = GUICtrlCreateInput("", 276, 133, 25, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sbsj = GUICtrlCreateInput("", 71, 133, 121, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sbbz = GUICtrlCreateInput("", 71, 75, 57, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sbyh = GUICtrlCreateInput("", 71, 45, 57, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sbfs = GUICtrlCreateInput("", 244, 45, 57, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$kkfk = GUICtrlCreateInput("", 244, 16, 57, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$fkfs = GUICtrlCreateInput("", 244, 75, 57, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sblx = GUICtrlCreateInput("", 244, 104, 57, 21)
GUICtrlSetState(-1, $GUI_DISABLE)
$sbrq = GUICtrlCreateCombo("", 71, 16, 65, 25)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
rq()
While 1
$nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE
Exit
Case $Button1
cx()
EndSwitch
WEnd
Func rq()
$addfld = ObjCreate("ADODB.Connection")
$RS = ObjCreate("ADODB.Recordset")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $datename & ";Jet Oledb:Database Password=" & $datepass)
$RS.ActiveConnection = $addfld
$jy = "select sbyf from sbzb"
$RS.Open($jy)
$data = ""
While (Not $RS.eof And Not $RS.bof)
$data &= $RS.Fields(0).value & "|"
$RS.movenext
WEnd
$RS.close
$addfld.Close
$temp = StringSplit($data, "|")
GUICtrlSetData($sbrq, $data, $temp)
EndFunc ;==>rq
Func cx()
Global $sbbz
$Strn = GUICtrlRead($sbrq)
$addfld = ObjCreate("ADODB.Connection")
$RS = ObjCreate("ADODB.Recordset")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $datename & ";Jet Oledb:Database Password=" & $datepass)
$RS.ActiveConnection = $addfld
$jy = "select sbbz from sbzb where sbyf= '" & $Strn & "'"
$RS.Open($jy)
If $RS.eof And $RS.bof Then
MsgBox(16, "提示", "没有找到申报记录", 5)
GUICtrlSetData($sbbz, "")
ControlFocus("", "", $sbbz)
Else
Switch $RS.Fields(0).value
Case 0
GUICtrlSetData($sbbz, "未申报")
Case 1
GUICtrlSetData($sbbz, "已申报")
Case Else
GUICtrlSetData($sbbz, "未知状态")
EndSwitch
EndIf
$RS.close
$addfld.Close
EndFunc ;==>cx这是我完成的部分代码~其实主要查询的数据是在SBZB里面！ SBZB表结构说明
字段名 字段描述 数据类型 长度 备注
KJYWRBM 扣缴义务人编码 TEXT(20) 20 扣缴义务人编码
WYSBH 唯一识别号 TEXT(40) 40 在生成扣缴所得税报告表的时候生成,起到一个批次号的标识作用,通过他可以反查到各明细表中本次申报的具体数据
SBYF 申报月份 TEXT(6) 6 是 申报月份
YHM 用户名 TEXT(10) 10 是
SBSJ 申报时间 DATETIME 是 申报时间
SBBZ 申报标志 INT 是 申报标志 0.可编辑 1.已发送,已导出,已打印
SBFK 申报反馈 INT 是 申报反馈 0.未反馈 1.接收成功 2.接收失败 3.申报成功 4.申报失败 5.局端数据处理中
KKFK 扣款反馈 INT 是 扣款反馈 0.未反馈 1.反馈成功 2.反馈失败 3.无需扣款
SBFS 申报方式 INT 是 申报方式 0.网络申报 1.介质申报 2.纸质申报
FKFS 反馈方式 INT 是 反馈方式 0.网络反馈 1.介质反馈 2.纸质反馈
SLXH 受理序号 TEXT(40) 40 是 受理序号 局端反馈给的
SBLX 申报类型 INT 是 申报类型 0正常申报 1修正申报
SBCS 申报次数 INT 是 从1开始记数 扣缴义务人编码和企业名称来自表KJYWRXXB中的KJYWRBM和KJYWRMC字段~麻烦各位了！ 看了,不会...... 龙山软件
如果上面是自己代码是自己写的，这些功能应该不是很难实现 龙山软件
如果上面是自己代码是自己写的，这些功能应该不是很难实现
auto 发表于 2010-4-1 19:29 http://www.autoitx.com/images/common/back.gif
哈哈~是龙山的！多重查询我不会写 本来很想帮你。但是数据库我压根就没打开。是在对不住了 帮你顶 C。L出马 一个顶俩！ 回复 10# 261869247
我也是初学者... 帮你改了一下,只做了数据读出写入,没有做把数字标志成汉字显示,这个你另处写个函数处理就行了.
代码:
C.L 发表于 2010-4-2 13:15 http://www.autoitx.com/images/common/back.gif
谢谢C.L~我看看先！ 很好的一个帖子 这样不更好吗？
C:\Users\SHERRY\Desktop\AU3???\????\mdb?????2.au3 (85) : ==> ??????(?????).:
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $datename & ";Jet Oledb:Database Password=" & $datepass)
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $datename & ";Jet Oledb:Database Password=" & $datepass)^ ERROR
页:
[1]
2