Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Client Batch Cursor???????????????????

Posted on 1997-11-08
2
Medium Priority
?
355 Views
Last Modified: 2008-03-04
I want to write a client/server named orders with a detailed grid in in with all product in that order. This is not a problem. The problem is that I want to use the client/batch cursor library. BUt I don't get that. If you insert a new row you don't need a connection to the SQL-server. This is what I don't understand, if the table has an identity column and you insert a new column and after that you call the update method. How is it possible that the identity value is created without a connection to SQL-server? Oh, by the way I use the remote data objects. Are there any guides on the internet how to write visual basic client/server programs using MS-RDO and client/batch cursor to create master detailed forms where you can insert and delete from records and also do this in the subform?
0
Comment
Question by:evertd
  • 2
2 Comments
 
LVL 9

Accepted Solution

by:
cymbolic earned 1400 total points
ID: 1089073
I think you are in trouble trying to use server generated identity keys, foreign key linked to sub tables, in the same batch cursor update and forms entry system.  Identity keys are only generated on the server.  The batch cursor library allows you to disassociated a resultset from a connection (but you had to have made the connection originally, and you need it later to update the batch), work on it locally, then batch update it to the server.  Since you don't know what the new keys are until they are generated, you can't insert them into another tables rows as foreign keys on the same batch cursor update.  You can think of the batch cursor library almost as a local form of the SQL server transaction log.  The main difference is you get the ability to detect and repair colliding batch updates, but that's additional coding and logic on your part, so you're better off picking a use that is uniquely restricted, to avoid a lot of conflict management.  

Incidentally, for a complete description of batch cursor use and appropriateness in particular circumstances, get "Hitchhiker's Guide to Visual Basic & SQL Server" by W. R. Vaughn, from Microsoft Press.  If you don't buy it, at least read the information on the new batch cursor library at your local Barnes & Noble.

The batch cursor library doesn't work well when there is a lot of mutliuser access against the data you are accessing, so choose  your situations carefully.  
0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1089075
To know your own keys, and use them to both create primary and related tables at the same time, you have to generate your own primary keys so you can have them on hand to place into your related tables.

The best method is to use some algorithm for key generation that works off of unique required data columns in your primary table.

Alternatively, you can update using a two step process with server generated identity columns.  Insert your primary row, then read it to get the generated key, then use that to save your related rows in other tables.

You have to handle simultaneous updates through error handling.  The best methods take considerable coding to manage.  Ideally, you'd like to present to your user the new row values along with his changes, and allow him to make a decsion about what to do.  It never seems to be quite that simple, however, especially when you have mulitple rows to update, they somehow have a logical correlation, and only some of them don't make it!

I recommend you borrow and/or buy "Visual Basic & SQL (server)" by W. R. Vaughn (Microsoft Press), then read it!  It goes into detail about using the batch cursor library and alternatives for collision management.  
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question