Searching in database and diplaying result.

[Appie78]

Hi all,

I'm using a database to log some things which are importent to me. I want to make a function to search in the database on 'reference number' and/or 'date(TimeStart)'.

As I am a noob on databases

Can somebody push me in a good direction?

Func SQLite()
    _SQLite_Startup()
    If @error > 0 Then
        MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
        Exit -1
    EndIf
    _SQLite_Open("C:\Program Files\JEOL\Scan Sample\Database.db")
    _SQLite_Exec(-1, "Create table if not exists JEOL (TimeStart,ReferenceNumber,AmountFields,PauseNr,TimeEnd);")
    If @error > 0 Then
        MsgBox(16, "SQLite Error", "Can't Load Database!")
        Exit -1
    EndIf
    $APause = $APause / 2
    $Reference = IniRead($ini, "Stub", "Input " & $Stub, "Not Found")
    $End = @MDAY & "-" & @MON & "-" & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC
    If Not FileExists("C:\Program Files\JEOL\Scan Sample\Database.db") Then
        _SQLite_Exec(-1, "Create table JEOL (TimeStart,ReferenceNumber,AmountFields,PauseNr,TimeEnd);")
    EndIf
    _SQLite_Exec(-1, "Insert into JEOL(TimeStart,ReferenceNumber,AmountFields,PauseNr,TimeEnd) values ('" & $Start & "','" & $Reference & "','" & $StepCount & "','" & $APause & "','" & $End & "');")
    _SQLite_GetTable2d(-1, "Select * From JEOL", $aResult, $iRows, $iColumns)
    _ArrayDisplay($aResult, "File dump all tables, JEOL")
    _SQLite_Close()
    _SQLite_Shutdown()
    $APause = 0
EndFunc   ;==>SQLite

This is my function to fill the database.

Thanks in advance,

Appie

[jvanegmond]

Hoi, Appie.

Is this what you were looking for:

SELECT * FROM JEOL WHERE ReferenceNumber = 5

?

[Appie78]

Hoi, Appie.

Is this what you were looking for:

SELECT * FROM JEOL WHERE ReferenceNumber = 5

?

Hi Manadar,

It's working like a charm, the only thing which I don't understand is, when I've done a search and got a 'hit' the search after that with a number that doesn't exists is giving the previous result!!! (long sentence I hope you understand)

And is there a way to embed _Arraydisplay into a GUI?

This is my code now.

expandcollapse popup

Func SQLite()
    _SQLite_Startup()
    If @error > 0 Then
        MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
        Exit -1
    EndIf
    _SQLite_Open("C:\Program Files\JEOL\Scan Sample\Database.db")
    _SQLite_Exec(-1, "Create table if not exists JEOL (TimeStart,ReferenceNumber,AmountFields,PauseNr,TimeEnd);")
    If @error > 0 Then
        MsgBox(16, "SQLite Error", "Can't Load Database!")
        Exit -1
    EndIf
    $APause = $APause / 2
    $Reference = IniRead($ini, "Stub", "Input " & $Stub, "Not Found")
    $End = @MDAY & "-" & @MON & "-" & @YEAR & " " & @HOUR & ":" & @MIN & ":" & @SEC
    If Not FileExists("C:\Program Files\JEOL\Scan Sample\Database.db") Then
        _SQLite_Exec(-1, "Create table JEOL (TimeStart,ReferenceNumber,AmountFields,PauseNr,TimeEnd);")
    EndIf
    _SQLite_Exec(-1, "Insert into JEOL(TimeStart,ReferenceNumber,AmountFields,PauseNr,TimeEnd) values ('" & $Start & "','" & $Reference & "','" & $StepCount & "','" & $APause & "','" & $End & "');")
    _SQLite_GetTable2d(-1, "Select * From JEOL", $aResult, $iRows, $iColumns)
    _ArrayDisplay($aResult, "File dump all tables, JEOL")
    _SQLite_Close()
    _SQLite_Shutdown()
    $APause = 0
EndFunc   ;==>SQLite

Func SearchSQLiteGUI()
    $GUISQL = GUICreate("Test", 300, 300)
    $SearchRef = GUICtrlCreateInput("Test", 10, 10)
    $Search = GUICtrlCreateButton("Search", 100, 100)
    GUISetState(@SW_SHOW)
    While 1
        $nMsg = GUIGetMsg()
        Select
            Case $nMsg = $Search
                SearchSQLite()
            Case $nMsg = $GUI_EVENT_CLOSE
                GUIDelete($GUISQL)
                ExitLoop
        EndSelect
    WEnd
EndFunc   ;==>SearchSQLiteGUI

Func SearchSQLite()
    _SQLite_Startup()
    If @error > 0 Then
        MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
        Exit -1
    EndIf
    _SQLite_Open("C:\Program Files\JEOL\Scan Sample\Database.db")
    _SQLite_Exec(-1, "Create table if not exists JEOL (TimeStart,ReferenceNumber,AmountFields,PauseNr,TimeEnd);")
    If @error > 0 Then
        MsgBox(16, "SQLite Error", "Can't Load Database!")
        Exit -1
    EndIf
    _SQLite_GetTable2d(-1, "SELECT * FROM JEOL WHERE ReferenceNumber ='" & GUICtrlRead($SearchRef) & "'", $SearchResult, $iRows, $iColumns)
    _ArrayDisplay($SearchResult, "File dump all tables, JEOL")
EndFunc   ;==>SearchSQLite

Thanks,

Appie