Jump to content

MySQL

yucatan

By yucatan,
in AutoIt General Help and Support

 Share

Recommended Posts

yucatan Universalist

yucatan

Posted
Posted

Hello guys i have some mysql problems.

this is my code 

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <Date.au3>
#include <Array.au3>
#include <TabConstants.au3>
#include <EditConstants.au3>
#include <UpdownConstants.au3>
#Include <GuiTab.au3>
#Include <GuiComboBox.au3>
#include <Misc.au3>
#include<EzMySql.au3>

Opt("GUICloseOnESC", 0)
If Not _EzMySql_Startup() Then
    MsgBox(0, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_Open("localhost", "root", "", "", "3306") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_Exec("CREATE DATABASE IF NOT EXISTS planning") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_SelectDB("planning") Then
    MsgBox(0, "Error setting Database to use", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

Global $fClicked = 0, $fWeek_0 = False, $fWeek_53 = False
Global $hListView = 0, $hEdit_Combo = 0, $aLV_Click_Info
Global $iListView_ID, $sItemText
Global $aLV_Handles[7], $aDayLabels[7]
Global $aWeeks[54][8]
Global $sYear = @YEAR
Global $sdate = $sYear & "/01/01"
Global $buswidth = 30, $monteurwidth = 60, $klantwidth = 155
global $listviews[7],$rows[1],$aok
global $maandag[1],$dinsdag[1],$woensdag[1],$donderdag[1],$vrijdag[1],$zaterdag[1],$maandagdata[1],$dinsdagdata[1],$woensdagdata[1],$donderdagdata[1],$vrijdagdata[1]

$clickedweek = _WeekNumberISO()
; Open DLL for _IsPressed
Global $dll = DllOpen("user32.dll")
Global $hGUI = GUICreate("Test", 900, 940)

$hTab = GUICtrlCreateTab(10, 10, 890, 940)
$hTab_0 = GUICtrlCreateTabItem("Week 0")
For $i = 1 To 53
    GUICtrlCreateTabItem("Week " & $i)
Next
GUICtrlCreateTabItem("")

$combo = GUICtrlCreateCombo("", 500, 45,120,50, $CBS_DROPDOWNLIST)
GUICtrlSetData(-1, "Kopieer vorige week|Laad Standaart", "") ; add other item snd set a new default

GUICtrlSetState(_WeekNumberISO() + $hTab_0, $GUI_SHOW)
$hInput = GUICtrlCreateInput($sYear, 420, 45, 50, 20, $ES_READONLY)
$hUpDown = GUICtrlCreateUpdown($hInput, BitOR($GUI_SS_DEFAULT_UPDOWN, $UDS_WRAP, $UDS_NOTHOUSANDS))
GUICtrlSetLimit($hUpDown, $sYear + 5, $sYear - 5)

For $i = 1 To 3
    For $j = 0 To 1
        $iX = 15 + (290 * ($i - 1))
        $iY = 90 + (440 * $j)
        $aDayLabels[(3 * $j) + $i] = GUICtrlCreateLabel("", $iX + 40, $iY - 20, 150, 25)
        $hListView = _GUICtrlListView_Create($hGUI, "Bus", $iX, $iY, 285, 407, BitOR($LVS_REPORT, $LVS_SINGLESEL, $WS_BORDER), $LVS_EX_FULLROWSELECT)
        _ArrayAdd($listviews,$hListView)
        _GUICtrlListView_SetExtendedListViewStyle($hListView, $LVS_EX_GRIDLINES) ; Set grid lines
        $aLV_Handles[$i + ($j * 3)] = $hListView
        _GUICtrlListView_AddColumn($hListView, "Monteur")
        _GUICtrlListView_AddColumn($hListView, "Klant en Project")
        _GUICtrlListView_AddColumn($hListView, "Wvb")
        ControlDisable($hGUI, "", HWnd(_GUICtrlListView_GetHeader($hListView)))
        _GUICtrlListView_SetColumnWidth($hListView, 0, $buswidth)
        _GUICtrlListView_SetColumnWidth($hListView, 1, $monteurwidth)
        _GUICtrlListView_SetColumnWidth($hListView, 2, $klantwidth)
        _GUICtrlListView_SetColumnWidth($hListView, 3, $LVSCW_AUTOSIZE_USEHEADER)
        For $k = 1 To 27
            _GUICtrlListView_AddItem($hListView, "")
            _GUICtrlListView_AddSubItem($hListView, $k - 1, "", 1)
            _GUICtrlListView_AddSubItem($hListView, $k - 1, "", 2)
        Next
        Next
    Next
_Calculate()
_refresh("week"&_WeekNumberISO())
GUISetState()
GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY")
$hAccel_Enter = GUICtrlCreateDummy()
Dim $aAccelKeys[1][2]=[["{ENTER}", $hAccel_Enter]]
While 1
    Switch GUIGetMsg()
        Case $combo
            ConsoleWrite(GUICtrlRead($combo))
        Case $GUI_EVENT_CLOSE
            DllClose($dll)
            Exit
Case $hTab
    ; Do not display weeks 0 and 53 if not used
    If GUICtrlRead($hTab) = 0 And Not $fWeek_0 Then _GUICtrlTab_ClickTab($hTab, 1)
    If GUICtrlRead($hTab) = 53 And Not $fWeek_53 Then _GUICtrlTab_ClickTab($hTab, 52)
    $clickedweek = GUICtrlRead($hTab)
    For $i = 1 To 6
        GUICtrlSetData($aDayLabels[$i], $aWeeks[$clickedweek][$i])
    Next
    ; Clear existing items
    For $j = 1 To 6
        For $i = 0 To 26 ; Because you have created 27 items initially
            _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "")
            _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 1)
            _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 2)
            _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 3)
        Next
    Next
    ; Fill with new data
    _refresh("week" & $clickedweek)
        Case $hUpDown
            _Calculate()
    EndSwitch

; If an item was double clicked
    If $fClicked Then
        ; Get ListView details
        $hListView = $aLV_Handles[$fClicked]
        $iListView_ID = $fClicked
        ; Reset flag
        $fClicked = 0
        ; Get details item clicked
        $aLV_Click_Info = _GUICtrlListView_SubItemHitTest($hListView)
        If $aLV_Click_Info[0] <> -1 Then

            ; Delete any existing combo
            GUICtrlDelete($hEdit_Combo)
            ; Get current text
            $sItemText = _GUICtrlListView_GetItemText($hListView, $aLV_Click_Info[0], $aLV_Click_Info[1])
            ; Get position
            Local $aRect = _GUICtrlListView_GetSubItemRect($hListView, $aLV_Click_Info[0], $aLV_Click_Info[1])
            Local $aPos = WinGetPos($hListView)
            Local $tPoint = DllStructCreate("int X;int Y")
            DllStructSetData($tPoint, "X", $aPos[0])
            DllStructSetData($tPoint, "Y", $aPos[1])
            _WinAPI_ScreenToClient($hGUI, $tPoint)
            Local $iEdit_X = DllStructGetData($tPoint, "X") + $aRect[0]
            Local $iEdit_Y = DllStructGetData($tPoint, "Y") + $aRect[1]

            ; Get column width
