Simple Inventory/database with autoit

[rony2006]

Hello, can somebody give me a UDF or code example for a simple inventory or database with autoit?

I have a big table with some spart parts for ex.

 

 

On one side I want to be able to add data (like open a form and manually enter name, type, parts, photo link, location.

On the other side I want to be able to search after what I want, for ex to search after Name,  and if I input Sensor in the name box than to get something like:

Name: Sensor # Type: 12v 55w # Parts: 3 # Location: Cabinet AB1

 

Or for exemple to be able to search after Location, and if I search after "Cabinet AB1" to get the hole row like:

Name: Sensor # Type: 12v 55w # Parts: 3 # Location: Cabinet AB1

 

 

Thank you very much!

[JLogan3o13]

Hi, @rony2006. Take a look at the _SQLite_* functions in the help file. The examples are already written for you

Start with _SQLite_Open, try the example script, and then try to modify it to your needs. If you run into trouble, please post your code here and we'll do our best to assist.

[rony2006]

Ok, I will do this and the I will return with feedback.

Thanks!

[jchd]

OTOH you can even possibly get  by without writing code if your needs don't go far beyond what you said.

Download SQLite Expert (freeware version will do) and then create a simple database which you'll be able to add to, query, modify its structure, etc.

If ever your needs extent beyond that, you can still write an AutoIt application to satisfy them, using the same DB.

[rony2006]

Hey guys, why I cannot execute any sql code in autoit? Each time I get the error "SQLite3.dll Can't be Loaded!".

I checked and in include folder there is sqlite3.dll. Also I downloaded it from the sql site but the same error.

I am using Windows 7 32 bits and last version of Autoit.

Edited October 30, 2015 by rony2006

[JLogan3o13]

Specify the path to the dll in your call to _SQLite_Startup(), as detailed in the help file.

[rony2006]

I don't understand from the help file.

Can you help me with an example please?

I have the path to C:\Program Files\AutoIt3\Include and for ex I want to open this code: 

expandcollapse popup

#include <MsgBoxConstants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

Local $aResult, $iRows, $iColumns, $iRval

_SQLite_Startup()
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit -1
EndIf
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Open() ; Open a :memory: database
If @error Then
    MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't Load Database!")
    Exit -1
EndIf

; Example Table
; Name        | Age
; -----------------------
; Alice       | 43
; Bob         | 28
; Cindy       | 21

If Not _SQLite_Exec(-1, "CREATE TEMP TABLE persons (Name, Age);") = $SQLITE_OK Then _
        MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg())
If Not _SQLite_Exec(-1, "INSERT INTO persons VALUES ('Alice','43');") = $SQLITE_OK Then _
        MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg())
If Not _SQLite_Exec(-1, "INSERT INTO persons VALUES ('Bob','28');") = $SQLITE_OK Then _
        MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg())
If Not _SQLite_Exec(-1, "INSERT INTO persons VALUES ('Cindy','21');") = $SQLITE_OK Then _
        MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg())

; Query
$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM persons;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
    _SQLite_Display2DResult($aResult)

    ; $aResult looks like this:
    ; Name   Age
    ; Alice  43
    ; Bob    28
    ; Cindy  21
    ; If the dimensions would be switched in _SQLite_GetTable2d the result would look like this:
    ; Name  Alice  Bob  Cindy
    ; Age   43     28   21

Else
    MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf

_SQLite_Close()
_SQLite_Shutdown()

 

[JLogan3o13]

_SQLite_Startup(< full path to your dll file>)

 

[ViciousXUSMC]

I did a database inventory program w/ kiosk interface using MySql and our MySql UDF

Here is an example snippet, and basically each compiled.exe was attached to a button on a HTML page that ran on the computer as a Kiosk.

I used MySQL since it was free, so the only thing it cost was time.

expandcollapse popup

#include <ButtonConstants.au3>
#include <ComboConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Array.au3>
#include <mysql.au3>
#include <WinAPI.au3>

AdlibRegister("TimeExit", 300000)

