RollBacking is not happening in some client systems

my server os is windows 2003 standarad edition and we installed the sql server 2000 in the os with service pack 4.
our application is developed in visual basic 6.0 and converted as a package and installed in the client system
the problem is when saving the data from the client system it stores the record partially for example i want to store the record in 4 tables from the client system but when saving it stores the record in 2 tables only it is not storeing the record in other two tables
we gave rollback statement in the application but some client system it is rollbacking properly and some client system it is not roll backing the records properly and wise wersa when deleting the records from the client system
i checked the coding thoroughly but in some cases it is not roll backing properly so i need the answer to solve this problem kindly send me the reply to this
LVL 2
rsomasundarAsked:
Who is Participating?
 
Shanmuga SundaramConnect With a Mentor Director of Software EngineeringCommented:
Hi rSomasundar,

Can you make me clear whether you are inserting records line by line or you are using the "insert into " sql statement to insert records in the table.

Before that let me give you a sugessition.

Better you try to handle one boolean variable to check whether the updation is successful or not.

For example please go through the code given below.

private sub cmdsave_click()
Dim updateprocess as boolean
on error goto errhandler:
begin transaction
  updateprocess = true
 
   sql1 = "insert into tablename1 and fieldvalues"
  connection.execute sql1
  if updateprocess = true then updateprocess = true

   sql2 = "insert into tablename2 and fieldvalues"
   connection.execute sql2
   if updateprocess = true then updateprocess = true

   sql3 = "insert into tablename3 and fieldvalues"
   connection.execute sql3
   if updateprocess = true then updateprocess = true

   sql4 = "insert into tablename4 and fieldvalues"
   connection.execute sql4
   if updateprocess = true then updateprocess = true
   
    if updateprocess = false then
           rollback trans
   else
         commit trans
    end if
exit sub

errhandler:
updateprocess =false
on error resume next
0
 
rsomasundarAuthor Commented:
Hi shasunder
i am using the insert into sql statement to store the values in the table
 
0
 
Shanmuga SundaramDirector of Software EngineeringCommented:
Hi rsomasundar,

Then I hope that you can use the above mentioned ways to solve your issue

by
d.Shanmuga sundaram
0
 
rsomasundarAuthor Commented:
Hi Shanmuga sundaram
Thanks for your Reply
My application was installed around 40 systems and at the same time 10 or 15 clients using my application and now the database is crossing 5.5gb so that when they are opening the application it is opening very slowly and retrieving the result set from the query also taking some time so that clients telling that that application is very slow
i did the index tunning and other things but still it is very slow
is there any way to increase my application speed kindly reply me

by
soma sundar
0
 
Shanmuga SundaramDirector of Software EngineeringCommented:
Hi rsomasundar,

Try optimizing your queries in the program. And also confirm the cursor type which you use in your program.

by
D.Shanmuga sundaram
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.