Local $iWidth = _GUICtrlListView_GetColumnWidth($hListView, $aLV_Click_Info[1])
; Create edit combo
$hEdit_Combo = GUICtrlCreateCombo("",  $iEdit_X,  $iEdit_Y, $iWidth, 20)
; Set combo values depending on column clicked
Switch $aLV_Click_Info[1]  ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2
    Case 0
        $sComboList = "1 Bus|2 Bus|3 Bus"
        If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText
        _GUICtrlComboBox_LimitText($hEdit_Combo, 2)
    Case 1
        $sComboList = "Monteur 1|Monteur 2|Monteur 3"
        If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText
        Case 2
        $sComboList = "Klant 1|Klant 2|Klant 3"
        If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText
    Case 3
        $sComboList = "Wvb 1|Wvb 2|Wvb 3"
        If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText
        _GUICtrlComboBox_LimitText($hEdit_Combo, 3)
EndSwitch
; Set values into combo
GUICtrlSetData($hEdit_Combo, $sComboList, $sItemText)
GUICtrlSetState($hEdit_Combo, BitOR($GUI_FOCUS, $GUI_ONTOP))

; Get handle of combo edit control
$hComboEdit = _WinAPI_GetFocus()
; Set flag for Enter pressed
$iLoopCode = 0
; Set flag for combo opened <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2
$fCombo_Opened = False

; Loop while the combo has focus
While _WinAPI_GetFocus() = $hComboEdit

    ; If combo has been opened, check if closed again ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2
    If $fCombo_Opened Then
        ; If combo is closed again
        If _GUICtrlComboBox_GetDroppedState($hEdit_Combo) = False Then
            $iLoopCode = 1
            ExitLoop
        EndIf
    Else
        ; Check if opened and set flag
        If _GUICtrlComboBox_GetDroppedState($hEdit_Combo) Then $fCombo_Opened = True
    EndIf

    ; If Enter Pressed ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2
    If _IsPressed("0D", $dll) Then
        $iLoopCode = 1
        ExitLoop
    EndIf

        ; If ESC pressed
        If _IsPressed("1B", $dll) Then
        $iLoopCode = -1
        ExitLoop
    EndIf

        ; Save CPU and prevent tab selection <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 1
        Switch GUIGetMsg()
        Case $hTab
            _GUICtrlTab_ClickTab($hTab, $clickedweek)
        ;Case $GUI_EVENT_CLOSE
        ;    DllClose($dll)
        ;    Exit
    EndSwitch
WEnd

; Read Combo
$sText = GUICtrlRead($hEdit_Combo)

; Check if content changed manually
If Not $iLoopCode And $sText <> $sItemText Then $iLoopCode = 1 ; <<<<<<<<<<<<<<<<<<<<< 2

; Edit ListView if required
If $iLoopCode = 1 Then
_GUICtrlListView_SetItemText($hListView, $aLV_Click_Info[0], $sText, $aLV_Click_Info[1])

$week = "week"&GUICtrlRead($hTab)
if $hListView = $listviews[7] then $dagread = "Maandag"
if $hListView = $listviews[9] then $dagread = "Dinsdag"
if $hListView = $listviews[11] then $dagread = "Woensdag"
if $hListView = $listviews[8] then $dagread = "Donderdag"
if $hListView = $listviews[10] then $dagread = "Vrijdag"
if $hListView = $listviews[12] then $dagread = "Zaterdag"

if $aLV_Click_Info[1] = 3 then $fields = "Wvb"
if $aLV_Click_Info[1] = 2 then $fields = "Klantenproject"
if $aLV_Click_Info[1] = 1 then $fields = "Naam"
if $aLV_Click_Info[1] = 0 then $fields = "Bus"


$value1 = ""
$value2 = ""
$value3 = ""
$value4 = ""


if $aLV_Click_Info[1] + 1 = 1 then $value1 = $sText
if $aLV_Click_Info[1] + 1 = 2 then $value2 = $sText
if $aLV_Click_Info[1] + 1 = 3 then $value3 = $sText
if $aLV_Click_Info[1] + 1 = 4 then $value4 = $sText

ConsoleWrite($aLV_Click_Info[0] + 1 & " "& $aLV_Click_Info[1] + 1 &@CRLF)

Local $sMySqlStatement = ""


$sMySqlStatement &= "INSERT INTO "&$week&" (Dag,Bus,Naam,Klantenproject,Wvb) VALUES (" & "'" & $dagread & "'," & "'" & $value1 & "'," & "'" & $value2 & "'," & "'" & $value3 & "'," & "'" & $value4 & "');"


If Not _EzMySql_Exec($sMySqlStatement) Then
    MsgBox(0, "Error inserting data to Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

$sMySqlStatement = ""
ConsoleWrite( _EzMySql_InsertID()&@CRLF)
EndIf
; Delete combo
GUICtrlDelete($hEdit_Combo)
$hEdit_Combo = 0

        EndIf

    EndIf

WEnd


Func WM_NOTIFY($hWnd, $iMsg, $wParam, $lParam)

    #forceref $hWnd, $iMsg, $wParam
    Local $hWndFrom, $iCode, $tNMHDR

    $tNMHDR = DllStructCreate($tagNMHDR, $lParam)
    $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom"))
    $iCode = DllStructGetData($tNMHDR, "Code")

    For $i = 1 To 6
        If $aLV_Handles[$i] = $hWndFrom Then
            Switch $iCode
                Case $NM_CLICK
                    $fClicked = $i
            EndSwitch
            ExitLoop
        EndIf
    Next
    Return $GUI_RUNDEFMSG

EndFunc   ;==>WM_NOTIFY


Func _Calculate()

    ; Clear array
    Global $aWeeks[54][8]

    ; Set local variables
    Local $asDatePart, $iTimePart
    Local $aDays[8] = [7, "Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag", "Zondag"]
    Local $aMonths[13] = [12, "Januari", "Februari", "Maart", "April", "Mei", "Juni", "Juli", "Augustus", "September", "October", "November", "December"]

    ; Read required year and week
    $sYear = GUICtrlRead($hInput)
    $clickedweek = GUICtrlRead($hTab)

    ; Set first day
    $sdate = $sYear & "/01/01"
    ; Set flag to indicate initial week
    $fStart = True
    ; Set flags to indicate 0/53 week empty
    $fWeek_0 = False
    $fWeek_53 = False

    While 1

        ; Split date
        _DateTimeSplit($sdate, $asDatePart, $iTimePart)
        ; Exit if past end required year

        If $asDatePart[1] > $sYear Then ExitLoop

        ; Get week number
        $iWeek = _WeekNumberISO($sYear, $asDatePart[2], $asDatePart[3])

        ; Adjust to cover final part of previous Week 52 if required
        If $fStart Then
            If $iWeek >= 52 Then $iWeek = 0
        Else
            If $iWeek = 1 Then $iWeek = 53
        EndIf
        ; Reset flag if past initial week
        If $iWeek = 2 Then $fStart = False

        ; Get day of week and adjust for Sunday
        $iIndex = _DateToDayOfWeek($sYear, $asDatePart[2], $asDatePart[3])
        If $iIndex = 1 Then
            $iIndex = 8
            ; Adjust for final week if needed
            If Not $fStart And $iWeek = 53 Then $iWeek = 52
        EndIf

        ; Fill array
        ConsoleWrite($aDays[$iIndex - 1] & " " & $asDatePart[3] & " " & $aMonths[$asDatePart[2]]&@CRLF)
        $aWeeks[$iWeek][$iIndex - 1] = $aDays[$iIndex - 1] & " " & $asDatePart[3] & " " & $aMonths[$asDatePart[2]]
        ; Move to next day
        $sdate = _DateAdd("d", 1, $sdate) ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

    WEnd

    ; Check if weeks 0 and 53 are used
    For $i = 1 To 6
        If $aWeeks[0][$i] <> "" Then $fWeek_0 = True
        If $aWeeks[53][$i] <> "" Then $fWeek_53 = True
    Next
    ; If not then do not display them if selected
    If GUICtrlRead($hTab) = 0 And Not $fWeek_0 Then _GUICtrlTab_ClickTab($hTab, 1)
    If GUICtrlRead($hTab) = 53 And Not $fWeek_53 Then _GUICtrlTab_ClickTab($hTab, 52)
    $clickedweek = GUICtrlRead($hTab)

    For $i = 1 To 6
        ;ConsoleWrite($aDayLabels[$i] & " - " & $aWeeks[$clickedweek][$i] & @CRLF)
        GUICtrlSetData($aDayLabels[$i], $aWeeks[$clickedweek][$i])
    Next

EndFunc   ;==>_Calculate

func _refresh($week)
;$arraysize = UBound($aok)
ConsoleWrite($aok&@CRLF)
$aOk = _EzMySql_GetTable2d("SELECT * FROM "&$week&";")
$error = @error
If Not IsArray($aOk) Then
$create = "CREATE TABLE IF NOT EXISTS "&$week&" (" & _
                   "RowID INT NOT NULL AUTO_INCREMENT," & _
                   "Dag TEXT NOT NULL ," & _
                   "Bus TEXT NOT NULL ," & _
                   "Naam TEXT NOT NULL ," & _
                   "Klantenproject TEXT NOT NULL ," & _
                   "Wvb TEXT NOT NULL ," & _
                   "PRIMARY KEY (`RowID`) ," & _
                   "UNIQUE INDEX RowID_UNIQUE (`RowID` ASC) );"

If Not _EzMySql_Exec($create) Then
    MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    _refresh($week)
EndIf
EndIf
;_ArrayDisplay($aok, "$avArray set manually 1D")
$j = 0
for $i =0 to UBound($aok) -1
    if $aok[$i][1] = "Maandag" then
    _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][2], 0)
    _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][3], 1)
    _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][4], 2)
    _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][5], 3)
