bazzer747

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.

What I've done now is created an internal calculated float field 'Exact2', converting the hyphen and Null values to 99, and set the IndexFieldNames to 'Exact2' and the SetRange now works correctly.

Hi Uwe, Darn, was hopeful there. 🙂 But just the same. However, it got me thinking deeper! An issue I have sometimes when a seemingly obvious problem might not actually be so 'clear cut'. The Field 'Exact' is used in a View made up from several tables, one of which owns the 'Exact' field. When I look at the design I can see the field is set as VarChar(4)! The field is 'supposed' to be (and should be) set as a float field as it shows golf handicaps that range from -6 to 54. I'm 'guessing' that the field was set as a varchar as some people don't have a handicap so the field is either NULL or has been set to '-'. I saw that just perusing all the values (about 100,000 of them) and then it dawned on me! I can see that this is why the SetRange doesn't work, as I've assumed (that dreadful word) the field is a number and it's actually a character. My solution is to (maybe) create a new float field which the SetRange will actually work on correctly. Sorry for this error on my part, but many thanks for steering me which caused me to find this out....

Hi, What I didn't show in my code snippet was this: dm.vEnrolments.IndexFieldNames:= 'Exact'; 'Exact' being the field the SetRange is acting upon. I did read Cary Jensen's FireDac book and took this idea of using SetRange setting the IndexFieldName to the field in question. Which works fine for all values from 0 upwards - it's just why does it not work on negative numbers that puzzles me.

FireDac query

Exotic? In what way? Is it known NOT to work in these instances?

I have a RadioGroup setup with all letters of the alphabet (for filtering surnames on first letter). The items are entered A, B, C, D, E ... etc and are spread over 9 columns and 3 rows (dictated by the space available on a form. However, the buttons don't display in a left-to-right format but a top-down format, so along the top row I see A, D, G, J ... etc. and A, B, C are column 1, D, E, F column 2 and so on. This layout isn't very User friendly in my view, and I suppose I could add the items in an order that visually fixes it so the top row reads A, B, C, D ... etc. Just wondering if there is another way? (Couldn't see a property that does this; BiDiMode I thought might do it but doesn't).

Hi, Often I have a project with many breakpoints to help in debugging. When I'm done with the session of debugging I no longer need those breakpoints. I know I can manually remove them one by one, but is there a way to remove them all at once to save time? I read that these are stored in the .dsk file, but is the only way to remove them is to edit this file and delete them there? Deleting that file, whilst an option, doesn't seem to be a good thing to do as it holds many other settings.

Hi, I have a button on a form that I initially have disabled, as I don't want it clickable until a couple of comboboxes on the form have values selected. When the two comboboxes have been selected I enable the button. However, a first click on the button does nothing. A second click actions the code behind the OnClick event - which is to open up another form. I have a breakpoint on the OnClick event but nothing happens on the first click. On the second click the breakpoint kicks in and takes me to the OnClick event. It's as though the first click never happened. Any thoughts on why this should be would be appreciated.

So easy! And obvious! Must look before asking :-). Thanks

Hi, I see Embarcadero have released 10.4.1. I'm especially happy they are fixing problems found in prior versions, more so than any 'new' features. However, yet again they say you need a total uninstall and reinstall for this update. What I don't understand is why a full uninstall is necessary for a 'minor' release. This isn't 10 to 11, or even 10.4 to 10.5 - this is one level further - 10.4 to 10.4.1! A full install causes me all sorts of issues. I have a few add ons which I I have to wait on other companies to provide their own updates to the new version which can take weeks. Recall the problems with the Konopka VCL components when 10.4 arrived, it was a couple of weeks before they provided and updated set in GetIt and that component suite is owned by Embarcadero! Even Ray Konopka couldn't understand why this happened. Some companies must have many more add-ons than I have and suffer more than I do with this issue? Surely for a minor upgrade all that is needed is replacing the files that have changed, which can't be that many (being a minor upgrade)?

Many thanks for the info, especially the blog by Darian, very clear explanation of why the 'problem' exists and why it won't go away soon. This is one of the reasons I find this forum so useful and view it regularly. My issues are minor in comparison to the larger world which uses Delphi on a more professional and deeper level than I ever will.

That's a bit pedantic. Ace Reporter is a suite of components as is eDocEngine, I accept, but these are the 'add-ons' I add on to my Delphi. They are applications I add to my Delphi, and need installation routines to integrate them. Hence all my comments above. So if you don't like my term add-on, please substitute with component suites.

eDocEngine and Ace Reporter I see the Konopka VCL components install OK via Getit.

No, I ran the installer letting it uninstall/reinstall keeping settings. The fact the add-on vendors needed to provide me with an update also meant they would not have worked using their previous installers. Are you suggesting that any add-ons, which have been installed OK in 10.4, will work in 10.4.1? This I hope for and will see what happens when my 'test' install on my laptop completes.

Traditions should be ditched if they don't provide a happy client base 🙂 When I went to 10.4 from 10.3 none of my add-ons worked and needed re-installs. I've contacted my vendors and asked this question, still awaiting their reply. My feeling is they will need an update, even though this is a minor upgrade. I hope for otherwise. Yes, I'm installing on my other machine to see what's what.

Hi, I mainly use Outlook to send emails, and on some I attach a document. My code mainly revolves around this line: vMailItem.Attachments.Add( filename ); (vMailItem being the Outlook item object. However, what I'm finding is that after I've sent an email with a file, if I want to send another email but without a file, the file is still attached to the email. I've cleared the edit box which holds the file name (which I use to get the name of the file). I use a boolean variable to say whether there is an attachment or not and that code does bypass the above line, but the previous file is still being attached. Is there a method which can remove any previous file attachments to an email, like 'vMailItem.Attachments.Remove' ?

Anders, That code works perfectly, many thanks, so my problem is solved. With Outlook closed, when I run the code the email is created and I can see Microsoft Outlook (32 bit) in Task Manager. Even after the VarClear( Outlook ) executes Outlook is still there. When I close or send the email Outlook then closes. When the line GetActiveOleObject('Outlook.Application') is run I get an error message: 'Operation unavailable', but the email still gets created and displayed/sent. This only occurs in debug mode. When I run in normal mode I don't get an error message, but as above Outlook opens only on the vMailItem.Display line, and only closes when I send/delete the actual email.

After I've done a vMailItem.Send (or .Display), I issue a VarClear( Outlook ). This is for every individual email sent.

Anders, try Outlook:= GetActiveOleObject('Outlook.Application'); except Outlook:= CreateOleObject('Outlook.Application'); end; vMailItem := Outlook.CreateItem( olMailItem ); .. and later on ... if gvbAttached = True then vMailItem.Attachments.Add( gvAttachedFile ); //(gvbAttached being boolean which determines if file is to be attached, and gvbAttached being the location/name of that file).