StungStang Posted February 19, 2011 Share Posted February 19, 2011 (edited) Hi to all, i want create a database for storing my radio station. Now i use an .ini file to do that, but it's too much slow now that have added too much radio. I want create a database...i see the _SQL function on autoit help.I want create a database like that :; Radio name |Streaming Link | WebSite Link | Genre | ; ----------------------------------------------------|-------------| ; MyRadio |mms://myradio.asx |www.myradio.com |Rock | ; Myprefradio |mms://mypref.asx |www.mypref.net |Pop | ; MyExample |http.//link.mp3 |www.myexample.com |Dance |How i can create .db the file with autoit? I dont see a funcion like that in the help file.EDIT :I think I've solved the problem, i've created the database file with this command :_SQLite_Open(@ScriptDir & "\Database.db",$SQLITE_OPEN_READWRITE + $SQLITE_OPEN_CREATE ,$SQLITE_ENCODING_UTF8)Next Step i've created a table :_SQLite_Exec (-1, "CREATE TABLE Radio (Name,Streaming,WebSite,Genre);")And add the values :_SQLite_Exec (-1, "INSERT INTO Radio VALUES ('MyRadio','mms://myradio.asx','www.myradio.com','Rock');")Now i want to display the contenet of this database on a listview...how i can that?Hi!THanks! Edited February 19, 2011 by StungStang Link to comment Share on other sites More sharing options...
saywell Posted February 19, 2011 Share Posted February 19, 2011 (edited) Hi, Here's a bit of code I use in my app to do similar: expandcollapse popup$GPquery = InputBox("Look up GP details?", "Enter GP Surname") Select Case $GPquery = "" ; cancel pressed Sleep(100) Case $GPquery <> "" Local $aResult, $iRows, $iColumns _SQLite_Startup() Local $aResult, $iRows, $iColumns _SQLite_Open(@ScriptDir & "\gp.sqlite") If @error Then MsgBox(16, "SQLite Error", "Can't Load Database!") EndIf $sSQL = "SELECT GPcode, GPname, GPsurgery, GPadd1, GPadd2, GPadd3, GPadd4 FROM GPs WHERE GPname LIKE '% " & $GPquery & "%'" Local $get = _SQLite_GetTable2d(-1, $sSQL, $aResult, $iRows, $iColumns) ;MsgBox (4096, "sSQL", $sSQL & @CR & @CR & @error) If $iRows <> 0 Then ClipPut ("") Local $GPline = _ResultsDisplay ($aResult, "Please select the Correct GP and press button below to proceed") ; displays matching GPs in modified arrayDisplay box, allowing single selection only ; returns 0 if closed with 'x' ; returns string with data items from selected line separated by | otherwise ;_ArrayDisplay($aResult, "Please select the Correct GP and press COPY SELECTED below then close this box to proceed") ;Local $GPline = ClipGet() ;MsgBox (1, "GPline", $GPline) Select Case $GPline <> "" Local $aGPcode = StringSplit($GPline, "|", 2) ;MsgBox (1, "Count", $aGPcode[0]) $sGPcode = $aGPcode[1] Case $GPline = 0 $sGPcode = "" EndSelect EndIf _SQLite_Close() _SQLite_Shutdown() _ResultsDisplay is a n modification of _arraydisplay that I've customised - the old line that used the native _ArrayDisplay command is in the comments. William Edited February 19, 2011 by saywell Link to comment Share on other sites More sharing options...
StungStang Posted February 19, 2011 Author Share Posted February 19, 2011 Thanks, your code was helpfully, but now i've another question...how i can display the resoult on listview? Hi! Link to comment Share on other sites More sharing options...
saywell Posted February 19, 2011 Share Posted February 19, 2011 That was why I modified the _arraydisplay function... .William Link to comment Share on other sites More sharing options...
ChrisL Posted February 19, 2011 Share Posted February 19, 2011 expandcollapse popup#include <SQLITE.au3> #include <SQLite.dll.au3> #include <GuiListView.au3> #include <GUIConstantsEx.au3> _SQLite_Startup() _SQLite_Open(@ScriptDir & "\Database.db",$SQLITE_OPEN_READWRITE + $SQLITE_OPEN_CREATE ,$SQLITE_ENCODING_UTF8) _SQLite_Exec (-1, "CREATE TABLE Radio (Name,Streaming,WebSite,Genre);") _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('MyRadio','mms://myradio.asx','www.myradio.com','Rock');") _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('MyRadio2','mms://myradio2.asx','www.myradio.com','Noise');") _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('MyRadio3','mms://myradio3.asx','www.myradio.com','Pop');") $hGui = GUICreate("Radio Stations",800,400, Default, Default) $hListView = GUICtrlCreateListView("Name|Streaming|WebSite|Genre",20, 20, 740, 300) _GUICtrlListView_SetColumnWidth($hListView, 0, 80) _GUICtrlListView_SetColumnWidth($hListView, 1, 230) _GUICtrlListView_SetColumnWidth($hListView, 2, 230) _GUICtrlListView_SetColumnWidth($hListView, 3, 80) GUISetState() Local $aStations, $iRows, $iColumns _SQLite_GetTable2d(-1,"Select Name,Streaming,WebSite,Genre From Radio",$aStations,$iRows,$iColumns) For $i = 1 to $iRows $Line = $aStations[$i][0] & "|" & $aStations[$i][1] & "|" & $aStations[$i][2] & "|" & $aStations[$i][3] ConsoleWrite($Line) GuiCtrlCreateListViewItem($Line,$hListView) Next While 1 $msg = GUIGetMsg() If $msg = $GUI_EVENT_CLOSE Then ExitLoop WEnd GUIDelete() _SQLite_Close() _SQLite_Shutdown() [u]Scripts[/u]Minimize gui to systray _ Fail safe source recoveryMsgbox UDF _ _procwatch() Stop your app from being closedLicensed/Trial software system _ Buffering Hotkeys_SQL.au3 ADODB.Connection _ Search 2d Arrays_SplashTextWithGraphicOn() _ Adjust Screen GammaTransparent Controls _ Eventlogs without the crap_GuiCtrlCreateFlash() _ Simple Interscript communication[u]Websites[/u]Curious Campers VW Hightops Lambert Plant Hire Link to comment Share on other sites More sharing options...
StungStang Posted February 19, 2011 Author Share Posted February 19, 2011 (edited) Thanks now i've this code : expandcollapse popup#include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <ListViewConstants.au3> #include <WindowsConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <GuiListView.au3> #include <ComboConstants.au3> Local $aResult, $iRows, $iColumns, $iRval, $hQuery, $aRow, $sMsg $Form1 = GUICreate("Try", 627, 464, 192, 124) $Input1 = GUICtrlCreateInput("Input1", 8, 8, 121, 21) $Search = GUICtrlCreateButton("Search", 136, 8, 75, 25) $Create = GUICtrlCreateButton("Create", 8, 432, 75, 25) $Add = GUICtrlCreateButton("Add", 88, 432, 75, 25) $Delete = GUICtrlCreateButton("Delete", 168, 432, 75, 25) $ListView = GUICtrlCreateListView("Name|Streaming|WebSite|Genre|Continent|State", 0, 40, 626, 382) $State = GUICtrlCreateCombo("England", 224, 8, 145, 25, BitOR($CBS_DROPDOWNLIST, $CBS_AUTOHSCROLL, $WS_HSCROLL, $WS_VSCROLL)) GUICtrlSetData (-1,"California") GUISetState(@SW_SHOW) _SQLite_Startup() If @error > 0 Then MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit EndIf _SQLite_Open(@ScriptDir & "\Database.db") If @error > 0 Then MsgBox(16, "Error", "Database not exist.") Exit Else Local $aStations, $iRows, $iColumns _SQLite_GetTable2d(-1,"Select Name,Streaming,Site,Genre,Continent,State From Radio",$aStations,$iRows,$iColumns) For $i = 1 to $iRows $Line = $aStations[$i][0] & "|" & $aStations[$i][1] & "|" & $aStations[$i][2] & "|" & $aStations[$i][3] & "|" & $aStations[$i][4] & "|" & $aStations[$i][5] ConsoleWrite($Line) GuiCtrlCreateListViewItem($Line,$ListView) Next EndIf While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Create $DATABASE = _SQLite_Open(@ScriptDir & "\Database.db",$SQLITE_OPEN_READWRITE + $SQLITE_OPEN_CREATE ,$SQLITE_ENCODING_UTF8) _SQLite_Exec (-1, "CREATE TABLE Radio (Name,Streaming,Site,Genre,Continent,State);") Case $Add _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 1','http://bbc.co.uk/radio/listen/live/r1.ram','http://www.bbc.co.uk/radio1/','Top 40/Dance','Europe','England');") _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 2','http://bbc.co.uk/radio/listen/live/r2.ram','http://www.bbc.co.uk/radio1/','Adult Contemporary','Europe','England');") _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 3','http://bbc.co.uk/radio/listen/live/r3.ram','http://www.bbc.co.uk/radio1/','Classical','Europe','England');") _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('Absolute Radio','http://aacplus-vr-32.smgradio.com:80/listen.pls','http://www.absoluteradio.co.uk/','Hot Adult Contemporary','Europe','England');") _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('KLOS','http://citadelcc-klos-fm.wm.llnwd.net/citadelcc_KLOS_FM','http://www.955klos.com/','Classic Rock','America','California');") Case $Delete $Element = _GUICtrlListView_GetItemTextArray($ListView,0) For $x = 1 To $Element[0] $Info = $Element[$x] Next _SQLite_Exec (-1, "DELETE FROM Radio WHERE Name = '" & $Info[1] & "'") Case $State $Selected = GUICtrlRead ($State) _Display_ListView($Selected) Case $Search ;I want to show the searched radio... EndSwitch WEnd Func _Display_ListView($sState) ;How i can show the radio station for selected state? EndFunc Now i want to show the result of search radio, and want to spect the radio station for selected state. How i can do that?, with command with sql?.And delete function dont work why? Thanks! Edited February 19, 2011 by StungStang Link to comment Share on other sites More sharing options...
jchd Posted February 19, 2011 Share Posted February 19, 2011 Beware that you open the DB more than once, but still refer to it with -1 instead of a proper handle. Doing so you open several connection to the same DB as if you were using several processes in parallel. I'd recommend using a proper handle instead of -1 and removing the open in the GUI loop. Also, even if SQLite will silently create a hidden integer primary key for you, I'd advise declaring one explicitely. Doing so doesn't increase the size of your DB and will save your life should your project eveolve into anything more complex. Mixing GUI* and _GUI functions is not recommended in general. For your delete, are your sure that $Info[1] is the name column? 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 hereRegExp tutorial: enough to get startedPCRE 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 More sharing options...
StungStang Posted February 20, 2011 Author Share Posted February 20, 2011 I dont understand that : Also, even if SQLite will silently create a hidden integer primary key for you, I'd advise declaring one explicitely. Doing so doesn't increase the size of your DB and will save your life should your project eveolve into anything more complex. What i've to declaring explicitely? I've Fixed the State Display : expandcollapse popup#include <ButtonConstants.au3> #include <GUIConstantsEx.au3> #include <ListViewConstants.au3> #include <WindowsConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <GuiListView.au3> #include <ComboConstants.au3> Local $aResult, $iRows, $iColumns, $iRval, $hQuery, $aRow, $sMsg $Form1 = GUICreate("Try", 627, 464, 192, 124) $Input1 = GUICtrlCreateInput("Input1", 8, 8, 121, 21) $Search = GUICtrlCreateButton("Search", 136, 8, 75, 25) $Create = GUICtrlCreateButton("Create", 8, 432, 75, 25) $Add = GUICtrlCreateButton("Add", 88, 432, 75, 25) $Delete = GUICtrlCreateButton("Delete", 168, 432, 75, 25) $ListView = GUICtrlCreateListView("Name|Streaming|WebSite|Genre|Continent|State", 0, 40, 626, 382) Local $hListView = GUICtrlGetHandle($ListView) $State = GUICtrlCreateCombo("England", 224, 8, 145, 25, BitOR($CBS_DROPDOWNLIST, $CBS_AUTOHSCROLL, $WS_HSCROLL, $WS_VSCROLL)) GUICtrlSetData (-1,"California") GUISetState(@SW_SHOW) _SQLite_Startup() _SQLite_Open(@ScriptDir & "\Database.db") If @error > 0 Then MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit EndIf While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Create $DATABASE = _SQLite_Open(@ScriptDir & "\Database.db",$SQLITE_OPEN_READWRITE + $SQLITE_OPEN_CREATE ,$SQLITE_ENCODING_UTF8) _SQLite_Exec (-1, "CREATE TABLE Radio (Name,Streaming,Site,Genre,Continent,State);") _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 1','http://bbc.co.uk/radio/listen/live/r1.ram','http://www.bbc.co.uk/radio1/','Top 40/Dance','Europe','England');") _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 2','http://bbc.co.uk/radio/listen/live/r2.ram','http://www.bbc.co.uk/radio1/','Adult Contemporary','Europe','England');") _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('BBC Radio 3','http://bbc.co.uk/radio/listen/live/r3.ram','http://www.bbc.co.uk/radio1/','Classical','Europe','England');") _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('Absolute Radio','http://aacplus-vr-32.smgradio.com:80/listen.pls','http://www.absoluteradio.co.uk/','Hot Adult Contemporary','Europe','England');") _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('KLOS','http://citadelcc-klos-fm.wm.llnwd.net/citadelcc_KLOS_FM','http://www.955klos.com/','Classic Rock','America','California');") Case $Add $sName = InputBox ("Name","") If $sName <> "" Then $sStream = InputBox ("Stream","") ElseIf $sStream <> "" Then $sWeb = InputBox ("Web Site","") ElseIf $sWeb <> "" Then $sGenre = InputBox ("Genre","") ElseIf $sGenre <> "" Then $sContinent = InputBox ("Continent","") ElseIf $sContinent <> "" Then $sState = InputBox ("State","") ElseIf $sState <> "" Then _SQLite_Exec (-1, "INSERT INTO Radio VALUES ('" & $sName & "','" & $sWeb & "','" & $sGenre & "','" & $sContinent & "','" & $sState & "'" & ');")') EndIf Case $Delete $Element = _GUICtrlListView_GetItemTextArray($ListView,0) For $x = 1 To $Element[0] $Info = $Element[$x] Next _SQLite_Exec (-1, "DELETE FROM Radio WHERE Name = '" & $Info[1] & "'") Case $State $Selected = GUICtrlRead ($State) _Display_ListView($Selected) EndSwitch WEnd Func _Display_ListView($Value) ;Ok FIXED! _GUICtrlListView_BeginUpdate($ListView) _GUICtrlListView_DeleteAllItems($ListView) Local $aStations, $iRows, $iColumns _SQLite_GetTable2d(-1, "Select Name,Streaming,Site,Genre,Continent,State FROM Radio WHERE State = '" & $Value &"'",$aStations,$iRows,$iColumns) For $i = 1 to $iRows $Line = $aStations[$i][0] & "|" & $aStations[$i][1] & "|" & $aStations[$i][2] & "|" & $aStations[$i][3] & "|" & $aStations[$i][4] & "|" & $aStations[$i][5] ConsoleWrite($Line) GuiCtrlCreateListViewItem($Line,$ListView) Next _GUICtrlListView_EndUpdate($ListView) EndFunc But dont work the DELETE function...and i want to know what command i have to do for create my search function? Hi! Link to comment Share on other sites More sharing options...
kylomas Posted February 20, 2011 Share Posted February 20, 2011 StungStang, With regard to the "delete" routine: - $Element = _GUICtrlListView_GetItemTextArray($ListView,0) returns an array of all columns for row = 0 to $element - you are then setting $info to the value in the LAST column in that row in a for...to loop - in the "delete" SQL cmd you are using $info as an array, it is a scalar variable (non-array) Change $info[1] to $info...also consider using "-1" instead of "0" in _GUICtrlListView_GetItemTextArray($ListView,0) to operate on the selected row (see doc). With regard to the search question: - construct a standard SQL SELECT bsaed on some column value(s) and execute it. kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now