$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Dinsdag" then
    _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][2], 0)
    _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][3], 1)
    _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][4], 2)
    _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][5], 3)
$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Woensdag" then
    _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][2], 0)
    _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][3], 1)
    _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][4], 2)
    _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][5], 3)
$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Donderdag" then
    _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][2], 0)
    _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][3], 1)
    _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][4], 2)
    _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][5], 3)
$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Vrijdag" then
    _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][2], 0)
    _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][3], 1)
    _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][4], 2)
    _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][5], 3)
$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Zaterdag" then
    _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][2], 0)
    _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][3], 1)
    _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][4], 2)
    _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][5], 3)
$j+=1
EndIf
Next
;_ArrayDisplay($maandagdata, "$avArray set manually 1D")
EndFunc

if i get a new week that is emty.

if i fill something in in the field monteur.

but the rest of the rule is emty.

then i only add's the value i have added to mysql.

as u proberly know mysql used rowid's

if i fill in a bus number in the same rule.

then it need to update the exsisting row instead of adding a totaly new row.

there are two ways i can think of to fix this issue.

one i log the rowid's if a new value is added.

so that if in the same rule but in another field someting is added i can update the exsisting row.

two i only send a mysql query if the rule is compleet.

so then only then all 4 fields have been filled with something.

that only then it send's a mysql query.

if we chose for the seconds way(wicht i think is the most easy one)

then we still have a problem with modifing.

if i edit a field.. i need to know the rowid that should be updated...

so thats the issue i have now.

but i dont know if i should ask this to u.

maby i need to make topic in general help and support.

instead of in the gui help and support.

What ur advice to me now?

Happy to hear from u.

Greetz Yucatan

Link to comment
Share on other sites
jchd Universalist

jchd

Posted
Posted

Your question doesn't seem to be AutoIt related at all and should probably find an answer faster in some generic SQL forum.

Now, it's unclear to me what schema you have and what your actual problem is. Do you create one table per week? If so that doesn't look a sound SQL schema, unless I misunderstand something big. Hint: try to separate SQL issues from AutoIt code as the whole stuff takes too much time to dissect.

Try (here, once your started the topic or elsewhere) to expose a pure SQL pseudocode, eg:

CREATE TABLE ....

INSERT INTO ...

SELECT ....

Expected result:

--- --- ---

Actual result:

/// /// ///

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)

Link to comment
Share on other sites
yucatan Universalist

yucatan

Posted
  • Author
Posted

my question is autoit related.

i know how MySQl works.

i just dont know what is the most efficent way to use it in autoit.

i shall try to explane it.

as u see i have a gui with 6 listviews with 4 colums

as u understand i wanne use that listview to insert data into mysql.

the problem is:

as u look at the code at this time it inserts data into the mysql table at the moment that the editing is done.

at that point is adds one value to the mysql.

that works fine.

but if i try to insert data into a exsisting row.

it goes wrong then it just create a new row.

if u know some mysql structure then u can see it in my code.

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <Date.au3>
#include <Array.au3>
#include <TabConstants.au3>
#include <EditConstants.au3>
#include <UpdownConstants.au3>
#Include <GuiTab.au3>
#Include <GuiComboBox.au3>
#include <Misc.au3>
#include<EzMySql.au3>

