Solved

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

Posted on 1997-11-08
2
343 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 350 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now