Table database

[kertz]

Hi there, i'm quite new to autoit and i need your help.

i need a database program, that takes info from a table. like an inventory, where it has all the items, quantity, price etc(that's the database). i want the program to let me select items, calculate price, total price,quantity, and add new rows...really need help, so pleaaase...

[czardas]

I'm currently writing a similar program but it is not at all easy, and the project is currently on hold. Writing the undo buffer is the problem. What about using Microsoft Excel? That would be the easiest thing to do. There are plenty of scripts for automating Excel.

[Melba23]

kertz,

First, welcome to the AutoIt forums.

We do not write code to order - especially anything as complicated as the script you describe. However, we are very happy to help you get over the problems that you come across as you build the script. Think of the old saying: "Give a man a fish, you feed him for a day; give a man a net and you feed him forever". We try to be net makers and repairers, not fishmongers.

Anyway, why not use a commercial spreadsheet and use AutoIt to automate it - for example there is a good Excel UDF.

M23

[kertz]

Hi there, that is what i want, i need some advice on what i want to do, i don't want the script, i want to learn by myself. Well, if you can imagine, i don't quite know how to automate an commercial spreasheet for autoit. i need hints, where to start and where to go.. i want autoit to grab the info from excel table, and a gui that let's me select codes from that table, give quantity and calcultate final price...i really don't need the code...just need what to search for

[Venix]

You should look in the help file on Scite and check out the Excel functions, these will all have example scripts which will help you learn how to use them.

Edited September 25, 2012 by Venix

[Melba23]

kertz,

Good to hear!

Open the AutoIt Help file (AutoIt3Help.exe as you might have guessed) and look under <User Defined Functions Reference - Excel Management>. There you will find a range of functions to help you automate Excel.

If you need help with the basics of Autoit coding then reading the Help file (at least the first few sections - Using AutoIt, Tutorials and the first couple of References) will help you enormously. You should also look at the excellent tutorials that you will find here and here.

You know where we are if you need more help.

M23

[kertz]

You should look in the help file on Scite and check out the Excel functions, these will all have example scripts which will help you learn how to use them.

thank you very much , if i manage to finish this, i'll post details .

[kertz]

well, i can say that i understand excel automating. i need to create a table in a gui, and link it to the table in the excel, is that possible?

[czardas]

Yes, you need a GUI with a ListView control. Search the helpfile for GUICtrlCreateListView, and also check the example scripts forum for examples. It might be an idea to learn some basic stuff about Arrays and GUI creation first. Don't only focus on your main project, but also spend some time familiarising yourself with AutoIt. It can do many things.

Edited September 25, 2012 by czardas

[kertz]

I tried some autoit scripts in the past...haven't focused on such a difficult script. So i have an idea of what autoit is capable of , i think ). I need to finish this one, cause it will make my work easier. don't know how, don't know how much time is gonna take. for the moment i'm having some trouble at understanding these new terms. reading the tutorials hope things will make sense

[czardas]

You might want to consider using csv format. I think it's one of the easier ways to store table data. There will likely be a few hurdles along the way, but there are a lot of experienced people around here to assist you. Making the right design choices early on always makes things easier in the long run.

Edit

The downside to using csv is that you can't store information about excel features such as linked cells or cell formulas which you can do with xls files. Lot's to consider.

Edited September 25, 2012 by czardas

[kertz]

Well...i started to search the forum for already made scripts, so i found 2 scripts that i need to link them or create a bound between them.

This is the first script, it helps me make the inventory of all items, edit them and so:

expandcollapse popup

#include

#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <ButtonConstants.au3>
#include <StaticConstants.au3>
#include <array.au3>
#include <ComboConstants.au3>

Global $productCode, $make, $model, $currentStock, $dataModified, $productToEdit

$Form1 = GUICreate("Form1", 623, 442, 192, 124)
$ListView1 = GUICtrlCreateDummy()
$Button1 = GUICtrlCreateButton("Add A Product", 16, 408, 91, 25)
$Button2 = GUICtrlCreateButton("Edit A Product", 120, 408, 91, 25)
$Button3 = GUICtrlCreateButton("Delete A Product", 224, 408, 107, 25)
GUISetState(@SW_SHOW)
_RefreshData()

While 1
$nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE
Exit
Case $Button1
_AddAProduct()
Case $Button2
_EditProduct()
Case $Button3
_DeleteItem()
EndSwitch
WEnd

Func _AddAProduct()
$Form2 = GUICreate("Product Inventory", 336, 258, 302, 218)
$aInput1 = GUICtrlCreateInput("", 16, 24, 193, 21)
$aInput2 = GUICtrlCreateInput("", 16, 64, 193, 21)
$aInput3 = GUICtrlCreateInput("", 16, 104, 193, 21)
$aInput4 = GUICtrlCreateInput("", 16, 144, 193, 21)
$aInput5 = GUICtrlCreateInput(@MON & "/" & @MDAY & "/" & @YEAR, 16, 184, 193, 21)
$Label1 = GUICtrlCreateLabel("Product Code", 224, 24, 69, 17)
$Label2 = GUICtrlCreateLabel("Make", 224, 64, 31, 17)
$Label3 = GUICtrlCreateLabel("Model", 224, 104, 33, 17)
$Label4 = GUICtrlCreateLabel("Current Stock", 224, 144, 69, 17)
$Label5 = GUICtrlCreateLabel("Date Modified", 224, 184, 70, 17)
$cButton3 = GUICtrlCreateButton("Add Product", 120, 216, 91, 25)
GUISetState(@SW_SHOW)

While 1
$nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE
GUIDelete($Form2)
ExitLoop
Case $cButton3
$productCode = GUICtrlRead($aInput1)
$make = GUICtrlRead($aInput2)
$model = GUICtrlRead($aInput3)
$currentStock = GUICtrlRead($aInput4)
$dataModified = GUICtrlRead($aInput5)
$errorCheck = IniRead("InventoryData.ini", $productCode, "Product Code", "NotInDatabase")
If $errorCheck = "NotInDatabase" Then
IniWrite("InventoryData.ini", $productCode, "Product Code", $productCode)
IniWrite("InventoryData.ini", $productCode, "Make", $make)
IniWrite("InventoryData.ini", $productCode, "Model", $model)
IniWrite("InventoryData.ini", $productCode, "Current Stock", $currentStock)
IniWrite("InventoryData.ini", $productCode, "Date Modified", $dataModified)
Else
MsgBox(0, "Error", "The product is already in the database")
EndIf
GUIDelete($Form2)
_RefreshData()
ExitLoop
EndSwitch
WEnd
EndFunc ;==>_AddAProduct

Func _RefreshData()
GUISwitch($Form1)
GUICtrlDelete($ListView1)
$ListView1 = GUICtrlCreateListView("Product Code|Make|Model|Current Stock|Date Modified", 16, 8, 586, 390)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 0, 100)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 1, 150)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 2, 150)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 3, 85)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 4, 100)