Opt("GUICloseOnESC", 0)
If Not _EzMySql_Startup() Then
    MsgBox(0, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_Open("localhost", "root", "", "", "3306") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_Exec("CREATE DATABASE IF NOT EXISTS planning") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_SelectDB("planning") Then
    MsgBox(0, "Error setting Database to use", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

Global $fClicked = 0, $fWeek_0 = False, $fWeek_53 = False
Global $hListView = 0, $hEdit_Combo = 0, $aLV_Click_Info
Global $iListView_ID, $sItemText
Global $aLV_Handles[7], $aDayLabels[7]
Global $aWeeks[54][8]
Global $sYear = @YEAR
Global $sdate = $sYear & "/01/01"
Global $buswidth = 30, $monteurwidth = 60, $klantwidth = 155
global $listviews[7],$rows[1],$aok
global $maandag[1],$dinsdag[1],$woensdag[1],$donderdag[1],$vrijdag[1],$zaterdag[1],$maandagdata[1],$dinsdagdata[1],$woensdagdata[1],$donderdagdata[1],$vrijdagdata[1]

$clickedweek = _WeekNumberISO()
; Open DLL for _IsPressed
Global $dll = DllOpen("user32.dll")
Global $hGUI = GUICreate("Test", 900, 940)

$hTab = GUICtrlCreateTab(10, 10, 890, 940)
$hTab_0 = GUICtrlCreateTabItem("Week 0")
For $i = 1 To 53
    GUICtrlCreateTabItem("Week " & $i)
Next
GUICtrlCreateTabItem("")

$combo = GUICtrlCreateCombo("", 500, 45,120,50, $CBS_DROPDOWNLIST)
GUICtrlSetData(-1, "Kopieer vorige week|Laad Standaart", "") ; add other item snd set a new default

GUICtrlSetState(_WeekNumberISO() + $hTab_0, $GUI_SHOW)
$hInput = GUICtrlCreateInput($sYear, 420, 45, 50, 20, $ES_READONLY)
$hUpDown = GUICtrlCreateUpdown($hInput, BitOR($GUI_SS_DEFAULT_UPDOWN, $UDS_WRAP, $UDS_NOTHOUSANDS))
GUICtrlSetLimit($hUpDown, $sYear + 5, $sYear - 5)

For $i = 1 To 3
    For $j = 0 To 1
        $iX = 15 + (290 * ($i - 1))
        $iY = 90 + (440 * $j)
        $aDayLabels[(3 * $j) + $i] = GUICtrlCreateLabel("", $iX + 40, $iY - 20, 150, 25)
        $hListView = _GUICtrlListView_Create($hGUI, "Bus", $iX, $iY, 285, 407, BitOR($LVS_REPORT, $LVS_SINGLESEL, $WS_BORDER), $LVS_EX_FULLROWSELECT)
        _ArrayAdd($listviews,$hListView)
        _GUICtrlListView_SetExtendedListViewStyle($hListView, $LVS_EX_GRIDLINES) ; Set grid lines
        $aLV_Handles[$i + ($j * 3)] = $hListView
        _GUICtrlListView_AddColumn($hListView, "Monteur")
        _GUICtrlListView_AddColumn($hListView, "Klant en Project")
        _GUICtrlListView_AddColumn($hListView, "Wvb")
        ControlDisable($hGUI, "", HWnd(_GUICtrlListView_GetHeader($hListView)))
        _GUICtrlListView_SetColumnWidth($hListView, 0, $buswidth)
        _GUICtrlListView_SetColumnWidth($hListView, 1, $monteurwidth)
        _GUICtrlListView_SetColumnWidth($hListView, 2, $klantwidth)
        _GUICtrlListView_SetColumnWidth($hListView, 3, $LVSCW_AUTOSIZE_USEHEADER)
        For $k = 1 To 27
            _GUICtrlListView_AddItem($hListView, "")
            _GUICtrlListView_AddSubItem($hListView, $k - 1, "", 1)
            _GUICtrlListView_AddSubItem($hListView, $k - 1, "", 2)
        Next
        Next
    Next
_Calculate()
_refresh("week"&_WeekNumberISO())
GUISetState()
GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY")
$hAccel_Enter = GUICtrlCreateDummy()
Dim $aAccelKeys[1][2]=[["{ENTER}", $hAccel_Enter]]
While 1
    Switch GUIGetMsg()
        Case $combo
            ConsoleWrite(GUICtrlRead($combo))
        Case $GUI_EVENT_CLOSE
            DllClose($dll)
            Exit
Case $hTab
    ; Do not display weeks 0 and 53 if not used
    If GUICtrlRead($hTab) = 0 And Not $fWeek_0 Then _GUICtrlTab_ClickTab($hTab, 1)
    If GUICtrlRead($hTab) = 53 And Not $fWeek_53 Then _GUICtrlTab_ClickTab($hTab, 52)
    $clickedweek = GUICtrlRead($hTab)
    For $i = 1 To 6
        GUICtrlSetData($aDayLabels[$i], $aWeeks[$clickedweek][$i])
    Next
    ; Clear existing items
    For $j = 1 To 6
        For $i = 0 To 26 ; Because you have created 27 items initially
            _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "")
            _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 1)
            _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 2)
            _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 3)
        Next
    Next
    ; Fill with new data
    _refresh("week" & $clickedweek)
        Case $hUpDown
            _Calculate()
    EndSwitch

; If an item was double clicked
    If $fClicked Then
        ; Get ListView details
        $hListView = $aLV_Handles[$fClicked]
        $iListView_ID = $fClicked
        ; Reset flag
        $fClicked = 0
        ; Get details item clicked
        $aLV_Click_Info = _GUICtrlListView_SubItemHitTest($hListView)
        If $aLV_Click_Info[0] <> -1 Then

            ; Delete any existing combo
            GUICtrlDelete($hEdit_Combo)
            ; Get current text
            $sItemText = _GUICtrlListView_GetItemText($hListView, $aLV_Click_Info[0], $aLV_Click_Info[1])
            ; Get position
            Local $aRect = _GUICtrlListView_GetSubItemRect($hListView, $aLV_Click_Info[0], $aLV_Click_Info[1])
            Local $aPos = WinGetPos($hListView)
            Local $tPoint = DllStructCreate("int X;int Y")
            DllStructSetData($tPoint, "X", $aPos[0])
            DllStructSetData($tPoint, "Y", $aPos[1])
            _WinAPI_ScreenToClient($hGUI, $tPoint)
            Local $iEdit_X = DllStructGetData($tPoint, "X") + $aRect[0]
            Local $iEdit_Y = DllStructGetData($tPoint, "Y") + $aRect[1]

            ; Get column width
Local $iWidth = _GUICtrlListView_GetColumnWidth($hListView, $aLV_Click_Info[1])
; Create edit combo
$hEdit_Combo = GUICtrlCreateCombo("",  $iEdit_X,  $iEdit_Y, $iWidth, 20)
; Set combo values depending on column clicked
Switch $aLV_Click_Info[1]  ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2
    Case 0
        $sComboList = "1 Bus|2 Bus|3 Bus"
        If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText
        _GUICtrlComboBox_LimitText($hEdit_Combo, 2)
    Case 1
        $sComboList = "Monteur 1|Monteur 2|Monteur 3"
        If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText
        Case 2
        $sComboList = "Klant 1|Klant 2|Klant 3"
        If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText
    Case 3
        $sComboList = "Wvb 1|Wvb 2|Wvb 3"
        If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText
        _GUICtrlComboBox_LimitText($hEdit_Combo, 3)
EndSwitch
; Set values into combo
GUICtrlSetData($hEdit_Combo, $sComboList, $sItemText)
GUICtrlSetState($hEdit_Combo, BitOR($GUI_FOCUS, $GUI_ONTOP))

; Get handle of combo edit control
$hComboEdit = _WinAPI_GetFocus()
; Set flag for Enter pressed
$iLoopCode = 0
; Set flag for combo opened <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2
$fCombo_Opened = False

; Loop while the combo has focus
While _WinAPI_GetFocus() = $hComboEdit

    ; If combo has been opened, check if closed again ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2
    If $fCombo_Opened Then
        ; If combo is closed again
        If _GUICtrlComboBox_GetDroppedState($hEdit_Combo) = False Then
            $iLoopCode = 1
            ExitLoop
        EndIf
    Else
        ; Check if opened and set flag
        If _GUICtrlComboBox_GetDroppedState($hEdit_Combo) Then $fCombo_Opened = True
    EndIf

    ; If Enter Pressed ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2
    If _IsPressed("0D", $dll) Then
        $iLoopCode = 1
        ExitLoop
    EndIf

        ; If ESC pressed
        If _IsPressed("1B", $dll) Then
        $iLoopCode = -1
        ExitLoop
    EndIf

        ; Save CPU and prevent tab selection <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 1
        Switch GUIGetMsg()
        Case $hTab
            _GUICtrlTab_ClickTab($hTab, $clickedweek)
        ;Case $GUI_EVENT_CLOSE
        ;    DllClose($dll)
        ;    Exit
    EndSwitch
WEnd

; Read Combo
$sText = GUICtrlRead($hEdit_Combo)

; Check if content changed manually
If Not $iLoopCode And $sText <> $sItemText Then $iLoopCode = 1 ; <<<<<<<<<<<<<<<<<<<<< 2

; Edit ListView if required
If $iLoopCode = 1 Then
_GUICtrlListView_SetItemText($hListView, $aLV_Click_Info[0], $sText, $aLV_Click_Info[1])

$week = "week"&GUICtrlRead($hTab)
if $hListView = $listviews[7] then $dagread = "Maandag"
if $hListView = $listviews[9] then $dagread = "Dinsdag"
if $hListView = $listviews[11] then $dagread = "Woensdag"
if $hListView = $listviews[8] then $dagread = "Donderdag"
if $hListView = $listviews[10] then $dagread = "Vrijdag"
if $hListView = $listviews[12] then $dagread = "Zaterdag"

