Recent

Author Topic: [Solved] Help on Database Insert  (Read 12644 times)

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
[Solved] Help on Database Insert
« on: October 08, 2017, 06:57:30 pm »
Hi,

I would appreciate a little help on inserting into a an access database, probably really easy, but I'm just missing something.
My SQL string includes an inner join and as I suspect it would, it says it is read only when I try to append a record.
I think I must create my own InsertSQL, but could someone give  me a clue or point me in the right direction to learn about this.

Code: Pascal  [Select][+][-]
  1. SELECT Stakeholders.*, Stakeholder_Types.SH_Type
  2. FROM Stakeholder_Types INNER JOIN Stakeholders ON Stakeholder_Types.ID = Stakeholders.T_Stakeholder_Type;    

Stakeholders has about 10 fields in it which are linked to controls on a form.
One of the fields points to an entry in the Stakeholder_Types which I want to show on the form instead of just the ID.

When I create an InsertSQL it no longer says it is read only, but I can't get the InsertSQL correct to update the just the Stakeholders table.

Any help or direction would be good, not just to solve this problem but to learn a little more about accessing databases.

Many thanks in advance
Dave
« Last Edit: October 18, 2017, 09:58:28 am by daveinhull »
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Help on Database Insert
« Reply #1 on: October 09, 2017, 05:08:53 am »
Have you read?
http://wiki.freepascal.org/Lazarus_Database_Overview

And seen?
Lazarus SQL database video tutorial - YouTube
https://www.youtube.com/watch?v=pq2oCiJePHo

Lazarus SQL database tutorial 2 - YouTube
https://www.youtube.com/watch?v=ewNjqFXqDDc

Lazarus SQL database tutorial part 3 - YouTube
https://www.youtube.com/watch?v=urEdbyV_AbE

Lazarus Tutorial : Creating, editing, and searching a Firebird database ...
https://www.firebirdnews.org/lazarus-tutorial-creating-editing-and-searching-a-firebird-database-printing-a-report-stored-procedures-and-clientdatasets/


As a general SQL information, you can read (SELECT) many joined tables, but you must create (INSERT), update (UPDATE) or delete (DELETE) only one table at a time. That is a CRUD:
https://forum.lazarus.freepascal.org/index.php?topic=37893.0

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Re: Help on Database Insert
« Reply #2 on: October 10, 2017, 10:41:09 pm »
Hi valdir.marcos,

I'd already been through a couple, but have been through then all again now, but they don't really answer my query.

I'm still not sure how I setup the InsertSQL in the Object Inspector to just update the min table (Stakeholder), i.e. ignoring the inner join to the Stakeholder_Type.

Any further help would be really appreciated so I can understand how the SQL query works with the other three SLQ queries in the Object Inspector.

Thanks
Dave
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Help on Database Insert
« Reply #3 on: October 11, 2017, 08:55:26 am »
Don't use * in your query, specially not with joins.
Code: SQL  [Select][+][-]
  1.     SELECT st.field1, st.field2, st.field3, stfield4 FROM Stakeholder_Types st
  2.       INNER JOIN Stakeholders s ON st.ID = s.T_Stakeholder_Type;    
  3.  
insertquery:
Code: SQL  [Select][+][-]
  1.     INSERT INTO  Stakeholder_Types (field1,field2,field3,field4)
  2.     VALUES (:field1,:field2,:field3,:field4);
  3.  
Be carefull with primary keys. Mostly they are autoincrement. This fields has to be set to property required fields to false.
updatequery:
Code: SQL  [Select][+][-]
  1.     UPDATE Stakeholder_Types  SET field2 = :field2, field3 = :field3, field4 = :field4)
  2.     WHERE field1 = :field1
  3.  
In this case you use your primary key to save the updated record. This is a unique value, so only one record will be saved.

I don't know which database you're using, but on firebird I create my primary id before I save my record to the database.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Re: Help on Database Insert
« Reply #4 on: October 11, 2017, 10:27:35 am »
Hi mangakissa,

Many thanks for the feedback and information, very useful for helping me understand, not just giving me the answer. I'll investigate your suggestions further and see what I can work out.

Thanks
Dave
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Re: Help on Database Insert
« Reply #5 on: October 11, 2017, 02:59:17 pm »
Hi,

Ok, so I've followed the advice and removed the .* and added specific fields to the SQL statement.
I've also changed the InsertSQL to the example shown and no longer received Dataset is read only (as I would probably expect now that I've specified an InsertSQL.

However when I run it, I get an error message saying that I'm trying to assign a Null value to a non variant - probably for a field that is connected to a form control which doesn't have anything in it. I could set all the field to a default value which would probably get rid of this error, but the question is why does it work when I leave it the ParseSQL to sort things out if I do not include an INNER JOIN and yet still leave controls empty?

