Recent

Author Topic: How to set SQLQuery1 FieldDefs to 0 items by code?  (Read 14599 times)

Elmug

  • Hero Member
  • *****
  • Posts: 849
Re: How to set SQLQuery1 FieldDefs to 0 items by code?
« Reply #15 on: August 08, 2012, 07:54:06 pm »
Taazz,

These are good points.

The problem is that actually, the GRID is built before any column is entered, and, as long as a new Query has been sent, OnColEnter will get confused because the Grid is still trying to use the previous values.

So I do believe that the way to do what I aim for can be solved by before sending the Query: DBGrid.Destroy and recreate it right after.

I have now a test button that shows that destroy works, but I have not figured how to create again DBGrid1. There would be no problem then setting its properties.

Another test button to do after destroy: DBGrid1.Create(Form1) or DBGrid1.Create(Self) crash the app. But maybe my create clause is wrong. So help if you know how to create again the DBGrid1, so it's like when the app starts.

Thanks.


Elmug

  • Hero Member
  • *****
  • Posts: 849
Re: How to set SQLQuery1 FieldDefs to 0 items by code?
« Reply #16 on: August 08, 2012, 07:59:46 pm »
I just found this, which maybe will work:

 with TDBGrid.Create(Self) do
  begin
    // Set properties of the grid
    DataSource := TempDataSource; // Connect the source to the grid
  end;


I will try it.

Thanks.

ludob

  • Hero Member
  • *****
  • Posts: 1173
Re: How to set SQLQuery1 FieldDefs to 0 items by code?
« Reply #17 on: August 08, 2012, 08:20:38 pm »
What about turning off OnColEnter when you close the first dataset, change the sql, open the dataset and then turn OnColEnter again? What I think is happening is that OnColEnter is triggered several times for fields that do not exist (yet) or anymore. An exception is raised which is probably messing up the normal continuation of opening the dataset and you get into all kind of weird situations.

If turning off OnColEnter is too difficult then at least call DisableControls and EnableControls when re-opening.

Also as Taaz said, don't assume anything on the validity of SelectedField and get rid of the sigsegv's before they cause more problems in sqldb.

And forget TDBgrid.Clear. This is a public method of TCustomGrid that should have been made protected for DBGrid. Delphi has done that. You don't clear a DBGrid but close the underlying dataset or delete all rows in the dataset.

Destroying the dbgrid and recreating it is going to have the same effect as turning off and on again OnColEnter, but it is much more work.


Elmug

  • Hero Member
  • *****
  • Posts: 849
Re: How to set SQLQuery1 FieldDefs to 0 items by code?
« Reply #18 on: August 08, 2012, 08:23:07 pm »
What about turning off OnColEnter when you close the first dataset,

Hey, that sounds like a great idea...

I feel it will work, and will try it right away.

Thanks a lot!
« Last Edit: August 08, 2012, 08:41:30 pm by Elmug »

Elmug

  • Hero Member
  • *****
  • Posts: 849
Re: How to set SQLQuery1 FieldDefs to 0 items by code?
« Reply #19 on: August 09, 2012, 01:31:04 am »
What about turning off OnColEnter when you close the first dataset, change the sql, open the dataset and then turn OnColEnter again? What I think is happening is that OnColEnter is triggered several times for fields that do not exist (yet) or anymore. An exception is raised which is probably messing up the normal continuation of opening the dataset and you get into all kind of weird situations.

If turning off OnColEnter is too difficult then at least call DisableControls and EnableControls when re-opening.

Also as Taaz said, don't assume anything on the validity of SelectedField and get rid of the sigsegv's before they cause more problems in sqldb.

And forget TDBgrid.Clear. This is a public method of TCustomGrid that should have been made protected for DBGrid. Delphi has done that. You don't clear a DBGrid but close the underlying dataset or delete all rows in the dataset.

Destroying the dbgrid and recreating it is going to have the same effect as turning off and on again OnColEnter, but it is much more work.

I managed to disable/enable  the OnColEnter event at run time, at will.

But the problem continues.

I do need to try to make the DBGrid Empty, I believe.

Also, the problem is not empty fields, nor fields with any known problems, since, as said, app  works perfectly as long as I do not Query another table in the same database.

The tables are very small with 4 columns or so. And I have only a handful of rows in each. None have empty fields.

The application does have a button to delete blob images, and to load and replace them. All that works fine. In fact, I recall that when I did have empty blob cell, the app had no problem.

I don't mean to say that the suggestions are bad, either. I will incorporate some. But right now, I believe the problem is that I need to have an empty grid, to work around a solution.

Thanks.
« Last Edit: August 09, 2012, 01:33:08 am by Elmug »

geno

  • Full Member
  • ***
  • Posts: 198
Re: How to set SQLQuery1 FieldDefs to 0 items by code?
« Reply #20 on: August 09, 2012, 02:30:52 am »
Is this issue related to your other thread "DBGrid1 destroy and create.."?

Have you tried SQLQuery1.Close?  As long as you did not set the column fieldname references in the object inspector, this should essentially reset the table for a new query, as long as your query on the new table has the same number of fields as columns in the grid.
version:      Lazarus 1.9   FPC 3.0.4
   
widget set:  x-86_64-linux-gtk 2
OS:             ArchLinux/ Xfce 4.12

taazz

  • Hero Member
  • *****
  • Posts: 5365
Re: How to set SQLQuery1 FieldDefs to 0 items by code?
« Reply #21 on: August 09, 2012, 04:46:14 am »
Please provide some information on what exactly you are doing and the error you are getting. So far you have given no useful information that could help us help you solve the problem.
Try to create a small test application that show the error you are getting with as little code as possible and attache it to one of your replies. I'm not going to jump from one topic to an other trying to solve a problem I have no idea what it is.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Elmug

  • Hero Member
  • *****
  • Posts: 849
