Forum > Databases
[Solved] Help on Database Insert
daveinhull:
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:
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:
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:
Don't use * in your query, specially not with joins.
--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- 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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- 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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- 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:
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
Navigation
[0] Message Index
[#] Next page