MSDASQL Version 6.1 causing misaligned data problem

[ilapko]

Hi all,

New to the forum. I'm in charge of migrating a very complex enterprise legacy VB6 app to run under a 64bit version of Windows 7. Getting the VB6 IDE running on Win7 was fun enough, but this issue is just driving me nuts.

The lowdown: the app uses an ADODB provider to insert the contents of a Recordset to a Sybase DB table. The VB project references Microsoft ActiveX Data Objects 2.8 Library on XP, I know Windows 7 uses the newer 6.0 version of this library. I've spent hours reading up on MSDN and various forums on possible breaks that this later version of MSDASQL could create for an app written on XP and running on Win7 but haven't found anything like the issue I'm experiencing. I can connect through this provider no problem, but the error occurs when trying to execute the "Update" method on the Recordset object. I get a Foreign Key Constraint Violation (Sybase error 546). My best guess is that somehow this new ADODB provider is screwing up the order of my data and trying to input the wrong data in the wrong columns thus throwing me the FK constraint violation error.

Any idea what I can do? I have a lot more details I can provide, but don't want to write a whole essay for the time being.

Thanks in advance for any help!

[Hack]

Welcome to the forums.

Instead of using the update method of the recordset object, how about using an UPDATE SQL query executed by the command object?

By using an UPDATE query you will be specifically stating what piece of data should go in what field.

[ilapko]

Hi Hack,

Thanks for the welcome and the tip. I've managed to just append a SQL insert statement and then execute it using the ADODB object. This works and I can insert a new record to the DB after performing a small hack ("SET CHAINED OFF BEGIN TRAN") after opening the connection...this is probably another issue related to the new MSDASQL driver on Win7 (fml).

Although this does ultimately work, we are inserting about 130 fields of data per row, meaning we have to define each field name in the "INSERT INTO tbl_name (field1, field2...)" statement and make sure that everything is lined up in the code. I guess you can realize this is quite an ugly way of doing this (especially for future application enhancements), as opposed to using a Recordset to store the data as was originally used.

Does anyone know how I can get around the MSDASQL version 6.0/.1 issue with the ADODB.Recordset object? Apparently, I can't even downgrade the driver version back to 2.8 on Win7...

[Sweeve]

I too am experiencing the same issues. Our client is updating their machines to win7 64-bit which affects legacy vb6 apps communicating with Sybase.

It may be worth noting the apps currently have the 32-bit drivers, and not the 64-bit drivers.

As you indicated, the .Update method does not work correctly. Running stored procedures against Text or Image fields also dont work.

I tried adjusting the ADO connection (aduselocal, aduseserver) and cursor locations, to no avail. Either the apps would bomb or just return Empty.

I DID have some limited success with using the Sybase ASEOLEDB provider instead of MSDASQL provider. At least large fields would return values.

But the .Update method still put in garbage due to the misalignment -- or due to the 32-bit versus 64-bit difference.

[ilapko]

Yeah mate no one could find a solution, so we just gave up and spent a day rewriting everything in SQL. It works, so be it. Good luck!