关于sqlite数据库写入数据数据库锁定问题

shenrenba · 发表于 2010-9-10 11:20:40

本帖最后由 shenrenba 于 2010-9-12 11:36 编辑

下面是代码请问下原因


#NoTrayIcon
#Region ;**** 参数创建于 ACNWrapper_GUI ****
#AutoIt3Wrapper_Icon=D:\AU3\AU3TOOL.exe_0.ico
#AutoIt3Wrapper_OutFile=D:\AU3\MiniERP.exe
#AutoIt3Wrapper_Res_Description=Cooclk制作
#AutoIt3Wrapper_Run_Obfuscator=y
#EndRegion ;**** 参数创建于 ACNWrapper_GUI ****
AutoItWinSetTitle('MyMiniERP')
#include <GuiListView.au3>
#include <GuiComboBox.au3>
#include <GuiComboBoxEx.au3>
#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <GuiEdit.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Misc.au3>
#include <Date.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
Opt("WinTitleMatchMode", 2);标题的任意子串皆可匹配
Opt("GUICloseOnESC", 0);在按下 ESC 时不退出

If _Singleton(@ScriptName, 1) = 0 Then
        MsgBox(16, "警告", "该程序已经运行，禁止重复运行！", 5)
        Exit
EndIf

$sqlpath = @ScriptDir & "\MyMiniERP.db"
$sqllogin = "login"
$sqlname = "company"
$sqlwork = "work"
$scr = 1

_SQLite_Startup()
If Not FileExists($sqlpath) Then
        sqlcreate()
EndIf

Local $hQuery, $aRow, $aNames
Local $a, $b, $c, $d, $e, $f, $g, $h, $i, $j, $x, $y, $z

Login()
Func Login()
        #Region ### START Koda GUI section ### Form=
        $Login = GUICreate("用户登陆", 300, 200, -1, -1)
        $LoginGroup = GUICtrlCreateGroup("登陆", 5, 5, 290, 190)
        $LoginLabel1 = GUICtrlCreateLabel("用户名", 30, 40, 50, 20)
        $LoginLabel2 = GUICtrlCreateLabel("密码", 30, 80, 50, 20)
        $LoginCombo1 = GUICtrlCreateCombo("", 120, 40, 130, 20, BitOR($CBS_DROPDOWN, $CBS_AUTOHSCROLL, $CBS_SIMPLE))
        showname($LoginCombo1)
        $LoginInput1 = GUICtrlCreateInput("", 120, 80, 130, 20, @SW_DISABLE)
        _GUICtrlEdit_SetPasswordChar($LoginInput1, "*")
        $LoginButton1 = GUICtrlCreateButton("登陆", 30, 130, 70, 40)
        $LoginButton2 = GUICtrlCreateButton("取消", 180, 130, 70, 40)
        GUICtrlCreateGroup("", -99, -99, 1, 1)
        GUISetState(@SW_SHOW)
        #EndRegion ### END Koda GUI section ###
        While 1
                $nMg = GUIGetMsg()
                Switch $nMg
                        Case $GUI_EVENT_CLOSE, $LoginButton2
                                _SQLite_Close()
                                _SQLite_Shutdown()
                                GUIDelete($Login)
                                Exit

                        Case $LoginButton1
                                If GUICtrlRead($LoginCombo1) = "" Or GUICtrlRead($LoginInput1) = "" Then
                                        MsgBox(16, "警告", "帐号和密码均不能为空！", 5)
                                Else
                                        Loginin(GUICtrlRead($LoginCombo1), GUICtrlRead($LoginInput1), $x, $y, $LoginInput1)
                                EndIf

                EndSwitch
        WEnd
EndFunc   ;==>Login