I don't think I've fully grasp the concepts of these SQL fields. I guess I'm asking "How do you insert a record when the underlying SQL/Dataset includes an INNER JOIN to another table.

BTW I'm using an MS access database.

As always, thanks in advance for any help.

Dave

Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Help on Database Insert
« Reply #6 on: October 11, 2017, 04:15:05 pm »
That's a problem with your primary key.
I explained it earlier. Make your fields persistent in desingtime. Go to your primary field and set the property required to false.

http://wiki.freepascal.org/SQLdb_Tutorial1
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Help on Database Insert
« Reply #7 on: October 11, 2017, 05:40:45 pm »
I'd already been through a couple, but have been through then all again now, but they don't really answer my query.

I'm still not sure how I setup the InsertSQL in the Object Inspector to just update the min table (Stakeholder), i.e. ignoring the inner join to the Stakeholder_Type.

Any further help would be really appreciated so I can understand how the SQL query works with the other three SLQ queries in the Object Inspector.

There are many ways. This is a very simple manual example for Firebird, it should be similar for Microsoft Access:
Code: Pascal  [Select][+][-]
  1.   with iqryTableB do
  2.   begin
  3.     Close;
  4.     ParseSQL := False;
  5.  
  6.     SQL.Clear;
  7.     SQL.Add('Select A.ID, A.Field1, A.Field2, A.Field3, B.ID, B.TableA_ID, B.Field1, B.Field2 B.Field3, B.Field4 From TableA A ');
  8.     SQL.Add('Inner Join TableB B ');
  9.     SQL.Add('  on B.TableA_ID = A.ID ');
  10.     SQL.Add('Where ');
  11.     SQL.Add('  B.TableA_ID = 1234567 ');
  12.     SQL.Add('Order By ');
  13.     SQL.Add('  B.ID;');
  14.  
  15.  
  16.     InsertSQL.Clear;
  17.     InsertSQL.Add('Insert Into TableB( ID,  TableA_ID,  Field1,  Field2,  Field3,  Field4) ');
  18.     InsertSQL.Add('            Values(:ID, :TableA_ID, :Field1, :Field2, :Field3, :Field4); ');
  19.  
  20.     UpdateSQL.Clear;
  21.     UpdateSQL.Add('Update TableB ');
  22.     UpdateSQL.Add('Set ');
  23.     UpdateSQL.Add('  Field1 = :Field1, ');
  24.     UpdateSQL.Add('  Field2 = :Field2, ');
  25.     UpdateSQL.Add('  Field3 = :Field3, ');
  26.     UpdateSQL.Add('  Field4 = :Field4 ');
  27.     UpdateSQL.Add('Where ');
  28.     UpdateSQL.Add('  ID = :ID;');
  29.  
  30.     DeleteSQL.Clear;
  31.     DeleteSQL.Add('Delete From TableB ');
  32.     DeleteSQL.Add('Where ');
  33.     DeleteSQL.Add('  ID = :ID;');
  34.  
  35.     Open;
  36.  
  37.     // ShowMessage('InsertSQL: ' + LineEnding + InsertSQL.Text);
  38.     // ShowMessage('UpdateSQL: ' + LineEnding + UpdateSQL.Text);
  39.     // ShowMessage('DeleteSQL: ' + LineEnding + DeleteSQL.Text);
  40.   end;
  41.  

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Help on Database Insert
« Reply #8 on: October 11, 2017, 05:55:08 pm »
That's a problem with your primary key.
I explained it earlier. Make your fields persistent in desingtime. Go to your primary field and set the property required to false.

http://wiki.freepascal.org/SQLdb_Tutorial1


@daveinhull
Maybe, you need some extra settings:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.FormCreate(Sender: TObject);
  2. begin
  3.   with iqryTableB do
  4.   begin
  5.     FieldByName('ID').Required := False;
  6.     FieldByName('Field1').Required := False;
  7.     FieldByName('Field2').Required := False;
  8.     FieldByName('Field3').Required := False;
  9.     FieldByName('Field4').Required := False;
  10.   end;
  11. end;
  12.  
  13. procedure TForm1.iqryTableBBeforePost(DataSet: TDataSet);
  14. begin
  15.   iqryTableB.FieldByName('ID').AsInteger := 1234567;
  16. end;

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Re: Help on Database Insert
« Reply #9 on: October 11, 2017, 10:11:36 pm »
Thanks all,

Ok so I've been through the tutorial again, but it doesn't really at anything about .required := false or around the other SQL statements in the Object Inspector.

I've also tried setting up .Required ;= false to all fields as suggested but I still get a MS Access error saying I'm trying to assign a null value.

What I don't understand fully is that if I just use a simple SQL statement such as
SELECT ID, Field2, Field3, Field4 From Table1

And do an insert from a set of assigned edit controls it works even if none of the field are filled in; it even sorts out the autoincrement field.

