Project Issues - DB Related lazarus / 16-Databases ---------------------------------------------------------------------- networkn · 2014-07-14 09:18 Hi There! Forgive me for I have been away from programming some 15 years. I have come from a Delphi Background back when Borland still owned it. I am trying to create a little project which consists of a single form with a drop down box, and a grid, and a close button. The drop down displays the list of users from an table and when it's selected, display filtered results in the grid. I have managed to get a DB Connection open to the MS SQL DB and display some sample information based on a quick query (tested in the SQL area of the Query Component).  I need to make sure the Grid Data is not editable (Read Only). I am unsure the best way to do this? I then decided to get the drop box working so I put a DBComboBox on the form and connected it to it's own SQLQuery component, with it's own SQL Transaction and it's own ListDataSource. I reused the prior MSSQLConnection component. For some reason I can't get the combobox to display the list of users despite the sqlquery component showing the expected results. I believe this is because I can't set the SQLQuery as active (No error, though I get a compile error).  The compile error I get is : 20019 Attempt to Initiate a new Adaptive Server operation with Results Pending I was also getting errors about Rollback which I think is because the SQLTransaction Component action is set to caRollback. I am wondering if it's safe to set that to caNone as the project is read only? I am running IDE 1.2.4 on Windows Thank you for any assistance you can offer. ---------------------------------------------------------------------- howardpc · 2014-07-14 10:12 Quote from: networkn on July 14, 2014, 11:18:55 amI need to make sure the Grid Data is not editable (Read Only). I am unsure the best way to do this? Assuming you are using a DBGrid, does setting its ReadOnly property to True not achieve what you want? ---------------------------------------------------------------------- BigChimp · 2014-07-14 10:44 Quote from: networkn on July 14, 2014, 11:18:55 am20019 Attempt to Initiate a new Adaptive Server operation with Results Pending This could be the issue mentioned in the documentation: http://www.freepascal.org/docs- html/fcl/mssqlconn/tmssqlconnection.html ---------------------------------------------------------------------- networkn · 2014-07-14 10:54 Quote from: howardpc on July 14, 2014, 12:12:48 pmQuote from: networkn on July 14, 2014, 11:18:55 amI need to make sure the Grid Data is not editable (Read Only). I am unsure the best way to do this? Assuming you are using a DBGrid, does setting its ReadOnly property to True not achieve what you want? Oh dear, that was dumb of me to miss that. ---------------------------------------------------------------------- networkn · 2014-07-14 10:54 Quote from: networkn on July 14, 2014, 12:54:17 pmQuote from: howardpc on July 14, 2014, 12:12:48 pmQuote from: networkn on July 14, 2014, 11:18:55 amI need to make sure the Grid Data is not editable (Read Only). I am unsure the best way to do this? Assuming you are using a DBGrid, does setting its ReadOnly property to True not achieve what you want? Oops self replied! Hmm that is an unfortunate bug to strike my first time back, is there another component I could use, or another workaround that exists? ---------------------------------------------------------------------- BigChimp · 2014-07-14 10:57 Quote from: networkn on July 14, 2014, 12:54:59 pmHmm that is an unfortunate bug to strike my first time back, is there another component I could use, or another workaround that exists? Not sure what you're saying here... did setting the ReadOnly property not work? ---------------------------------------------------------------------- networkn · 2014-07-14 11:30 Quote from: BigChimp on July 14, 2014, 12:57:43 pmQuote from: networkn on July 14, 2014, 12:54:59 pmHmm that is an unfortunate bug to strike my first time back, is there another component I could use, or another workaround that exists? Not sure what you're saying here... did setting the ReadOnly property not work? Sorry no I am not having a good day and replied to myself. I meant to reply that it was unfortunate I had struck a known issue with the Adaptive Server results pending, and was asking if I could use some other component to resolve the issue or was a workaround available. ---------------------------------------------------------------------- BigChimp · 2014-07-14 11:37 Well, yes the workaround is written right there in the documentation: Quote set TSQLQuery.PacketRecords=-1 to fetch all pending rows so something like (air code, untested, adapt to your situation): Code: [Select]MySQLQuery.PacketRecords:=-1; ... or you can probably set that property in the object inspector which might be better (as it'll apply design-time as well). ---------------------------------------------------------------------- BigChimp · 2014-07-14 11:42 Quote from: networkn on July 14, 2014, 11:18:55 amThank you for any assistance you can offer. As indicated in the first link in my signature, attaching your project source as a zip file (and database schema/table definitions in this case) via menu project/publish project is often a good idea - it allows others to understand what's going on (less explanations needed) and allows for easier/more precise suggestions for improvement. Of course, this only works if your project is not confidential in nature... ---------------------------------------------------------------------- BigChimp · 2014-07-14 11:48 And yes, you can use an odbc connection instead of MSSQL Connection: http://wiki.lazarus.freepascal.org/ODBCConn ---------------------------------------------------------------------- networkn · 2014-07-14 23:57 Quote from: BigChimp on July 14, 2014, 01:48:50 pmAnd yes, you can use an odbc connection instead of MSSQL Connection: http://wiki.lazarus.freepascal.org/ODBCConn Thanks. Sorry for the dumb questions, I am still getting my bearings. Does ODBC require the PC to have a connection to the DB? I want this to be as independent as possible. ---------------------------------------------------------------------- networkn · 2014-07-14 23:58 Quote from: BigChimp on July 14, 2014, 01:42:41 pmQuote from: networkn on July 14, 2014, 11:18:55 amThank you for any assistance you can offer. As indicated in the first link in my signature, attaching your project source as a zip file (and database schema/table definitions in this case) via menu project/publish project is often a good idea - it allows others to understand what's going on (less explanations needed) and allows for easier/more precise suggestions for improvement. Of course, this only works if your project is not confidential in nature... Thanks I have done this now. Hopefully it helps make things clearer. ---------------------------------------------------------------------- networkn · 2014-07-15 00:26 Quote from: BigChimp on July 14, 2014, 01:37:00 pmWell, yes the workaround is written right there in the documentation: Quote set TSQLQuery.PacketRecords=-1 to fetch all pending rows so something like (air code, untested, adapt to your situation): Code: [Select]MySQLQuery.PacketRecords:=-1; ... or you can probably set that property in the object inspector which might be better (as it'll apply design-time as well). So to clarify, the program is throwing an error, because its' not able to get the refreshed data or something similar? Where would I put that code in Object Inspector? Under which property or Event? ---------------------------------------------------------------------- BigChimp · 2014-07-15 07:57 Quote from: networkn on July 15, 2014, 01:58:28 amQuote from: BigChimp on July 14, 2014, 01:42:41 pmAs indicated in the first link in my signature, attaching your project source as a zip file (and database schema/table definitions in this case) via menu project/publish project is often a good idea - it allows others to understand what's going on (less explanations needed) and allows for easier/more precise suggestions for improvement. Of course, this only works if your project is not confidential in nature... Thanks I have done this now. Hopefully it helps make things clearer. Thanks a lot, I can open the project from the attachment added to your first post but no idea what the database looks like so I can't run it to debug (with my admittedly limited GUI capabilities). Quote from: networkn on July 15, 2014, 01:57:49 amThanks. Sorry for the dumb questions, I am still getting my bearings. Does ODBC require the PC to have a connection to the DB? I want this to be as independent as possible. There are no dumb questions - only questions that are not asked well For an ODBC connection you rather obviously require an ODBC driver, in this case to MS SQL Server, to be installed on the PC you want to run the code on. ODBC allows you to set up so-called DSNs which to me are connections: system DSN, user DSN etc see the ODBC control panel. Lazarus/FPC odbcconn can work with those but can also work with DSN-less connections. See the wiki article I linked to for details. Quote from: networkn on July 15, 2014, 02:26:35 amSo to clarify, the program is throwing an error, because its' not able to get the refreshed data or something similar? Well, the error message you quoted and the one mentioned in the docs is the same so that could be the case. IIRC there's probably a description of the exact "pending results" error and cause in the original forum thread about the mssqlconn connector - but I don't know enough about it. Also, the problem could be something else but the error messages just happen to be the same. Quote from: networkn on July 15, 2014, 02:26:35 amWhere would I put that code in Object Inspector? Under which property or Event? In GridSQLQuery1, in the PacketRecords property, as mentioned. It's currently set to 10 Probably likewise for UserListSQLQuery1 ---------------------------------------------------------------------- networkn · 2014-07-16 03:38 Ok I have added that to both my GridQuery1 and UserlistSQLQuery1. Is now giving me The RollBack Transaction request has no corresponding Begin Transaction when I click the close button, Also if I try to run the application again. I set the action of the SQLTranactions to caNone but that didn't help. I also note I can't set my UserListSQLQuery1 to active, no error. The SQL results it's supposed to be displaying show up fine in the query results. ---------------------------------------------------------------------- BigChimp · 2014-07-17 09:51 Sorry I couldn't get back to you earlier. As I didn't want to replicate your tables/views (with tens if not hundreds of fields) please find attached my version. The combobox is linked to the same datasource/transaction as the grid. Not sure if you want a dblookupcombobox in your situation? (Not much of a GUI guy). Works for me with Lazarus stable and SQL 2008R2... Regarding the transaction stuff: assuming that's the fix in bug http://bugs.freepascal.org/view.php?id=26068 (can't remember right now and bugtracker is down) ---------------------------------------------------------------------- networkn · 2014-07-17 10:04 No problems I am just grateful to get any help. I didn't know I could use one transaction/datasource for mulitple DB Aware components, so it's likely that was the problem. I extracted your project into a new directory and tried to open it, but I have no form? ---------------------------------------------------------------------- BigChimp · 2014-07-17 10:14 !?!?! Strange! Just opened the .lpi in Laz 1.2.4 and I get an hourglass without anything happening... I'll have a look if I can figure out what's going on... ---------------------------------------------------------------------- BigChimp · 2014-07-17 10:24 Seems the form got corrupted in some way?! Anyway, I just changed the db connection to my local server, changed the select queries to some simpler ones, and set the combobox query to use the first transaction... ---------------------------------------------------------------------- networkn · 2014-07-17 10:34 Hi There! I am finding Laz to be very very buggy, it's crashed about 10 times for me, and now not only your project, but my original won't load. How annoying! so I can use 1 SQL Connection, 1 transaction even with multiple components like DbGrid, so long as they are all pointing at 1 DB ? I suspect if that's the case, having two transaction etc was causing an issue. ---------------------------------------------------------------------- BigChimp · 2014-07-17 10:40 Well, yes you can use 1 transaction - used to have one single view of your data. AFAIK, the datasource between the query and your components is meant to navigate through the query and present your components with a unified view. If you meanwhile want to do separate e.g. insert/update/delete work or do an anlysis of long running query data you can use multiple transactions. At least that's what I gather/understand... still not much of a GUI guy As for crashes: sorry you're seeing that. The problem I had just now was the first one in a long time. You could raise an issue on the bugtracker if a similar issue has not already been raised (and then you could add a comment/upload your sample project). ---------------------------------------------------------------------- networkn · 2014-07-17 11:25 Quote from: BigChimp on July 17, 2014, 12:40:50 pmWell, yes you can use 1 transaction - used to have one single view of your data. AFAIK, the datasource between the query and your components is meant to navigate through the query and present your components with a unified view. If you meanwhile want to do separate e.g. insert/update/delete work or do an anlysis of long running query data you can use multiple transactions. At least that's what I gather/understand... still not much of a GUI guy As for crashes: sorry you're seeing that. The problem I had just now was the first one in a long time. You could raise an issue on the bugtracker if a similar issue has not already been raised (and then you could add a comment/upload your sample project). I deleted my extra transaction and datasource, but then couldn't get the dropbox to show the data from query I wanted, and when I added another Datasource it broke again. I don't seem to understand the relationships ---------------------------------------------------------------------- BigChimp · 2014-07-17 11:48 I've used the same datasource as the grid because the dbcombobox is bound to the query in the table. As this explains: http://www.delphigroups.info/2/aa/316884.html QuoteDBCombobox is just like a normal combobox : You put directly some values in the "items" property (for example : 'blue', 'red', 'green') and you link to a field of your table with properties datafield and datasource. Now when you choose a value, it will directly be written in the field of the table (for example 'red') Now if you want to fill a field of a table by a field of another table, you must use DBLookupCombobox : datafield and datasource to indicate what field and table you want to fill, ListField and listsource to indicate the filling field. Rather than a dbcombobox, I suspect you'd want a dblookupcombobox http://wiki.lazarus.freepascal.org/dblookupcombobox e.g. if you want to let your user pick an entry from another table and use that to save in your grid/the underlying query. ---------------------------------------------------------------------- networkn · 2014-07-17 12:09 Great Catch thank you, yes I had a DBLookupComboBox and accidentally put the DBComboBox in after I deleted the original whilst troubleshooting. I am getting a warning whilst compiling in this line: procedure DataSource1DataChange(Sender: TObject; Field: TField);  tasview.pas(27,54) Hint: Parameter "Field" not used ---------------------------------------------------------------------- networkn · 2014-07-17 12:29 Ugh I can't catch a break I have changed the DBLookupComboBox on the form, created a query in the SQLQuery1 which when tested produces the list I want displayed, however when I point the TSQLQuery at the Datasource and configure the DbLookupComboBox to point to the Datasource and set a listsource and listfield nothing is displayed! ---------------------------------------------------------------------- BigChimp · 2014-07-17 12:43 Quote from: networkn on July 17, 2014, 02:09:32 pmI am getting a warning whilst compiling in this line: procedure DataSource1DataChange(Sender: TObject; Field: TField);  tasview.pas(27,54) Hint: Parameter "Field" not used If you do nothing with Field inside that procedure, yes that warning could be displayed, but it's not a problem. ---------------------------------------------------------------------- BigChimp · 2014-07-17 12:46 Quote from: networkn on July 17, 2014, 02:29:44 pmI have changed the DBLookupComboBox on the form, created a query in the SQLQuery1 which when tested produces the list I want displayed, however when I point the TSQLQuery at the Datasource and configure the DbLookupComboBox to point to the Datasource and set a listsource and listfield nothing is displayed! You should be pointing: * the control at the datasource * the datasource at the query Are you sure you bound all fields - not very familiar with control but wiki also menionts keyfield (field to use to store the value in) ---------------------------------------------------------------------- rvk · 2014-07-17 17:13 Quote from: networkn on July 17, 2014, 02:29:44 pmI have changed the DBLookupComboBox on the form, created a query in the SQLQuery1 which when tested produces the list I want displayed, however when I point the TSQLQuery at the Datasource and configure the DbLookupComboBox to point to the Datasource and set a listsource and listfield nothing is displayed! Yeah, i think you did it the wrong way around. You should point the datasource of GridDataSource1 to UserListSQLQuery1 and leave the datasource of UserListSQLQuery1 empty. (Because you want to use the selected field USERID from UserListSQLQuery1 as a parameter in the query in GridDataSource1 and the datasource of a dataset does that, but you need to set it right) As i see it: To make a combobox where you choose a user and you want to display only the records from that user in the grid. You could do the following: MSSQLConnection1 SQLTransaction1 connected to MSSQLConnection1 UserListSQLQuery1   with SQL "Select * from usr_User where status = 1;"   and connected to MSSQLConnection1   and IMPORTANT leave datasource empty UserListDataSource1 connected to UserListSQLQuery1 Now a DBLookupComboBox1   don't connect it to a datasource because you're not doing anything with the result   the UserListDataSource1 will "follow" your choice and you'll use that in GridSQLQuery1   don't connect it to a datafield for the same reason   connect listsource to UserListDataSource1   choose keyfield as USERID   choose Listfield as USERNAME   set the ScrollListDataset to true   set the style to csDropDownList At this point you have a working Lookupbox. Now: GridSQLQuery1   with SQL "select * from sm_job a where a.userid = :userid and a.statusid <= 3"   ( please note the use of :userid there )   and connected to MSSQLConnection1   and IMPORTANT datasource to UserListDataSource1 GridDataSource1 connected to GridSQLQuery1 and finally TaskListDBGrid1 connected to GridDataSource1 You see the datasource of GridSQLQuery1. When doing this it will take the chosen USERID in the other UserListSQLQuery1. UserListSQLQuery1 follows the combobox automatically. In the sql select for your grid you can use :userid to only display the records of that user. You can see another explanation about this here under unbound use PS. You don't need the procedure DataSource1DataChange at all. Is this about right? If not... could you please provide your project again (as you have it now)? (It probably changed a few times over the last few days  ) ---------------------------------------------------------------------- networkn · 2014-07-17 23:41 RVK: Thanks for the detailed reply, I will look at this after work tonight and will post the updated code if that doesn't work. I was trying to do things in stages so I could re familiarise myself with the logic, so I was trying initially to get the grid working with the basic information and this IS working ok. I then wanted a drop down to show me all the names of the users in the DB, which I have NOT got working despite spending a while last night changing datasources etc.  Then I thought I'd link them so that the combobox changed the filtering on the grid, but perhaps that isn't possible. Esssentially I was trying to get each component working 1 at a time.