if $aLV_Click_Info[1] = 3 then $fields = "Wvb"
if $aLV_Click_Info[1] = 2 then $fields = "Klantenproject"
if $aLV_Click_Info[1] = 1 then $fields = "Naam"
if $aLV_Click_Info[1] = 0 then $fields = "Bus"


$value1 = ""
$value2 = ""
$value3 = ""
$value4 = ""


if $aLV_Click_Info[1] + 1 = 1 then $value1 = $sText
if $aLV_Click_Info[1] + 1 = 2 then $value2 = $sText
if $aLV_Click_Info[1] + 1 = 3 then $value3 = $sText
if $aLV_Click_Info[1] + 1 = 4 then $value4 = $sText

ConsoleWrite($aLV_Click_Info[0] + 1 & " "& $aLV_Click_Info[1] + 1 &@CRLF)

Local $sMySqlStatement = ""


$sMySqlStatement &= "INSERT INTO "&$week&" (Dag,Bus,Naam,Klantenproject,Wvb) VALUES (" & "'" & $dagread & "'," & "'" & $value1 & "'," & "'" & $value2 & "'," & "'" & $value3 & "'," & "'" & $value4 & "');"


If Not _EzMySql_Exec($sMySqlStatement) Then
    MsgBox(0, "Error inserting data to Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

$sMySqlStatement = ""
ConsoleWrite( _EzMySql_InsertID()&@CRLF)
EndIf
; Delete combo
GUICtrlDelete($hEdit_Combo)
$hEdit_Combo = 0

        EndIf

    EndIf

WEnd


Func WM_NOTIFY($hWnd, $iMsg, $wParam, $lParam)

    #forceref $hWnd, $iMsg, $wParam
    Local $hWndFrom, $iCode, $tNMHDR

    $tNMHDR = DllStructCreate($tagNMHDR, $lParam)
    $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom"))
    $iCode = DllStructGetData($tNMHDR, "Code")

    For $i = 1 To 6
        If $aLV_Handles[$i] = $hWndFrom Then
            Switch $iCode
                Case $NM_CLICK
                    $fClicked = $i
            EndSwitch
            ExitLoop
        EndIf
    Next
    Return $GUI_RUNDEFMSG

EndFunc   ;==>WM_NOTIFY


Func _Calculate()

    ; Clear array
    Global $aWeeks[54][8]

    ; Set local variables
    Local $asDatePart, $iTimePart
    Local $aDays[8] = [7, "Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag", "Zondag"]
    Local $aMonths[13] = [12, "Januari", "Februari", "Maart", "April", "Mei", "Juni", "Juli", "Augustus", "September", "October", "November", "December"]

    ; Read required year and week
    $sYear = GUICtrlRead($hInput)
    $clickedweek = GUICtrlRead($hTab)

    ; Set first day
    $sdate = $sYear & "/01/01"
    ; Set flag to indicate initial week
    $fStart = True
    ; Set flags to indicate 0/53 week empty
    $fWeek_0 = False
    $fWeek_53 = False

    While 1

        ; Split date
        _DateTimeSplit($sdate, $asDatePart, $iTimePart)
        ; Exit if past end required year

        If $asDatePart[1] > $sYear Then ExitLoop

        ; Get week number
        $iWeek = _WeekNumberISO($sYear, $asDatePart[2], $asDatePart[3])

        ; Adjust to cover final part of previous Week 52 if required
        If $fStart Then
            If $iWeek >= 52 Then $iWeek = 0
        Else
            If $iWeek = 1 Then $iWeek = 53
        EndIf
        ; Reset flag if past initial week
        If $iWeek = 2 Then $fStart = False

        ; Get day of week and adjust for Sunday
        $iIndex = _DateToDayOfWeek($sYear, $asDatePart[2], $asDatePart[3])
        If $iIndex = 1 Then
            $iIndex = 8
            ; Adjust for final week if needed
            If Not $fStart And $iWeek = 53 Then $iWeek = 52
        EndIf

        ; Fill array
        ConsoleWrite($aDays[$iIndex - 1] & " " & $asDatePart[3] & " " & $aMonths[$asDatePart[2]]&@CRLF)
        $aWeeks[$iWeek][$iIndex - 1] = $aDays[$iIndex - 1] & " " & $asDatePart[3] & " " & $aMonths[$asDatePart[2]]
        ; Move to next day
        $sdate = _DateAdd("d", 1, $sdate) ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

    WEnd

    ; Check if weeks 0 and 53 are used
    For $i = 1 To 6
        If $aWeeks[0][$i] <> "" Then $fWeek_0 = True
        If $aWeeks[53][$i] <> "" Then $fWeek_53 = True
    Next
    ; If not then do not display them if selected
    If GUICtrlRead($hTab) = 0 And Not $fWeek_0 Then _GUICtrlTab_ClickTab($hTab, 1)
    If GUICtrlRead($hTab) = 53 And Not $fWeek_53 Then _GUICtrlTab_ClickTab($hTab, 52)
    $clickedweek = GUICtrlRead($hTab)

    For $i = 1 To 6
        ;ConsoleWrite($aDayLabels[$i] & " - " & $aWeeks[$clickedweek][$i] & @CRLF)
        GUICtrlSetData($aDayLabels[$i], $aWeeks[$clickedweek][$i])
    Next

EndFunc   ;==>_Calculate

func _refresh($week)
;$arraysize = UBound($aok)
ConsoleWrite($aok&@CRLF)
$aOk = _EzMySql_GetTable2d("SELECT * FROM "&$week&";")
$error = @error
If Not IsArray($aOk) Then
$create = "CREATE TABLE IF NOT EXISTS "&$week&" (" & _
                   "RowID INT NOT NULL AUTO_INCREMENT," & _
                   "Dag TEXT NOT NULL ," & _
                   "Bus TEXT NOT NULL ," & _
                   "Naam TEXT NOT NULL ," & _
                   "Klantenproject TEXT NOT NULL ," & _
                   "Wvb TEXT NOT NULL ," & _
                   "PRIMARY KEY (`RowID`) ," & _
                   "UNIQUE INDEX RowID_UNIQUE (`RowID` ASC) );"

If Not _EzMySql_Exec($create) Then
    MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    _refresh($week)
EndIf
EndIf
;_ArrayDisplay($aok, "$avArray set manually 1D")
$j = 0
for $i =0 to UBound($aok) -1
    if $aok[$i][1] = "Maandag" then
    _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][2], 0)
    _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][3], 1)
    _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][4], 2)
    _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][5], 3)
$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Dinsdag" then
    _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][2], 0)
    _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][3], 1)
    _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][4], 2)
    _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][5], 3)
$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Woensdag" then
    _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][2], 0)
    _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][3], 1)
    _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][4], 2)
    _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][5], 3)
$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Donderdag" then
    _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][2], 0)
    _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][3], 1)
    _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][4], 2)
    _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][5], 3)
$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Vrijdag" then
    _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][2], 0)
    _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][3], 1)
    _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][4], 2)
    _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][5], 3)
$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Zaterdag" then
    _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][2], 0)
    _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][3], 1)
    _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][4], 2)
    _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][5], 3)
$j+=1
EndIf
Next
;_ArrayDisplay($maandagdata, "$avArray set manually 1D")
EndFunc

somebody any advice?

Link to comment
Share on other sites
jchd Universalist

