Sign in to follow this  
Followers 0
searchresult

listview to sqlite table

15 posts in this topic

How to save data from listview ti sqlite db file?

Share this post


Link to post
Share on other sites



Right now i have this

Func Sqlite()
    
    Local $n
    Local $row
    Local $ListViewDB[18]
    
    _SQLite_Startup()
    _SQLite_Open("@ScriptDir\medorg.db")
    
    $row = _SQLite_LastInsertRowID()
    
    _SQLite_Exec(-1,"Create table pcaijent (br_prijave integer, ime text, prezime text, spol text, godina_r text, starost text, db_skup text, prebivaliste text, mjesto text, ispostava text, lokalizacija text, uzrok text, mikr text, kult text, rezistencija text, bolest text, terapija text, ishod text, lijecnik text);")

    
    For $n = 0 To $row
            
            $ListViewDB = _GUICtrlListViewGetItemTextArray($ListView1,$n)

            _SQLite_Exec(-1,"Insert into pacijent values ($ListViewDB[1], $ListViewDB[2], $ListViewDB[3], $ListViewDB[4], $ListViewDB[5], 
                        $ListViewDB[6], $ListViewDB[7], $ListViewDB[9], $ListViewDB[9], $ListViewDB[10], $ListViewDB[11], $ListViewDB[12], $ListViewDB[13],
                         $ListViewDB[14], $ListViewDB[15], $ListViewDB[16], $ListViewDB[17], $ListViewDB[18]);")
            
    Next
    
    _SQLite_Close("medorg.db")
    _SQLite_Shutdown() 
    
EndFunc

But it doesn't work.

Somebody help?

Share this post


Link to post
Share on other sites

hi,

Give me a chance!?

Give me a script to run, not a func?

Best, Randall

here is the script

#cs

To do List:

    1. Add Sqlite database
    2. Add admin log in Control
    3. Add Sqlite database manipulation app or child gui
    4. Add Checking input's for incorecct data
    5. Add Checking for double data

#ce

#include <GUIConstants.au3>
#include <ExcelCOM_UDF.au3>
#Include <GuiListView.au3>
#include <file.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>



HotKeySet("^o","open")
HotKeySet("^s","save")
HotKeySet("^n","new")
HotKeySet("^p","print")


#Region ### START Koda GUI section ### Form=c:\documents and settings\marko\desktop\koda_2007.06.09\forms\medica.kxf
$Form1_1 = GUICreate("Medorg", 793, 567, 193, 115)
GUISetBkColor(0x004080)

Global $InputIDs[19], $w = 153

For $n = 0 To 18
    $y = 72 + ($n * 32)
    If $n <= 7 Then $l = 128
    If $n >= 8 Then
        $l = 392
        $y = 72 + (($n - 8) * 32)
        If $n > 15 Then
            $l = 624
            $y = 72 + (($n - 16) * 32)
        EndIf
        
    EndIf
    
    $InputIDs[$n] = GUICtrlCreateInput("", $l, $y, $w, 24,  BitOR($ES_AUTOHSCROLL,$WS_BORDER))
    GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
Next



