Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query to calculate avaerage 21 41
create an aggregate function 9 36
[SQL server / powershell] bulk delete table from CSV 8 33
Create snapshot on MSSQL 2012 3 21
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

831 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