Trust ID PBCEX logo 300x102

Inserting record into table in SQL Server

9 years 4 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Inserting record into table in SQL Server #2111
Hi Emiel,

On the last page of the data-definition wizard, where you define the storage items, please select each storage item and click on teh Advanced options button. A small window will pop-up where you can specify whether records for existing primary keys should be updated in teh target table or whether CardExchange should always insert a new record. It seems that you use the latter option instead of the first one.

regards,
Rutger.

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)
9 years 4 months ago
Emiel
Senior Boarder
Senior Boarder
Posts: 78
More
Topic Author
Inserting record into table in SQL Server #2112
Updating an id is not what I want. I want to always INSERT records, not UPDATE them.

If I read your reply correctly, it is not possible to let CE insert records when you are using an auto-increment field. Because under the hood CE always generates an INSERT statement including the auto-increment id, which causes an error from the dbms.

I need to write records back to a return file, including a chip serial number. This return file is a database table. I have tried different ways to do this, but no luck. Should I turn off auto-increments in the database and let CE do the incrementing of ids? Could you please tell me what the best approach is to let CE insert records into an empty database table?

Regards,

Emiel
9 years 4 months ago
Sergio Agostinho
Administrator
Administrator
Posts: 508
More
Inserting record into table in SQL Server #2113
Hi Emiel,

In the 4th page of the Data-definition wizard, where you can define the "Allow editing database records" you can find the "Primary-key properties" settings. You need to select the one that says "Primary keys generated by the database". That option will avoid that CardExchange generates the keys for the table if it already contains and incremented key definition.

Regards
Sergio

With best regards
Sergio Agostinho
9 years 4 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Inserting record into table in SQL Server #2114
Hi Emiel,

Since you always want to insert records, you have to specify that in the Advanced options of the storage-item page. For the storage items itself, you have to take into account that the field you specify as Primary key for a storage item will be used to store the value of the Primary key field of the main table you are connecting to, that is, the value of [Tabel].[RecordID]. Since you are always inserting, this field cannot be the primary key of the [ReturnFile] table. If the [ReturnFile] table has a primary key defined in the database, this primary key should be filled automatically and not be mentioned in the storage item definition.

If this doesn't help you out, please create a document with all screen shots of your data-definition wizard, because we are trying to give suggestions based on rather incomplete information.

kind regards,
Rutger.

Rutger Koperdraad
Senior Software Engineer

"A reasonable man adapts himself to the world. The unreasonable man persists in trying to adapt the world to himself. All progress, therefore, depends on the unreasonable man." (Bernard Shaw)
9 years 4 months ago
Emiel
Senior Boarder
Senior Boarder
Posts: 78
More
Topic Author
Inserting record into table in SQL Server #2115
Hi Rutger, Sergio,

Unfortunately both of your suggestions don't seem to solve my problem; it still complains about the id column. No matter what I specify, it always wants to use the id in the INSERT statement.

Enclosed is the data definition wizard in screen prints.

Best regards,

Emiel
Attachments:
9 years 4 months ago
Sergio Agostinho
Administrator
Administrator
Posts: 508
More
Inserting record into table in SQL Server #2116
Hi Emiel,

Can we make a remote session tomorrow morning where i can look to this issue directly on your system?
My timezone is GMT 0, please let me know what is the best time to do it, my suggestion is to do it between 10:00 and 11:00 in the morning.

Regards
Sergio

With best regards
Sergio Agostinho
9 years 4 months ago
Emiel
Senior Boarder
Senior Boarder
Posts: 78
More
Topic Author
Inserting record into table in SQL Server #2117
Hi Sergio,

I'm sorry, but for security reasons this is not allowed. I can send you the complete .dtd if you want.
Please let me know if you need any other information.

Thanks in advance,

Emiel
9 years 4 months ago
Sergio Agostinho
Administrator
Administrator
Posts: 508
More
Inserting record into table in SQL Server #2118
Hi Emiel,

Ok, so if possible send me the files below:
the .dtd file (this is the database defintion);
the metadata.cdd file (this has the mappings between the card end the database)
the .ced file (this is the card design, you can remove any backgrounds or logos, i just need the fields)
the sql script to create those 2 tables including all primary and foreign keys (or if it's a tests a database a backup of it will be fine)

You can send those files in a zip file to our support email.

Regards
Sergio

With best regards
Sergio Agostinho
9 years 4 months ago
Emiel
Senior Boarder
Senior Boarder
Posts: 78
More
Topic Author
Inserting record into table in SQL Server #2119
Okay I have sent the requested files to the e-mail address.

I'm very curious about what's wrong here.

Regards,

Emiel
9 years 4 months ago
Sergio Agostinho
Administrator
Administrator
Posts: 508
More
Inserting record into table in SQL Server #2120
Hi Emiel,

if you change the primary key of the ReturnFile table to not be identity CardExchange will use the recordid key from the source database as a primary key to store the info on the target database, instead of auto increment it.

Regards
Sergio

With best regards
Sergio Agostinho