Why rows are duplicated for a Table?

Question

I am working on some sample codes from the Microsoft Press Book -- ADO.net 2.0. I downloaded and installed the sample database AdoStepByStep to our SQL Server 2000. For the Sample Application, Chapter - 03, clicking the Load Listboxes button, then Show Orders button showed an error msg on the code, dr("EmployeeID") = .GetInt32(0)-- When casting from a number, the value must be less tahn infinity. I found only for 1 Employee, the Show Orders button worked fine. After debugging, I found the Table EmployeeList and CustomerList have duplicated rows -- e.g. 2 rows for each of 9 Employees Trying to delete the extra row(s) showed an error msg: : The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows). My questions are: 1. Why there are the duplicated rows for EmployeeList and CustomerList tables? Both the EmployeeID and CustomerID are not the PK, why the extra rows can not be deleted? 2. Why there is one Employee that was working fine for Chapter - 03 application? Reviewing the database can not see any unique difference between this Employee and other emplyees. 3. What do the error messages mean? Thanks.

Answer 1

Well, this is probably not the place to answer this question, and we should probably move the question to an ADO.net forum.  But, if you want to know more about how to fix the structures, if you would post the script, we can probably tell you what is wrong.

1. Duplicated rows mean that you are missing a unique or primary key constraint.  Strategically adding these constrains can eliminate duplicates (well, after you remove the dups first, since the constraints won't allow you to add them)

2. Not sure, would need to see it

3. THe error likely means that you are trying to delete using a value that needs to be unique, but > 1 row is being affected by the query. Grids seem to do this often because you are clicking on one row and deleting it, but the number of rows affected by the query is greater than the number of rows expected to be deleted.  I often include such checks in the stored procedures that I write.

---

Louis