New Record Problem

Classic Servoy
1

I have an addresses_mailing form on a tab panel of a People form. There is an add record button on the addresses_mailing form which executes: controller.newRecord().

This creates a new blank address record, but when I click into it I get a Cannot save form data error with the following detail: com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -194: No primary key value for foreign key ‘addresses_mailing_have_types’ in table ‘addresses_mailing’ ASA Error -194: No primary key value for foreign key ‘addresses_mailing_have_types’ in table ‘addresses_mailing’

The addresses_mailing_have_types relation is checked to allow the creation of new records, and I also set a default value for the related field id_t_address_mailing in the addresses_mailing table in Sybase.

What am I doing wrong?

Attached is a picture of my form with the generated error.

2

What are you using for the database? If its MySQL this may be the same problem I struggled with for many days. It seems there is a bug in the Mysql driver related to unsigned int values. See the following thread for more info:

3

The sybase manual tells me about error ASA-194:

Probable cause
You attempted to insert or update a row that has a foreign key for another table. The value for the foreign key is not NULL and there is not a corresponding value in the primary key

So there is a database constraint between 2 tables you violate.

4

you should not do controller.newRecord() on that adress tab, you should make a newRecord through the relation. (people_to_address.newRecord())
Or set the relation you have specified directly youreself after the call controller.newRecord()

5

I tried people_to_addresses_mailing.newRecord(), but it didn’t work.

I did manage to get the following code to work though:

controller.newRecord()
id_t_addresses_mailing = 1;
locked = 0;
controller.saveData()

I shouldn’t have to set the id_t_addresses_mailing and locked fields myself, since they are default values in the Sybase table, but at least its working now.

6

Johan,

It appears I spoke too soon. My code does create a new record, and I can enter data into it – but that data is not saved. If I close the form and come back, the new record is there, but all of the field values I entered are gone.

I imagine, I must need to do what you said:

<set the relation you have specified directly youreself after the call controller.newRecord() >

Can you tell me more about this or give me a sample line of code?

Thanks!

7

if you set it through the relation then it should be filled in for you.

else you have to do something like this at some place:

form.FOREIGNFORM.controller.newRecord()
form.FOREIGNFORM.fkdataprovider = pkofprimarytable

(if pk is used in the relation)

but the best thing to do is do it through the relation because then you don’t have to be bothered about this.

Ofcourse i can be then you have more then one relation to that table and that that relation FK also needs to be filled in. then you have to do that by hand anyway.

8

Johan,

I tried to implement your suggestion, both from a button on the main people form and also a button on the tab panel form.

The button on the main form does:

people_have_addresses_mailing.newRecord()
form.addresses.id_t_addresses_mailing = 1;
form.addresses.locked = 0;
form.addresses.controller.saveData()

This gives me an error: The undefined value has no properties, but then creates the blank record. When I try ti click into it gives me the Cannot save form data error I described above.

I also tried:

form.addresses.controller.newRecord()
form.addresses.id_people = id_people
form.addresses.id_t_addresses_mailing = 1;
form.addresses.locked = 0;
form.addresses.controller.saveData()

This also gives me the error: The undefined value has no properties, but does not create the blank record.

The button on the tab panel addresses form does:

controller.newRecord()
id_t_addresses_mailing = 1;
locked = 0;
controller.saveData()

This works best, but not good enough. It creates a blank record, lets me click into it and add data, but then doesn’t save the changes.

I also tried:

form.addresses.controller.newRecord()
form.addresses.id_people = form.people.id_people

This also gives me the error: The undefined value has no properties, but does not create the blank record.

9

you always should do all the things through the relation:

try this:

people_have_addresses_mailing.newRecord() 
people_have_addresses_mailing.id_t_addresses_mailing = 1; 
people_have_addresses_mailing.locked = 0; 
controller.saveData()
10

When I call that code from the button on the people form, it creates the new address record and lets me edit it, but the changes are not saved when I close the form. If I later reopen the form I can then make more changes which are saved. They are only not saved when the record is first created.

This may be related to something I have noticed about the id_mailing_address field which is part of the primary key. This field is set by a Sybase database trigger. When the record is first created, the id on the form appears as a value of 0 (which is actually a valid value), but when the form is closed and reopened the correct number value (1 or more) is displayed.

11

ahh you are changing data behind servoy in the database when you save?
And that data is even part of the PK??
yes then it can go wrong. You must refresh the record then! (see databasemanager)

Can’t you do that trigger youself in servoy?

12

We don’t want to do the trigger in Servoy because there is a web application that also accesses the same database.

I tried:

people_have_addresses_mailing.newRecord()
people_have_addresses_mailing.id_t_addresses_mailing = 1;
people_have_addresses_mailing.locked = 0;
databaseManager.refreshRecordFromDatabase(people_have_addresses_mailing, currentcontroller.getSelectedIndex())
controller.saveData()

but got the same result.

I also tried:

people_have_addresses_mailing.newRecord()
people_have_addresses_mailing.id_t_addresses_mailing = 1;
people_have_addresses_mailing.locked = 0;
controller.saveData()
databaseManager.refreshRecordFromDatabase(people_have_addresses_mailing, currentcontroller.getSelectedIndex())

but then I don’t get a new record on the form at all.

13

Johan,

I’ve been thinking that due to the whole database trigger situation, maybe it would be better for me to add the record to the database via a stored procedure and then refresh what the user sees.

So, I made a new method which does this:

var iArray = new Array(1);
var argArray = new Array(1);
iArray[0] = 0;
argArray[0] = forms.people.id_people;
databaseManager.executeStoredProcedure(currentcontroller.getServerName(),‘{call spAddAddMail(?)}’,argArray,iArray,0)
databaseManager.refreshRecordFromDatabase(forms.people.people_have_addresses_mailing, currentcontroller.getSelectedIndex())

That correctly adds the new record to the database, but it is not displayed on the form unless I close and reopen it.

Any ideas?

14

that is logical.
Because you are only refreshing the selected index. which is a record that is already there.

You have to do this:

databaseManager.refreshRecordFromDatabase(forms.people.people_have_addresses_mailing, -1)

but this is a pretty heavy operation.
So you really should look how you don’t do these kind of things in youre db. Using of DB identity sequences is not a problem. Because those are looked up again by servoy.

But this:

people_have_addresses_mailing.newRecord()
people_have_addresses_mailing.id_t_addresses_mailing = 1;
people_have_addresses_mailing.locked = 0;
controller.saveData()
databaseManager.refreshRecordFromDatabase(people_have_addresses_mailing, currentcontroller.getSelectedIndex())

should work fine. OR you are setting somevalue that also delete the record directly from the relation (the X = Y params of the relation don’t apply anymore)

You say that you don’t see a record at all. This also happens if you don’t do the call refreshRecordFromDB? Because that can only mean that you are setting somevalue that droppes the record out of the relation. Are you seeing that newRecord after a restart??

I really need to see a sample solution if i have to help you further.

15

Johan,

The code:

databaseManager.refreshRecordFromDatabase(forms.people.people_have_addresses_mailing, -1)

did work!

I know, and we are doing that in lots of places, but we didn’t want to do it here, because we wanted all of the addresses ids for each user to start with one and then increment from there for each user.

<Are you seeing that newRecord after a restart?? >

Yes.

Your call. I’m happy to send it to you if you want to take a peak. Or, we can leave it since your “heavy” solution did work.

Thanks!!!
16

fine by me if it now works for you.
It is still a bit strange why the selectedIndex refresh doesn’t work. (Why the record is falling out of the relation..)
But if it works now for you then it is fine by me.