[Solved] Help on Database Insert

[mangakissa]

@valdir.marcos

de property required should only uses if fields are not visible on form, but requires an input into the table.
That's why the primary field mostly is set to false. But it doesn't have to. In firebird I get the autoincrement value of the table first and put it in my field. In this case the default value of property required is true.
In most cases the property required is very usefull, because it tells the user not set the field on blank.

[rvk]

However when I run it, I get an error message saying that I'm trying to assign a Null value to a non variant - probably for a field that is connected to a form control which doesn't have anything in it.

And this might also not even have anything to do with your insert statement.

What kind of controls are you connecting to that field. Maybe that control can only handle valid numbers and not NULL.

Anyway, when you create some example code things might get clear to you.
If the example code works try to recreate the same controls you have in your project to see if the control is the problem.

B.T.W. You also didn't mention the EXACT error message. That's usually very important to determine the problem. Trying to translate the error-message into "human-speak" is never a good idea.

[valdir.marcos]

the property required should only be used if fields are not visible on form, but requires an input into the table.
That's why the primary field mostly is set to false. But it doesn't have to. In firebird I get the autoincrement value of the table first and put it in my field. In this case the default value of property required is true.
In most cases the property required is very usefull, because it tells the user not set the field on blank.

+1

I was just providing means to @daveinhull understand what he was trying to achieve.

[daveinhull]

Hi,

Ok I was creating  small test setup and in doing so I found the problem.

I had included the (autoincrement ID field, ie like INSERT INTO table (ID, field1) VALUE (:ID, :Field1) in the InsertSQL query and it was this that was the problem. As soon as I removed reference to this (and basically let the Access connection deal with it) everything works.

I didn't then need the .Required on the fields.

Many thanks for helping me work out my own problem - best way to learn - although I may have more questions now - as they say you can't work out how well a plane will fly until you put the wings on, and then it might not 

Dave

[mangakissa]

Then you have created a small problem. If there's not a unique field values, which is indexed, the update will fails. Example:

john  father
john  grandfather
jane  sister

If you update a record with the value john, you're database raises an error.
That's why primary keys are important. It gives you an unique record to update.

It doesn't have to be like this, but I will mention it if it occurs.

[daveinhull]

Hi mangakissa,

The autoincrememt ID is the primary key in the database. As I said when I removed reference to it in the SQL Insert query everything works for the insert. Of course I still have it included for the Delete and Update queries.
I guess MS Access will deal with it as an autoincrememt field like it does when actually working in Access.

Does this sound right?

Thanks
Dave

[mangakissa]

Yes. But if the insert record is saved to the database, your application doesn't know the new primary key if the record is not refreshed. That's an issue to think about.

[daveinhull]

Hi,

In thinking about this further the error is as expected. If I do an insert in MS Access, the Autoincrement field (or in FPC :ID) will be nothing until something is written to any of the other fields and then it gets it value assigned. So when I do an insert and include the :ID, it is of course Null at the point of the insert.

Anyway thanks for all the help - I think I get it!