Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1879
Members Online:
1
Guests Online:
36

Total Post History
Posts:
81448
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
mwojcik

Other

FAQ
All times are UTC [ DST ]

Google Ads

Debugging OleDB errors

Please wait...

Debugging OleDB errors

Postby Naomi on Tue Sep 01, 2009 3:58 am

Hi everybody,

Do you have any tips on debugging OleDB errors? Also, do you know how reliable error line numbers are? Would they include comments, so if we see error on line number N, would it mean the exact number N in the code?

Unfortunately we can not run this application from VB, so we have to work with exe.

Thanks in advance.
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated

Re: Debugging OleDB errors

Postby tarwn on Tue Sep 01, 2009 11:05 am

If you cannot get at the code that is calling a stiored procedure or complex SQL statement, then the next best thing is going to be to get profiler and capture the calls. Once you have captured the calls then you can use SQL Studio to step through the individual calls (if there are any), print or select from any temporary tables in between steps, etc. It be both easier and faster to debug the issue if you take the VB code out of the equation and focus on just working from the SQL side first.

Um, yeah...I'm going to need you to come in on Saturday -- Bill Lumbergh, Office Space
User avatar
tarwn
LTD Admin
LTD Admin
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897
 
Posts: 3838
Joined: Fri Oct 12, 2007 11:10 am
Location: Raleigh, NC, USA

Re: Debugging OleDB errors

Postby gmmastros on Tue Sep 01, 2009 12:53 pm

I don't know if you can get the actual SQL if you are using an Access database. If this was SQL Server, you could run profiler.

The line numbers are usually pretty accurate. They do not include comments. But, a previous "glitch" on a previous line could be the actual cause of the error.
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2367
Joined: Tue Oct 09, 2007 5:19 pm
Unrated

Re: Debugging OleDB errors

Postby tarwn on Tue Sep 01, 2009 2:05 pm

Sorry, I didn't realize we were talking about MS Access.

Um, yeah...I'm going to need you to come in on Saturday -- Bill Lumbergh, Office Space
User avatar
tarwn
LTD Admin
LTD Admin
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897LTD Gold - Rating: 897
LTD Gold - Rating: 897
 
Posts: 3838
Joined: Fri Oct 12, 2007 11:10 am
Location: Raleigh, NC, USA
Unrated

Re: Debugging OleDB errors

Postby gmmastros on Tue Sep 01, 2009 2:18 pm

I only knew that from other posts.
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2367
Joined: Tue Oct 09, 2007 5:19 pm
Unrated

Re: Debugging OleDB errors

Postby Naomi on Tue Sep 01, 2009 2:59 pm

No, it is VFP (Visual FoxPro) database, so no profiler available.

Also, are you saying that comments are not included? And if we have source code with comments, the line number is not going to help us?

That's a pity :(
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated

Re: Debugging OleDB errors

Postby gmmastros on Tue Sep 01, 2009 3:25 pm

Let's step back a sec.

How are you getting line numbers in the error message? Also, is this the message you see during compile, or is this the message you see while the customer is running the application?

With VB6, you can put your own line numbers in to the code, and then report on them. You have a lot of flexibility here (which can be good or bad).

for example:

  1. Option Explicit
  2. ' module level comment
  3.  
  4. Private Sub cmdCommand1_Click()
  5.  
  6. 100:    ' comment 1
  7. 200:    ' comment 2
  8. 300:
  9. 400:     On Error GoTo ErrorHandler
  10. 500:
  11. 1:    MsgBox 1 / 0
  12. 20:
  13. 5:
  14.  
  15.     Exit Sub
  16.    
  17. ErrorHandler:
  18.    
  19.     MsgBox "Line: " & Erl & vbCrLf & vbCrLf & Err.Description
  20.  
  21. End Sub
  22.  


If you start a new vb6 project, and run this code, you will get an error message that says:

line 1
Division by zero

You do not need to number your lines (and most people prefer not to). The erl variable will contain the last line number it found, so this.....

  1. Option Explicit
  2. ' module level comment
  3.  
  4. Private Sub cmdCommand1_Click()
  5.  
  6. 100:    ' comment 1
  7.     ' comment 2
  8.  
  9. 200:     On Error GoTo ErrorHandler
  10.  
  11.     MsgBox 1 / 0
  12.  
  13.  
  14.  
  15.     Exit Sub
  16.    
  17. ErrorHandler:
  18.    
  19.     MsgBox "Line: " & Erl & vbCrLf & vbCrLf & Err.Description
  20.  
  21. End Sub
  22.  


erl will be 200 because that is the last line number it has. There could be dozens of lines between between the last line number and actual erroring line. It does not matter.

There is a really nice tool named "MZ Tools", which is a free add-in for vb that will allow you to quickly add line numbers to every line of code. It can also add error handling to your code too.

I hope this helps.
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2367
Joined: Tue Oct 09, 2007 5:19 pm

Re: Debugging OleDB errors

Postby Naomi on Tue Sep 01, 2009 3:43 pm

We can not run VB application from VB because of the licensing problem with ItGrid - we sent customer two proposals (one to replace ItGrid with something else - MSFlexGrid, I suppose) and another to re-write this application.

In the meantime we need to troubleshoot an error - we're getting error from VB application saying that it's OleDB error and saying what line number and which procedure it is. Last time we checked this it was on a MsgBox statement, but as I understand now, it is not necessarily the line with the error.

Anyway, we identified 12 records in the database that caused the problem. We don't see anything wrong with them, so my colleague right now is trying to narrow down this list to see if we can pin-point to one (or few) records out of these 12.
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated

Re: Debugging OleDB errors

Postby Naomi on Tue Sep 01, 2009 7:23 pm

With another colleague we determined the cause of the problem. In one of the fields (first name) we had space and ( in the field. This caused a problem in OleDB RecordSet on UPDATE command.

That's weird, but that's what we found.
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1754
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated