Wrong query in a macro

English
,
#1

Hello.

I would like to insert all the rows from the ITE_ID column of the T_ITEMS table to the ITE_ID column of the T_MAIN_SUB table (MainForm and SubForm internal forms). With this macro, I have an error message : T_MAIN_SUB.MAI_ID column not found.

Sub InsertItems

	Dim objContext As Object
	Dim objDatabase As Object
	Dim objConnection As Object
	Dim objStatement As Object
	Dim strSQL As String
	Dim objForm As Object
	
	objContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
	objDatabase = objContext.GetByName("dbPrimusTest")
	objConnection = objDatabase.GetConnection("","")
	objStatement = objConnection.CreateStatement()
  
	strSQL = "INSERT INTO T_MAIN_SUB (ITE_ID) SELECT ITE_ID FROM T_ITEMS WHERE (T_MAIN_SUB.MAI_ID = T_MAIN.MAI_ID)"

	objStatement.ExecuteUpdate(strSQL)

	objConnection.Close()
	
	objForm = ThisComponent.DrawPage.Forms.GetByName("MainForm")
	objForm.Reload()
	objForm.Last()
	
End Sub

I think here’s a problem with the query.

Thanks.

dbPrimusTest.odb (14,9 Ko)

#2

Hello,
The error is correct - there is no table named in any FROM selection for tableT_MAIN_SUB and therefore the field MAI_IDcannot be located. As this table you are attempting to insert into is a junction table, it is not clear as to what you are trying to accomplish.
Are you attempting to copy ALL items from T_ITEMS for the MAI_ID shown on the form?

#3

Correct, (T_MAIN_SUB.MAI_ID = T_MAIN.MAI_ID) is not in the table. Theoretically, this simple query does the job:

INSERT INTO T_MAIN_SUB (ITE_ID) SELECT ITE_ID FROM T_ITEMS

But I forgot something because I have this error message:

Attempt to insert null into a non-nullable column: column: MAI_ID table: T_MAIN_SUB

Problem with the foreign key. However, the ON UPDATE CASCADE is configured.

For your question, yes. Be able to copy the rows of the ITE_ID column of T_ITEMS in ITE_ID of MAIN_SUB, like this screenshot but in one click.

screenshot
screenshot962×817 51.3 KB

#4

Ok, it’s works with :

INSERT INTO T_MAIN_SUB (ITE_ID) SELECT ITE_ID FROM T_ITEMS EXCEPT SELECT ITE_ID FROM T_MAIN_SUB

But after reload, rows are not stored.

#5

Try this:

Option Explicit
Sub InsertItems
    Dim objController As Object
	Dim objDatabase As Object
	Dim objConnection As Object
	Dim objStatement As Object
	Dim strSQL As String
	Dim objForm As Object
	Dim nMAI_ID as integer
	
	objForm = ThisComponent.DrawPage.Forms.GetByName("MainForm")
	nMAI_ID = objForm.Columns.MAI_ID.getint
    objController = ThisDatabasedocument.CurrentController
    if not objController.isconnected then objController.connect
    objConnection = objController.activeConnection
    objStatement = objConnection.CreateStatement
    strSQL = "INSERT INTO T_MAIN_SUB (MAI_ID,ITE_ID) SELECT " & nMAI_ID & ", ITE_ID FROM T_ITEMS"

	objStatement.ExecuteUpdate(strSQL)
	objForm = ThisComponent.DrawPage.Forms.GetByName("MainForm")
	objForm.Reload()
	objForm.Last()
End Sub
#6

Thank you, but there is a problem before the macro is executed. After entering the date, since the record is not yet stored in the T_MAIN table, an error message indicates that there is a breach of referencial integrity (MAI_ID), which is logical. But if I create before a record directly in the T_MAIN table, your macro works perfectly.

#7

That is because the push button is on the main form. Move it to the sub form and the main form item will be automatically updated.
.
Edit:
The sample posted gave me problems with the primary key field on the main form when creating a new record. Had to delete & replace it. That with moving the button had all working.

#8

Yes, the button in the subform, I didn’t pay attention when I inserted it.

#9

Thanks to all, it’s working properly.

However, one detail remains to be resolved. In the example database, the 24 rows (ITE_LABEL) of the T_ITEMS table are classified from Item 01 to 24 in the same order as their key (ITE_ID). In a real database, for example, a T_STUDENTS table, there will be STU_ID, STU_LAST_NAME and STU_FIRST_NAME that will not be ordered.

STU_ID	STU_LAST_NAME	STU_FIRST_NAME
--------------------------------------
1		Tanner			Roger
2		Grant			Jessica
3		Morgan			George
4		Doerti			Sarah
5		Ambers			Elena
...

When inserting, I would like these lines to be sorted by STU_LAST_NAME. I added an ORDER BY but it doesn’t make any difference.

