Trust ID PBCEX logo 300x102

Inserting record into table in SQL Server

9 years 4 months ago
Emiel
Senior Boarder
Senior Boarder
Posts: 78
More
Topic Author
Inserting record into table in SQL Server #2101
Hi,

I have a problem when trying to insert records into a table in SQL Server. I have defined a storage item for the primary key of this table as a counter. Tried it with and without an identity on this column. When I print a card, I receive the error message attached.

How can I solve this?

Kind regards,

Emiel
Attachments:
9 years 4 months ago
Craig Bechelli
Administrator
Administrator
Posts: 624
More
Inserting record into table in SQL Server #2102
Hi Emiel,

Is it only the primary key that you are trying to store back ?
Usually you need to store a few fields back such as name etc..

You do not need to set up a storage item for the primary key in this case, just set up the other storage items and define the primary key in the setup for these fields.
The primary key is automatically stored back along with the other data.

Hopefully that should solve the problem :)

Craig Bechelli
CardExchange Solutions Support Team

"If you define the problem correctly, you almost have the solution." (Steve Jobs)
9 years 4 months ago
Emiel
Senior Boarder
Senior Boarder
Posts: 78
More
Topic Author
Inserting record into table in SQL Server #2103
Hi Craig,

I removed the storage item defined for the id and still I get this error message.
It states that in the SQL statement the id is specified twice, which is odd.

Regards,

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

Could you copy the faulty SQL from the DataExchange.log into the ticket? Maybe it is a good idea to define the data definition again from cratch, by deleting the old *.dtd file and creating it again. That way, you can exclude that some of the faulty SQL got stuck in the data definition somehow.

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 #2105
Hi Rutger,

below is a 'snippet' from the log:
-- ReturnFileProxy:
INSERT INTO [ReturnFile]
([id], [Name], [ClientName], [HardwareSerialNumber], [ProcessDate], [CustomField1], [id])
VALUES (@PARAMETER0, @PARAMETER1, @PARAMETER2, @PARAMETER3, @PARAMETER4, @PARAMETER5, @PARAMETER6)
@PARAMETER0 = 
@PARAMETER1 = 
@PARAMETER2 = 
@PARAMETER3 = 27FAD647
@PARAMETER4 = 19-4-2012 14:55:20
@PARAMETER5 = 25005012163507
@PARAMETER6 = 1

-- An error occurred in .Net SqlClient Data Provider:
The column name 'id' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name 'id' may appear twice in the view definition.

Is recreating the .dtd the only solution? I really prefer not to do this.

Regards,

Emiel
9 years 4 months ago
Craig Bechelli
Administrator
Administrator
Posts: 624
More
Inserting record into table in SQL Server #2106
Hi Emiel,

I had the same issue with a customer this morning and they had the column to write data too and the primary key set the same in one of the storage items. Attached is an example.
Also make sure you are not storing the Primary key back as a storage item.

Craig Bechelli
CardExchange Solutions Support Team

"If you define the problem correctly, you almost have the solution." (Steve Jobs)
Attachments:
9 years 4 months ago
Rutger Koperdraad
Platinum Boarder
Platinum Boarder
Posts: 1566
More
Inserting record into table in SQL Server #2107
Hi Emiel,

The SQL written to the DataExchange.log suggests that there is stil a storage item for the Id column. If you are sure that that is not true, the storage item has remained somewhere in the data definition. That could be solved by redefining the data definition, as suggested above. If you want, you can post the data definition file on this forum so I can check it for any irregularities.

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 9 years 4 months ago by Emiel.
Emiel
Senior Boarder
Senior Boarder
Posts: 78
More
Topic Author
Inserting record into table in SQL Server #2108
Okay I recreated the storage items in the .dtd for the return file (without a storage item for the id) and it got me a small step further. Now the log shows the following:
-- sourcedb:
SELECT * FROM [Tabel]

-- ReturnFile:
SELECT REPLACE(TABLE_SCHEMA + '.' + TABLE_NAME, 'dbo.', '') FROM INFORMATION_SCHEMA.TABLES

-- ReturnFile:
SELECT * FROM [ReturnFile]

-- sourcedb:
SELECT [RecordID] AS CXID, [RecordID]
FROM [Tabel]

-- sourcedb:
SELECT * 
FROM [Tabel]
WHERE [RecordID] = @PARAMETER0
@PARAMETER0 = 1

-- sourcedb:
SELECT * 
FROM [Tabel]
WHERE [RecordID] = @PARAMETER0
@PARAMETER0 = 1

-- sourcedb:
SELECT * 
FROM [Tabel]
WHERE [RecordID] = @PARAMETER0
@PARAMETER0 = 1

-- ReturnFile:
INSERT INTO [ReturnFile]
([id], [ClientName], [CustomField1], [HardwareSerialNumber], [ProcessDate], [Name])
VALUES (@PARAMETER0, @PARAMETER1, @PARAMETER2, @PARAMETER3, @PARAMETER4, @PARAMETER5)
@PARAMETER0 = 1
@PARAMETER1 = A customer name
@PARAMETER2 = 25005012163507
@PARAMETER3 = 1206048327
@PARAMETER4 = 20-4-2012 9:44:02
@PARAMETER5 = 

-- An error occurred in .Net SqlClient Data Provider:
Violation of PRIMARY KEY constraint 'PK__ReturnFi__3213E83F17CE3F1E'. Cannot insert duplicate key in object 'dbo.ReturnFile'. The duplicate key value is (1).
The statement has been terminated.

So we're using two databases, one to read from (MS Access) and a return file database to write to (MS SQL Server).

The problem I have now is that it wants to add a 1 in every new record I insert. I don't know why, it looks like it's using the id from the source database (RecordID) and write it to the returnfile database, though I'm not sure. I don't want this, because I have an identity specified for the id column, so inserting values in the id field is handled by the database itself.

Is there a logical explanation for this?

Thanks in advance,

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

can you please remove the ID storage item? That one is mapped the the id column of the ReturnFile table and that column is the primary key of it. So it's not possible to write on it.

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 #2110
I'm sorry, but as I've mentioned I have no id storage item. So maybe the .dtd is corrupted as Rutger suspected.
I will recreate this .dtd and see if the problem still exists.

Thanks.

Regards,

Emiel