SQL Server Secrets: Stored Procedures and ADO

Here's a VB add-in you can use to automate tasks

One of the reasons I became a Microsoft fan long ago was because Microsoft has always done a good job of integrating technologies and providing developers tools to build their own applications.

Using SQL Server stored procedures and ADO is a hard task for many Visual Basic (VB) developers. It’s tedious to create ADO parameters and set options for ADO in VB code. One small error can keep the application from working or from working efficiently.

Microsoft has just released a new tool that makes it easier to work with ADO and stored procedures. This new tool is an add-in for VB that automates the tasks you need to accomplish and serves as a good example of how to build add-ins that work with VB.

Using the tool is simple. You download the file (ADO Code Wizard.exe), install it, and use regsvr32.exe to register sprocwizard.dll. (To download the file, click on the file 7402.zip in the article information box in the right navigation bar.) The next time you open VB, you’ll find the ADO Stored Procedure Wizard on the Add-Ins menu.

To use the wizard, open the VB source module that you’re going to insert the ADO stored procedure code into. Make sure the cursor is in the location where you want to add the code. Next, start the ADO Stored Procedure Wizard from the Add-Ins menu. Fill in the parameters and click OK. That’s it. Now your module should contain the ADO code for the stored procedure you selected.

The wizard comes with complete source code, so you can modify the code to fit your needs. The add-in functionality of VB gives you a rich feature set that you can use to automate tasks such as working with stored procedures. You can tweak the wizard by customizing the connection information or adding new features to let developers create parameter variables on the fly.

Explore the add-in capabilities of VB and other tools. Some of the examples, such as this wizard, are helpful and you don’t have to write a single line of code.