[Solved] Help on Database Insert lazarus / 16-Databases ---------------------------------------------------------------------- daveinhull · 2017-10-08 16:57 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT Stakeholders.*, Stakeholder_Types.SH_TypeFROM 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 ---------------------------------------------------------------------- valdir.marcos · 2017-10-09 03:08 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 · 2017-10-10 20:41 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 ---------------------------------------------------------------------- mangakissa · 2017-10-11 06:55 Don't use * in your query, specially not with joins. Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     SELECT st.field1, st.field2, st.field3, stfield4 FROM Stakeholder_Types st     INNER JOIN Stakeholders s ON st.ID = s.T_Stakeholder_Type;     insertquery: Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     INSERT INTO  Stakeholder_Types (field1,field2,field3,field4)    VALUES (:field1,:field2,:field3,:field4); 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     UPDATE Stakeholder_Types  SET field2 = :field2, field3 = :field3, field4 = :field4)    WHERE field1 = :field1 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. ---------------------------------------------------------------------- daveinhull · 2017-10-11 08:27 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 ---------------------------------------------------------------------- daveinhull · 2017-10-11 12:59 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 ---------------------------------------------------------------------- mangakissa · 2017-10-11 14:15 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 ---------------------------------------------------------------------- valdir.marcos · 2017-10-11 15:40 Quote from: daveinhull on October 10, 2017, 10:41:09 pmI'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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   with iqryTableB do  begin    Close;    ParseSQL := False;     SQL.Clear;  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 ');    SQL.Add('Inner Join TableB B ');    SQL.Add('  on B.TableA_ID = A.ID ');    SQL.Add('Where ');    SQL.Add('  B.TableA_ID = 1234567 ');    SQL.Add('Order By ');    SQL.Add('  B.ID;');      InsertSQL.Clear;    InsertSQL.Add('Insert Into TableB( ID,  TableA_ID,  Field1,  Field2,  Field3,  Field4) ');    InsertSQL.Add('            Values(:ID, :TableA_ID, :Field1, :Field2, :Field3, :Field4); ');     UpdateSQL.Clear;    UpdateSQL.Add('Update TableB ');    UpdateSQL.Add('Set ');    UpdateSQL.Add('  Field1 = :Field1, ');    UpdateSQL.Add('  Field2 = :Field2, ');    UpdateSQL.Add('  Field3 = :Field3, ');    UpdateSQL.Add('  Field4 = :Field4 ');    UpdateSQL.Add('Where ');    UpdateSQL.Add('  ID = :ID;');     DeleteSQL.Clear;    DeleteSQL.Add('Delete From TableB ');    DeleteSQL.Add('Where ');    DeleteSQL.Add('  ID = :ID;');     Open;   // ShowMessage('InsertSQL: ' + LineEnding + InsertSQL.Text);    // ShowMessage('UpdateSQL: ' + LineEnding + UpdateSQL.Text);    // ShowMessage('DeleteSQL: ' + LineEnding + DeleteSQL.Text);  end; ---------------------------------------------------------------------- valdir.marcos · 2017-10-11 15:55 Quote from: mangakissa on October 11, 2017, 04:15:05 pmThat'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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.FormCreate(Sender: TObject);begin  with iqryTableB do  begin  FieldByName('ID').Required := False;    FieldByName('Field1').Required := False;    FieldByName('Field2').Required := False;    FieldByName('Field3').Required := False;    FieldByName('Field4').Required := False;  end;end; procedure TForm1.iqryTableBBeforePost(DataSet: TDataSet);begin  iqryTableB.FieldByName('ID').AsInteger := 1234567;end; ---------------------------------------------------------------------- daveinhull · 2017-10-11 20:11 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 ---------------------------------------------------------------------- valdir.marcos · 2017-10-11 21:22 ParseSQL := False; Quote from: daveinhull on October 11, 2017, 10:11:36 pm... 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.FormCreate(Sender: TObject);begin  with SQLQuery1 do  begin    FieldByName('ID').Required := False;    FieldByName('Field1').Required := False;    FieldByName('Field2').Required := False;    FieldByName('Field3').Required := False;    FieldByName('Field4').Required := False;  end;end; ---------------------------------------------------------------------- daveinhull · 2017-10-12 11:57 Hi valdir.macros, Many thanks for your help  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 insertI 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 valueI 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 ---------------------------------------------------------------------- rvk · 2017-10-12 12:34 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 · 2017-10-12 16:24 Quote from: daveinhull on October 12, 2017, 01:57:34 pmI've already done what you suggest. I'm just not sure what or where to do next. Any further thoughts? Quote from: rvk on October 12, 2017, 02:34:06 pmThis 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 · 2017-10-12 20:47 @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 ---------------------------------------------------------------------- mangakissa · 2017-10-13 06:23 @valdir.marcos de property required should only uses if fields are not visible on form, but requires an input into the table. That's why the primary field mostly is set to false. But it doesn't have to. In firebird I get the autoincrement value of the table first and put it in my field. In this case the default value of property required is true. In most cases the property required is very usefull, because it tells the user not set the field on blank. ---------------------------------------------------------------------- rvk · 2017-10-13 07:44 Quote from: daveinhull on October 11, 2017, 02:59:17 pmHowever 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.And this might also not even have anything to do with your insert statement. What kind of controls are you connecting to that field. Maybe that control can only handle valid numbers and not NULL. Anyway, when you create some example code things might get clear to you. If the example code works try to recreate the same controls you have in your project to see if the control is the problem. B.T.W. You also didn't mention the EXACT error message. That's usually very important to determine the problem. Trying to translate the error-message into "human-speak" is never a good idea. ---------------------------------------------------------------------- valdir.marcos · 2017-10-14 01:10 Quote from: mangakissa on October 13, 2017, 08:23:11 amthe property required should only be used if fields are not visible on form, but requires an input into the table. That's why the primary field mostly is set to false. But it doesn't have to. In firebird I get the autoincrement value of the table first and put it in my field. In this case the default value of property required is true. In most cases the property required is very usefull, because it tells the user not set the field on blank. +1 I was just providing means to @daveinhull understand what he was trying to achieve. ---------------------------------------------------------------------- daveinhull · 2017-10-14 08:48 Hi, Ok I was creating  small test setup and in doing so I found the problem. I had included the (autoincrement ID field, ie like INSERT INTO table (ID, field1) VALUE (:ID, :Field1) in the InsertSQL query and it was this that was the problem. As soon as I removed reference to this (and basically let the Access connection deal with it) everything works. I didn't then need the .Required on the fields. Many thanks for helping me work out my own problem - best way to learn - although I may have more questions now - as they say you can't work out how well a plane will fly until you put the wings on, and then it might not  Dave ---------------------------------------------------------------------- mangakissa · 2017-10-15 07:02 Then you have created a small problem. If there's not a unique field values, which is indexed, the update will fails. Example: Quotejohn  father john  grandfather jane  sister If you update a record with the value john, you're database raises an error. That's why primary keys are important. It gives you an unique record to update. It doesn't have to be like this, but I will mention it if it occurs. ---------------------------------------------------------------------- daveinhull · 2017-10-15 07:48 Hi mangakissa, The autoincrememt ID is the primary key in the database. As I said when I removed reference to it in the SQL Insert query everything works for the insert. Of course I still have it included for the Delete and Update queries. I guess MS Access will deal with it as an autoincrememt field like it does when actually working in Access. Does this sound right? Thanks Dave ---------------------------------------------------------------------- mangakissa · 2017-10-16 07:18 Yes. But if the insert record is saved to the database, your application doesn't know the new primary key if the record is not refreshed. That's an issue to think about. ---------------------------------------------------------------------- daveinhull · 2017-10-17 14:39 Hi, In thinking about this further the error is as expected. If I do an insert in MS Access, the Autoincrement field (or in FPC :ID) will be nothing until something is written to any of the other fields and then it gets it value assigned. So when I do an insert and include the :ID, it is of course Null at the point of the insert. Anyway thanks for all the help - I think I get it!