Sign in to follow this  
Followers 0
Appie78

Searching in database and diplaying result.

3 posts in this topic

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 :D

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


Electron microscopes rule!!!

Share this post


Link to post
Share on other sites



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.

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


Electron microscopes rule!!!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

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

Create an account

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


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0