MDAC - VB6 - MS SQL Server

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
LVL 101
mlmccAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CimperialiCommented:
>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
mlmccAuthor Commented:
We tried that.  Still have the same problem.

mlmcc
0
fluglashCommented:
you need to register on client machine msvbvm60.dll
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Arthur_WoodCommented:
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
mlmccAuthor Commented:
>>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
Anthony PerkinsCommented:
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
mlmccAuthor Commented:
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
Anthony PerkinsCommented:
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
mlmccAuthor Commented:
acperkins :
What do you use for dat access?

mlmcc
0
Anthony PerkinsCommented:
>>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
mlmccAuthor Commented:
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
mlmccAuthor Commented:
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
Anthony PerkinsCommented:
I have no problem that they PAQ this question.

Anthony
0
NetminderCommented:
User resolves; points refunded and question closed.

Netminder
EE Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.