handling run-time error

Posted on 1998-06-21
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
  • 2

Accepted Solution

bin_huwairib earned 140 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

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
Determine Range to Select 5 48
Getting warning: You are about to delete 1 row(s) 9 48
Advice in Xamarin 21 79
How to read File Date Created using VB6 8 39
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

777 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