Func sqlcreate()
        _SQLite_Open($sqlpath)
        _SQLite_Exec(-1, "Create Table IF NOT Exists " & $sqllogin & " (name TEXT PRIMARY KEY,password TEXT,competence INTEGER,sign INTEGER);")
        _SQLite_Exec(-1, "Create Table IF NOT Exists " & $sqlname & " (name TEXT,ID TEXT,company TEXT,contact TEXT,tel TEXT,mobile TEXT,fax TEXT,e_mail TEXT,address TEXT,notes TEXT);")
        _SQLite_Exec(-1, "Create Table IF NOT Exists " & $sqlwork & " (client TEXT,data TEXT,category TEXT,itemnumber TEXT,quantity INTEGER,price INTEGER,money INTEGER,paid INTEGER,notpay INTEGER,suppliers TEXT,purchaseprice TEXT,notes TEXT,annex TEXT);")
        _SQLite_Exec(-1, "Insert into " & $sqllogin & " (name,password,competence,sign) values ('Cooclk','cooclk','1','0');")
        _SQLite_Exec(-1, "Insert into " & $sqllogin & " (name,password,competence,sign) values ('ceshi1','ceshi1','2','0');")
        _SQLite_Exec(-1, "Insert into " & $sqllogin & " (name,password,competence,sign) values ('ceshi2','ceshi2','3','0');")
        _SQLite_Exec(-1, "Insert into " & $sqllogin & " (name,password,competence,sign) values ('ceshi3','ceshi3','3','1');")
        _SQLite_Exec(-1, "Insert into " & $sqlname & " (name,ID,company,contact,tel,mobile,fax,e_mail,address,notes) values ('ceshi2','1','2','3','4','5','6','7','8','9');")
        _SQLite_Exec(-1, "Insert into " & $sqlname & " (name,ID,company,contact,tel,mobile,fax,e_mail,address,notes) values ('ceshi2','11','22','33','44','55','66','77','88','99');")
        _SQLite_Close()
EndFunc   ;==>sqlcreate

Func showname($a)
        _SQLite_Open($sqlpath)
        _SQLite_Query(-1, "SELECT * FROM " & $sqllogin & " ORDER BY name DESC;", $hQuery)
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
                _GUICtrlComboBox_BeginUpdate($a)
                _GUICtrlComboBox_AddString($a, $aRow[0])
                _GUICtrlComboBox_EndUpdate($a)
        WEnd
        _SQLite_Close()
EndFunc   ;==>showname

Func Loginin($a, $b, $c, $d, $e)
        Global $Login
        _SQLite_Open($sqlpath)
        _SQLite_Query(-1, "SELECT * FROM " & $sqllogin & " where name ='" & $a & "';", $hQuery)
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
                $c = $aRow[2]
                $d = $aRow[3]
                If $d = 1 Then
                        MsgBox(16, "警告", "此帐号已禁用！", 5)
                        GUICtrlSetData($e, "")
                ElseIf $b <> $aRow[1] Then
                        MsgBox(16, "警告", "密码错误！", 5)
                        GUICtrlSetData($e, "")
                Else
                        GUIDelete($Login)
                        company($a, $c)
                EndIf
        WEnd
        _SQLite_Close()
EndFunc   ;==>Loginin

