Solved

handling run-time error

Posted on 1998-06-21
3
390 Views
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....

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

<FOrm4>
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.
0
Comment
Question by:kian042298
  • 2
3 Comments
 
LVL 5

Accepted Solution

by:
bin_huwairib earned 140 total points
ID: 1463928
kian,

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:

Err_ShowMessage:
 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
0
 

Author Comment

by:kian042298
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.
0
 
LVL 5

Expert Comment

by:bin_huwairib
ID: 1463930
kian,

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

Example
=======
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....
 '..................

Exit_Form_Load:
 On Error Goto 0
 Exit Sub

Err_Form_Load:
 MsgBox Err.Description
 Resume Exit_Form_Load
End Sub

Sub ConnectToSQL(Param1 As String)
 On Error Goto Err_ConnectToSQL

 ' Your code....
 '..................

Exit_ConnectToSQL:
 On Error Goto 0
 Exit Sub

Err_ConnectToSQL:
 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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now