$sectionNames = IniReadSectionNames("InventoryData.ini")
If @error Then
Dim $sectionNames[2]
$sectionNames[0] = 1
$sectionNames[1] = "NotFound"
EndIf
_ArraySort($sectionNames, 0, 1)

For $i = 1 To $sectionNames[0] Step 1
$productCode = IniRead("InventoryData.ini", $sectionNames[$i], "Product Code", "NotFound")
$make = IniRead("InventoryData.ini", $sectionNames[$i], "Make", "NotFound")
$model = IniRead("InventoryData.ini", $sectionNames[$i], "Model", "NotFound")
$currentStock = IniRead("InventoryData.ini", $sectionNames[$i], "Current Stock", "NotFound")
$dataModified = IniRead("InventoryData.ini", $sectionNames[$i], "Date Modified", "NotFound")
GUICtrlCreateListViewItem($productCode & "|" & $make & "|" & $model & "|" & $currentStock & "|" & $dataModified, $ListView1)
If Number($currentStock) < 100 Then
GUICtrlSetBkColor(-1, 0xfff333)
EndIf
If Number($currentStock) < 10 Then
GUICtrlSetBkColor(-1, 0xff0000)
EndIf
Next
EndFunc ;==>_RefreshData

Func _DeleteItem()
$Form3 = GUICreate("Delete A Product", 303, 169, 302, 218)
$sectionNames = IniReadSectionNames("InventoryData.ini")
If @error Then
;MsgBox(0, "", "Error")
Dim $sectionNames[2]
$sectionNames[0] = 1
$sectionNames[1] = "NotFound"
EndIf
_ArraySort($sectionNames, 0, 1)
$delCombo = GUICtrlCreateCombo("", 24, 40, 241, 25, BitOR($CBS_DROPDOWNLIST,$CBS_AUTOHSCROLL))
$delLabel1 = GUICtrlCreateLabel("Select an item to delete", 24, 16, 115, 17)
$delLabel2 = GUICtrlCreateLabel("Warning: this cannot be undone", 24, 72, 186, 17)
GUICtrlSetFont(-1, 8, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFF0000)
$delButton1 = GUICtrlCreateButton("Delete Item", 168, 112, 91, 25)
GUISetState(@SW_SHOW)

