Hopefully that fixed things for you. :)
Just looking at your code I see multiple errors.
Your insert statement reads INERT.
And in your update statement you are missing a comma after :gender.
These errors should have given you an exception when executed them.
If these are typos.... Then please don't just edit your code because it can create these typos.
Post actual code.
Then another observation... You fill in Query.
But you actually perform the ExecSQL on a AsianLadiesQuery.
AsianLadiesQuery has nothing to do with Query so you actually never perform the update or insert.
And... You can't set Query.SQL.Text when you are in browse or edit mode (and you are with Query.Edit in edit mode).Interesting. It doesn't throw any errors. I have just changed the code ever so slightly. Observe this section now and see if you think it will work better:
Second... You can't use statements like insert and update an a dataset. If you use Query.SQL.Edit and Query.Findbyfield, you can't use insert/update and ExelSQL. You just use Query.Edit and Query.Post in conbination with Query.Findbyfield.I will look again. I did read that and kind of got lost. Maybe because of my BDE perspective.
If you use INSERT/UPDATE in SQL.Text you'll need to use Query.Parambyname to fill in the parameter fields.
Now you are mixing two methods if updating (Query.Edit and Query.ExelSql) and you can't do that.
https://wiki.freepascal.org/Working_With_TSQLQuery
Not knowing, what connection-parameters are set, i'm missing the Transaction.Commit after an UPDATE/INSERT
I use often aQuery.Options := [sqoAutoApplyUpdates,sqoAutoCommit] , if not i have to apply the updates and do the transaction in the correct position by myself.
It's still not working. I added a commit line (SQLTransaction1.Commit(); ) , I ran the program. It reported no errors at all. But when I look at the database, the fields I changed in the program are unchanged on disk. So what am I doing wrong now?And your Transaction is correctly linked to the Connection and Query?
Is the Data tranfered from the local Buffer to the DB-Server ? I did not found any ApplyUpdates ? See https://wiki.freepascal.org/Working_With_TSQLQuery chapter Cached Updates. This is the reason for sqoAutoApplyUpdates in the queryoptions.
Back to the roots. Have your table a PK (Primary Key) ? Is this key listed in the select statement ?
If you have a good, simple Select-Statement without joins, Query is able to autocreate the nessesary insert, update, delete messages by itself.
You mus only set the dataset in the correct state. For a simple test, put a connetion, transaction, query, dataset, dbgrid and a dbnavigator on a form. Connect these components together and set a select statement in the query. In the designer, if you make the connection and the query active you will see the actual data in the db. Make it inactive. Now make a button and set in the click the connection and query active. Make a second button and place there the applyupdates an make the connection inactive. Now run your program. You should able to browse, edit, delete insert data with the navigator and the grid.
If you close the programm and reopen, your changes should be saved. If this work you have the basics.
It's still not working. I added a commit line (SQLTransaction1.Commit(); ) , I ran the program. It reported no errors at all. But when I look at the database, the fields I changed in the program are unchanged on disk. So what am I doing wrong now?And your Transaction is correctly linked to the Connection and Query?
I even have a Transaction.Commit at the end, and nothing. I believe I don't need the Query.ExecSQLPlease post the code you have now.
Please post the code you have now.
(Your previous code mixed different methods and was incorrect)
Try to uncomment 'insert', 'update' and 'ExecSQL' lines. :)
@dseligo, do not use Query.Fieldbyname with a direct insert/update in Query.SQL.Text. It just doesn't work.
Try to uncomment 'insert', 'update' and 'ExecSQL' lines. :)
Just tried. No effect. All three lines are uncommented. Here's the code now (including extra lines I threw in just to ensure I was getting the right values from the form):
Yup. Your mixing your methods again.I see. I am getting mixed messages that's why it's not working. I updated it and things look good on that end. Now for a related question. I updated the (incomplete) code to include querying from another table, one with a One-To-Many relationship with Table1 (i.e 1 record in Table1 can have multiple records in Table2 referencing it). That works great for the select, but when I try to update something screwy goes on. Here's what I tried, but I get an error saying the operation cannot be performed on an inactive dataset:
Where is your Query.Post ???
And where is the assignment of :idno for the update ?
@dseligo, do not use Query.Fieldbyname with a direct insert/update in Query.SQL.Text. It just doesn't work.
@HopefulGuy only use the correct statements with each other.
(following on an open query with a result-set)
Query.Edit or Query.Insert (with a select in sql.text so you have a result dataset)
Query.Fieldbyname
Query.Post
Query.Applyupdates
Query.Transaction.commit (note that the open dataset is closed unless you add something to the options)
Now, to move on to SQL programming,
Use separate TSQLQuery --- I mean drop another TSQLQuery component, and use it for SQL statements of 'insert into table1 values ...' or 'update table1 set ....' etc.
That works great for the select, but when I try to update something screwy goes on. Here's what I tried, but I get an error saying the operation cannot be performed on an inactive dataset:About this code:
You can almost program an entire address-editing program with zero lines of code if you use TDBEdits and a TDBNavigator 8)
1. With your program, try insertingIt did, thanks!
ReadQuery.First;
just after Requery.Open; . Not sure for now whether this will solve the problem.
2. You can define calculated field for sCBXEntry.I saw the TDBComboBox but I couldn't see how to make it a compound of multiple fields.
3. Then, you don't have to populate combobox manually as you did. There is a TDBComboBox component.
Before you try combobox, just drop a DBGrid and a DataSource on the form, and set DBGrid's datasource property to the DataSource, and DataSource's DataSet to ReadQuery. And see whether the query result is displayed correctly whenever you open ReadQuery.
What am I doing wrong this time?Your ReadQuery has datasource filled in, but you don't work with master/client relations here yet. And your datasource does not connect to any query. A datasource needs to connect with a query. Not the other way around (unless you are using master/client relation which you are not).
So you need to switch that. Fill ReadQuery in in DBData datasource.
And remove DBData as datasource from ReadQuery.
If you are using TDBEdits you don't need to set any fieldbyname.
That's the whole point of the TDBEdit.
Next... You also don't need a separate WriteQuery. You can just switch the ReadQuery to ReadQuery.Edit or .Insert and the fields will automatically be changed (after the ReadQuer.Post and ReadQuery.ApplyUpdates and Transaction.Commit.
If you change that you'll see you'll have hardly any code at all left :D
One extra note... You need to decide before/on opening the form if you need to insert or edit. For example on retrieving the record. If recordcount = 0 for the select you need to do the ReadQuery.Insert and if it's 1 you do a ReadQuery.Edit. Then the dataset is in edit,mode and you can change the fields. Your save-button only does the post, applyupdates and commit and close-form.
You also need to remove the whole WriteQuery. You don't need a second Query to write. You can just use the ReadQuery. It's designed to be read AND write.If you are using TDBEdits you don't need to set any fieldbyname.
That's the whole point of the TDBEdit.
I tried without, and nothing saved at all. I can comment them out and try again, I suppose.
The TSQLQuery.Transaction is a base clase. It always points to a TSQLTransaction but essentially it is just an empty class. You can either use casting like TSQLTransaction(Query.Transaction).Commit or just use SQLTransaction1.Commit directly.Next... You also don't need a separate WriteQuery. You can just switch the ReadQuery to ReadQuery.Edit or .Insert and the fields will automatically be changed (after the ReadQuer.Post and ReadQuery.ApplyUpdates and Transaction.Commit.
Speaking of Transaction.Commit, the previous example given to me was Query.Transaction.Commit. But if I look at Query.Transaction there IS no Commit routine. Now if I reference the Transaction component directly, it's there. But for some weird reason when I reference it as Query.Transaction the Commit routine is missing.
Yes.. but you don't do an Insert in SetupForm. You do it far too late in SaveButton.One extra note... You need to decide before/on opening the form if you need to insert or edit. For example on retrieving the record. If recordcount = 0 for the select you need to do the ReadQuery.Insert and if it's 1 you do a ReadQuery.Edit. Then the dataset is in edit,mode and you can change the fields. Your save-button only does the post, applyupdates and commit and close-form.
That's already done in SetupForm(). It takes 2 paramaters. A string (NEW or EDIT) and an integer (record number to view or 0 if new record). The mode gets stored in a strFrmMode variable. That variable is queried in the btnSaveEdit() routine. IF mode = NEW then it calls (currently) WriteQuery.Insert. If the mode is EDIT then it calls WriteQuery.Edit
Oh, and you never mentioned how to fix the form so that blank fields are ACCEPTABLE.If you do it correctly directly on ReadQuery then you don't have blank fields and it will work automatically. So remove the WriteQuery and it should be fine.
Oh, and you never mentioned how to fix the form so that blank fields are ACCEPTABLE.If you do it correctly directly on ReadQuery then you don't have blank fields and it will work automatically. So remove the WriteQuery and it should be fine.
BTW there isn't even a need to pass NEW of EDIT. If you pass a 0 as ID it will mean there is no record found and you want to do insert. If you pass a valid ID there is a record found and you do an Edit.
That's all you need.
Edit: o, one final note. You have set all the fields in you db to NOT NULL. That's also why you need to fill in everything. Just remove the NOT NULL and you can insert blank fields.
Actually, in MariaDB (and I think mysql) NOT NULL just means it cannot be a NULL value. An EMPTY value is still perfectly fine. It just stores an empty value rather than recording NULL in the field. At least, that's been my experience.I wasn't sure what the value would be if the TDBEdit would be empty. I thought it might have been NULL in that case. But if it works now I guess it's just an empty string.
BTW there isn't even a need to pass NEW of EDIT. If you pass a 0 as ID it will mean there is no record found and you want to do insert. If you pass a valid ID there is a record found and you do an Edit.
Something like this:
procedure TfrmEditInfo.SetupForm(strID : string);
begin
intPersonNo:=StrToInt(strID);
ReadQuery.SQL.Text:='select * from Contact where IDNo = ' + strID;
ReadQuery.Open();
if ReadQuery.RecordCount = 0 then
ReadQuery.Edit
else
begin
ReadQuery.Insert;
ReadQuery.FieldByName('IDNo').AsInteger := intPersonNo; // so even a nonexisting id > 0 will be ok
end;
end;
By the way, if I want to branch and read from a second table and have DB Aware controls read from the second, then I take it I need another TSQLQuery and Another TDatasource? Right now, notes is just a text field. What I'd like to do is convert it into a separate table with both text and image components.