Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 21st, 2006, 06:03 AM
Registered User
 
Join Date: Aug 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default database concurreny problem with vb6 and sql2000

I have two tables one for transactions and another for balance
The table design:
Table1(AutoID(PK),AccountNo,Valuedate,DrAmt,CrAmt )
Table2(AccountNo(PK),Valuedate(PK),Balance )
ValueDate can be any order.Suppose you enter valuedate 12/08/06 then you can enter 20/08/06 also enter 15/08/06 etc.
Valuedate can be previous or future
In table1 one record add for per transaction
In table2
if the enter valueDate is not in the table for the entry Account then new record add with previous max(Valuedate) date balance with +- new amount. and also update all balance field greater than the entry valueDate balance for the entry AccountNo.

if the enter valueDate is in the table for the entry Account then update all balance field >= the entry valueDate balance
this is the scenario.

I use beginTransaction and CommitTransaction becoz of two table will be modified for per transactions
and use command object with sql query for insert and update.

Every thing is ok.
But this is a multi user mode. if more than one user update balance table(Table2) with same account at a time then
balance will not consistence. because suppose in 123 account valuedate 12/08/06 balance 500. Same time two user
entry for 123 account with valuedate 13/08/06 and 14/08/06 100 and 200 credit.After their insert 13/08/06 balance will be
600 and 14/08/06 balance will be 700. but it is wrong with 14/08/06 balance it must be 600+200=800
How can I apply that more than one user cannot update/insert for same account when transaction is in live?
but other Account entry also be possible.

The code Like---------
rs as new adodb.recordset
cmd as new adodb.connection
'cnn is my connection
on error goto err1:
cnn.beginTransaction
cmd.activeconnection=cnn
cmd.commandtype=adcmdtext

cmd.commandtext= someting for get max previous Valuedate Balance
set rs=cmd.execute
cmd.commandtext= insert in Table1 query
cmd.execute
cmd.commandtext= update Table2 query
cmd.execute
'if needed
cmd.commandtext= insert Table2 query
cmd.execute
cnn.committrans
exit sub

err1:
cnn.rollback
msgbox err.description
--------------
help me
shamim



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
database and vb6 Probie VB Databases Basics 3 August 2nd, 2007 01:14 PM
SQL2000 Database available with any one ..? SoftMind BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 9 September 13th, 2006 10:48 AM
Database Printing Recordset VB6 Hondacars VB How-To 1 March 11th, 2006 06:15 AM
how to save images in sql2000 using vb6 vaneza VB How-To 5 August 17th, 2005 03:08 AM
Upgrade from SQL7 to SQL2000 - Problem with an SP paulymac SQL Server 2000 2 December 10th, 2004 05:20 AM



All times are GMT -4. The time now is 02:40 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.