$ListView1 = GUICtrlCreateListView("Br. prijave|Ime|Prezime|Spol|God. rođenja|Starost|Dobna skupina|Prebivalite|Mjesto|Ispostava|Lokalizacija|Uzrok|Mikr|Kultura|Rezistencija|Bolest|Terapija|I

shod|Liječnik", 16, 360, 762, 174, BitOR($LVS_REPORT,$LVS_SINGLESEL,$LVS_SHOWSELALWAYS,$WS_BORDER), BitOR($WS_EX_CLIENTEDGE,$LVS_EX_GRIDLINES,$LVS_EX_FULLROWSELECT))
GUICtrlSendMsg(-1, 0x101E, 0, 120)
GUICtrlSendMsg(-1, 0x101E, 1, 100)
GUICtrlSendMsg(-1, 0x101E, 2, 100)
GUICtrlSendMsg(-1, 0x101E, 3, 50)
GUICtrlSendMsg(-1, 0x101E, 4, 100)
GUICtrlSendMsg(-1, 0x101E, 5, 100)
GUICtrlSendMsg(-1, 0x101E, 6, 110)
GUICtrlSendMsg(-1, 0x101E, 7, 100)
GUICtrlSendMsg(-1, 0x101E, 8, 100)
GUICtrlSendMsg(-1, 0x101E, 9, 100)
GUICtrlSendMsg(-1, 0x101E, 10, 200)
GUICtrlSendMsg(-1, 0x101E, 11, 200)
GUICtrlSendMsg(-1, 0x101E, 12, 200)
GUICtrlSendMsg(-1, 0x101E, 13, 200)
GUICtrlSendMsg(-1, 0x101E, 14, 200)
GUICtrlSendMsg(-1, 0x101E, 15, 200)
GUICtrlSendMsg(-1, 0x101E, 16, 200)
GUICtrlSendMsg(-1, 0x101E, 17, 200)
GUICtrlSendMsg(-1, 0x101E, 18, 200)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
$Label1 = GUICtrlCreateLabel("Broj prijave:", 16, 80, 88, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Graphic1 = GUICtrlCreateGraphic(16, 40, 756, 4)
GUICtrlSetColor(-1, 0xFFFBF0)
$Label2 = GUICtrlCreateLabel("Medorg", 32, 16, 49, 20)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label3 = GUICtrlCreateLabel("Ime:", 16, 112, 33, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label4 = GUICtrlCreateLabel("Prezime:", 16, 144, 64, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label5 = GUICtrlCreateLabel("Spol:", 16, 176, 40, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label6 = GUICtrlCreateLabel("God. rođenja:", 16, 208, 98, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label7 = GUICtrlCreateLabel("Starost:", 16, 240, 57, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label8 = GUICtrlCreateLabel("Dobna skupina:", 16, 272, 112, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label9 = GUICtrlCreateLabel("Prebivalite:", 16, 304, 91, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label10 = GUICtrlCreateLabel("Mjesto:", 296, 80, 54, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label11 = GUICtrlCreateLabel("Ispostava:", 296, 112, 76, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label12 = GUICtrlCreateLabel("Lokalizacija:", 296, 144, 91, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label13 = GUICtrlCreateLabel("Uzrok:", 296, 176, 48, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label14 = GUICtrlCreateLabel("Mikr:", 296, 208, 37, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label15 = GUICtrlCreateLabel("Kultura:", 296, 240, 55, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label16 = GUICtrlCreateLabel("Rezistencija:", 296, 272, 93, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label17 = GUICtrlCreateLabel("Bolest:", 296, 304, 52, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label18 = GUICtrlCreateLabel("Terapija:", 552, 80, 67, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label19 = GUICtrlCreateLabel("Ishod:", 552, 112, 46, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Label20 = GUICtrlCreateLabel("Liječnik:", 552, 144, 61, 20)
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFFFFFF)
$Button1 = GUICtrlCreateButton("Novi zapis", 704, 176, 75, 25, 0)
$Button2 = GUICtrlCreateButton("Brii zapis", 704, 208, 75, 25, 0)
$Button3 = GUICtrlCreateButton("Trai", 704, 240, 75, 25, 0)
$Button4 = GUICtrlCreateButton("Spremi", 704, 272, 75, 25, 0)
$Graphic2 = GUICtrlCreateGraphic(19, 340, 756, 4)
GUICtrlSetColor(-1, 0xFFFBF0)
$Button5 = GUICtrlCreateButton("Ispis", 704, 304, 75, 25, 0)
$MenuItem1 = GUICtrlCreateMenu("&Izbor")
$MenuItem3 = GUICtrlCreateMenuItem("Novi zapis", $MenuItem1)
$MenuItem4 = GUICtrlCreateMenuItem("Otvori zapis", $MenuItem1)
$MenuItem5 = GUICtrlCreateMenuItem("Spremi", $MenuItem1)
$MenuItem8 = GUICtrlCreateMenuItem("Uredi zapis", $MenuItem1)
$MenuItem9 = GUICtrlCreateMenuItem("Osvjei", $MenuItem1)
$MenuItem6 = GUICtrlCreateMenuItem("Izlaz", $MenuItem1)
$MenuItem10 = GUICtrlCreateMenu("&Baza")
$MenuItem11 = GUICtrlCreateMenuItem("Dodaj u bazu", $MenuItem10)
$MenuItem2 = GUICtrlCreateMenu("&Pomoć")
$MenuItem7 = GUICtrlCreateMenuItem("O programu", $MenuItem2)

GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    
    If _IsPressed("74") Then
        
        For $n = 0 To 18
            GUICtrlSetData($InputIDs[$n], "")
        Next        
        
    EndIf
    
    $nMsg = GUIGetMsg()
    Switch $nMsg
        
    Case $GUI_EVENT_CLOSE
        
        Exit
        
    Case $MenuItem7
        Run("about.exe")
        
    Case $Button1, $MenuItem3
        
        new()

    Case $Button4, $MenuItem5
        
        $Mess = MsgBox(1,"Spremiti?", "elite li sačuvati zapise?")
        
        If $Mess = 1 Then
            
            save()
        
        EndIf   
        
    Case $Button2
        
        $Mess = MsgBox(1,"Brisati?", "elite li izbrisati odabrani zapis?")
        
        If $Mess = 1 Then
            
        _GUICtrlListViewDeleteItemsSelected($ListView1)
        
        EndIf
        
    Case $MenuItem8
        
        $Mess = MsgBox(1,"Urediti?", "elite li urediti odabrani zapis?")
        
        If $Mess = 1 Then
            
            getsel()
            _GUICtrlListViewDeleteItemsSelected($ListView1) 
            
        EndIf   

    Case $Button3
        
        For $n = 0 To 18
            
            $find = GUICtrlRead($InputIDs[$n])
            
            If $find <> "" Then ExitLoop
                
        Next
        
        $test = _GUICtrlListViewFindItem($ListView1, $find, -1,BitOR ($LVFI_STRING, $LVFI_WRAP))
        
        _GUICtrlListViewSetItemSelState($ListView1, $test)
        
        
        If @error Then
            
            Beep(700, 150)
            MsgBox(48,"","Zapis nije pronađen!")
            
        Else
            
            MsgBox(64,"Pretraga", "Zapis pronađen")
            
        EndIf
        
        getsel()
        
    Case $MenuItem4
        
        open()
        
    Case $Button5
                
        print()
        
    Case $MenuItem9
        
        Sqlite()
        refresh()

    Case $MenuItem11
        
        $Mess = MsgBox(1,"Baza podataka", "Jeste li sigurni da elite dodati nove zapise u bazu podataka?")
        If $Mess = 1 Then
        add()
        EndIf

    Case $MenuItem6
        
        quit()
    
EndSwitch
WEnd

;~ Function for filling inputboxes whit data from selected row in listview

Func getsel()
    
    $var = _GUICtrlListViewGetCurSel($ListView1)
    
    For $n = 0 To 18
        
        $ured = _GUICtrlListViewGetItemText($ListView1, $var, $n)   
        GUICtrlSetData($InputIDs[$n],$ured)
        
    Next

EndFunc

;~ Function for opening *.xls document and reading it to listview

Func open()
    
        $ListViewItems = ""
        
        $var = FileOpenDialog("Otvori dokument", @MyDocumentsDir , "excel (*.xls)", 2, "med.xls")
        
        $oExcel = _ExcelBookOpen($var,0)
        
        $SheetName = _ExcelSheetNameGet($oExcel)
        
        Local $CellNum[4]
        
        If @error Then
            
            Beep(700, 150)
            MsgBox(48,"","Dokument ne postoji ili je pokvaren!")
            
        Else
            
            _GUICtrlListViewDeleteAllItems ( $ListView1 )
            
;~      For $j = 0 To 3
            
            $CellNum = _ExcelSheetUsedRangeGet($oExcel,$SheetName)
            
;~      Next
                
        For $n = 3 To $CellNum[3]
            
                For $i = 0 To 18
                
                    $ListViewItems &= _ExcelReadCell($oExcel, $n, $i+1) & "|"
                
                Next

            _GUICtrlListViewInsertItem($ListView1, -1, $ListViewItems & "|")
                
            $ListViewItems = ""

        Next
            
        EndIf
        

    
EndFunc

;~ Function for saving to *.xls

Func save()
    
        $var = FileSaveDialog("Spremi", @MyDocumentsDir , "excel (*.xls)", 2, "med.xls")
            
        If @error Then
            
            Beep(700, 150)
            MsgBox(48,"","Greka kod spremanja!")
            
        Else
            
            MsgBox(64,"","Spremljeno")
            
            $oExcel = _ExcelBookNew(0)
                
            _ExcelWriteCell($oExcel, "'================================================================================================

=============================================================", 2, 1)

            $SaveListView = ""
        
            $ItemCount = _GUICtrlListViewGetItemCount($ListView1)
        
            For $n = 0 To $ItemCount

                $SaveListView = _GUICtrlListViewGetItemTextArray($ListView1,$n)
                _ExcelWriteArray($oExcel, $n+3, 1, $SaveListView, 0, 1)
            
            Next
        
            _ExcelBookSaveAs($oExcel, $var, "xls", 1, 0)
            
            _ExcelColWidthSet($oExcel, "A", 20)
    
            _ExcelBookClose($oExcel)    
            
        EndIf
    
    
EndFunc

;~ Print whole listview

Func print()

        $ItemCount = _GUICtrlListViewGetItemCount($ListView1)
        
        If $ItemCount = 0 Then
            
            Beep(700, 150)
            MsgBox(48,"Greka", "Nema podataka za ispis!")
        
        Else
        
        
        FileOpen("Ispis.txt",1)
        
        For $n = 0 To $ItemCount

            $PrintListView = _GUICtrlListViewGetItemTextArray($ListView1,$n)
            FileWrite("Ispis.txt",$PrintListView)
            
        Next

        $mess = MsgBox(1,"Ispis", "Ispisati?")
                
        If $mess = 2 Then
            
            Beep(700, 150)
            MsgBox(48,"Ispis","Greka kod ispisa!")
            
        Else
            
            MsgBox(64,"Ispis", "Uspjeno ispisano.")
            _FilePrint("Ispis.txt")
            
        EndIf
        
        FileClose("Ispis")
        FileDelete("Ispis.txt")
        
    EndIf
    
    
EndFunc

;~ New data

Func new()

    $NewListViewItem = ""
        
    For $n = 0 To 18
        $NewListViewItem &= GUICtrlRead($InputIDs[$n]) & " | "
        GUICtrlSetData($InputIDs[$n], "")
    Next


        $NewListViewItem = StringTrimRight($NewListViewItem, 3)
        _GUICtrlListViewInsertItem ($ListView1,0,$NewListViewItem & "|")

EndFunc

;~ exit

Func quit()
    
    $ItemCount = _GUICtrlListViewGetItemCount($ListView1)
        
        If $ItemCount > 0 Then
            
        $mess = MsgBox(3,"Izlaz", "Da li elite sačuvati podatke?")
            
        If $mess = 6 Then

            save()
                
        EndIf
                            
        If $mess = 7 Then

            Exit
                
        EndIf
            
    Else
        
        Exit
                        
    EndIf
                
EndFunc

;~ Add new data to existing one

Func add()
    
    $ListViewItems = ""
        
    $var = FileOpenDialog("Otvori dokument", @MyDocumentsDir , "excel (*.xls)", 2, "med.xls")
        
    $oExcel = _ExcelBookOpen($var,0)
        
    $SheetName = _ExcelSheetNameGet($oExcel)
        
    Local $CellNum[4]
        
    If @error Then
            
        Beep(700, 150)
        MsgBox(48,"","Dokument ne postoji ili je pokvaren!")
            
    Else
            
        $ItemCount = _GUICtrlListViewGetItemCount($ListView1)
    
        $CellNum = _ExcelSheetUsedRangeGet($oExcel,$SheetName)
            
        For $n = 3 To $CellNum[3]
            
            For $i = 0 To 18
                
                $ListViewItems &= _ExcelReadCell($oExcel, $n, $i+1) & "|"
                
            Next

        $ListViewItems = ""

        Next
            
        EndIf
#cs     
    $ItemCount = _GUICtrlListViewGetItemCount($ListView1)
        
    For $n = 0 To $ItemCount

        $Single = _GUICtrlListViewGetItemText($ListView1,$n)
        
        For $i = 0 To $ItemCount
            
            $Double = _GUICtrlListViewGetItemText($ListView1, $i+1)
            
            If $Single = $Double Then
                
                _GUICtrlListViewSetItemSelState($ListView1, $Double)
                _GUICtrlListViewDeleteItemsSelected($ListVIew1)
                
            EndIf
            
        Next
        
    Next
        
#ce
    
EndFunc

;~ refresh inputbox

Func refresh()

    For $n = 0 To 18
        GUICtrlSetData($InputIDs[$n], "")
    Next
    
    
EndFunc

Func Sqlite()
    
    Local $n
    Local $row
    Local $ListViewDB[18]
    
    _SQLite_Startup()
    _SQLite_Open("@ScriptDir\medorg.db")
    
    $row = _SQLite_LastInsertRowID()
    
    _SQLite_Exec(-1,"Create table pcaijent (br_prijave integer, ime text, prezime text, spol text, godina_r text, starost text, db_skup text, prebivaliste text, mjesto text, ispostava text, lokalizacija text, uzrok text, mikr text, kult text, rezistencija text, bolest text, terapija text, ishod text, lijecnik text);")

    
    For $n = 0 To $row
        
;~      For $i = 0 To 18
            
            $ListViewDB = _GUICtrlListViewGetItemTextArray($ListView1,$n)
            _SQLite_Exec(-1,"Insert into pacijent values ($ListViewDB[1], $ListViewDB[2], $ListViewDB[3], $ListViewDB[4], $ListViewDB[5], $ListViewDB[6], $ListViewDB[7], $ListViewDB[9], $ListViewDB[9], $ListViewDB[10], $ListViewDB[11], $ListViewDB[12], $ListViewDB[13], $ListViewDB[14], $ListViewDB[15], $ListViewDB[16], $ListViewDB[17], $ListViewDB[18]);")
            
;~      Next
        
    Next
    
    _SQLite_Close("medorg.db")
    _SQLite_Shutdown() 
    
EndFunc

Share this post


Link to post
Share on other sites

Explain how it doesn't work. What happens? What errors come up on SciTE console?

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Explain how it doesn't work. What happens? What errors come up on SciTE console?

:)

no errors shown everything is ok but it doesn't create db file and table with data in it

Share this post


Link to post
Share on other sites

no errors shown everything is ok but it doesn't create db file and table with data in it

Fix this:

; _SQLite_Open("@ScriptDir\medorg.db")
        _SQLite_Open(@ScriptDir & "\medorg.db")

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Fix this:

; _SQLite_Open("@ScriptDir\medorg.db")
        _SQLite_Open(@ScriptDir & "\medorg.db")

:)

ok now i have error:

! SQLite.au3 Error

--> Function: _SQLite_Exec

--> Query: Insert into pacijent values ($ListViewDB[0], $ListViewDB[1], $ListViewDB[2], $ListViewDB[3], $ListViewDB[4], $ListViewDB[5], $ListViewDB[6], $ListViewDB[7], $ListViewDB[8], $ListViewDB[9], $ListViewDB[10], $ListViewDB[11], $ListViewDB[12], $ListViewDB[13], $ListViewDB[14], $ListViewDB[15], $ListViewDB[16], $ListViewDB[17], $ListViewDB[18]);

Share this post


Link to post
Share on other sites

ok now i have error:

! SQLite.au3 Error

--> Function: _SQLite_Exec

--> Query: Insert into pacijent values ($ListViewDB[0], $ListViewDB[1], $ListViewDB[2], $ListViewDB[3], $ListViewDB[4], $ListViewDB[5], $ListViewDB[6], $ListViewDB[7], $ListViewDB[8], $ListViewDB[9], $ListViewDB[10], $ListViewDB[11], $ListViewDB[12], $ListViewDB[13], $ListViewDB[14], $ListViewDB[15], $ListViewDB[16], $ListViewDB[17], $ListViewDB[18]);

Insert into pacijent values (" & $ListViewDB[1] & ", " & $ListViewDB[2] & ",.......................

Share this post


Link to post
Share on other sites

ok now i have error:

! SQLite.au3 Error

--> Function: _SQLite_Exec

--> Query: Insert into pacijent values ($ListViewDB[0], $ListViewDB[1], $ListViewDB[2], $ListViewDB[3], $ListViewDB[4], $ListViewDB[5], $ListViewDB[6], $ListViewDB[7], $ListViewDB[8], $ListViewDB[9], $ListViewDB[10], $ListViewDB[11], $ListViewDB[12], $ListViewDB[13], $ListViewDB[14], $ListViewDB[15], $ListViewDB[16], $ListViewDB[17], $ListViewDB[18]);

That's not the entire error, is it?

Regardless, you are passing literal strings of the variables, not their contents. Try it this way:

$sQuery = "Insert into pacijent values (" ; Start of string
For $q = 1 To 18
    $sQuery &= "'" & $ListViewDB[$q] & "'," ; Add variable contents in single quotes, comma delimited
Next
$sQuery = StringTrimRight($sQuery, 1) ; Remove extra comma
$sQuery &= ");" ; End of string
ConsoleWrite("Debug: $sQuery = " & $sQuery & @LF) ; Debug display
_SQLite_Exec(-1, $sQuery)

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

That's not the entire error, is it?

Regardless, you are passing literal strings of the variables, not their contents. Try it this way:

$sQuery = "Insert into pacijent values (" ; Start of string
For $q = 1 To 18
    $sQuery &= "'" & $ListViewDB[$q] & "'," ; Add variable contents in single quotes, comma delimited
Next
$sQuery = StringTrimRight($sQuery, 1) ; Remove extra comma
$sQuery &= ");" ; End of string
ConsoleWrite("Debug: $sQuery = " & $sQuery & @LF) ; Debug display
_SQLite_Exec(-1, $sQuery)

:)

I have tried this

Func Sqlite()
    
    Local $n
    Local $row
    Local $ListViewDB[19]
    
    _SQLite_Startup()
    _SQLite_Open(@ScriptDir & "\medorg.db")
    
    $row = _GUICtrlListViewGetItemCount($ListView1)
    
    _SQLite_Exec(-1,"Create table pcaijent (br_prijave integer, ime text, prezime text, spol text, godina_r text, starost text, db_skup text, prebivaliste text, mjesto text, ispostava text, lokalizacija text, uzrok text, mikr text, kult text, rezistencija text, bolest text, terapija text, ishod text, lijecnik text);")

    
    For $n = 0 To $row
        
;~      For $i = 0 To 18
            
            $ListViewDB[$n] = _GUICtrlListViewGetItemTextArray($ListView1,$n)
            _SQLite_Exec(-1,"Insert into pacijent values ('"& $ListViewDB[0] &"', '"& $ListViewDB[1] &"','"& $ListViewDB[2] &"','"& $ListViewDB[3] &"','"& $ListViewDB[4] &"','"& $ListViewDB[5] &"', '"& $ListViewDB[6] &"', '"& $ListViewDB[7] &"','"& $ListViewDB[8] &"','"& $ListViewDB[9] &"','"& $ListViewDB[10] &"','"& $ListViewDB[11] &"','"& $ListViewDB[12] &"','"& $ListViewDB[13] &"','"& $ListViewDB[14] &"','"& $ListViewDB[15] &"','"& $ListViewDB[16] &"','"& $ListViewDB[17] &"','"&$ListViewDB[18]&"');")
            
;~      Next

    Next
    
    _SQLite_Close("medorg.db")
    _SQLite_Shutdown() 
    
EndFunc

It creates table but doesn't write in it

i get this error

#1 i run without db

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Documents and Settings\Marko\Desktop\koda_2007.06.09\Forms\medika\medorg_db.au3"

Debug: $sQuery = Insert into pacijent values ('','','','','','','','','','','','','','','','','','');

! SQLite.au3 Error

--> Function: _SQLite_Exec

--> Query: Insert into pacijent values ('','','','','','','','','','','','','','','','','','');

--> Error: no such table: pacijent

>Exit code: 0 Time: 13.713

#2 whit db created

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Documents and Settings\Marko\Desktop\koda_2007.06.09\Forms\medika\medorg_db.au3"

! SQLite.au3 Error

--> Function: _SQLite_Exec

--> Query: Create table pcaijent (br_prijave integer, ime text, prezime text, spol text, godina_r text, starost text, db_skup text, prebivaliste text, mjesto text, ispostava text, lokalizacija text, uzrok text, mikr text, kult text, rezistencija text, bolest text, terapija text, ishod text, lijecnik text);

--> Error: table pcaijent already exists

! SQLite.au3 Error

--> Function: _SQLite_Exec

--> Query: Insert into pacijent values ('', '','','','','', '', '','','','','','','','','','','','');

--> Error: no such table: pacijent

>Exit code: 0 Time: 3.893

Share this post


Link to post
Share on other sites

I have tried what you posted me but i get same error

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Documents and Settings\Marko\Desktop\koda_2007.06.09\Forms\medika\medorg_db.au3"

! SQLite.au3 Error

--> Function: _SQLite_Exec

--> Query: Create table pcaijent (br_prijave integer, ime text, prezime text, spol text, godina_r text, starost text, db_skup text, prebivaliste text, mjesto text, ispostava text, lokalizacija text, uzrok text, mikr text, kult text, rezistencija text, bolest text, terapija text, ishod text, lijecnik text);

--> Error: table pcaijent already exists

Debug: $sQuery = Insert into pacijent values ('','','','','','','','','','','','','','','','','','','');

! SQLite.au3 Error

--> Function: _SQLite_Exec

--> Query: Insert into pacijent values ('','','','','','','','','','','','','','','','','','','');

--> Error: no such table: pacijent

>Exit code: 0 Time: 4.665

Share this post


Link to post
Share on other sites

Hi,

1. You need to check if table already exists before trying to write it again; or delete it if you want to start again.

2. spelling; table is "pcaijent" , but trying to write to "pacijent"

Best, randall

[PS

$ListViewDB = _GUICtrlListViewGetItemTextArray($ListView1, 0)
;~      $ListViewDB[$n] = _GUICtrlListViewGetItemTextArray($ListView1, $n)
    _ArrayDelete($ListViewDB, 0)
    _ArrayDisplay($ListViewDB)
]

Share this post


Link to post
Share on other sites

Hi,

1. You need to check if table already exists before trying to write it again; or delete it if you want to start again.

2. spelling; table is "pcaijent" , but trying to write to "pacijent"

Best, randall

[PS

$ListViewDB = _GUICtrlListViewGetItemTextArray($ListView1, 0)
;~      $ListViewDB[$n] = _GUICtrlListViewGetItemTextArray($ListView1, $n)
    _ArrayDelete($ListViewDB, 0)
    _ArrayDisplay($ListViewDB)
]
I still have a problem about checking if the table exist. Can't figure it out. Is there some example about this i couldn't find it on the forum

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

I still have a problem about checking if the table exist. Can't figure it out. Is there some example about this i couldn't find it on the forum

i,

Look at the syntax in these old non-working commands, work from there? [look at createtable, droptable, etc];

Func _SQL_CreateTable(ByRef $sDb1, $sDbTable1, $sHeader = "", $i_Execute = 1)
    Local $sOutput, $sInput, $s_ListHeader    ;$sHeader,
    ;$sHeader=StringReplace($sHeader,",","','")
    $sInput = "DROP TABLE IF EXISTS " & $sDbTable1 & " ;"
    $sInput &= "CREATE TABLE  " & $sDbTable1 & "  (" & $sHeader & ");" & @CRLF
    If $i_Execute Then
        _SQLiteExe($sDb1, $sInput, $sOutput)
        ;return $sOutput
        Return $sOutput
    Else
        Return $sInput
    EndIf
EndFunc   ;==>_SQL_CreateTable
Best, Randall Edited by randallc

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