Func company($a, $c)
        #Region ### START Koda GUI section ### Form=
        $CForm1 = GUICreate("MyMiniERP-----用户:" & $a, 650, 450, -1, -1, $WS_MINIMIZEBOX + $WS_MAXIMIZEBOX)
        $CMenuItem1 = GUICtrlCreateMenu("文件")
        $CMenuItem11 = GUICtrlCreateMenuItem("新公司信息录入", $CMenuItem1)
        $CMenuItem12 = GUICtrlCreateMenuItem("公司信息修改", $CMenuItem1)
        GUICtrlSetState($CMenuItem12, $GUI_DISABLE)
        $CMenuItem13 = GUICtrlCreateMenuItem("公司交易录入", $CMenuItem1)
        $CMenuItem14 = GUICtrlCreateMenuItem("显示所有公司信息", $CMenuItem1)
        GUICtrlSetState($CMenuItem14, $GUI_DISABLE)
        $CMenuItem15 = GUICtrlCreateMenuItem("导入", $CMenuItem1)
        GUICtrlSetState($CMenuItem15, $GUI_DISABLE)
        $CMenuItem16 = GUICtrlCreateMenuItem("导出", $CMenuItem1)
        GUICtrlSetState($CMenuItem16, $GUI_DISABLE)
        $CMenuItem17 = GUICtrlCreateMenuItem("切换用户", $CMenuItem1)
        $CMenuItem18 = GUICtrlCreateMenuItem("退出", $CMenuItem1)
        $CMenuItem2 = GUICtrlCreateMenu("编辑")
        $CMenuItem21 = GUICtrlCreateMenuItem("刷新", $CMenuItem2)
        $CMenuItem22 = GUICtrlCreateMenuItem("删除", $CMenuItem2)
        GUICtrlSetState($CMenuItem22, $GUI_DISABLE)
        $CMenuItem23 = GUICtrlCreateMenuItem("搜索", $CMenuItem2)
        $CMenuItem3 = GUICtrlCreateMenu("用户管理")
        $CMenuItem31 = GUICtrlCreateMenuItem("添加新用户", $CMenuItem3)
        $CMenuItem32 = GUICtrlCreateMenuItem("用户信息修改", $CMenuItem3)
        $CMenuItem33 = GUICtrlCreateMenuItem("用户信息删除", $CMenuItem3)
        GUICtrlSetState($CMenuItem3, $GUI_DISABLE)
        If $c = "1" Then
                GUICtrlSetState($CMenuItem3, $GUI_ENABLE)
                GUICtrlSetState($CMenuItem12, $GUI_ENABLE)
                GUICtrlSetState($CMenuItem14, $GUI_ENABLE)
                GUICtrlSetState($CMenuItem15, $GUI_ENABLE)
                GUICtrlSetState($CMenuItem16, $GUI_ENABLE)
                GUICtrlSetState($CMenuItem22, $GUI_ENABLE)
        ElseIf $c = "2" Then
                GUICtrlSetState($CMenuItem12, $GUI_ENABLE)
                GUICtrlSetState($CMenuItem22, $GUI_ENABLE)
        Else
                Sleep(20)
        EndIf
        $CMenuItem4 = GUICtrlCreateMenu("说明")
        $CMenuItem41 = GUICtrlCreateMenuItem("关于", $CMenuItem4)
        $CListView1 = GUICtrlCreateListView("", 5, 0, 635, 330, $LVS_LIST + $LVS_SINGLESEL + $LVS_SHOWSELALWAYS + $LVS_EX_GRIDLINES + $LVS_EX_FLATSB)
        _GUICtrlListView_AddColumn($CListView1, "公司编号", 100, 0)
        _GUICtrlListView_AddColumn($CListView1, "公司名", 100, 0)
        _GUICtrlListView_AddColumn($CListView1, "联系人", 100, 0)
        _GUICtrlListView_AddColumn($CListView1, "联系电话", 100, 0)
        _GUICtrlListView_AddColumn($CListView1, "手机", 100, 0)
        _GUICtrlListView_AddColumn($CListView1, "传真号", 100, 0)
        _GUICtrlListView_AddColumn($CListView1, "E_mail", 100, 0)
        _GUICtrlListView_AddColumn($CListView1, "联系地址", 100, 0)
        _GUICtrlListView_AddColumn($CListView1, "备注", 100, 0)
        $CGroup1 = GUICtrlCreateGroup("", 5, 330, 635, 45)
        $CCombo1 = GUICtrlCreateCombo("", 70, 340, 150, 25, $CBS_DROPDOWN + $CBS_AUTOHSCROLL + $CBS_SIMPLE)
        GUICtrlSetData($CCombo1, "公司编号|公司名|联系人", "公司名")
        $CInput1 = GUICtrlCreateInput("", 300, 340, 150, 20, @SW_DISABLE)
        $CButton1 = GUICtrlCreateButton("查询", 530, 340, 70, 25)
        GUICtrlCreateGroup("", -99, -99, 1, 1)
        $CLabel1 = GUICtrlCreateLabel("当前系统：" & @OSVersion & " " & @OSServicePack & " " & @OSArch & "位", 5, 385, 250, 25)
        GUICtrlSetState(-1, $GUI_DISABLE)
        $CLabel2 = GUICtrlCreateLabel("", 255, 385, 200, 25)
        GUICtrlSetState(-1, $GUI_DISABLE)
        $CLabel3 = GUICtrlCreateLabel("By Cooclk", 505, 385, 100, 25)
        GUICtrlSetState(-1, $GUI_DISABLE)
        GUISetState()
        sqlnameread($a, $CListView1)
        #EndRegion ### END Koda GUI section ###

        While 1
                Global $sj2
                $sj1 = _NowCalc()
                If $sj1 <> $sj2 Then
                        $sj2 = $sj1
                        GUICtrlSetData($CLabel2, "系统时间：" & $sj1)
                EndIf
                $nMg = GUIGetMsg()
                Switch $nMg
                        Case $GUI_EVENT_CLOSE, $CMenuItem18
                                _SQLite_Close()
                                _SQLite_Shutdown()
                                Exit

                        Case $CMenuItem21
                                sqlnameread($a, $CListView1)

                        Case $CMenuItem17
                                GUIDelete($CForm1)
                                Login()

                        Case $CMenuItem14
                                sqlnameread("*", $CListView1)

                        Case $CMenuItem11
                                GUIDelete($CForm1)
                                companywrite($a)

                EndSwitch
        WEnd
EndFunc   ;==>company