#Region ### START Koda GUI section ### 
$Form1_1 = GUICreate("Black Magic Automation", 405, 207, 253, 149)
$Label1 = GUICtrlCreateLabel("Change Location or Status of Public Safety Toughpads", 16, 0, 380, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
$Combo1 = GUICtrlCreateCombo("TabletName", 24, 56, 145, 25, BitOR($CBS_DROPDOWNLIST,$CBS_AUTOHSCROLL, $WS_VSCROLL))
;GUICtrlSetTip(-1, "Select Tablet Name From Dropdown", "How to use:",1, 1)
$Label2 = GUICtrlCreateLabel("Select Tablet", 56, 32, 67, 17)
$Label3 = GUICtrlCreateLabel("Select Status", 256, 32, 67, 17)
$Label4 = GUICtrlCreateLabel("Type New Location", 240, 88, 97, 17)
$Combo2 = GUICtrlCreateCombo("Status", 208, 56, 145, 25, BitOR($CBS_DROPDOWNLIST,$CBS_AUTOHSCROLL))
;GUICtrlSetTip(-1, "Select Status: Active, Spare, Repair", "How to use:", 1, 1)
$Input2 = GUICtrlCreateInput("Input Your Name", 24, 112, 145, 21)
;GUICtrlSetTip(-1, "Enter your Full Name or User ID", "How to use:", 1, 1)
$Input1 = GUICtrlCreateInput("Location", 208, 112, 145, 21)
GUICtrlSetTip(-1, "Fire Admin, MD73, BAT2 Truck", "Examples:", 1, 1)
$Button1 = GUICtrlCreateButton("Submit", 112, 160, 171, 25)
$Label5 = GUICtrlCreateLabel("Your Name", 60, 88, 83, 17)
GUIRegisterMsg($WM_COMMAND, "_WM_COMMAND")

GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###


Dim $ret[1][1], $rs

$sql = _MySQLConnect("psuser", "snip", "toughpads", "snip")
If @Error Then
    MsgBox(0, "", "Can Not Connect to the Database.  Error Code " & @Error)
    Exit
EndIf
$rs=_Query($sql,"Select TabletName From toughpads.status where TabletName Like 'Toughpad%';")
With $rs
    $nLaenge = $rs.Fields.Count ; get count of fields

; add fields value to 2 dimension array
    While Not .EOF
        ReDim $ret[UBound($ret, 1) + 1][$nLaenge]
        For $i = 0 To $nLaenge - 1
            $ret[UBound($ret, 1) - 1][$i] = $rs.Fields ($i).value
        Next
        .MoveNext
     WEnd
     ; add fields name to first row of array, must run below fields value because the redim is there
For $i = 0 To $rs.Fields.Count -1
    $ret[0][$i]=$rs.Fields($i).name
 Next

EndWith

 ;_ArrayDisplay($ret, "Check Status", "", 64)

For $i=0 To Ubound($ret)-1
    GUICtrlSetData($Combo1, $ret[$i][0], $ret[0][0])
Next

GUICtrlSetData($Combo2, "Active|Spare|Repair", "Status")

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            ExitLoop

    EndSwitch
WEnd


DIM $ColumName[5]
DIM $NewData[5]

$ColumName[0] = "TabletName"
$ColumName[1] = "Location"
$ColumName[2] = "Status"
$ColumName[3] = "LastOwner"
$ColumName[4] = ""
$NewData[0] = GUICtrlRead($Combo1)
$NewData[1] = GUICtrlRead($Input1)
$NewData[2] = GUICtrlRead($Combo2)
$NewData[3] = GUICtrlRead($Input2)
$NewDAta[4] = ""

_DeleteRecord($sql, "toughpads.status", "tabletname", GUICtrlRead($Combo1))
_AddRecord($sql, "toughpads.status", $ColumName, $NewData)


Dim $ret2[1][1], $rs2
$rs2=_Query($sql,"Select * From toughpads.status where status.tabletname ='" & GUICtrlRead($Combo1) & "';")
With $rs2
    $nLaenge = $rs2.Fields.Count ; get count of fields

; add fields value to 2 dimension array
    While Not .EOF
        ReDim $ret2[UBound($ret2, 1) + 1][$nLaenge]
        For $i = 0 To $nLaenge - 1
            $ret2[UBound($ret2, 1) - 1][$i] = $rs2.Fields ($i).value
        Next
        .MoveNext
     WEnd
     ; add fields name to first row of array, must run below fields value because the redim is there
For $i = 0 To $rs2.Fields.Count -1
    $ret2[0][$i]=$rs2.Fields($i).name
 Next

EndWith
_MySQLEnd($sql)

_ArrayDisplay($ret2, "New Toughpad Status", Default, 96)

Func TimeExit()
    _MySQLEnd($sql)
    Exit
EndFunc


Func _WM_COMMAND($hWHnd, $iMsg, $wParam, $lParam)

    ; If it was an update message from our input
    If _WinAPI_HiWord($wParam) = $EN_CHANGE And _WinAPI_LoWord($wParam) = $Input2 Then

        ; Read content
        $sContent = GUICtrlRead($Input2)
        ; check if any non-letters
        If StringRegExp($sContent, "[^A-Za-z ]") Then
            ; Replace any non-letters
            $sContent = StringRegExpReplace($sContent, "[^A-Za-z ]", "")
            ; Colour input
            GUICtrlSetBkColor($Input2, 0xFFCCCC)
            ; Create tootip

            $aPos = WinGetPos($Form1_1)
            ToolTip("Letters Only", $aPos[0] + 30, $aPos[1] + 100, "Error", 3)
            ; Register function to clear tooltip and reset backcolour

            AdlibRegister("_ResetBkColor", 1000)
        EndIf

        ; Set the label to the new data
        GUICtrlSetData($Input2, $sContent)

    EndIf



EndFunc   ;==>_WM_COMMAND

Func _ResetBkColor()
    AdlibUnRegister("_ResetBkColor")
    GUICtrlSetBkColor($Input2, 0xFEFEFE)
    ToolTip("")
EndFunc

 

[rony2006]

_SQLite_Startup(< full path to your dll file>)

 

I tried:

_SQLite_Startup("D:\rocimpma\Desktop\install\Include\sqlite3.dll")
or

_SQLite_Startup(D:\rocimpma\Desktop\install\Include\sqlite3.dll)

or

_SQLite_Startup(<D:\rocimpma\Desktop\install\Include\sqlite3.dll>)

 

but I still get the same error.

[BrewManNH]

Do you have the DLL in that location? Have you verified that it is there?

Are you running the AutoIt exe as a 64 bit program and trying to run the 32 bit DLL or vice versa?

[ViciousXUSMC]

@rony Your first guess is the proper one, I would imagine your 32/64 bit issue that BrewMan hinted at is your issue.  Also you may have a unique situation for your path but normally for windows its C:\Users\rocimpma\Desktop

[rony2006]

Ok but normally it should work with "" like _SQLite_Startup("D:\rocimpma\Desktop\install\Include\sqlite3.dll") or with <> like _SQLite_Startup(<D:\rocimpma\Desktop\install\Include\sqlite3.dll>) ??

[BrewManNH]

You would use quotes around the string for the path/filename. You would only use <> for the include file names in an #include statement.