davee Posted October 24, 2013 Posted October 24, 2013 I have a forms, which is a feature where you can choose image, this image I want to insert a SQLite-based database (database.db) the path and other data, the image will choose FileOpenDialog function, the insert works as long as I do not use this feature, but let's say I enter the path manually. As I use insert on the selection process will fail utterly pointless.The main point I took from the program:GUI part: $Label5 = GUICtrlCreateLabel("Select Image", 13, 278, 98, 22) $Button2 = GUICtrlCreateButton("Browse...", 360, 272, 89, 25) GUICtrlSetOnEvent(-1, "OpenImage") ;OpenImage $image = GUICtrlCreateInput("", 112, 272, 241, 26) Background: OpenDialog Function: Func OpenImage() $var = FileOpenDialog("Select Image", "","Images (*.jpg;*.bmp;*.jpeg;*.gif)", 1) If $var Then GUICtrlSetData($image, $var) EndIf EndFunc SQL Insert: Local $listQuery, $listRow, $listMsg _SQLite_Startup() _SQLite_Open('database.db') _SQLite_Exec(-1, "CREATE TABLE IF NOT EXISTS cars (id INTEGER PRIMARY KEY, type TEXT, vintage TEXT, enginecode TEXT, cubic TEXT, performance TEXT, license TEXT, image TEXT);", $listQuery) ; Create Table _SQLite_Exec(-1, "INSERT INTO cars (type,vintage,enginecode,cubic,performance,license,image) VALUES ('" & GUICtrlRead($type) & "','" & GUICtrlRead($vintage) & "','" & GUICtrlRead($enginecode) & "','" & GUICtrlRead($cubic) & "','" & GUICtrlRead($performance) & "','" & GUICtrlRead($license) & "','" & GUICtrlRead($image) & "');", $listQuery) ; Insert Row MsgBox(0, "SQLite Error", "Error Code: " & _SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg()) _SQLite_Close() _SQLite_Shutdown() EndFunc When I use the function in the error message is as follows: --> Error: table cars has no column named license If you hand write it runs the process without any defect What could be the problem?The FileOpenDialog is incompatible with SQLite?Thank you in advance for your help!Sorry for my bad english.
kylomas Posted October 24, 2013 Posted October 24, 2013 davee, Can you post this as runnable code? 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
davee Posted October 24, 2013 Author Posted October 24, 2013 (edited) davee, Can you post this as runnable code? kylomas expandcollapse popup#include <ButtonConstants.au3> #include <DateTimeConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <GUIListBox.au3> #include <GuiListView.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> ;SQLite Include #include <SQLite.au3> #include <SQLite.dll.au3> #include <file.au3> Opt("GUIOnEventMode", 1) Global $type,$vintage,$enginecode,$cubic,$performance,$license,$image $AddForm = GUICreate("Autó Hozzáadás", 468, 364, -1, -1) GUISetOnEvent($GUI_EVENT_CLOSE, "_GoMainScreen") ;GoMainScreen GUISetState(@SW_SHOW) GUISetFont(11, 400, 0, "Myriad Hebrew") $Group1 = GUICtrlCreateGroup("Autó adatai", 8, 8, 449, 225) $Label1 = GUICtrlCreateLabel("Típus", 90, 38, 38, 22) $type = GUICtrlCreateInput("", 144, 32, 289, 26) $Label2 = GUICtrlCreateLabel("Évjárat", 83, 68, 46, 22) $vintage = GUICtrlCreateInput("", 144, 64, 289, 26) $Label3 = GUICtrlCreateLabel("Motorkód", 64, 103, 65, 22) $enginecode = GUICtrlCreateInput("", 144, 96, 289, 26) $cubic = GUICtrlCreateInput("", 144, 128, 289, 26) $performance = GUICtrlCreateInput("", 144, 160, 289, 26) $license = GUICtrlCreateInput("", 144, 192, 289, 26) $Label4 = GUICtrlCreateLabel("Hengerürtartalom", 16, 133, 118, 22) $Label6 = GUICtrlCreateLabel("Teljesítmény", 48, 163, 86, 22) $Label7 = GUICtrlCreateLabel("Rendszám", 64, 198, 68, 22) GUICtrlCreateGroup("", -99, -99, 1, 1) $Group2 = GUICtrlCreateGroup("Kép", 8, 248, 449, 65) $Label5 = GUICtrlCreateLabel("Kép kiválasztás", 13, 278, 98, 22) $Button2 = GUICtrlCreateButton("Tallózás...", 360, 272, 89, 25) GUICtrlSetOnEvent(-1, "OpenImage") ;OpenImage $image = GUICtrlCreateInput("", 112, 272, 241, 26) GUICtrlCreateGroup("", -99, -99, 1, 1) $Button3 = GUICtrlCreateButton("Mégse", 328, 320, 121, 33) GUICtrlSetOnEvent(-1, "_GoMainScreen") ;GoMainScreen $Button4 = GUICtrlCreateButton("Hozzáadás", 192, 320, 121, 33) GUICtrlSetOnEvent(-1, "InsertCar") ;Insert Car #EndRegion ### END Koda GUI section ### While 1 Sleep(100) WEnd Func OpenImage() $var = FileOpenDialog("Válaszd ki az album mappáját", "","Képek (*.jpg;*.bmp;*.jpeg;*.gif)", 1) If $var Then GUICtrlSetData($image, $var) EndIf EndFunc Func InsertCar() Local $listQuery, $listRow, $listMsg _SQLite_Startup() _SQLite_Open('database.db') _SQLite_Exec(-1, "CREATE TABLE IF NOT EXISTS cars (id INTEGER PRIMARY KEY, type TEXT, vintage TEXT, enginecode TEXT, cubic TEXT, performance TEXT, license TEXT, image TEXT);", $listQuery) ; Create Table _SQLite_Exec(-1, "INSERT INTO cars (type,vintage,enginecode,cubic,performance,license,image) VALUES ('" & GUICtrlRead($type) & "','" & GUICtrlRead($vintage) & "','" & GUICtrlRead($enginecode) & "','" & GUICtrlRead($cubic) & "','" & GUICtrlRead($performance) & "','" & GUICtrlRead($license) & "','" & GUICtrlRead($image) & "');", $listQuery) ; Insert Row MsgBox(0, "SQLite Error", "Error Code: " & _SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg()) _SQLite_Close() _SQLite_Shutdown() ;_UpdateListView() ;_GoMainScreen() EndFunc I hope you think so! (Originally prepare in Hungarian!) Edited October 24, 2013 by davee
kylomas Posted October 24, 2013 Posted October 24, 2013 davee, Thank you but function _GoMainScreen is missing... 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
davee Posted October 24, 2013 Author Posted October 24, 2013 davee, Thank you but function _GoMainScreen is missing... kylomas Really, I'm sorry. Otherwise, there is only one Gui hide.
kylomas Posted October 24, 2013 Posted October 24, 2013 Really, I'm sorry. Otherwise, there is only one Gui hide. Huh???!? Whenever you are adding strings to an SQLite table you should escape the strings using _SQLite_FastEscape. See the Help file. I don't know if this is your problem and until you post runnable code I can only guess. 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
davee Posted October 24, 2013 Author Posted October 24, 2013 Huh???!? Whenever you are adding strings to an SQLite table you should escape the strings using _SQLite_FastEscape. See the Help file. I don't know if this is your problem and until you post runnable code I can only guess. kylomas They have missed that piece of code only served to make the pop-up window to hide what I sent you closed it. The base image is not of the form. The error of no importance but the content of many features: GuiSetState(@SW_HIDE) The idea is to try tomorrow, thanks
jchd Posted October 24, 2013 Posted October 24, 2013 davee, If SQLite is saying "--> Error: table cars has no column named license" you have to give it some credit and consider that your table CARS might indeed miss the license column. My (wild) guess is that you've created the table without the license column. Afterwards, statements like the one you use with IF NOT EXISTS clause won't add new column by mere magic. Either recreate your database from scratch or use ALTER table cars add column license... Refer to SQLite docs for more. 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)
kylomas Posted October 24, 2013 Posted October 24, 2013 davee, There were several problems with your code. See the comments in the code. This works for me using the file select dialog. I did not try to insert any other data. expandcollapse popup#include <ButtonConstants.au3> #include <DateTimeConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <GUIListBox.au3> #include <GuiListView.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> ;SQLite Include #include <SQLite.au3> #include <SQLite.dll.au3> #include <file.au3> Opt("GUIOnEventMode", 1) Global $type, $vintage, $enginecode, $cubic, $performance, $license, $image ; Start sqlite and open the DB once. Add some error checking. ; There is no need to start/stop sqlite with every access to the DB. _SQLite_Startup() If @error Then ConsoleWrite('error loading sqlite.dll' & @LF) Exit EndIf Local $hDB = _SQLite_Open('database.db3') ; changed extention to sqlite ".db3" If @error Then ConsoleWrite('Unable to open DB' & @LF) _GoMainScreen() EndIf OnAutoItExitRegister("_GoMainScreen") Local $sql = 'CREATE TABLE IF NOT EXISTS cars ' & _ '(id INTEGER PRIMARY KEY, type TEXT, vintage TEXT, enginecode TEXT, cubic TEXT, performance TEXT, license TEXT, image TEXT);' If _SQLite_Exec(-1, $sql) <> $sqlite_ok Then MsgBox(0, '*** Create Table Error ***', _SQLite_ErrMsg()) Exit EndIf ; The third parm of _sqlite_exec is for a callback function and is specified as a string. What are you trying to do? ; _SQLite_Exec(-1, "CREATE TABLE IF NOT EXISTS cars (id INTEGER PRIMARY KEY, type TEXT, vintage TEXT, enginecode TEXT, cubic TEXT, performance TEXT, license TEXT, image TEXT);", $listQuery) ; Create Table $AddForm = GUICreate("Autó Hozzáadás", 468, 364, -1, -1) GUISetOnEvent($GUI_EVENT_CLOSE, "_GoMainScreen") ;GoMainScreen GUISetState(@SW_SHOW) GUISetFont(11, 400, 0, "Myriad Hebrew") $Group1 = GUICtrlCreateGroup("Autó adatai", 8, 8, 449, 225) $Label1 = GUICtrlCreateLabel("Típus", 90, 38, 38, 22) $type = GUICtrlCreateInput("", 144, 32, 289, 26) $Label2 = GUICtrlCreateLabel("Évjárat", 83, 68, 46, 22) $vintage = GUICtrlCreateInput("", 144, 64, 289, 26) $Label3 = GUICtrlCreateLabel("Motorkód", 64, 103, 65, 22) $enginecode = GUICtrlCreateInput("", 144, 96, 289, 26) $cubic = GUICtrlCreateInput("", 144, 128, 289, 26) $performance = GUICtrlCreateInput("", 144, 160, 289, 26) $license = GUICtrlCreateInput("", 144, 192, 289, 26) $Label4 = GUICtrlCreateLabel("Hengerürtartalom", 16, 133, 118, 22) $Label6 = GUICtrlCreateLabel("Teljesítmény", 48, 163, 86, 22) $Label7 = GUICtrlCreateLabel("Rendszám", 64, 198, 68, 22) GUICtrlCreateGroup("", -99, -99, 1, 1) $Group2 = GUICtrlCreateGroup("Kép", 8, 248, 449, 65) $Label5 = GUICtrlCreateLabel("Kép kiválasztás", 13, 278, 98, 22) $Button2 = GUICtrlCreateButton("Tallózás...", 360, 272, 89, 25) GUICtrlSetOnEvent(-1, "OpenImage") ;OpenImage $image = GUICtrlCreateInput("", 112, 272, 241, 26) GUICtrlCreateGroup("", -99, -99, 1, 1) $Button3 = GUICtrlCreateButton("Mégse", 328, 320, 121, 33) GUICtrlSetOnEvent(-1, "_GoMainScreen") ;GoMainScreen $Button4 = GUICtrlCreateButton("Hozzáadás", 192, 320, 121, 33) GUICtrlSetOnEvent(-1, "InsertCar") ;Insert Car #endregion ### END Koda GUI section ### While 1 Sleep(100) WEnd Func OpenImage() $var = FileOpenDialog("Válaszd ki az album mappáját", "", "Képek (*.jpg;*.bmp;*.jpeg;*.gif)", 1) If $var Then GUICtrlSetData($image, $var) EndIf EndFunc ;==>OpenImage Func InsertCar() local $ret ; again, you had a 3RD parm specified. Also, use _sqlite_fastescape() for string values. ; Always check for an error condition. And, whatever sqlite errors you are getting should be mirrored in the console area. $ret = _SQLite_Exec(-1, 'INSERT INTO cars (type,vintage,enginecode,cubic,performance,license,image) VALUES (' & _ _sqlite_fastescape(GUICtrlRead($type)) & ',' & _ _sqlite_fastescape(GUICtrlRead($vintage)) & ',' & _ _sqlite_fastescape(GUICtrlRead($enginecode)) & ',' & _ _sqlite_fastescape(GUICtrlRead($cubic)) & ',' & _ _sqlite_fastescape(GUICtrlRead($performance)) & ',' & _ _sqlite_fastescape(GUICtrlRead($license)) & ',' & _ _sqlite_fastescape(GUICtrlRead($image)) & ');') if $ret <> $sqlite_ok then MsgBox(0, "SQLite Error", "Error Code: " & _SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg()) _GoMainScreen() endif EndFunc ;==>InsertCar Func _GoMainScreen() _SQLite_Close() _SQLite_Shutdown() Exit EndFunc ;==>_GoMainScreen 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
Solution MHz Posted October 25, 2013 Solution Posted October 25, 2013 What could be the problem?The FileOpenDialog is incompatible with SQLite? Using relative paths can a problem if the script is not handling the relative paths with any change of working directory. I am not changing too much to the code other then getting it working. The single quotes around text parameters to SQLite seem fine. The major concern was the working directory change when using FileOpenDialog. FileOpenDialog changes working directory to the path of the selected file. The database.db being used in _SQLite_Open() may not be at the same path with successful use of FileOpenDialog. So when the working directory changed, then your SQL Insert statements failed as database.db did not exist in the current working directory. This is what I tested with. expandcollapse popup; Changes: ; Added missing _GoMainScreen() that was added to test with ; Updated OpenImage() to handle workingdir change to fix major issue ; Removed 3rd parameter used for _SQLite_Exec() as unneeded and it was doing nothing as being "" #include <ButtonConstants.au3> #include <DateTimeConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <GUIListBox.au3> #include <GuiListView.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> ;SQLite Include #include <SQLite.au3> #include <SQLite.dll.au3> #include <file.au3> Opt("GUIOnEventMode", 1) Global $type,$vintage,$enginecode,$cubic,$performance,$license,$image $AddForm = GUICreate("Autó Hozzáadás", 468, 364, -1, -1) GUISetOnEvent($GUI_EVENT_CLOSE, "_GoMainScreen") ;GoMainScreen GUISetState(@SW_SHOW) GUISetFont(11, 400, 0, "Myriad Hebrew") $Group1 = GUICtrlCreateGroup("Autó adatai", 8, 8, 449, 225) $Label1 = GUICtrlCreateLabel("Típus", 90, 38, 38, 22) $type = GUICtrlCreateInput("", 144, 32, 289, 26) $Label2 = GUICtrlCreateLabel("Évjárat", 83, 68, 46, 22) $vintage = GUICtrlCreateInput("", 144, 64, 289, 26) $Label3 = GUICtrlCreateLabel("Motorkód", 64, 103, 65, 22) $enginecode = GUICtrlCreateInput("", 144, 96, 289, 26) $cubic = GUICtrlCreateInput("", 144, 128, 289, 26) $performance = GUICtrlCreateInput("", 144, 160, 289, 26) $license = GUICtrlCreateInput("", 144, 192, 289, 26) $Label4 = GUICtrlCreateLabel("Hengerürtartalom", 16, 133, 118, 22) $Label6 = GUICtrlCreateLabel("Teljesítmény", 48, 163, 86, 22) $Label7 = GUICtrlCreateLabel("Rendszám", 64, 198, 68, 22) GUICtrlCreateGroup("", -99, -99, 1, 1) $Group2 = GUICtrlCreateGroup("Kép", 8, 248, 449, 65) $Label5 = GUICtrlCreateLabel("Kép kiválasztás", 13, 278, 98, 22) $Button2 = GUICtrlCreateButton("Tallózás...", 360, 272, 89, 25) GUICtrlSetOnEvent(-1, "OpenImage") ;OpenImage $image = GUICtrlCreateInput("", 112, 272, 241, 26) GUICtrlCreateGroup("", -99, -99, 1, 1) $Button3 = GUICtrlCreateButton("Mégse", 328, 320, 121, 33) GUICtrlSetOnEvent(-1, "_GoMainScreen") ;GoMainScreen $Button4 = GUICtrlCreateButton("Hozzáadás", 192, 320, 121, 33) GUICtrlSetOnEvent(-1, "InsertCar") ;Insert Car #EndRegion ### END Koda GUI section ### While 1 Sleep(100) WEnd Func OpenImage() Local $var, $workingdir ; save workingdir $workingdir = @WorkingDir ; FileOpenDialog changes the workingdir on success $var = FileOpenDialog("Válaszd ki az album mappáját", "","Képek (*.jpg;*.bmp;*.jpeg;*.gif)", 1) If $var Then GUICtrlSetData($image, $var) EndIf ; If FileExists code block is for temporary debugging If Not FileExists('database.db') Then MsgBox(0x40030, 'Debug', 'database.db does not exist' & @CRLF & _ '$workingdir = "' & $workingdir & '"' & @CRLF & _ '@WorkingDir = "' & @WorkingDir & '"' _ ) EndIf ; go back to save workingdir FileChangeDir($workingdir) EndFunc Func InsertCar() Local $listQuery, $listRow, $listMsg _SQLite_Startup() _SQLite_Open('database.db') _SQLite_Exec(-1, "CREATE TABLE IF NOT EXISTS cars (id INTEGER PRIMARY KEY, type TEXT, vintage TEXT, enginecode TEXT, cubic TEXT, performance TEXT, license TEXT, image TEXT);") ; Create Table _SQLite_Exec(-1, "INSERT INTO cars (type,vintage,enginecode,cubic,performance,license,image) VALUES ('" & GUICtrlRead($type) & "','" & GUICtrlRead($vintage) & "','" & GUICtrlRead($enginecode) & "','" & GUICtrlRead($cubic) & "','" & GUICtrlRead($performance) & "','" & GUICtrlRead($license) & "','" & GUICtrlRead($image) & "');") ; Insert Row MsgBox(0, "SQLite Error", "Error Code: " & _SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg()) _SQLite_Close() _SQLite_Shutdown() ;_UpdateListView() ;_GoMainScreen() EndFunc ; missing function that was added to test with Func _GoMainScreen() Exit EndFunc The code that kylomas has posted does not have an issue with working directory change as the handle created by _SQLite_Open() is kept open. So hopefully the question of FileOpenDialog is compatible with SQLite has been answered.
Juvigy Posted October 25, 2013 Posted October 25, 2013 From the error u get it might be an issue i noticed with table column names in SQL . For example if i use "Test Test" as column name in the script it wont work but if i use "[Test Test]" it works. You may be facing similar issue.
jchd Posted October 25, 2013 Posted October 25, 2013 From the error u get it might be an issue i noticed with table column names in SQL . For example if i use "Test Test" as column name in the script it wont work but if i use "[Test Test]" it works. You may be facing similar issue. Schema names must be enclosed in double quotes when they contain whitespaces or some other kid of special character (but Unicode letters and symbols are fine). SQLite also accepts schema names in square brackets and in backward quotes (for compatibility with some other engines). 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)
davee Posted October 25, 2013 Author Posted October 25, 2013 Thanks for all the quick help, it looks MHz FileChangeDir came up with a solution.Looks like you caught quite a GoMainScreen function, so I share with you what is included in this function. Func _GoMainScreen() GUISetState(@SW_HIDE) GUIDelete() EndFunc ;==>_GoMainScreen kylomas your code was also a special thank you, I will look at in more detail, because I found a lot of good stuff in there, their cars, "programming" is still just learning. davee
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