oceandeep
Posts: 17
|
| Posted: 06/02/2014, 11:28 AM |
|
Hi,
I'm working with an Ed. grid with several fields and needs two tables to show the rows but only inserts into one of the tables. Another thing is, the table that doesn't get any data inserted is used to show some info. from the database in some fields of the grid (lets call this one Table1). The problem is, when the grid is shown it already has as many rows as there are in Table1 and the fields I got from it are filled with data, the rest of the fields that came from the other table are empty so that the user can add the info they need. When I try to add a row nothing gets inserted unless there is an empty row (from the property Empty Rows: n), and then only the last row is entered into the database.
I really need to get the grid to insert the rows that are already shown with some info, any help is appreciated.
To show how my grid must look like:
| Table1_1 | Table1_2 | Table2_1 | Table2_2 |
|aaaaaaaaa|bbbbbbbb|...............|...............|
|
 |
 |
eratech
Posts: 513
|
| Posted: 06/03/2014, 2:09 AM |
|
Hi oceandeep
I'm a little confused with your description, but it sounds like Editable Grid can do what you want.
I'm assuming you have the Datasource like SQL or Table and have joined Table1 and Table 2 on some matching key value.
For those items that need to be updated, use the Textbox and for non-updatable fields use Labels.
Depending on your requirements, you can also use the "Custom Update" feature of the Editable Grid to do precisely the update you want, and there is a "Custom Insert" as well to override the normal insert.
So, if you are showing Table 1 and Table 2, and you want to do a custom "update" but actually do an insert, change the Custom Update type to SQL and put the SQL code you want.
And remember that for an Update, the 'Custom Update' is run for each row separately, so you can also add some 'Before Insert' and 'After Insert' if you need special processing.
Hope it helps, and please try to outline what you need more clearly so we can help.
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
oceandeep
Posts: 17
|
| Posted: 06/03/2014, 11:24 PM |
|
Thanks for the reply Eric.
I'll try to be as specific as I can this time:
I'm trying to create an app very similar to the Master/Detail one in the example pack, I'm dealing with employees information, I have an emp_table and a workedhours_table, both used for the ed. grid. The record form works fine with another table.
As I tried to say before I need the emp_table only to show employees info previously added to the DB (name, last name, code, etc.) and the workedhours_table to show other fields in it (hours worked in a week, daily salary, total salary, etc.) and it's also the table where the data should be inserted, updated and deleted. I use the builder to make it and made a query to select the fields.
This grid must look like how I tried to represent to make it easier to put the extra info. about the hours each employee worked.
For the DataSource: after the grid is created it has a multi-column Id but when I run the page the grid appears with rows filled with data from the workedhours_table I added before as a test in all the fields and the final rows have only info in the emp_table fields. So this doesn't work for me since it's looking more as a grid than the editable grid I need. Looking for a way to show just the emp_table fields filled I changed the DataSource to only the emp_table, this way the grid now shows the correct amount of rows with the correct field already field and the others empty. (NOTE: I've tried this with all the fields as Textboxes and with the emp_table fields as Labels, got the same result).
Now I set Empty Rows: 0. If I leave it just like that, when I try to insert the rows I think I already have nothing gets inserted into the database; if I change Empty Rows: 1, a new row is created and after inserting only that row gets to the database. Something I just noticed is that the previous rows are understood as rows to be Updated and not Inserted since the Delete Checkbox is enabled.
Also, Custom Insert, Update and Delete are all configured as Type: Table, and Table: workedhours_table.
I tried your idea for the Custom Update but I think I couldn't do it right, changed nothing, I'll try again though... And I don't know if you'd consider it still as something to try first with my extra info.
Cheers and thank you!,
|
|
|
|
oceandeep
Posts: 17
|
| Posted: 06/04/2014, 11:10 AM |
|
Is there a way to change the Editable Grid from the Edit Mode into Add Mode?
|
|
|
|
eratech
Posts: 513
|
| Posted: 06/04/2014, 8:31 PM |
|
(I'll go through your detailed answer later)
The 'update mode' will naturally happen on existing rows, and the Inserts on the 'blank' rows.
However, you can change the Custom Update to do an INSERT instead (use the SQL method). I've done that before when I've had that requirement of displaying several items, but needing to insert the entered data into another location.
Similary, I have used the 'delete' checkbox with a custom delete to update a status_flag instead of deleting the record. You can override most of the default code if you wish.
Cheers
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
eratech
Posts: 513
|
| Posted: 06/04/2014, 8:45 PM |
|
Sorry - I should have read your longer answer, and then my previous answer again instead of repeating myself.
It sounds like you are on the right track, but if you are only wanting to Insert the records (like a list of all employees with their current hours), then you really don't need to link it to the 'workedhours_table' - you want to insert not update that table.
So try an editable grid with only the 'employees' as the source and make the fields labels (and some hidden fields with linking information, like employee_id), and add the other 'input' fields with relevant names (eg: start_time, end_time) etc but not linked to a table.
Then in the Editable Grid -> Custom Update, make it an INSERT into the 'workedhours_table' with the linking details from the hidden employee_id, and the input boxes (start_time). It can probably be an Table Insert as you are doing it all into a single table (workedhours_table)
That way you get the benefits of multiple inserts, but it is a nicer format for data entry.
If I've mis-understood, let me know 
Cheers
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
oceandeep
Posts: 17
|
| Posted: 06/05/2014, 5:52 PM |
|
Ok, maybe it is because my brain is working slower than usual right now but I got a bit confused with this: "It can probably be an Table Insert as you are doing it all into a single table (workedhours_table)"... 
Anyway, I made the editable grid with only the emp_table as datasource and the fields as labels (I used the builder too) and then added the extra fields (the ones for workedhours_table) includding the one that receives the record form id.
Then I went to Custom Update and made it SQL, there I wrote an INSERT INTO workedhours_table code with all the fields from the grid, no Where parameter. Published the page, load it, added the data in the fields, pressed submit and this kind of error showed in each row (the only difference were the numbers according to what I wrote in the last fields):
Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '15', '3', 66)' at line 1
I also tried the Custom Update as Table type and configured the fields and the table as workedhours_table. After "submitting" the rows no error showed up but also nothing was inserted into the database. Did I do something wrong? (Well, most likely... ). I thought of adding a custom code to some server event in the grid so I made one with an INSERT INTO query and pasted it in Before Submit, After Submit, Before Execute Update and After Execute Update... and so far, no luck...
Thanks!
|
|
|
|
eratech
Posts: 513
|
| Posted: 06/07/2014, 12:41 AM |
|
oceandeep,
You are on the right track, but likely a setting in the Custom Update is a little wrong. The Error message shows something is definitely not quite right.
Here is a trick to help find the MySQL error using a 'debug' flag - in the 'Common Files' part, look - in the file `db_mysql.php' (or db_mysqli.php) for the '$Debug' value around line 30 - change it from 0 to 1 on your test area and all the SQL queries run for a page will show up at the top of the page. Handy for finding out exactly what is happening.
I'll be back in the office tomorrow and I'll see if I can dig up the exact edittable grid example I had that works.
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
oceandeep
Posts: 17
|
| Posted: 06/09/2014, 8:13 PM |
|
Hey Eric,
I did the $Debug=1; thing and this are the messages I got:
1. When the page loads for the first time it only shows the Record form, after adding some info. and pressing the Add button, this data gets inserted and the Ed. Grid shows up. The queries shown are:
Debug: query = SELECT * FROM gral_workedhours_table WHERE GralWh_ID = 107
Debug: query = SELECT COUNT(*) FROM emp_table WHERE state_id = 1
Debug: query = SELECT emp_fname, emp_lname, emp_socialn, emp_dailysalary, emp_workarea, emp_code, emp_id FROM emp_table WHERE state_id = 1
2. Finished filling the empty fields in the grid, pressed Submit and this is added:
Debug: query = INSERT INTO workedhours_table(wh_code, wh_socialn, wh_emp_fname, wh_emp_lname, wh_workarea, wh_state, wh_dailysalary, wh_totalhours, wh_totalextrahours, wh_totalsalary, wh_totalextrasalary, GralWh_ID) VALUES('{emp_code}', '{emp_socialn}', '{emp_fname}', '{emp_lname}', '{emp_workarea}', {wh_state}, {emp_dailysalary}, '{wh_totalhours}', '{wh_totalextrahours}', {wh_totalsalary}, {wh_totalextrasalary}, 107);
Debug: query = INSERT INTO workedhours_table(wh_code, wh_socialn, wh_emp_fname, wh_emp_lname, wh_workarea, wh_state, wh_dailysalary, wh_totalhours, wh_totalextrahours, wh_totalsalary, wh_totalextrasalary, GralWh_ID) VALUES('{emp_code}', '{emp_socialn}', '{emp_fname}', '{emp_lname}', '{emp_workarea}', {wh_state}, {emp_dailysalary}, '{wh_totalhours}', '{wh_totalextrahours}', {wh_totalsalary}, {wh_totalextrasalary}, 107);
To see what could be happening on CC, so far I have tried changing Allow Insert and Update from 'yes' to 'no', both changed nothing in those errors. Changed the labels to textbox (when I was configuring the Custom Update Type= Table the first time none of the labels names appeared in the Parameters column while the textboxes did, so I thought it might have something to do with it), still got the same problems.
What do you think is happening?
Cheers.
|
|
|
|
eratech
Posts: 513
|
| Posted: 06/11/2014, 2:10 AM |
|
I really need to find my example, I think it might be on a client computer.
Regarding the second bunch, I would expect to see the proper SQL query rather than the '{fieldname}' in the SQL.
Yes, the input / textboxes appear in the Custom bits, not the labels. And changing the 'Allow Insert' and Allow Update to 'no' will not allow the 'Add' 'Save' buttons to appear and won't run the Insert/Update code , so that won't help.
If you can't get the Custom Update to work with a Table type, try a SQL type and put in wahtever SQL you need and select in the parameters (controls) as needed.
As I said, I need to find my example, or just make a new one as I'm sure it can be done.
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
oceandeep
Posts: 17
|
| Posted: 06/17/2014, 2:41 AM |
|
Ok I added a more proper sql query on Custom Update, sql type, also selected the parameters in accordance to the db. After I load the page, add info. and press the Submit button on the grid it's still not inserting and now I get a new error: Database Error: Cannot add or update a child row: a foreign key constraint fails (`plndb`.`workedhours_table`, CONSTRAINT `gralidfk` FOREIGN KEY (`GralWh_ID`)
REFERENCES `gral_workedhours_table` (`GralWh_ID`) ON DELETE CASCADE ON UPDATE CASCADE)
I know this is trying to say there's something wrong with my db but no matter what, I can't find it, the ID from the gral_workedhours_table, wich I need to pass onto workedhours_table, does exist since it's correctly updated by the record form. I even tried with a 'Test' db with only 3 tables, just to see if I could understand the problem but no, it happened again...
|
|
|
|
|