jchd

Posted
Posted

but if i try to insert data into a exsisting row.

it goes wrong then it just create a new row.

Are you talking about some GUI element (listview or similar) or about rows of a table in your DB?

If the later, you have no UPDATE SQL statement, only INSERTs. No surprise it always inserts and never updates.

BTW, I don't have MySQL installed on this machine so I can't run your script in order to follow your explanations closely.

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)

Link to comment
Share on other sites
yucatan Universalist

yucatan

Posted
  • Author
Posted

i have now build in a update command.

but it fails for some reason.

this is the command: UPDATE week3 SET naam='mysql' WHERE rowid='5');

this is the insert command: INSERT INTO week3 (Dag,Bus,Naam,Klantenproject,Wvb) VALUES ('Maandag','','','','Wvb 2');

what am i doing wrong?

if $sRowid = "" then
Local $sMySqlStatement = ""
$sMySqlStatement &= "INSERT INTO "&$week&" (Dag,Bus,Naam,Klantenproject,Wvb) VALUES (" & "'" & $dagread & "'," & "'" & $value1 & "'," & "'" & $value2 & "'," & "'" & $value3 & "'," & "'" & $value4 & "');"
;INSERT INTO week3 (Dag,Bus,Naam,Klantenproject,Wvb) VALUES ('data','data','data','data','data');
Else
Local $sMySqlStatement = ""
$sMySqlStatement &= "UPDATE "&$week&" SET naam='mysql' WHERE rowid='5');"
ConsoleWrite($sMySqlStatement&@CRLF)
EndIf
;UPDATE example SET age='22' WHERE age='21'")

If Not _EzMySql_Exec($sMySqlStatement) Then
    MsgBox(0, "Error inserting data to Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

$sMySqlStatement = ""
ConsoleWrite( _EzMySql_InsertID()&@CRLF)
EndIf
Link to comment
Share on other sites
jchd Universalist

jchd

Posted
Posted

I'm currently converting your initial code to use SQLite so testing can be made ubiquitous. Hopefully most DB calls are very similar.

In your last code exerpt, where does $RowId come from? BTW the update is wrong: WHERE rowid = '5' should be WHERE rowid = 5 (without quotes). rowid is defined as an integer pripary key 'just like it should be) but you're testing if it equals the string '5'. This UPDATE will never work as is.

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)

Link to comment
Share on other sites
yucatan Universalist

yucatan

Posted
  • Author
Posted

the code as is.

u can see where i get the rowid.

i shall modify my update command and i test it.

i let u know.

#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <Date.au3>
#include <Array.au3>
#include <TabConstants.au3>
#include <EditConstants.au3>
#include <UpdownConstants.au3>
#Include <GuiTab.au3>
#Include <GuiComboBox.au3>
#include <Misc.au3>
#include<EzMySql.au3>

Opt("GUICloseOnESC", 0)
If Not _EzMySql_Startup() Then
    MsgBox(0, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_Open("localhost", "root", "", "", "3306") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_Exec("CREATE DATABASE IF NOT EXISTS planning") Then
    MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

If Not _EzMySql_SelectDB("planning") Then
    MsgBox(0, "Error setting Database to use", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

Global $fClicked = 0, $fWeek_0 = False, $fWeek_53 = False
Global $hListView = 0, $hEdit_Combo = 0, $aLV_Click_Info
Global $iListView_ID, $sItemText
Global $aLV_Handles[7], $aDayLabels[7]
Global $aWeeks[54][8]
Global $sYear = @YEAR
Global $sdate = $sYear & "/01/01"
Global $buswidth = 30, $monteurwidth = 60, $klantwidth = 155
global $listviews[7],$rows[1],$aok
global $maandag[1],$dinsdag[1],$woensdag[1],$donderdag[1],$vrijdag[1],$zaterdag[1],$maandagdata[1],$dinsdagdata[1],$woensdagdata[1],$donderdagdata[1],$vrijdagdata[1]

$clickedweek = _WeekNumberISO()
; Open DLL for _IsPressed
Global $dll = DllOpen("user32.dll")
Global $hGUI = GUICreate("Test", 900, 940)

$hTab = GUICtrlCreateTab(10, 10, 890, 940)
$hTab_0 = GUICtrlCreateTabItem("Week 0")
For $i = 1 To 53
    GUICtrlCreateTabItem("Week " & $i)
Next
GUICtrlCreateTabItem("")

$combo = GUICtrlCreateCombo("", 500, 45,120,50, $CBS_DROPDOWNLIST)
GUICtrlSetData(-1, "Kopieer vorige week|Laad Standaart", "") ; add other item snd set a new default

GUICtrlSetState(_WeekNumberISO() + $hTab_0, $GUI_SHOW)
$hInput = GUICtrlCreateInput($sYear, 420, 45, 50, 20, $ES_READONLY)
$hUpDown = GUICtrlCreateUpdown($hInput, BitOR($GUI_SS_DEFAULT_UPDOWN, $UDS_WRAP, $UDS_NOTHOUSANDS))
GUICtrlSetLimit($hUpDown, $sYear + 5, $sYear - 5)

For $i = 1 To 3
    For $j = 0 To 1
        $iX = 15 + (290 * ($i - 1))
        $iY = 90 + (440 * $j)
        $aDayLabels[(3 * $j) + $i] = GUICtrlCreateLabel("", $iX + 40, $iY - 20, 150, 25)
        $hListView = _GUICtrlListView_Create($hGUI, "RowID", $iX, $iY, 285, 407, BitOR($LVS_REPORT, $LVS_SINGLESEL, $WS_BORDER), $LVS_EX_FULLROWSELECT)
        _ArrayAdd($listviews,$hListView)
        _GUICtrlListView_SetExtendedListViewStyle($hListView, $LVS_EX_GRIDLINES) ; Set grid lines
        $aLV_Handles[$i + ($j * 3)] = $hListView
        _GUICtrlListView_AddColumn($hListView, "Bus")
        _GUICtrlListView_AddColumn($hListView, "Monteur")
        _GUICtrlListView_AddColumn($hListView, "Klant en Project")
        _GUICtrlListView_AddColumn($hListView, "Wvb")
        ControlDisable($hGUI, "", HWnd(_GUICtrlListView_GetHeader($hListView)))
        _GUICtrlListView_SetColumnWidth($hListView, 0, 20)
        _GUICtrlListView_SetColumnWidth($hListView, 1, $buswidth)
        _GUICtrlListView_SetColumnWidth($hListView, 2, $monteurwidth)
        _GUICtrlListView_SetColumnWidth($hListView, 3, $klantwidth)
        _GUICtrlListView_SetColumnWidth($hListView, 4, $LVSCW_AUTOSIZE_USEHEADER)
        For $k = 1 To 27
            _GUICtrlListView_AddItem($hListView, "")
            _GUICtrlListView_AddSubItem($hListView, $k - 1, "", 1)
            _GUICtrlListView_AddSubItem($hListView, $k - 1, "", 2)
        Next
        Next
    Next
_Calculate()
_refresh("week"&_WeekNumberISO())
GUISetState()
GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY")
$hAccel_Enter = GUICtrlCreateDummy()
Dim $aAccelKeys[1][2]=[["{ENTER}", $hAccel_Enter]]
While 1
    Switch GUIGetMsg()
        Case $combo
            ConsoleWrite(GUICtrlRead($combo))
        Case $GUI_EVENT_CLOSE
            DllClose($dll)
            Exit
Case $hTab
    ; Do not display weeks 0 and 53 if not used
    If GUICtrlRead($hTab) = 0 And Not $fWeek_0 Then _GUICtrlTab_ClickTab($hTab, 1)
    If GUICtrlRead($hTab) = 53 And Not $fWeek_53 Then _GUICtrlTab_ClickTab($hTab, 52)
    $clickedweek = GUICtrlRead($hTab)
    For $i = 1 To 6
        GUICtrlSetData($aDayLabels[$i], $aWeeks[$clickedweek][$i])
    Next
    ; Clear existing items
    For $j = 1 To 6
        For $i = 0 To 26 ; Because you have created 27 items initially
            _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "")
            _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 1)
            _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 2)
            _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 3)
        Next
    Next
    ; Fill with new data
    _refresh("week" & $clickedweek)
        Case $hUpDown
            _Calculate()
    EndSwitch

