Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 805
  • Last Modified:

Calling stored procedure on MS SQL Server via DAO/VB6

I just inherited a project that is written in VB6. The application was using Sybase for it's data store.  We're converting it to use MS SQL Server 2008 so a .NET web application can interact with the data as well. the whole application uses DAO for it's data interactions. Simple ad-hoc queries work fine but when calling stored procedures that do things such as delete or update I get invalid cursor location errors when trying to use any data that is returned from the SP.

Does any one know of the limitations with DAO and MS SQL stored procedures that might cause this and potentially have a fix?

Thanks :)
~B
0
used2could
Asked:
used2could
2 Solutions
 
PaulHewsCommented:
DAO works well with Access and some other file based databases, but may not be ideal for working with SQL Server.  DAO is also pretty ancient, and it is hard to find answers and support.  Personally I would migrate the code to ADO.

0
 
game-masterCommented:


DAO doesn't expose many of the capabilities that you could use if working directly with ODBC API functions, etc. For example, you can't perform asynchronous queries or connections using DAO, nor can you work with multiple result sets.


I suggest migrate it to ADO, ADO.NET in the future..

game-master
0
 
used2couldAuthor Commented:
Oh how i would love migrating to ADO or even convert to .NET but that isn't an option at the time. The original developer didn't separate DB calls into a data access layer. It's a mess of ad-hoc + SP sql.

I did find out what was causing the problem. There is a limitation in DAO when a stored procedure runs a delete or update. DAO can't do client side cursors  with MS SQL so the expected position is invalid.

I'm just going to have to break the calls up into individual steps.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now