none
Extention LitDev Command LDDataBase.Query RRS feed

  • Question

  • Hello LitDev,

    is it possible to expand the command LDDataBase.Query for a ListBox and for a Edit-Mode to replace the data to the database. Then it is easy to write a database Program *smile*.


    Best Regards Martin

    Saturday, June 13, 2015 4:23 PM

Answers

  • OK, here we go - uploaded a new beta test extension with LDControls.AddDataView.

    Here is my test code using all new methods...

    GraphicsWindow.Left = 0
    TextWindow.Left = 800
    headings[1] = "Index"
    headings[2] = "Car"
    headings[3] = "Price"
    dataview = LDControls.AddDataView(GraphicsWindow.Width,GraphicsWindow.Height,headings)
    LDControls.DataViewSelectionChanged = OnDataViewSelectionChanged
    
    LDControls.DataViewSetRow(dataview,1,"1=1;2=Fiat;3=1000;")
    LDControls.DataViewSetRow(dataview,2,"1=2;2=Ford;3=2000;")
    LDControls.DataViewSetRow(dataview,10,"1=3;2=Mazda;3=1500;")
    Program.Delay(1000)
    LDControls.DataViewSetRow(dataview,3,"1=3;2=Mazda;3=1800;")
    Program.Delay(1000)
    data = LDControls.DataViewGetValue(dataview,3,2)
    LDControls.DataViewSetValue(dataview,2,2,data)
    Program.Delay(1000)
    LDControls.DataViewDeleteRow(dataview,1)
    Program.Delay(1000)
    TextWindow.WriteLine("DataViewGetFocus "+LDControls.DataViewGetFocus(dataview))
    Program.Delay(1000)
    LDControls.DataViewSetFocus(dataview,2,1)
    Program.Delay(1000)
    TextWindow.WriteLine("DataViewGetRow "+LDControls.DataViewGetRow(dataview,2))
    Program.Delay(1000)
    TextWindow.WriteLine("DataViewGetSelected "+LDControls.DataViewGetSelected(dataview))
    
    Program.Delay(10000)
    TextWindow.WriteLine("DataViewRowCount "+LDControls.DataViewRowCount(dataview))
    Program.Delay(1000)
    LDControls.DataViewClear(dataview)
    
    Sub OnDataViewSelectionChanged
      TextWindow.WriteLine("LastDataView "+LDControls.LastDataView)
    EndSub
    

    • Marked as answer by martmen Friday, July 3, 2015 1:33 PM
    Thursday, June 18, 2015 8:28 PM
    Moderator
  • Small database Utility NLN504

    or Show title "Do you has database knowhow? ...."


    Best Regards Martin

    • Marked as answer by martmen Friday, July 3, 2015 1:33 PM
    Friday, July 3, 2015 1:33 PM