For $i = 1 To $sectionNames[0] Step 1
GUICtrlSetData($delCombo, $sectionNames[$i])
Next

While 1
$nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE
GUIDelete($Form3)
ExitLoop
Case $delButton1
$delete = GUICtrlRead($delCombo)
IniDelete("InventoryData.ini", $delete)
GUIDelete($Form3)
_RefreshData()
ExitLoop
EndSwitch
WEnd
EndFunc ;==>_DeleteItem

Func _EditProduct()
$sectionNames = IniReadSectionNames("InventoryData.ini")
If @error Then
Dim $sectionNames[2]
$sectionNames[0] = 1
$sectionNames[1] = "NotFound"
EndIf
_ArraySort($sectionNames, 0, 1)

$Form4 = GUICreate("Edit a product", 303, 169, 302, 218)
$delCombo = GUICtrlCreateCombo("", 24, 40, 241, 25, BitOR($CBS_DROPDOWNLIST,$CBS_AUTOHSCROLL))
$delLabel1 = GUICtrlCreateLabel("Select an item to edit", 24, 16, 115, 17)
$delButton1 = GUICtrlCreateButton("Edit Item", 168, 112, 91, 25)
GUISetState(@SW_SHOW)

For $i = 1 To $sectionNames[0] Step 1
GUICtrlSetData($delCombo, $sectionNames[$i])
Next

While 1
$nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE
GUIDelete($Form4)
ExitLoop
Case $delButton1
$productToEdit = GUICtrlRead($delCombo)
If $productToEdit = "" Then
GUIDelete($Form4)
ExitLoop
Else
GUIDelete($Form4)
_EditProductForm()
ExitLoop
EndIf
EndSwitch
WEnd
EndFunc ;==>_EditProduct

Func _EditProductForm()
$make = IniRead("InventoryData.ini", $productToEdit, "Make", "NotFound")
$model = IniRead("InventoryData.ini", $productToEdit, "Model", "NotFound")
$currentStock = IniRead("InventoryData.ini", $productToEdit, "Current Stock", "NotFound")
$dataModified = IniRead("InventoryData.ini", $productToEdit, "Date Modified", "NotFound")
$Form5 = GUICreate("Form1", 336, 258, 302, 218)
$cInput1 = GUICtrlCreateInput($productToEdit, 16, 24, 193, 21)
$cInput2 = GUICtrlCreateInput($make, 16, 64, 193, 21)
$cInput3 = GUICtrlCreateInput($model, 16, 104, 193, 21)
$cInput4 = GUICtrlCreateInput($currentStock, 16, 144, 193, 21)
$cInput5 = GUICtrlCreateInput(@MON & "/" & @MDAY & "/" & @YEAR, 16, 184, 193, 21)
$Label1 = GUICtrlCreateLabel("Product Code", 224, 24, 69, 17)
$Label2 = GUICtrlCreateLabel("Make", 224, 64, 31, 17)
$Label3 = GUICtrlCreateLabel("Model", 224, 104, 33, 17)
$Label4 = GUICtrlCreateLabel("Current Stock", 224, 144, 69, 17)
$Label5 = GUICtrlCreateLabel("Date Modified", 224, 184, 70, 17)
$Button3 = GUICtrlCreateButton("Edit Product", 120, 216, 91, 25)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
$nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE
GUIDelete($Form5)
ExitLoop
Case $Button3
$productCode = GUICtrlRead($cInput1)
$make = GUICtrlRead($cInput2)
$model = GUICtrlRead($cInput3)
$currentStock = GUICtrlRead($cInput4)
$dataModified = GUICtrlRead($cInput5)
IniWrite("InventoryData.ini", $productCode, "Product Code", $productCode)
IniWrite("InventoryData.ini", $productCode, "Make", $make)
IniWrite("InventoryData.ini", $productCode, "Model", $model)
IniWrite("InventoryData.ini", $productCode, "Current Stock", $currentStock)
IniWrite("InventoryData.ini", $productCode, "Date Modified", $dataModified)
GUIDelete($Form5)
_RefreshData()
ExitLoop
EndSwitch
WEnd
EndFunc ;==>_EditProductForm

