rony2006

Simple Inventory/database with autoit

14 posts in this topic

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.

 

hjhvh.thumb.png.07e1ce6456e50a35baf76056

 

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!

Share this post


Link to post
Share on other sites



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.


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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

Thanks!

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by rony2006

Share this post


Link to post
Share on other sites

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


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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: 

#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()

 

Share this post


Link to post
Share on other sites
_SQLite_Startup(< full path to your dll file>)

 


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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.

#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

 

Share this post


Link to post
Share on other sites
_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.

Share this post


Link to post
Share on other sites

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?


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

@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

Share this post


Link to post
Share on other sites

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>) ??

Share this post


Link to post
Share on other sites

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


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now