Func sqlnameread($a, $CListView1)
        _GUICtrlListView_DeleteAllItems(GUICtrlGetHandle($CListView1))
        _SQLite_Open($sqlpath)
        _SQLite_QueryReset($a)
        If $a = "*" Then
                _SQLite_Query(-1, "SELECT * FROM " & $sqlname & " ORDER BY name;", $hQuery)
        Else
                _SQLite_Query(-1, "SELECT * FROM " & $sqlname & " where name ='" & $a & "' ORDER BY name;", $hQuery)
        EndIf
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
                _GUICtrlListView_AddItem($CListView1, $aRow[1])
                _GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow[1]), $aRow[2], 1)
                _GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow[1]), $aRow[3], 2)
                _GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow[1]), $aRow[4], 3)
                _GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow[1]), $aRow[5], 4)
                _GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow[1]), $aRow[6], 5)
                _GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow[1]), $aRow[7], 6)
                _GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow[1]), $aRow[8], 7)
                _GUICtrlListView_AddSubItem($CListView1, _GUICtrlListView_FindInText($CListView1, $aRow[1]), $aRow[9], 8)
        WEnd
        _SQLite_Close()
EndFunc   ;==>sqlnameread

Func companywrite($a)
        #Region ### START Koda GUI section ### Form=
        $CWForm1 = GUICreate("MyMiniERP-----用户:" & $a, 650, 400, -1, -1)
        $CWGroup1 = GUICtrlCreateGroup("新公司信息录入", 10, 10, 630, 370)
        $CWLabel1 = GUICtrlCreateLabel("公司编号", 90, 35, 60, 25)
        $CWLabel2 = GUICtrlCreateLabel("公司名称", 90, 65, 60, 25)
        $CWLabel3 = GUICtrlCreateLabel("联系人", 90, 95, 60, 25)
        $CWLabel4 = GUICtrlCreateLabel("联系电话", 90, 125, 60, 25)
        $CWLabel5 = GUICtrlCreateLabel("手机号", 90, 155, 60, 25)
        $CWLabel6 = GUICtrlCreateLabel("传真号", 90, 185, 60, 25)
        $CWLabel7 = GUICtrlCreateLabel("电子邮箱", 90, 215, 60, 25)
        $CWLabel8 = GUICtrlCreateLabel("联系地址", 90, 245, 60, 25)
        $CWLabel9 = GUICtrlCreateLabel("备注", 90, 275, 60, 25)
        $CWInput1 = GUICtrlCreateInput("", 250, 30, 300, 20, @SW_DISABLE)
        $CWInput2 = GUICtrlCreateInput("", 250, 60, 300, 20, @SW_DISABLE)
        $CWInput3 = GUICtrlCreateInput("", 250, 90, 300, 20, @SW_DISABLE)
        $CWInput4 = GUICtrlCreateInput("", 250, 120, 300, 20, @SW_DISABLE)
        $CWInput5 = GUICtrlCreateInput("", 250, 150, 300, 20, @SW_DISABLE)
        $CWInput6 = GUICtrlCreateInput("", 250, 180, 300, 20, @SW_DISABLE)
        $CWInput7 = GUICtrlCreateInput("", 250, 210, 300, 20, @SW_DISABLE)
        $CWInput8 = GUICtrlCreateInput("", 250, 240, 300, 20, @SW_DISABLE)
        $CWInput9 = GUICtrlCreateInput("", 250, 270, 300, 20, @SW_DISABLE)
        $CWButton1 = GUICtrlCreateButton("确认新增", 50, 315, 145, 45)
        $CWButton2 = GUICtrlCreateButton("全部清空", 250, 315, 145, 45)
        $CWButton3 = GUICtrlCreateButton("返回", 450, 315, 145, 45)
        GUICtrlCreateGroup("", -99, -99, 1, 1)
        GUISetState()
        #EndRegion ### END Koda GUI sectio
        While 1
                $nMg = GUIGetMsg()
                Switch $nMg
                        Case $GUI_EVENT_CLOSE, $CWButton3
                                GUIDelete($CWForm1)
                                _SQLite_Open($sqlpath)
                                _SQLite_Query(-1, "SELECT * FROM " & $sqllogin & " where name ='" & $a & "';", $hQuery)
                                While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
                                        $c = $aRow[2]
                                        company($a, $c)
                                WEnd
                                _SQLite_Close()

                        Case $CWButton2
                                GUICtrlSetData($CWInput1, "")
                                GUICtrlSetData($CWInput2, "")
                                GUICtrlSetData($CWInput3, "")
                                GUICtrlSetData($CWInput4, "")
                                GUICtrlSetData($CWInput5, "")
                                GUICtrlSetData($CWInput6, "")
                                GUICtrlSetData($CWInput7, "")
                                GUICtrlSetData($CWInput8, "")
                                GUICtrlSetData($CWInput9, "")

                        Case $CWButton1
                                If GUICtrlRead($CWInput1) = "" Or GUICtrlRead($CWInput2) = "" Then
                                        MsgBox(16, "提示", "公司编号和公司名称均不能为空！", 5)
                                Else
                                        _SQLite_Open($sqlpath)
                                        _SQLite_QuerySingleRow(-1, "SELECT * FROM " & $sqlname & " WHERE  company = '" & GUICtrlRead($CWInput2) & "';", $aRow)
                                        _SQLite_QuerySingleRow(-1, "SELECT * FROM " & $sqlname & " WHERE  ID = '" & GUICtrlRead($CWInput1) & "';", $aNames)
                                        $Temp = $aRow[0]
                                        $hQuery = $aNames[0]
                                        If $Temp <> "" And $hQuery="" Then
                                                MsgBox(16, "警告", "公司名中含有" & GUICtrlRead($CWInput2) & "的信息！"&@CRLF&"此客户编号为"&$aRow[1]&"！"&@CRLF&"此客户属于"&$aRow[0]&"！", 5)
                                        ElseIf $hQuery <> "" And $Temp="" Then
                                                MsgBox(16, "警告", "此编号" & GUICtrlRead($CWInput1) & "已使用！"&@CRLF&"使用于"&$aNames[2]&"！"&@CRLF&"此客户属于"&$aNames[0]&"！", 5)
                                        ElseIf $hQuery <> "" And $Temp<>"" Then
                                                MsgBox(16,"警告","编号和公司名均存在！",5)
                                        Else
                                                sqlnamewrite($a, GUICtrlRead($CWInput1), GUICtrlRead($CWInput2), GUICtrlRead($CWInput3), GUICtrlRead($CWInput4), GUICtrlRead($CWInput5), GUICtrlRead($CWInput6), GUICtrlRead($CWInput7), GUICtrlRead($CWInput8), GUICtrlRead($CWInput9))
                                                GUICtrlSetData($CWInput1, "")
                                                GUICtrlSetData($CWInput2, "")
                                                GUICtrlSetData($CWInput3, "")
                                                GUICtrlSetData($CWInput4, "")
                                                GUICtrlSetData($CWInput5, "")
                                                GUICtrlSetData($CWInput6, "")
                                                GUICtrlSetData($CWInput7, "")
                                                GUICtrlSetData($CWInput8, "")
                                                GUICtrlSetData($CWInput9, "")
                                                MsgBox(64, "提示", "写入完成！", 5)
                                        EndIf
                                        _SQLite_Close()
                                EndIf

                EndSwitch
        WEnd