; If an item was double clicked
    If $fClicked Then
        ; Get ListView details
        $hListView = $aLV_Handles[$fClicked]
        $iListView_ID = $fClicked
        ; Reset flag
        $fClicked = 0
        ; Get details item clicked
        $aLV_Click_Info = _GUICtrlListView_SubItemHitTest($hListView)
        If $aLV_Click_Info[0] <> -1 Then

            ; Delete any existing combo
            GUICtrlDelete($hEdit_Combo)
            ; Get current text
            $sItemText = _GUICtrlListView_GetItemText($hListView, $aLV_Click_Info[0], $aLV_Click_Info[1])
            ; Get Rowid
            $sRowid = _GUICtrlListView_GetItemText($hListView, $aLV_Click_Info[0], 0)
            ; Get position
            Local $aRect = _GUICtrlListView_GetSubItemRect($hListView, $aLV_Click_Info[0], $aLV_Click_Info[1])
            Local $aPos = WinGetPos($hListView)
            Local $tPoint = DllStructCreate("int X;int Y")
            DllStructSetData($tPoint, "X", $aPos[0])
            DllStructSetData($tPoint, "Y", $aPos[1])
            _WinAPI_ScreenToClient($hGUI, $tPoint)
            Local $iEdit_X = DllStructGetData($tPoint, "X") + $aRect[0]
            Local $iEdit_Y = DllStructGetData($tPoint, "Y") + $aRect[1]
            ; Get column width
Local $iWidth = _GUICtrlListView_GetColumnWidth($hListView, $aLV_Click_Info[1])

ConsoleWrite($sRowid &@CRLF)
; Create edit combo
$hEdit_Combo = GUICtrlCreateCombo("",  $iEdit_X,  $iEdit_Y, $iWidth, 20)
; Set combo values depending on column clicked
Switch $aLV_Click_Info[1]  ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2
    Case 0
        $sComboList = "1 Bus|2 Bus|3 Bus"
        If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText
        _GUICtrlComboBox_LimitText($hEdit_Combo, 2)
    Case 1
        $sComboList = "Monteur 1|Monteur 2|Monteur 3"
        If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText
        Case 2
        $sComboList = "Klant 1|Klant 2|Klant 3"
        If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText
    Case 3
        $sComboList = "Wvb 1|Wvb 2|Wvb 3"
        If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText
        _GUICtrlComboBox_LimitText($hEdit_Combo, 3)
EndSwitch
; Set values into combo
GUICtrlSetData($hEdit_Combo, $sComboList, $sItemText)
GUICtrlSetState($hEdit_Combo, BitOR($GUI_FOCUS, $GUI_ONTOP))

; Get handle of combo edit control
$hComboEdit = _WinAPI_GetFocus()
; Set flag for Enter pressed
$iLoopCode = 0
; Set flag for combo opened <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2
$fCombo_Opened = False

; Loop while the combo has focus
While _WinAPI_GetFocus() = $hComboEdit

    ; If combo has been opened, check if closed again ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2
    If $fCombo_Opened Then
        ; If combo is closed again
        If _GUICtrlComboBox_GetDroppedState($hEdit_Combo) = False Then
            $iLoopCode = 1
            ExitLoop
        EndIf
    Else
        ; Check if opened and set flag
        If _GUICtrlComboBox_GetDroppedState($hEdit_Combo) Then $fCombo_Opened = True
    EndIf

    ; If Enter Pressed ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2
    If _IsPressed("0D", $dll) Then
        $iLoopCode = 1
        ExitLoop
    EndIf

        ; If ESC pressed
        If _IsPressed("1B", $dll) Then
        $iLoopCode = -1
        ExitLoop
    EndIf

        ; Save CPU and prevent tab selection <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 1
        Switch GUIGetMsg()
        Case $hTab
            _GUICtrlTab_ClickTab($hTab, $clickedweek)
        ;Case $GUI_EVENT_CLOSE
        ;    DllClose($dll)
        ;    Exit
    EndSwitch
WEnd

; Read Combo
$sText = GUICtrlRead($hEdit_Combo)

; Check if content changed manually
If Not $iLoopCode And $sText <> $sItemText Then $iLoopCode = 1 ; <<<<<<<<<<<<<<<<<<<<< 2

; Edit ListView if required
If $iLoopCode = 1 Then
_GUICtrlListView_SetItemText($hListView, $aLV_Click_Info[0], $sText, $aLV_Click_Info[1])

$week = "week"&GUICtrlRead($hTab)
if $hListView = $listviews[7] then $dagread = "Maandag"
if $hListView = $listviews[9] then $dagread = "Dinsdag"
if $hListView = $listviews[11] then $dagread = "Woensdag"
if $hListView = $listviews[8] then $dagread = "Donderdag"
if $hListView = $listviews[10] then $dagread = "Vrijdag"
if $hListView = $listviews[12] then $dagread = "Zaterdag"

if $aLV_Click_Info[1] = 3 then $fields = "Wvb"
if $aLV_Click_Info[1] = 2 then $fields = "Klantenproject"
if $aLV_Click_Info[1] = 1 then $fields = "Naam"
if $aLV_Click_Info[1] = 0 then $fields = "Bus"


$value1 = ""
$value2 = ""
$value3 = ""
$value4 = ""


if $aLV_Click_Info[1] + 1 = 1 then $value1 = $sText
if $aLV_Click_Info[1] + 1 = 2 then $value2 = $sText
if $aLV_Click_Info[1] + 1 = 3 then $value3 = $sText
if $aLV_Click_Info[1] + 1 = 4 then $value4 = $sText

if $sRowid = "" then
Local $sMySqlStatement = ""
$sMySqlStatement &= "INSERT INTO "&$week&" (Dag,Bus,Naam,Klantenproject,Wvb) VALUES (" & "'" & $dagread & "'," & "'" & $value1 & "'," & "'" & $value2 & "'," & "'" & $value3 & "'," & "'" & $value4 & "');"
;INSERT INTO week3 (Dag,Bus,Naam,Klantenproject,Wvb) VALUES ('data','data','data','data','data');
ConsoleWrite($sMySqlStatement&@CRLF)
Else
Local $sMySqlStatement = ""
$sMySqlStatement &= "UPDATE "&$week&" SET naam='mysql' WHERE rowid='5');"
EndIf
;UPDATE example SET age='22' WHERE age='21'")

