bazzer747

version 10.4.1 Found the answer!!! Grrhhh, silly me. I have a status bar on the form using it to write various things happening. I'd called it 'word' would you believe! Changed it to SayWord and all works fine.

I have a MSSQL table of financial transactions and I retrieve the data with a FDQuery which has the following statement: 'SELECT * FROM tAccounts ORDER BY AccName, TxnDate, Deposit' And executing that from the TFDQuery gives the correct results - a list in Account order and within that date order. See attached SQLView.jpg, specifically records 501 and 502 (correct date order). However, when I view this same table in a DBGrid in my application, the order is not the same. See attached DBGView.jpg, note the 20 Feb is before 17th Feb. The field is a Date datatype and there is no alteration to the Select statement anywhere in the application. The DBGrid is the first thing shown in the application and neither Formcreate or FormActivate do anything to alter the data returned. This 'mis-sorting' shows on several other of the records and my thinking is that in the DBGrid the TxnDate field is sorted in yyyy-mm-dd order, as in '20210217', which numerically comes before '20210220'. So this is what's happening, it seems, but why, when the SQL returns the data in the correct order does the DBGrid 're-sort' in a numeric fashion?

Oops! My bad .... just seen at sometime in the past I set IndexFieldNames on the query to the 'ID' field. Took it off, voila sort is good. I set that field when I was testing the locate functionality to help speed up finding a record. I can do that in code now switching it on or off when necessary. Sorry for asking too quickly ....

Ahh, just found something that might explain something! I have another field called 'ID' - this is a auto-incrementing field to ensure uniqueness of my records. If I show this ID field in the DBGrid then it is clear the order is in 'ID' order! Why should this be, as the Select statement doesn't specify the ID field as something to sort on. However, I did make this field a Key field when I set the table up. Is this the problem? The DBGrid (for some reason) uses the Keyfield to display data rather than what the Select statement tells it to?

Hi, I'm aware that you can use a macro substitution in a select statement when you don't know some value until runtime (like a fieldname). If there a similar function that can be used in a Locate? I am developing a 'Search' on an accounts program where the user first selects a fieldname in which he looks for a value, maybe a 'Payee' fieldname or an 'Account' fieldname or a 'Deposit' fieldname. So the field on which to conduct the locate isn't known until runtime. A usual locate might look like this: if fdqA.Locate('Payee', cPayee ), []) then ShowMessage('Located!') else ShowMessage('Failed!'); But the user might want to search on 'Account' or 'Deposit'.

Hi I have a table with a field called 'cTaxYear', which is created as an Internal Calculated field (fkInternalCalc), it's a 7 character string field and contains, for instance, '2008-09', being a tax year (here in the UK) from 7Apr2008 to 6Apr2009. The calculation is based on the date a dividend is paid out and works out what tax year that dividend falls into. It works perfectly well in dbgrids etc. However, when I try to create a filter, for instance to filter all the records with a tax year of '2008-09' the dbgrid displays nothing. There are about 150 records which do contain '2008-09' in the 'cTaxYear' field and the filter expression is 'cTaxYear = ' + QuotedStr( '2008-09' ); Reading about filters I see it says 'Only the fkInternalCalc and fkAggregate fields can be used in filtering, sorting, or locating operations....' (which is why I created an fkInternalCalc field)) but says nothing more about any settings that should be used (like maybe setting indexfieldnames). Does anyone know for sure that an InternalCalc field can actually be used in a filter expression? Or if it definitely can't?

Hi I setup my small project and tried again, with no success. I tried with a normal Filter and also with OnFilterRecord, neither worked with an InternalCalc field. The documentation suggests it should work, but it doesn't (for me anyway). If you did get it working can you show some code showing the internalcalc field and the filter code. Maybe it's just a small mistake somewhere that stops it working. PS I have it all working fine now using a SELECT SQL statement, so IO'm just now interested in the final answer.

