• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

VB6 ERROR HANDLER

Hi, i have a function in my application which inserts value to the db. if a user try to insert a record that violates the unique key constraint i have used an err handler in the function like

Sub fun1
'' some code that retrieves the max val from the db and increments by 1 and does some othr code and inserts the rec which will be unique.
 If Err.Number = -2147217873 Then
    fun1
    Else
   
    MsgBox "Error :" & Err.Number & " - " & Err.Description, vbCritical
    Exit Sub
end if
End Sub


My question is, if two users gets the err handler at 2 diff times it works as it picks diff max vals from db.

 But will this loop go infinitely if two or more users hits the err handler and picks the max val, adds 1 to it and tries to insert at the same time?
0
AnnaJames77
Asked:
AnnaJames77
  • 3
  • 3
  • 2
  • +2
3 Solutions
 
Paul JacksonCommented:
There is the potential that the code will loop infinitely but this does depend on the number of users and the likelyhood they will query the database at the same time and the delay in between determining the max value and actually commiting the record to the database.
0
 
gplanaCommented:
To insert an autoincremental number is not correct to retrieve max and add 1. As you say, if 2 users are executing these instructions in parallel, it could be problematic.

What database management system are you using ? MySQL ? SQL-Server ?
If you are using SQL-Server you could consider to activate the identity property for this column. MySQL has also an autoincrement property for doing this.

You could also has this maximum value on a table and access it by using a transaction. Something like this:

BEGIN TRANSACTION
   UPDATE table_with_counters SET counter=counter+1 WHERE id_counter = my_counter;
   SELECT counter INTO :myvariable FROM table_with_counters WHERE id_counter = mycounter;
   INSERT INTO my_table (id, field2, ...., fieldN) VALUES (:myvariable, value2, ....);
COMMIT;

Maybe I could help more if you say which database management system are you using.
0
 
HooKooDooKuCommented:
The way you have the fun1 function outlined, the error handler within fun1 attempts to call fun1 again.  That is very poor design because if the error continues to occur, you keep calling the function recursivinely builing the call stack until ou run out of stack space and crash the program.

The better approach would be to RESUME within the function to try again.

Sub Fun1
TryAgain:
  On Error Goto CatchError
  Attempt to update the Database
  Exit Sub
CatchError:
  If Err.Number = -2147217873 Then
    Resume TryAgain
  Else
    Display the Error
  end if
end Sub
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
AnnaJames77Author Commented:
so what will Resume do. can you plz explain. wont it also go on in loop if the error occurs
0
 
gplanaCommented:
You should consider to redesign you function. Ifyou say which database are you using we will help more.
0
 
AnnaJames77Author Commented:
i'm using SQL-Server
0
 
AnnaJames77Author Commented:
THIS IS MY ACTUAL PROBLEM.

In my vb application, it uses a table header which has 5 columns

month   slno   PO      COL   col_sl_no
------------------------------------------------------
1            1     nnn      1         1
1            2    aop       2         1
1            3     afg       2         2
1            4     abd      1         2  
2            1    qqq       3         1
2            2    eee       1         1
2            3    ddd       4         1
2            4    ccc       3         2
-----------------------------------------------------------

right now the appln is used by a single user.  If the user selects a month, it finds the last SL_NO of the month.
 There are say, 4 COL. If the users enters a PO in the textbox and clicks enter,the application finds out to which col the PO entered by the user should be added to.(will be any of 1 to 4). And it updates in this table.  
Say if user, selects a month 1, and enters a PO, nnn, then the appln finds which COL it belongs to,say if its 1, then it finds out the last entered col_ser_no of the COL and of the month 1 from the db and enters the new PO to this db adding 1 to last col_ser_no and 1 to last SL_NO of the month. Say, row will be 1  1  nnn 1  1. Next if the user enters another PO and it belongs to same COL then finds the last COL_SER_NO,adds 1 and inserts new rec to db as 1  2  ddd  1  '2' .  