EndFunc   ;==>companywrite

Func sqlnamewrite($a, $b, $c, $d, $e, $f, $g, $h, $i, $j)
        _SQLite_Open($sqlpath)
        _SQLite_Exec(-1, "Insert into " & $sqlname & " (name,ID,company,contact,tel,mobile,fax,e_mail,address,notes) values ('" & $a & "','" & $b & "','" & $c & "','" & $d & "','" & $e & "','" & $f & "','" & $g & "','" & $h & "','" & $i & "','" & $j & "');")
        _SQLite_Close()
EndFunc   ;==>sqlnamewrite

hzxymkb · 发表于 2010-9-10 15:03:45

这个要等蛋蛋来帮你了！他比较懂行！

woeiwoei · 发表于 2010-9-10 16:36:06

没明白楼主意思。。。锁定？锁定什么呢?

shenrenba · 发表于 2010-9-10 17:23:40

回复 3# woeiwoei

图片中有个错误说明 “Error: database is locked”

不是被锁定了吗?

newuser · 发表于 2010-9-10 17:33:54

回复 1# shenrenba
可以的话,能否提供手里关于sqlite的相关资料.

netegg · 发表于 2010-9-11 18:24:38

本帖最后由 netegg 于 2010-9-11 18:25 编辑

不太清楚你的意思，向sqlite写入数据？还是别的？可能的话把数据库发上来，帮你试试看
另外，有点不明白，就是为什么要多次开数据库？

shenrenba · 发表于 2010-9-12 11:35:34

回复 6# netegg

数据库不是有个超时自动断开的功能吗为了防止这所以每次都是打开后就关闭

那个错误提示是录入界面写入的时候就会出错数据库会自动创建的

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

[系统综合] 关于sqlite数据库写入数据数据库锁定问题

本帖子中包含更多资源

相关帖子