Hi, I see this but it doesn't help me in that it isn't working for me - I've listed the code I'm using above and there is nothing complicated about it. So if you can filter on an internally calculated field then why does it not work? Should work and actually working are two different things, as I've experienced in Delphi many times over the years. I'll set up a small project with nothing other than the bare minimum and try again with a more simple filter and see what happens. If that still doesn't work for me I'm going back to a simple Select statement to filter the records (which I know works as that's what I did before trying the internal calc way.

Lajos, Are you referring specifically to Internal Calculated fields here?

I'm misunderstanding a little here. I am using OnCalcFields AND it is working - I can see the result in a dbgrid. And the documentation indicates you can filter on an internal calculated field. I see what that link and you say about it but it doesn't ring true does it if the calculation is doing what it should do? And they give an example using it which seems to refute what they say. It must be a nightmare trying to keep the documentation up to date. However, It's looking like it's a no goer for me then. I'll use the old SELECT statement to filter the records. Pity Cary Jensen didn't note an issue in his in-depth book (I'll drop him a line, but no doubt he's inundated with questions about his book), it would've saved me some time, but hey-ho, you learn something every day. Thanks for your time helping me with this.

procedure Tdm.fdqDivsCalcFields(DataSet: TDataSet); begin //PART OF ... if ( fdqDivsDividendDate.AsDateTime > StrToDate('06/04/2018') ) AND ( fdqDivsDividendDate.AsDateTime < StrToDate('07/04/2019') ) then fdqDivscTaxYear.AsString:= '2018-19'; if ( fdqDivsDividendDate.AsDateTime > StrToDate('06/04/2019') ) AND ( fdqDivsDividendDate.AsDateTime < StrToDate('07/04/2020') ) then fdqDivscTaxYear.AsString:= '2019-20'; if ( fdqDivsDividendDate.AsDateTime > StrToDate('06/04/2020') ) AND ( fdqDivsDividendDate.AsDateTime < StrToDate('07/04/2021') ) then fdqDivscTaxYear.AsString:= '2020-21'; END; And in the click on a radio group: with dm.fdqDivs do begin Filtered:= False; if (cAll = 'ALL') AND (cTaxYear = 'ALL') then Exit; //No filter required if (cAll <> 'ALL') AND (cTaxYear = 'ALL') then Filter:= 'Whose = ' + QuotedStr( cWhose ); if (cAll = 'ALL') AND (cTaxYear <> 'ALL') then Filter:= 'DividendDate = ' + QuotedStr( cTaxYear ); if (cAll <> 'ALL') AND (cTaxYear <> 'ALL') then Filter:= 'Whose = ' + QuotedStr( cWhose ) + ' AND cTaxYear = ' + QuotedStr( cTaxYear ); Filtered:= True; end; cWhose is a letter value indicating a persons initial. cTaxYear holds the selected value to filter on (eg '2018-19').

Sorry, no. The original SQL query returns all the records, of which I want to filter just some of them. I can use a new SQL query to do this job (as I have done in the past) but I wanted to try using an internal calc field to see if this is quicker/more efficient (which I believe it is). All I want to know for certain is that I can filter on an internal calc field. If I can I'll dig a lot deeper with testing etc. to see why it isn't working. I would have thought a simple 'Filter = 'QuotedStr( 'cTaxYear' ); statement, where cTaxYear holds the relevant data would work, but it doesn't (and I've checked the values from this variable against the internal calculation results and they match.

Interesting. How would an InternalCalc field be calculated then? Yes, the calculation is done on the OnCalcField, and works fine displaying all the correct calculations (which I see in a dbgrid).

Hi I have. I've read the documentation on the Embarcadero wiki site, and I have Cary Jenson's book 'Delphi in Depth: Firedac', and quoted what it says above. It isn't in enough detail to say specifically you can filter on an internal calulated field, but it does say: 'Only the fkInternalCalc and fkAggregate fields can be used in filtering, sorting, or locating operations....' which says you can use filtering on an internal calculated field. But I'm trying it and it doesn't - but that maybe because there are some details or constraints required to do this which I can't find anywhere.

Thanks for the information, that's certainly one way to go. I have setup the internal calc field so am keen to understand if it really is possible to filter on that field (as the official documentation seems to suggest).

Oops, sorry. I'm using a MSSQL database.

Hi, I came across an oddity a few days ago in a report I had created some years ago (this was with Ace Reporter). This report had a jpg image which I wanted to replace with a png image. I deleted the image from the report then placed a TSctImageLabel (the equivalent of a TImage the reporter requires for inserting images). Problem was, whilst the 'Picture' property was there with the 3 dots ready for selection of an image, it was sort of greyed out, in that I couldn't click on the dots. I thought this might be an issue with the report component (and have put in a support request to them). However, today, on a different project, I had need to insert an image onto a form. Lo and behold, when I added a TImage to the form the 'Picture' property behaved the same way. I couldn't select an image. I've now also created a new VCL project and just added a TImage and can't select an image. I can load the image I want successfully from code with: Image1.Picture.LoadFromFile('D:\logo3.png'); - setting whatever properties I want from the object inspector. But I really need this image 'included' in the executable and not have to distribute it with the exe. Could this be a problem with my Delphi installation (I use 10.4.1 with all patches applied), or the TImage itself? Or have I messed up somewhere (even I think this is most likely! but I can't see how)

Got it! I have been systematically removing add-ons and testing the TImage 'Picture' property. In a recent webinar Embarcadero were giving a 'free' component from Dev Express - a VCL NavBar toolbar component. Whiuch I downloaded and tried, very nice it was. However, as soon as I removed the DevExpress component TImage worked OK. I've informed DevExpress, maybe Embarcadero should make sure any giveaways they do work correctly 🙂 Thanks for the suggestion Remy.

Often I'll trawl through the different forums and maybe open one or two of them, as my interest is peaked. Sometimes I don't see anything of interest. What I always do when I come out of that topic is to press the big icon which supposedly marks the forum as read. If I enter that forum then, sure enough, all the topics are marked read. However, when I come here on the following day and see there are 'new' topics, when I enter the forum the same questions I thought had been marked as read yesterday are not. It's only if I open the question and close it does it remain read. Or if I press the 'Mark forum as read' text to the right. But to do that I need to enter the forum first. It's annoying to see what I think is new material only to enter the forum to see it's questions I've previously decided not to open. Is this the way it is supposed to work?

Hi I'm struggling to enter a Null value into a field with a Firedac ExecSQL Update. My code to update a record looks like this: dm.fdc.ExecSQL( 'UPDATE tbCalendar SET EventDate = :pEd, Event = :pEv, EventTime = :pEt, Venue = :pVe, EventType = :pEvt, [Order] = :pOr, Competition = :pCo ' + ' WHERE ID = :pID', [ dDate, edEvent.Text, edTime.Text, cbVenue.Text, cbType.Text, StrToInt(cbOrder.Text), Null, iMatchID ]); 'dm.fdc' is the connection in a Data Module (dm). The 'Competition' field is not filled in by the User so needs to be assigned a Null value. Adding a new record does this by default but if the user has added a value and then wants to remove it I need to assign a Null back to the field. The above code has worked but at other times gives an error message: ..' data type is unknown... Specify TFDParam.DataType or assign TFDParam value before Prepare/Execute call.' But what DataType is Null and how does that code look? I've searched for how to do this but can't find a clear result. I've seen that you can assign Null to a field using 'Clear', but how does this get implemented. I've tried dm.fdcCalendar.Edit; dm.fdcCalendar.FieldByName('Competition').Clear; dm.fdcCalendar.Post; And that gives an error - '..update table does not have a PK or row identifier, record has been changed/deleted by another user' which is of little or no help as there is no other user as I'm testing. The Embarcadero wikipedia help is of little help, fofr some reason it refrains from giving code examples on most items I look up. And other sources, whilst nearly echoing what my issue is aren't quite the same issue.

Ah, Isn't this what Stano says - don't uuse direct assignment to a field because of possibility of SQL injection? Also, I did try the .Clear setting but it didn't work, as I mentioned earlier.. Thanks for responding..

Hi Yes, I agree. That's why I always use parameters in my ExecSQL statements. My problems with the ExecSQL is that I don't know how to assign a null value in the statement.

Yay. My code now looks like this: dm.fdc.ExecSQL( 'UPDATE tbCalendar SET EventDate = :pEd, Event = :pEv, EventTime = :pEt, Venue = :pVe, EventType = :pEvt, [Order] = :pOr WHERE ID = :pID', [ dDate, edEvent.Text, edTime.Text, cbVenue.Text, cbType.Text, StrToInt(cbOrder.Text), iMatchID ]); dm.fdqvCalendar.Edit; dm.fdqvCalendar.FieldByName('Competition').Value:= null; //To set the field to Null dm.fdqvCalendar.Post; And this works without any error. I've dropped the Competition field and parameter from the ExecSQL but other fields may well be updated, and then apply the Edit/Post afterwards and this works OK. So many thanks for the quick response. I suppose it would be 'cleaner' if I couldd apply a Null value withing the ExecSQL statement, but, ,hey, if it works I'm happy with that.

I used Delphi Professional for years, and had the Firedac add-on as together they gave me all I wanted from the product. I'm not into cross-platform development or server development so Enterprise was of no interest. Until they stopped the FireDac add-on in Profession a few years back. However, at the same time, they did a very good deal (so I thought!) to migrate to Enterprise, which includes Firedac. Which I did. Pity, I should have bitten the bullet then and used a different method of connecting databases, and stuck with Professional. All my projects used Firedac quite intensively so to move away from Enterprise now would be a major step. The cost of an Enterprise licence went up over 30% this year, completely unjustifiable in my opinion, and I've therefore lapsed my licence. 10.4.1 is where I am and where I'll stay for the foreseeable future.

Hi, I have a table with a column of numbers, which go from -6 to 54. This table is split into 3 divisions -6 to 16, 14-20, and 21 to 54.. I display the records in a dbGrid with a radiogroup (rgDivs) showing the 3 divisions, plus 'All'. When a user selects a division I use a SetRange to limit what is shown. The code in the radiogroup OnClick event looks like this: cChoice:= rgDivs.Items[ rgDivs.ItemIndex ] with dm.vEnrolments do begin if cChoice = 'All' then CancelRange else if cChoice = 'Div 1' then SetRange( [ -6 ],[ 16 ] ); if cChoice = 'Div 2' then SetRange( [ 14 ],[ 20 ] ); if cChoice = 'Div 3' then SetRange( [ 21 ],[ 54 ] ); end; dm.vEnrolments is a View of a table with all the records and cChoice is the value selected in the radiogroup. Clicking 'All', 'Div 2' and 'Div 3' does exactly what it is supposed to do, but clicking 'Div 1', which should show all records from -6 to 16 does not show the negative numbers. Any thoughts on why this should be would be appreciated.