Next if the user selects another month, it finds the last SL_NO of the month and does the same above process.
This appln runs fine if a single user runs it. Now this app need to be used my multiple user. if two users try to get the last SL_NO and also the COL_SL_NO at the same time it may get duplicated.  i donot want for the same month,the SL_NO be duplicated. As well as for the same week,for the same COL,the COL_SER_NO shouldn't be duplicated.

I tried creating  two constraints like month+ SL_No and 2nd constraint as MONTH+COL+COL_SER_No. the apln throws an error but

Pls let me know how should i handle such situation like if multiple user hits the button at the very same time. i donot want to throw the constraint error to the user. is there any other way

0
 
BrianVSoftCommented:
The Resume statement (See HooKoo above) is like a "GoTo TryAgain" except that it clears the ErrorHandler Stack.. Otherwise that stack will overflow.
Most Error Handlers need to use a Resume XYZ statement for Busy + ReTry handling..
We also choose to manually increment a primary key on certain tables (to allow us to use our own import and replication functions).. We use a tiny separate table that holds these numbers in unique 'control' records. This allows us to Lock that control record while we read the next number, increment it, apply it to the new primary key; If successful, we update the control record (which releases the Lock)
This locked control record prevents others attempting to create a new primary key for a milli-second.
0
 
gplanaCommented:
Now I understand. As you want the next serial number for the selected month, an Identity column doesn't help. You should make a insert with a SELECT instead of VALUES, like this:

INSERT INTO your_table(month, slno, PO, COL, col_sl_nl)
SELECT @month, MAX(slno)+1, @po, @col, @col_sl_nl
FROM your_table
WHERE month = @month;

where @month, @po, @col, @col_sl_nl are variables.
I haven't understand fully the other variables, but you can substitute every variable on the SELECT clause by another SELECT which gets the desired value. For example:

INSERT INTO your_table(month, slno, PO, COL, col_sl_nl)
SELECT @month, MAX(slno)+1, @po, @col, (SELECT col_sl_nl+1 FROM your_table WHERE month=@month AND slno = @slno)
FROM your_table
WHERE month = @month;

The advantage of using this method is SQL-server can guarantee that an SQL instruction is executed without interferences problems. So we are executing all in a single SQL.
0
 
HooKooDooKuCommented:
Once an error has occured, and an error handler has caught the error, the error is still "active".  If another error occurs while the error is "active", then the error will throw you out of your error handler going up the call stack looking for another error handler to handle the error.

The RESUME keyword clears the current "active" error and jumps to a location specified by RESUME.  If you specify a location, RESUME acts like a "clear the error" paired with a "goto" statement.  If you use RESUME by itself, control jumps to the line that caused the error and attempts to execute the command again.  RESUME NEXT clears the error and continues execution with the command that follows the RESUME statement.

I devised an entire process for dealing with errors.  I usually have an error handler save a copy of the error into a set of variables.  Then do On Error Resume Next so that if any more errors occur, the code doesn't jump to an error handler, then I execute Resume Next to finish processing the error...

Sub Fun1
Dim theError as typeError 'UDT I defined to hold a number, description and source
  On Error Goto CatchError
  'Code that might raise an error
  Exit Sub
CatchError:
  ERROR_Save theError  'A function I wrote to save the contents of Err into theError
  On Error Resume Next  'Don't allow errors to interupt error handler (but it's not active yet)
  Resume Next  'Activate the above error handler
  'Handle the error with what ever code is needed.
  'I might display the error, or clear any data that needs to be cleared before returning from the sub
  'I might also raise the error up to the calling function adding the name of this function to the Error Source string to bulid a string that shows what the call stack looked like when the function was called.


Here's an example of my error handlers when I have a subroutine that needes to do some cleanup before raising an error up the calling stack.  All functions that start with ERROR_ are functions or subroutines I wrote to simplity error handling.

Sub Fun1
Const Source as string = "Fun1"
Dim theError as typeError
  On Error Goto CatchError
  'Code to initialize stuff
  'Code to do stuff
CloseAndExitSub:
  'Cleanup Code
  ERROR_IfActiveRaise theError, Source  'Raises an error IF there is on in theError, appending Source
  Exit Sub
CatchError:
  ERROR_Save theError
  On Error Resume Next
  Resume CloseAndExitSub
End Sub
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now