the second script helps me export a table to excel which i need to do.

expandcollapse popup

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <Excel.au3>

#NoTrayIcon

Local $style = BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT)
Local $msg

GUICreate("Export Listview to Excel & Print!",500,500); CREATE GUI
$lv = GUICtrlCreateListView("",0,0,500,450)
_GUICtrlListView_SetExtendedListViewStyle(-1,$style)
_GUICtrlListView_SetSelectedColumn(-1, 0)
$export = GUICtrlCreateButton("EXPORT TO EXCEL",20,460,120,30)
$exit = GUICtrlCreateButton("EXIT",360,460,120,30)

colanditems()

GUISetState(@SW_SHOW)

While 1

Select
Case $msg = $export
exporttoexcel()

Case $msg = $exit
Exit
EndSelect

$msg = GUIGetMsg()

If $msg = $GUI_EVENT_CLOSE Then ExitLoop
WEnd

GUIDelete()

Func colanditems(); CREATE COLUMNS, ITEMS AND SUBITEMS
For $x = 1 to 9
_GUICtrlListView_InsertColumn($lv,$x,"Col " & $x, 100)
_GUICtrlListView_AddItem($lv, "Row " & $x & " item " & $x)
For $x2 = 1 to 10
_GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 1", 1)
_GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 2", 2)
_GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 3", 3)
_GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 4", 4)
_GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 5", 5)
_GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 6", 6)
_GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 7", 7)
_GUICtrlListView_AddSubItem($lv, $x2 - 2, "subitem 8", 8)
Next
Next
EndFunc

Func exporttoexcel();EXPORT TO EXCEL
$col = 9
$count = _GUICtrlListView_GetItemCount($lv)
GUICtrlSetState($export,$gui_disable)
$excel = _ExcelBookNew()
For $colexcel = 1 To $col
_ExcelWriteCell($excel,"Col " & $colexcel,1,$colexcel)
$i = 0
do
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,0),2 + $i,1)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,1),2 + $i,2)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,2),2 + $i,3)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,3),2 + $i,4)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,4),2 + $i,5)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,5),2 + $i,6)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,6),2 + $i,7)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,7),2 + $i,8)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($lv,$i,8),2 + $i,9)
$i = $i + 1
Until $i = $count
Next

$ask = MsgBox(4,"Message","Export completed!" & @cr & @cr & "Do you want to print it now?")
if $ask = 6 Then
Send("^p")
EndIf

EndFunc

the thing is that i don't know how to link these 2 scripts, i want the second script to be able to get data from the first, and to be able to edit and insert rows

(the scripts don't belong to me and want to thank those that wrote them)

Edited September 25, 2012 by kertz

[abberration]

How many products do you think this program will need to manage? If it is only a couple thousand, I think an INI file would be the easiest way to go.

[kertz]

How many products do you think this program will need to manage? If it is only a couple thousand, I think an INI file would be the easiest way to go.

i don't think that they are gonna be thousands, couple o'hundred of items

the first script writes it's details in a .ini file

Edited September 25, 2012 by kertz

[Mechaflash]

I think an INI file would be the easiest way to go.

no

Edited September 25, 2012 by mechaflash213

[kertz]

no

why? have a better idea?

[Mechaflash]

You're building a database (whether it's strictly inventory for your company, or selling widgets... doesn't matter). You're going to want a solid infrastructure so you don't have to mess around with it in the future. Excel will work in the meantime, however I'm always looking 5... 10... 20 years down the line. Always plan for expansion.

INI files just become a pain in the neck trying to maintain because of its simple structure.

If it were me, I'd just go the MySQL route. It gives you a huge amount of flexibility for future implementations (maybe want to make a web-based app, desktop app, etc. to interface with it). It also offers a layer of security as opposed to using flat files like INIs and XLSX. a flat file like an INI and also XLSX files.

Edited September 25, 2012 by mechaflash213