handling run-time error

Posted on 1998-06-21
Medium Priority
Last Modified: 2012-06-21
Dear friends,
    I've got a VB application with four forms. And each of the forms contain some sql statement such as insert,delete, update and select. I'm using ODBC and sql passthrough. However there would be times when my sql passthrough would have error due to some server problem(ie when server is down). Below is so called the pictorial form.

<Form 1>
Dim connect as string,db as database
connect$="ODBC;DSN=Oracle;UID='" & txt_1.Text & "';Pwd='" & txt_2.TExt & "';
set db=opendatabase("",false,false,connect)

<Form 2>
other vb codes....
sql_insert="Insert into table......"
db.execute sql_insert,dbsqlpassthrough
other vb codes....

other vb codes.....
sql_truncate="Truncate table...."
db.execute sql,dbsqlpassthrough
sql_insert="Insert into table......"
db.execute sql_insert,dbsqlpassthrough

other vb codes......
sql_delete="Delete table......"
db.execute sql_delete,dbsqlpassthrough
other vb codes...

Expert friends,
Can anyone please advise me with workable VB codes as how I should handle the run-time errors for these sql passthrough.
Q1)For statement "On error go to Error handler",are we supposed to define only once in every form or do we only need to define in one form.And in every form can we define more than once, is it necessary.

Q) Besides sql passthrough giving me run-time errors, there would be times when other of my VB Codes would give me run-time errors as well.
Therefore I would be most grateful if someone Can advise me on how I should code my application in order to handle these errors together(other vb codes and sql passthrough) elegantly.
Question by:kian042298
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Accepted Solution

bin_huwairib earned 560 total points
ID: 1463928

A1) You must define an error handler in each sub or function to get a better result and performance and to a void the regular VB error message "Run-time error 'xxxxx'".

A2) SQL passthrough errors and VB errors can be handled with the Err object, so the below error handler format will handle both of them unless you want specifically to track some errors. In this case you have to know the error number and assign its action when the handler get the error.

Here is my stander error handler:

Sub ShowMessage()
 On Error Goto Err_ ShowMessage

Exit_ ShowMessage:
 On Error Goto 0
 Exit Sub

Err_ ShowMessage:
 MsgBox Err.Description, vbCritical
 Resume Exit_ ShowMessage
End Sub

That was a global error handler, to make it specific replace the Err_ShowMessage label with the following:

 Select Case Err.Number
  Case 100
   Resume Next
  Case Else
   MsgBox Err.Description, vbCritical
   Resume Exit_ ShowMessage  
 End Select

NOTE that you must know the error number or at least the errors range, I mentioned the error no.100 just for explanation purpose.

Best regards
Ben Huwairib

Author Comment

ID: 1463929
Hi Bin_huwairib,
   I realised you always provide others with sound and solid VB codes and explanation. And I also do noticed that your ranking is going up as well. So sincerity and being helpful does, isn't it? Yes men! Bravo! Once again, thank you for all that.
   Just one more doubt to clarify with you, does it mean that I will have to declare the above section of codes in every of my VB forms and with that I would be able to capture and handle any run-time errors so long as I know the error number. If that is so where would be the best place to put in those error handling codes based on my scenario in the early question (any guidelines). Could you please enlighten me. Once again, thank you.

Expert Comment

ID: 1463930

Define it in every sub/function in each form because the handler will be in the procedure level only not for the whole form.

Suppose you have form1 with two subs: Form_Load and ConnectToSQL in this case you have to define the handler for both of the subs, it will be as follows:

Sub Form_Load()
 On Error Goto Err_Form_Load

 ' Your code....

 On Error Goto 0
 Exit Sub

 MsgBox Err.Description
 Resume Exit_Form_Load
End Sub

Sub ConnectToSQL(Param1 As String)
 On Error Goto Err_ConnectToSQL

 ' Your code....

 On Error Goto 0
 Exit Sub

 MsgBox Err.Description
 Resume Exit_ConnectToSQL
End Sub

Now you must similarly define the handler for the rest of subs/functions for this form and the other forms as well.

Ben Huwairib

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month8 days, 14 hours left to enroll

764 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