Jump to content

Table database


kertz
 Share

Recommended Posts

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...

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Moderators

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. :huh:

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Moderators

kertz,

Good to hear! :D

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

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

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

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 by czardas
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. :think:

Edited by czardas
Link to comment
Share on other sites

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:

#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.

#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 by kertz
Link to comment
Share on other sites

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 by kertz
Link to comment
Share on other sites

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

no

Edited by mechaflash213
Spoiler

“Hello, ladies, look at your man, now back to me, now back at your man, now back to me. Sadly, he isn’t me, but if he stopped using ladies scented body wash and switched to Old Spice, he could smell like he’s me. Look down, back up, where are you? You’re on a boat with the man your man could smell like. What’s in your hand, back at me. I have it, it’s an oyster with two tickets to that thing you love. Look again, the tickets are now diamonds. Anything is possible when your man smells like Old Spice and not a lady. I’m on a horse.”

 

Link to comment
Share on other sites

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 by mechaflash213
Spoiler

“Hello, ladies, look at your man, now back to me, now back at your man, now back to me. Sadly, he isn’t me, but if he stopped using ladies scented body wash and switched to Old Spice, he could smell like he’s me. Look down, back up, where are you? You’re on a boat with the man your man could smell like. What’s in your hand, back at me. I have it, it’s an oyster with two tickets to that thing you love. Look again, the tickets are now diamonds. Anything is possible when your man smells like Old Spice and not a lady. I’m on a horse.”

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...