Response to Quality Central Bug

This is a quality central "bug" (#7827) that I've responded to. It shows how to make the bdp components work for data-aware controls in asp.net where you are using an identity column in SQL Server.

-----------

Report from Delphi for .NET / Database [Add a report in this area]

Report #: 7827 Status: Reported
BDPDataadapter cannot update MSSQL table with Identity field
Project: Delphi for .NET Build #: D8 Update 2
Version: 8.0 Submitted By: Gerald Tavares
Date Reported: 4/6/2004 3:08:18 PM Report Type: Crash / Data loss / Total failure
Severity: Serious / Highly visible problem
Platform: 95, 98, 2000, NT, XP
Resolution: None Resolved in Build: None
My Rating: Not Rated - [Click Bar to Rate]

My Votes: None
Vote for Report
Overall Rating: 1.00

(1 total ratings) Total Votes: None

Description
Cannot update a MSSQL Database Table which uses a auto incr. identity field as a primary key. The update sql is incorrect and causes errors when the autoupdate or update is called,
Steps to Reproduce
None
Workaround
None
Attachment
None


Comments -- [Add a New Comment]

James Merrill at 4/15/2004 8:25:41 AM - [Reply to this comment]
Why no simple example of the incorrect SQL that's generated?


Micromet Software Developer at 5/19/2004 3:16:02 AM - [Reply to this comment]
1 - Drop a bdpConnection component on the form and connect it to your sql server. I'm connecting to database Northwind (I'll be using table Employees, which has an autoincrement key called EmployeeID)

2 - Drop a bdpDataAdapter on the form and click "Configure Data
Adapter..." (right popup menu, or select the adapter and click the hyperlink at the bottom of the object inspector). It'll link automatically to the connection component.

3 - In the "Command" tab, select "dbo.Employees" from the "Table" list box, check the "Optimize" checkbox, then hit "Generate". You'll now find the following 4 sql statements in the tabs below:

************************
SELECT:
SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath FROM dbo.Employees

UPDATE:
UPDATE dbo.Employees SET EmployeeID = ?, LastName = ?, FirstName = ?, Title = ?, TitleOfCourtesy = ?, BirthDate = ?, HireDate = ?, Address = ?, City = ?, Region = ?, PostalCode = ?, Country = ?, HomePhone = ?, Extension = ?, Photo = ?, Notes = ?, ReportsTo = ?, PhotoPath = ? WHERE EmployeeID = ?

INSERT:
INSERT INTO dbo.Employees (EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

DELETE:
DELETE FROM dbo.Employees WHERE EmployeeID = ?
************************

The Update and Insert statements are incorrect. To fix the update statement, you need to remove EmployeeID from the SET clause. To fix the insert statement, you need to remove EmployeeID from the field list, and remove one questionmark/comma pair from the VALUES list. So the fixed statements are as follows:

(nb: including blobs in the insert statement seems to break (causes Multiple Step OLE-DB errors). So, I've also removed the Photo and Notes fields).

************************
UPDATE:
UPDATE dbo.Employees SET LastName = ?, FirstName = ?, Title = ?, TitleOfCourtesy = ?, BirthDate = ?, HireDate = ?, Address = ?, City = ?, Region = ?, PostalCode = ?, Country = ?, HomePhone = ?, Extension = ?, Photo = ?, Notes = ?, ReportsTo = ?, PhotoPath = ? WHERE EmployeeID = ?

INSERT:
INSERT INTO dbo.Employees (LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, ReportsTo, PhotoPath ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
************************

Make these changes. You can check that the select statement works using the preview tab (it will, don't bother :-). In the Dataset tab, choose "New Dataset" (you can leave the default name if you have no preference).

4 - Click "OK". The adapter will be set up, and the new dataset will be created. Now, we need to go modify the adapter a little to make it work. In particular, you will see in both the insert statement and the update statement that the parameters in the parameters collection and the question marks in the insert or update SQL (in CommandText) are out of alignment. We need to fix this.

5 - Select the adapter, then in the object browser expand the "InsertCommand". Click on the Parameters collection, then click the build button "...". You will see that the first parameter is "EmployeeID". "Remove" this parameter (it's not actually present in your modified SQL). Also, remove Photo and Notes, as they have been removed from the SQL (see my note above in step 3). Then click "OK".

6 - Select the adapter, then in the object browser expand the "UpdateCommand". Click on the Parameters collection, then click the build button "...". You will see that the first parameter is "EmployeeID" and the last one is "EmployeeIDOriginal". You only need the latter, so select "EmployeeID" at the top of the list, and "Remove" it. Click "OK".

---

You are done! This adapter should now perform correctly for data aware controls such as the DBWeb controls, allowing insert, update and delete. Oh, and don't forget to go from design view to code view and back before doing any work with your DataSet tables collection, or it wont update itself correctly. Just click the tabs in the status bar for the .pas file, then back to Design. Weird, but true, and a very useful thing to do in most circumstances in D8. Saving a lot is also a really good idea!