?
Solved

MDAC - VB6 - MS SQL Server

Posted on 2003-03-21
14
Medium Priority
?
326 Views
Last Modified: 2010-04-07
We are about to field an databse application.  The front end is written in VB 6 using ADO with MDAC 2.5.  The back end is MS SQL Server 7.0.

Until the last week everything worked well in both the development and production environments.  When we tested some changes on the Production LAN we started getting some strange results.

Forms with date fields display properly so long as a record exists (even with NULL for the date field).  If no record exists then we get 30 Dec 1899 displayed as the date.  On several of the forms if a date is in certain fields, the form is locked from changes even though no record exists.

There have been no changes on the development LAN (we control it) and everything is working fine.  On the production LAN they recently upgraded to MDAC 2.7.  

As a test we rebuilt one our production side machines with an old load (MDAC 2.5) and everything is working fine.  The code run was the same as on machines with MDAC 2.7.

Does anybody have any ideas on what we can do?  

mlmcc
0
Comment
Question by:mlmcc
[X]
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
14 Comments
 
LVL 5

Expert Comment

by:Cimperiali
ID: 8181752
>As a test we rebuilt one our production side machines with an old load (MDAC 2.5)
You should do the contrary: install mdac2.7, open your projects, change reference to the proper "micorsoft ActiveX Data Object (2.7) library" and then try your software...
0
 
LVL 101

Author Comment

by:mlmcc
ID: 8182012
We tried that.  Still have the same problem.

mlmcc
0
 
LVL 7

Expert Comment

by:fluglash
ID: 8182105
you need to register on client machine msvbvm60.dll
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 8182331
just as a note:

30 Dec 1899

is the Date when the value returned is treated as 0.  In VB 6, Date variables are handled internally as NUMBERS or type Single (a number WITH as Decimal point), such that the Integer part of the value (to the LEFT if the decimnal point) is the Number of Days since 12/31/1899  and the Fractional part is the Fraction of 1 day, as measured in Seconds from MIDNIGHT.

sounds like you are not handling NULL dates properly.  

AW
0
 
LVL 101

Author Comment

by:mlmcc
ID: 8182567
>>30 Dec 1899 is the Date when the value returned is treated as 0.  

We realized what the date meant.  What we don't understand is how come it is suddenly causing a probelm.



>> sounds like you are not handling NULL dates properly

This only occurs when the record doesn't exist.  If the record is built with only the PK fields we don't have a problem.  


>>you need to register on client machine msvbvm60.dll

Happens even on a machine with Visual Studio installed. Also, the application was fine last week.  We made no changes to the application or the machine other than MDAC 2.7 was pushed.  Could the push have unregister msvbvm60?

mlmcc
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8182983
As Arthur has pointed out, the problem has to do with not handling dates correctly.

>>We made no changes to the application or the machine other than MDAC 2.7 was pushed. <<
This is not the full picture, is it?  The Production data is different to the Development data.  To prove this: Move the Production data to the Development environment and you will probably see the same there.

Anthony
0
 
LVL 101

Author Comment

by:mlmcc
ID: 8183299
Actually that is the full picture.

Installed MDAC 2.7 on a development workstation.  Ran the applicatioin.  As expected we have the same problem.  

Changed the reference from MDAC 2.5 to MDAC 2.7.  Recompiled the application.  Still have the same problem.

Selected a simple form.  Added a new ADO control.  Pointed some of the date fields to it.  These fields showed as blank when the application was recompiled.

Solution
Delete and rebuild all ADO controls in the application.

mlmcc
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8183522
Actually the problem was when you said you were using the ADO control <smile> Since I have never used the ADO Data Control (and don't intend to) I have never come across this problem and trust me if this is the only problem you have encountered using them, then you must walk on water <g>

Anthony
0
 
LVL 101

Author Comment

by:mlmcc
ID: 8183861
acperkins :
What do you use for dat access?

mlmcc
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8183952
>>What do you use for dat access?<< I do not use the ADO Data control, instead I write code (using ADO).  I also, very rarely use bound controls, except perhaps for a grid control. The prime advantage is that you as a programmer have far better control of what is happening.

The last time I used a Data Control was over 6 years ago using DAO, I learned my lesson and never repeated the same mistake twice.

Anthony
0
 
LVL 101

Author Comment

by:mlmcc
ID: 8184483
We may decide to take that approach since we have to change all the controls anyways.  May decide to convert to VB recordsets or VB recordsets using database views and stored procedures.

mlmcc
0
 
LVL 101

Author Comment

by:mlmcc
ID: 8214423
I have solved the problem.  The problem ado controls were set to adAddNew on the EOFAction.  This creates a new record and apparently ADO 2.7 puts 0 in for the date rather than null.

We changed the property of all the ado controls and the application now runs as expected.  

mlmcc
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8214530
I have no problem that they PAQ this question.

Anthony
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 8215339
User resolves; points refunded and question closed.

Netminder
EE Admin
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month14 days, 12 hours left to enroll

771 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