#10

Hello,
This latest comment about sorting name makes no sense. The inserted items are going to a junction table and the code you accepted produces results such as:

Screenshot at 2022-12-18 09-38-26
.
The names should not be in the junction table (although I still have no understanding as to why all records are copied). The names you refer to would be in another table (result sorted as you wish) from the junction link. Has your request changed? Starting to wonder as to the design you have compared to what you may need but not clearly specified yet.

#11

The goal is to copy the names of a table of people into the subform to enter notes. And it would be ideal if alphabetical order were taken into account.

In the subform, instead of the T_MAIN_SUB table, I tried with this SQL order.

SELECT
MAI_ID,
ITE_ID,
ITE_LABEL,
MAI_NOTE
FROM
T_MAIN_SUB
INNER JOIN T_ITEMS ON T_MAIN_SUB.ITE_ID = T_ITEMS.ITE_ID

The order is good but an entry in any row of the MAI_NOTE column is reflected in all other rows.

#12

Sorry, do not see the practicality here. To me there is a high waste of space and duplication of information.
.
Edit:
.
Possible better method is to have Student table as master and a sub table with dates and comments. A lot less data and no macros.

#13

Here is a much more specific database.

dbPrimusTest2.odb (18,6 Ko)

#14

Yes, as I thought. Lot of duplication and wasted storage. At the least, Rating and Comments belong in another table.

#15

There is indeed an unnecessary space consumption for the WOR_COMMENT column, which I can integrate into another table with a junction, but not for the WOR_RATING column so each row is always filled. But either way, it won’t solve the alphabetical ordering problem.

#16

.
Sure it can. Class->Students->daily info (dates, ratings, comments)
.
Duplication of student names is a great waste. Again no macros. Student table easily presented in whatever sequence wanted.
.
Again, design is an issue and I believe there is much more to be re-done just on this section (form) alone.

#17

Students are not duplicated in T_WORKS_SUB. Only their STU_ID key is integrated as required. Their name just displays through the list box query.

SELECT (STU_LASTNAME || ' ' || STU_FIRSTNAME), STU_ID FROM T_STUDENTS ORDER BY STU_LASTNAME ASC

#18

Still duplicating the ID which in that design makes it worse. You can use the drop down (intentionally or accidentally) and change the name leaving a duplicate. Doesn’t need to be.
.
Still see as design issue - now increasing.
.
Edit:
Tried to look at this again for some better solution but too many questions keep arising. For example it appears that as many as 16 Works records can be generated per day 8 subjects with 2 work types. Hard to imagine time needed rating each (I would not want ANY ?rating? - just comments). On top of that throw in a Subject line. Adding a student; deleting one - dates for each. Much more.
.
At this point I have nothing further to add.

#19

I’m not sure what’s bothering you about the design. Replace T_STUDENTS by T_PRODUCTS and T_WORKS_SUB by T_ORDERS_SUB (or T_INVOICES_SUB), the operating mode would be exactly the same. The difference here ? We insert all items at every work.
.
You can’t put the same student in a work twice, it would violate referential integrity. And as for the listbox, it is the only control possible to display lastname and firstname from a query. Just disable it (enabled = false) so that you can’t touch it.
.
Now you talk about the amount of work the teacher does to grade students. It is quite obvious that in reality there is not one job per day and per discipline. In addition, each student is scored for a work, but the comment can be very short and/or affect only a few students.

#20

In order to get alphabetical classification when running the macro, I modified the table like this with a new column with primary key and added an ORDER BY ITE_LABEL in the macro query.

Replace this :

CREATE TABLE T_MAIN_SUB (
	MAI_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1, INCREMENT BY 1) NOT NULL,
	ITE_ID INTEGER NOT NULL,
	CONSTRAINT PK_MAI_ITE PRIMARY KEY (MAI_ID, ITE_ID),
	CONSTRAINT FK_MAI_MAI FOREIGN KEY (MAI_ID) REFERENCES T_MAIN (MAI_ID),
	CONSTRAINT FK_MAI_ITE FOREIGN KEY (ITE_ID) REFERENCES T_ITEMS (ITE_ID)
);

By :

CREATE TABLE T_MAIN_SUB (
	MAS_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1, INCREMENT BY 1) NOT NULL,
	MAI_ID INTEGER NOT NULL,
	ITE_ID INTEGER NOT NULL,
	CONSTRAINT PK_MAS_ID PRIMARY KEY (MAS_ID),
	CONSTRAINT FK_MAI_MAI FOREIGN KEY (MAI_ID) REFERENCES T_MAIN (MAI_ID),
	CONSTRAINT FK_MAI_ITE FOREIGN KEY (ITE_ID) REFERENCES T_ITEMS (ITE_ID)
);

It was finally quite simple.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.