However as soon as I include an inner join to another table it fails with either the dataset is read only (without InterSQL setup) or assigning a Null value (if I set up an InsertSQL)

I'm really going around in circles here.

So I have

SQL:
SELECT Stakeholder.ID, Stakeholders.T_Stakeholder_Type, Stakeholders.SH_Name, Stakeholders.SH_Phone, Stakeholder_Types.SH_Type
FROM Stakeholders INNER JOIN Stakeholder_Types ON Stakeholders_Type.ID = Stakeholders.T_Stakeholder_Type; 

InsertSQL:
INSERT INTO Stakeholder (ID, T_Stakeholder_Type, Stakeholder.SH_Name, Stakeholders.SH_Phone, Stakeholder_Types.SH_Type)
VALUES (:ID, :T_Stakeholder_Type, :Stakeholder.SH_Name, :Stakeholders.SH_Phone, :Stakeholder_Types.SH_Type);

I've set all the fields to .Required := False

All fields are attached to data aware controls.

And I get the MS access error trying to assign Null to non variant.....

Again any further help would be much appreciate.

Thanks
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Help on Database Insert
« Reply #10 on: October 11, 2017, 11:22:19 pm »

ParseSQL := False;
...
What I don't understand fully is that if I just use a simple SQL statement such as
SELECT ID, Field2, Field3, Field4 From Table1

And do an insert from a set of assigned edit controls it works even if none of the field are filled in; it even sorts out the autoincrement field.

However as soon as I include an inner join to another table it fails with either the dataset is read only (without InterSQL setup) or assigning a Null value (if I set up an InsertSQL)
...

As you already did, make one only table to work, then change SQLQuery1.ParseSQL to False. This will prevent InsertSQL, UpdateSQL and DeleteSQL to be rebuilt.
Then change SQLQuery1.SQL to join another table.
Try to use it and tell us if it works.

SQLQuery1.SQL may contain none, one or many JOINs, but InsertSQL, UpdateSQL and DeleteSQL must contain only one table. And setting SQLQuery1.ParseSQL to False will make their content manually manageable.

Please, inform which fields of your SQLQuery1 are required or not:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.FormCreate(Sender: TObject);
  2. begin
  3.   with SQLQuery1 do
  4.   begin
  5.     FieldByName('ID').Required := False;
  6.     FieldByName('Field1').Required := False;
  7.     FieldByName('Field2').Required := False;
  8.     FieldByName('Field3').Required := False;
  9.     FieldByName('Field4').Required := False;
  10.   end;
  11. end;

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Re: Help on Database Insert
« Reply #11 on: October 12, 2017, 01:57:34 pm »
Hi valdir.macros,

Many thanks for your help  :D

I've already done what you suggest.
  • I added a simple query with one table and ParseSQL true and it works when I do an insert
  • I changed the query to include another table on an inner join and left everything else the same and (of course) it fails with a dataset is read only.
  • I change the ParseSQL to false and added a seperate query for InsertSQL which refers to just the first table and it fails with an Access reported error about assigning a Null value
  • I added code to set which field is required and it still errors with an access Null assignment error.
Note that the fields are assigned to data aware controls and in both set-ups (e.g. single table or two table) the controls contain exactly the same information (i.e some controls have entries and others don't - but it is the same in both cases).

I'm just not sure what or where to do next.

Any further thoughts?

Many thanks
Dave
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Help on Database Insert
« Reply #12 on: October 12, 2017, 02:34:06 pm »
This is really hard to diagnose without knowing the table definitions.

But setting required to false isn't going to help you if the field is defined as NOT NULL.
Your T_Stakeholder_Type for instance is a foreign key which points to another table. If you have declared that field NOT NULL you need to fill it correctly.

Otherwise try to make a small example project with a small database and see if it happens there too. If it does you can post that example.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Help on Database Insert
« Reply #13 on: October 12, 2017, 06:24:45 pm »
I've already done what you suggest.

I'm just not sure what or where to do next.

Any further thoughts?

This is really hard to diagnose without knowing the table definitions.

Otherwise try to make a small example project with a small database and see if it happens there too. If it does you can post that example.

@daveinhull
Make a small sample project with a small sample Microsoft Access database and attach them to this thread so we can check what you might be missing.

daveinhull

  • Sr. Member
  • ****
  • Posts: 297
  • 1 divided by nothing must still be 1!
Re: Help on Database Insert
« Reply #14 on: October 12, 2017, 10:47:02 pm »
@valdir.marcos, @rvk,

Thanks for getting back to me and yes, I'll produce a small example programme, see if the same error occurs and then attach it.

Really appreciate your time.

in the meantime, I'll have a look at the T_Stakeholder_Type foreign key and look at making sure it is defined.

Thanks again.
Dave
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

 

TinyPortal © 2005-2018