If Not _EzMySql_Exec($sMySqlStatement) Then
    MsgBox(0, "Error inserting data to Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    Exit
EndIf

$sMySqlStatement = ""
ConsoleWrite( _EzMySql_InsertID()&@CRLF)
EndIf
; Delete combo
GUICtrlDelete($hEdit_Combo)
$hEdit_Combo = 0

        EndIf
    EndIf
WEnd


Func WM_NOTIFY($hWnd, $iMsg, $wParam, $lParam)

    #forceref $hWnd, $iMsg, $wParam
    Local $hWndFrom, $iCode, $tNMHDR

    $tNMHDR = DllStructCreate($tagNMHDR, $lParam)
    $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom"))
    $iCode = DllStructGetData($tNMHDR, "Code")

    For $i = 1 To 6
        If $aLV_Handles[$i] = $hWndFrom Then
            Switch $iCode
                Case $NM_CLICK
                    $fClicked = $i
            EndSwitch
            ExitLoop
        EndIf
    Next
    Return $GUI_RUNDEFMSG

EndFunc   ;==>WM_NOTIFY


Func _Calculate()

    ; Clear array
    Global $aWeeks[54][8]

    ; Set local variables
    Local $asDatePart, $iTimePart
    Local $aDays[8] = [7, "Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag", "Zondag"]
    Local $aMonths[13] = [12, "Januari", "Februari", "Maart", "April", "Mei", "Juni", "Juli", "Augustus", "September", "October", "November", "December"]

    ; Read required year and week
    $sYear = GUICtrlRead($hInput)
    $clickedweek = GUICtrlRead($hTab)

    ; Set first day
    $sdate = $sYear & "/01/01"
    ; Set flag to indicate initial week
    $fStart = True
    ; Set flags to indicate 0/53 week empty
    $fWeek_0 = False
    $fWeek_53 = False

    While 1

        ; Split date
        _DateTimeSplit($sdate, $asDatePart, $iTimePart)
        ; Exit if past end required year

        If $asDatePart[1] > $sYear Then ExitLoop

        ; Get week number
        $iWeek = _WeekNumberISO($sYear, $asDatePart[2], $asDatePart[3])

        ; Adjust to cover final part of previous Week 52 if required
        If $fStart Then
            If $iWeek >= 52 Then $iWeek = 0
        Else
            If $iWeek = 1 Then $iWeek = 53
        EndIf
        ; Reset flag if past initial week
        If $iWeek = 2 Then $fStart = False

        ; Get day of week and adjust for Sunday
        $iIndex = _DateToDayOfWeek($sYear, $asDatePart[2], $asDatePart[3])
        If $iIndex = 1 Then
            $iIndex = 8
            ; Adjust for final week if needed
            If Not $fStart And $iWeek = 53 Then $iWeek = 52
        EndIf

        ; Fill array
        ConsoleWrite($aDays[$iIndex - 1] & " " & $asDatePart[3] & " " & $aMonths[$asDatePart[2]]&@CRLF)
        $aWeeks[$iWeek][$iIndex - 1] = $aDays[$iIndex - 1] & " " & $asDatePart[3] & " " & $aMonths[$asDatePart[2]]
        ; Move to next day
        $sdate = _DateAdd("d", 1, $sdate) ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

    WEnd

    ; Check if weeks 0 and 53 are used
    For $i = 1 To 6
        If $aWeeks[0][$i] <> "" Then $fWeek_0 = True
        If $aWeeks[53][$i] <> "" Then $fWeek_53 = True
    Next
    ; If not then do not display them if selected
    If GUICtrlRead($hTab) = 0 And Not $fWeek_0 Then _GUICtrlTab_ClickTab($hTab, 1)
    If GUICtrlRead($hTab) = 53 And Not $fWeek_53 Then _GUICtrlTab_ClickTab($hTab, 52)
    $clickedweek = GUICtrlRead($hTab)

    For $i = 1 To 6
        ;ConsoleWrite($aDayLabels[$i] & " - " & $aWeeks[$clickedweek][$i] & @CRLF)
        GUICtrlSetData($aDayLabels[$i], $aWeeks[$clickedweek][$i])
    Next

EndFunc   ;==>_Calculate

func _refresh($week)
;$arraysize = UBound($aok)
ConsoleWrite($aok&@CRLF)
$aOk = _EzMySql_GetTable2d("SELECT * FROM "&$week&";")
$error = @error
If Not IsArray($aOk) Then
$create = "CREATE TABLE IF NOT EXISTS "&$week&" (" & _
                   "RowID INT NOT NULL AUTO_INCREMENT," & _
                   "Dag TEXT NOT NULL ," & _
                   "Bus TEXT NOT NULL ," & _
                   "Naam TEXT NOT NULL ," & _
                   "Klantenproject TEXT NOT NULL ," & _
                   "Wvb TEXT NOT NULL ," & _
                   "PRIMARY KEY (`RowID`) ," & _
                   "UNIQUE INDEX RowID_UNIQUE (`RowID` ASC) );"

If Not _EzMySql_Exec($create) Then
    MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg())
    _refresh($week)
EndIf
EndIf
_ArrayDisplay($aok, "$avArray set manually 1D")
$j = 0
for $i =0 to UBound($aok) -1
    if $aok[$i][1] = "Maandag" then
    _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][0], 0)
    _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][2], 1)
    _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][3], 2)
    _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][4], 3)
    _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][5], 4)
$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Dinsdag" then
    _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][0], 0)
    _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][2], 1)
    _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][3], 2)
    _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][4], 3)
    _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][5], 4)
$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Woensdag" then
    _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][0], 0)
    _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][2], 1)
    _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][3], 2)
    _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][4], 3)
    _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][5], 4)

$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Donderdag" then
    _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][0], 0)
    _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][2], 1)
    _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][3], 2)
    _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][4], 3)
    _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][5], 4)
$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Vrijdag" then
    _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][0], 0)
    _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][2], 1)
    _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][3], 2)
    _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][4], 3)
    _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][5], 4)
$j+=1
EndIf
Next
$j = 0
for $i =0 to UBound($aok) -1
if $aok[$i][1] = "Zaterdag" then
    _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][0], 0)
    _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][2], 1)
    _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][3], 2)
    _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][4], 3)
    _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][5], 4)
$j+=1
EndIf
Next
EndFunc

Greetz Yucatan

Link to comment
Share on other sites
jchd Universalist

jchd

Posted
Posted

Sorry for interruption (I also have a business to run).

I'd rather see you use the following schema:

CREATE TABLE [planning] (

[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

[Year] INTEGER NOT NULL,

[Week] INTEGER NOT NULL,

[Dag] INTEGER NOT NULL,

[bus] TEXT NOT NULL DEFAULT (''),

[Naam] TEXT NOT NULL DEFAULT (''),

[Klantenproject] TEXT NOT NULL DEFAULT (''),

[Wvb] TEXT NOT NULL DEFAULT (''));

CREATE INDEX [idxDate] ON [planning] ([Year], [Week], [Dag]);

My personnal thoughs:

You need a year field or you're going to have hard time.

Place your week in the global table instead of having one table per week.

Make Dag (day ?) an index in a DayOf Week table.

Other columns are applicative as I see them.

You may have to add indexes if the baby get havily populated.

MySQL seems a sledgehammer for this.

Now your problem is that you were inserting a row after selection of any item in any combo box.

Without using a MySQL-specific verb or feature and relying only on portable SQL statements, what you should do is, after validation of an item in a combo box, read the corresponding row and insert if there is none or update the particular column if that row already exists.

Now the question is: how do you differentiate between rows in the same listview? I mean how does the position of the row in the listview translate into a condition in the SQL table?

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)

Link to comment
Share on other sites
jchd Universalist

jchd

Posted
Posted

Standard SQL does it nicely.

insert into week3 select * from week2;

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)

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
Go to topic listing

  • Recently Browsing   0 members

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