All replies

  • LitView are not editable, but I will look into it or someway to edit a database table visually.
    Saturday, June 13, 2015 5:12 PM
    Moderator
  • Martin,

    I have added 3 new methods to LDDatabase - text beta uploaded.

    The ListViewis really just to view results of a query, so I added a new view control (DataView).  As a rule ALL databse changes are done by SQL by the programmer and they build an interface round the program functionality, calling SQL commands as required.

    LDDataBase.AddDataView - this is similar to ListView, except it is editable and does not show the results of a query, it shows the entire table.

    LDDataBase.EditTable - load an existing databse table into the DataView which can the be edited - be careful with the ids (must be unique) and data types.

    LDDataBase.SaveTable - saves any changes made in the DataView to the database, so perhaps a button to do this after some editing.

    I have tested a bit but some of the methods used with data binding are newish to me - in my testing with SQLite it works.

    Sunday, June 14, 2015 3:15 PM
    Moderator
  • LitDev

    thank you verry much. I will test it and answer if i find an error.


    Best Regards Martin

    Sunday, June 14, 2015 3:36 PM
  • LitDev

    test it with DJL364 it is a database with 3 Tables. The ListView is ok. If i remarked the commands for DataView it do nothing. Program start, worked long come back to the SB Editior, but do nothing.

    I have Windows 7 Home Premium German Version installed.

    Please check my program where is the error? 


    Best Regards Martin

    Sunday, June 14, 2015 5:20 PM
  • I don't have your database, so can you test my sample which creates the database, import GQP435.
    Sunday, June 14, 2015 5:27 PM
    Moderator
  • It is the equal reaction. start program, worked, come back to sb-Editor.


    Best Regards Martin

    Sunday, June 14, 2015 5:36 PM
  • OK strange - so step by step.

    1] I re-uploaded test beta build to be sure - please get it

    2] Save my test sample somewhere - so Program.Directory is happy- then re-run it - if itfails, then please give some more details any error messages etc.

    In your code this commented line may cause a problem since DV is dataview not listview (listview is required for Query as before).

    'LDDataBase.Query(database, "SELECT * FROM " + TAB[Index] +";", DV, F)

    EDIT, if it is just returning to SB editor, then perhaps add WriteLine as the last stement to keep the program running, eg.

    TextWindow.WriteLine("Done")

    Sunday, June 14, 2015 5:45 PM
    Moderator
  • It was my mistake. I has renamed the dll and XML file to LitDevTest and the other LitDev dll and XML also in the lib Directory.

    I deleted all files in the lib Directory an copy new the test dll and XML files in the lib Directory.

    It is now ok.

    I will tested with my database.


    Best Regards Martin

    Sunday, June 14, 2015 6:09 PM
  • So it runs perfectly. I will write a database-tool to define a database with tables, table-fields and edit the table. 

    Best Regards Martin

    Sunday, June 14, 2015 7:21 PM
  • Good, I made one small improvement - if you make an edit in a cell, this edit was not recognised when saved unless you select away from the edited row before saving.  This is now fixed in test beta upload.
    Sunday, June 14, 2015 7:24 PM
    Moderator
  • Yes i noticed it also. You are the best because to fixed it. I wait hopefull for the next Version.

    I edit on the Database Tool.


    Best Regards Martin

    Sunday, June 14, 2015 9:58 PM
  • The fix was uploaded to test beta

    You tool looks nice, prhaps you will share it when done.
    Sunday, June 14, 2015 10:07 PM
    Moderator
  • Yes i will share it when done. But i have ane Problem. The data file type is in a list box. The select data file type will write to the actual field in the dataview. It is possible to do this?

    New commands:

    Position = ldcontrols.GetDataViewField(dataview) :get the field with the focus in the dataview.

    True/False = ldcontrols.SetDataViewField(dataview, positon, text) set text in the dataview on position

    I know i am nerved you *smile*


    Best Regards Martin

    Monday, June 15, 2015 2:43 PM
  • You can manually edit the dataview, but pasting into it is not really the best way due the way the databse and dataview interact through databinding (the database is the master).  To add a row from some other data the best approach is to write the SQL to edit the database, not try to do it through dataview.

    So just do the SQL to add a row from other list box or textbox data.  SQL will let you find a row and modify it and commit the changes - this is what SQL does.

    Think of it this way.

    • The database has the underlying data
    • any changes to this data can be achieved using SQL - this has to be learned how to do it.
    • The listview shows the resut of a query
    • The dataview shows an entite table and can be edited by hand - any other changes to data should be with SQL

    Perhaps give more details on what exactly you want to do to the database and we can try to work out the SQL to do it.

    See http://zetcode.com/db/sqlite/datamanipulation/ for some basic info only modifying SQLite database rows.

    EDIT

    I added and uploaded beta release with an event for DataView selection changed - so you can get selected id # (first column) which can be used for any SQL to change these rows with a replace in some way.

    DataViewSelected, LastTable and LastSelection

    Basically I want to explore and comfirm that what you want to do cannot be done with SQL before adding lots of complexiy to the extension. 
    Monday, June 15, 2015 7:40 PM
    Moderator
  • You identived right. I encroached the editdataview. The right way is a editable grid.

    Show a grid, edit a grid, get focus of actual field, setfocus to a field and set the data to a field and set the title of row and column.

    I will do like this:
    If edit a field i get the focus from the actual field. If the field a field with defination data so open a listbox. The selct data from listbox set to the actual field in the grid.

    If i left the key "Return" up then i will set the focus to the next field. I will defination the next field.

    To set the width and height of the fields in the Grid.

    It is possible to create a new object "Grid" with this function? 


    Best Regards Martin


    • Edited by martmen Tuesday, June 16, 2015 9:51 AM
    Tuesday, June 16, 2015 9:46 AM
  • I'm not sure how a dataview control is going to help with a database - you will stiil need to do the SQL to act on the database, then manually update the dataview - asit is, the databinding does this for you when you change the database via SQL.

    I may create a dataview control separately, but without a database behind it would be a bit lame, and with a database, as I say it should be controlled by SQL.

    I think perhaps I don't understand what you are trying to do, or yu don't understand how databases and interfaces are usually programmed; or may be a bit of both.

    Tuesday, June 16, 2015 7:44 PM
    Moderator
  • By define a database table i will define the name of table field and the data type for this field. There are some definition data types as sample TEXT, NUM, and so on.

    I need a grid with a field column,  a data type column and may be some more column. Is the focus in the data type column the program open a list box to select the definied data types. All fields definate the table.

    After defined all fields in the grid i put it in the database by SQL command.

    That is what i will do.

    I think a new object grid-table (like Excel) without database conection is the right way for this. The data are in an arry with 2D like Grid[column][row].

    I hope you can understanding me.

      

    Best Regards Martin

    Tuesday, June 16, 2015 8:10 PM
  • So you want to create a datagrid that is not bound to any database  thatyou can set data in, then programatically edit and get this data to store in a database using SQL - i.e. no data binding.

    EDIT

    Have you checked ou LDControls.AddListView?

    Tuesday, June 16, 2015 8:46 PM
    Moderator
  • Yes that is right. Sorry for the missunderstanding that is my bad english.


    Best Regards Martin


    • Edited by martmen Tuesday, June 16, 2015 9:09 PM
    Tuesday, June 16, 2015 9:09 PM
  • First check out LDCOntrols.AddListView and see where this falls short.
    Tuesday, June 16, 2015 9:12 PM
    Moderator
  • Show QSD627. It is not complete. I wrote text what i will do in the program.


    Best Regards Martin

    Tuesday, June 16, 2015 10:38 PM
  • The complete sample is DPJ747. There are 2 new commands. If You run the sample it showing the error lines with the new commands. I hope it runs with the new commands.


    Best Regards Martin


    • Edited by martmen Wednesday, June 17, 2015 2:23 PM
    Wednesday, June 17, 2015 6:49 AM
  • I will look into adding these.

    Strangely this seems to be very hard (at least to me the way I implemented listview through dependency binding to allow textbox editing).  So not an easy fix and I will have to think about it or just do the dataview as a control, which has some other issues since it is a forms control (another technology) that needs to be embedded in wpf (windows presentation framework - the controls type used in SB GraphicsWindow) for SB - with some issues passing events between wpf and forms .... fun).

    Wednesday, June 17, 2015 5:53 PM
    Moderator
  • I am a friend from programs with assistings users. I like to set the fokus to the right field if it give one way. I hope you supporting it :-)

    I thank you for your hard Job.

    Is better to begin a new way by a dead end.

    Sorry for my bad english.


    Best Regards Martin

    My program you can see as SCL511. it is not ready.
    • Edited by martmen Thursday, June 18, 2015 8:18 AM
    Thursday, June 18, 2015 8:11 AM
  • OK, here we go - uploaded a new beta test extension with LDControls.AddDataView.

    Here is my test code using all new methods...

    GraphicsWindow.Left = 0
    TextWindow.Left = 800
    headings[1] = "Index"
    headings[2] = "Car"
    headings[3] = "Price"
    dataview = LDControls.AddDataView(GraphicsWindow.Width,GraphicsWindow.Height,headings)
    LDControls.DataViewSelectionChanged = OnDataViewSelectionChanged
    
    LDControls.DataViewSetRow(dataview,1,"1=1;2=Fiat;3=1000;")
    LDControls.DataViewSetRow(dataview,2,"1=2;2=Ford;3=2000;")
    LDControls.DataViewSetRow(dataview,10,"1=3;2=Mazda;3=1500;")
    Program.Delay(1000)
    LDControls.DataViewSetRow(dataview,3,"1=3;2=Mazda;3=1800;")
    Program.Delay(1000)
    data = LDControls.DataViewGetValue(dataview,3,2)
    LDControls.DataViewSetValue(dataview,2,2,data)
    Program.Delay(1000)
    LDControls.DataViewDeleteRow(dataview,1)
    Program.Delay(1000)
    TextWindow.WriteLine("DataViewGetFocus "+LDControls.DataViewGetFocus(dataview))
    Program.Delay(1000)
    LDControls.DataViewSetFocus(dataview,2,1)
    Program.Delay(1000)
    TextWindow.WriteLine("DataViewGetRow "+LDControls.DataViewGetRow(dataview,2))
    Program.Delay(1000)
    TextWindow.WriteLine("DataViewGetSelected "+LDControls.DataViewGetSelected(dataview))
    
    Program.Delay(10000)
    TextWindow.WriteLine("DataViewRowCount "+LDControls.DataViewRowCount(dataview))
    Program.Delay(1000)
    LDControls.DataViewClear(dataview)
    
    Sub OnDataViewSelectionChanged
      TextWindow.WriteLine("LastDataView "+LDControls.LastDataView)
    EndSub
    

    • Marked as answer by martmen Friday, July 3, 2015 1:33 PM
    Thursday, June 18, 2015 8:28 PM
    Moderator
  • Small database Utility NLN504

    or Show title "Do you has database knowhow? ...."


    Best Regards Martin

    • Marked as answer by martmen Friday, July 3, 2015 1:33 PM
    Friday, July 3, 2015 1:33 PM