Re: How to set SQLQuery1 FieldDefs to 0 items by code?
« Reply #22 on: August 09, 2012, 04:46:39 am »
Is this issue related to your other thread "DBGrid1 destroy and create.."?

Have you tried SQLQuery1.Close?  As long as you did not set the column fieldname references in the object inspector, this should essentially reset the table for a new query, as long as your query on the new table has the same number of fields as columns in the grid.

Hi Geno,

YES, of course, I close SQLQuery1

I have no problem doing QUERIES on the same table or on other tables of the database, as long as I don't have the procedure for DBGrid OnColEnter. Setting that to None clears the issue.

Now I have a test button that toggles the OnColEnter procedure on an off at will. When OFF, no problem whatsovever.

Without change in code, just toggle the event on and off.

The DBGrid OnColEnter is not handled properly, for some reason that so far is beyond me.

But the OnColEnter procedure is handled well as long as I am in the same table. I can change queries and they work.

If I am on Table1, all is fine. But if I now say select from Table2, then SIGSEGV.

Now if I first select from Table2, all is fine. Then I select from Table1, and ditto, SIGSEGV.

3 days now and I can't find the problem. I think I'll have to do without the OnColEnter and do it by manual clicks.

Thanks.
Thanks.

geno

  • Full Member
  • ***
  • Posts: 198
Re: How to set SQLQuery1 FieldDefs to 0 items by code?
« Reply #23 on: August 09, 2012, 05:03:54 am »
OK - fine.  So now, as you have been asked repeatedly, create a small sample application that gives you the errors you describe and drop it here so someone else can actually see what you are trying to do.  It just might be a simple matter of headspace and timing issue; you can't see the solution because you are too close to the problem.
version:      Lazarus 1.9   FPC 3.0.4
   
widget set:  x-86_64-linux-gtk 2
OS:             ArchLinux/ Xfce 4.12

teos

  • Full Member
  • ***
  • Posts: 147
Re: How to set SQLQuery1 FieldDefs to 0 items by code?
« Reply #24 on: August 20, 2012, 04:57:46 pm »
Elmug,

I don't know why the code you posted compiles. And ehm... "GOTO" is jumping to different memory. That is not needed (because you're calling a procedure anyway) and the most unreliable call you can make.

But apart from that: TDBGrid refreshes it's columns when you open the dataset you are using to run the SQL statement. There is no need to free and create a new dbgrid instance or to clear the TDBGrid. In Delphi that is called "hacking". *GRIN*

I think your mistake is that you use a TFieldType as a string:

  if dbgrid1.SelectedField <> nil then begin
     if Fieldtypenames[dbgrid1.SelectedField.DataType] = 'Memo'
        then caption := 'memo';
      if Fieldtypenames[dbgrid1.SelectedField.DataType] = 'Blob'
        then caption := 'blob';
   end;

That should be:
  if dbgrid1.SelectedField <> nil then begin
     if Fieldtypenames[dbgrid1.SelectedField.DataType] = ftMemo
        then caption := 'memo';
      if Fieldtypenames[dbgrid1.SelectedField.DataType] = ftBlob
        then caption := 'blob';
   end;

Again: I stronly advise against the "GOTO" which is considered as spagetti code but also is not needed to get your code to work.
« Last Edit: August 20, 2012, 09:47:11 pm by teonieuwlande »

Elmug

  • Hero Member
  • *****
  • Posts: 849
Re: How to set SQLQuery1 FieldDefs to 0 items by code?
« Reply #25 on: August 21, 2012, 04:08:27 am »
if Fieldtypenames[dbgrid1.SelectedField.DataType] = 'Memo'

Hi  teonieuwlande,

NO, this in this function,  the square brackets amount to strings, they are a predeclared array in one of the external units provided,  of datatype names of string type. It compiles OK.

The GOTO's I found work well to avoid nested try statements, and all jumps are within a few lines away in short procedures.

Thanks for commenting.

teos

  • Full Member
  • ***
  • Posts: 147
Re: How to set SQLQuery1 FieldDefs to 0 items by code?
« Reply #26 on: August 22, 2012, 01:13:29 am »
I see the "fieldtypenames" code in db.pas indeed. And learned something new about something really not needed.. because TField.Fieldtype is commonly used for that and fieldtypenames also brings another string comparision which I don't doubt is slower than testing an enumerated type.
I would say: forget about the fieldtypenames and test for TField.Datatype.

BTW: the DBGrid has columns. I expect if you loop through columns and free them one by one, the grid is cleared.
But if you need to free and re-create a DBGrid because you have trouble with the columns, you really have some error somewhere. Like said: the dbrid refreshes its columns on a tdataset.open.

It's simple: My code doesn't produce the error you try to work around.

In your case, if you would use else if statements, only the one matching would be executed, or if you use begin..end you can use exit to get out of the loop, and if you use the case statement, also only the matching code is executed.

I hope you are aware that the event is triggered from somewhere in a unit where there it is expected that the code after the event is also executed. Your goto (if there is no more code than I have seen) does not bring you back to the point where you called it, so the code after the part triggering the event is never executed. Don't be supprised to see very unwanted behaviour. If not in this event, it will be in others.

If you don't believe what I say (because I have only 4 days of Lazarus and 15 years of Delphi):
http://www.delphibasics.co.uk/RTL.asp?Name=Goto&ExpandCode1=Yes

(btw: the link says in red that goto should NEVER be used in modern languages)

My 5 cents, the rest is up to you.
« Last Edit: August 22, 2012, 01:22:12 am by teonieuwlande »

 

TinyPortal © 2005-2018