Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2003-03-17 : 13:59:50
|
| I am having a problem with ADO 2.7 mishandling identity fields inclient-side cursors. I am using VB6 and SQL Server 7. When arecordset using an identity field has this code: rs.addnewrs.field...rs.field...rs.updatepast this point the rs!Id has a value of 0. This error doesn't occurin ADO 2.5, which does receive the identity correctly. Also, theIRowSetIdentity is set to true. Anyone has a workaround or solution?Sarah Berger MCSD |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-17 : 14:24:37
|
| Don't use rs.addnew.If all access is via SPs you won't have this (and lots of other) problems.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2003-03-17 : 14:57:45
|
| Yeah, I'm aware of that...(sigh)But I don't like sp's for one reason:If you change a table's structure, you must change the sp and the front end, rather than only the front end. Yuck!Sarah Berger MCSD |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-17 : 15:39:44
|
| >> If you change a table's structure, you must change the sp and the front end.no, no, no, no, no.That's the whole point. If you change the database structure you don't have to change the app only the SPs.The SPs give an abstraction of the database structurre for the business requirements.If the requirements change you change the app and maybe SPs. If the database structure chages you change the tables and SPs.Only if the structure changes are caused by a application requirements change do you have to change them all. And the change is made easier by the existance of thye SPs as the database interface can be tested separately from the app. You can also probably change the database separately from the app.But then a lot has been written about advantages of the abstraction SP layer. As you go you will probably continue to find problems that would not have occurred.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2003-03-20 : 11:10:32
|
quote:
Only if the structure changes are caused by a application requirements change do you have to change them all. And the change is made easier by the existance of thye SPs as the database interface can be tested separately from the app. You can also probably change the database separately from the app.
Why else would you change the database structure? To experiment with new data types?Mostly, you're right, because it's not really that different using SPs and recordsets as far as the app code is concerned, but my point still holds on having to change 3 places, with me always forgetting to change one place or the other only using recordsets (app code) and tables.Edit: On second thought: wrapping the addnew into an SP wouldn't solve the current problem. I am getting out the new identity value with a stored proc, but this problem happens in the app after that. Scenario: User opens Customers form, does a search that returns (a recordset of) 10 customers, clicks a New button, enters a new customer in a detail form, the detail form adds it to the table and retrieves the new Identity value that it assigns to a local (to the detail form) variable, the user closes the detail form, the variable holding the new identity value dies, and then the user clicks the row of the newly added customer in the Customers form, presumably to modify or view information in the detail screen, and the recordset hasn't retrieved the new identity value and the customer isn't properly associated with the rest of his info.Sarah Berger MCSDEdited by - simondeutsch on 03/20/2003 12:08:41 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-20 : 13:50:45
|
| I agree with Nigel. The more you make an application be almost purely a presentation layer, the better off you'll be. Hey, it's Client server isn't it? Use the power of the box.MOONigel, you mentioned there are a lot of articles on this, got any locations."abstraction SP layer" doesn't return anything close here.Brett8-) |
|
|
|
|
|
|
|
|
ADO 2.7 mishandling Identity columns