ADO Stored Procedure Generator add-in

ADO Stored Procedure Generator add-in	Applies To
	OS: VB:	NT, 9x, 2000 5(?), 6

Writing code to handle stored procedures in ADO is tedious at best. At worst, it means walking through code, mindlessly checking for errors and typos. This is where this add-in comes in. It generates source code for you based on either ADO, ODBC or your custom connection.

I didn't write this add-in, but I added various functionality to enhance usability. It was initially released to developers at one of Microsoft's seminars.

I added/changed the following items:

Added support for Sybase Adaptive Server Enterprise 11, 12. There is now an option to get data using Sybase native methods, which eliminates incompatibilities between some of the Sybase ODBC drivers and ADO.
Fixed the bugs where adNumeric and adDecimal data types were not getting processed properly
Sorted the stored procedures for easier retrieval
Option to build, then connect to the data at will
Option to get either all stored procs or ones owned by the user
Option to get either all stored procs or ones owned by the user
Wizard saves user prefs
Cool ADO icon :-)
Last update: April 16, 2001

It was tested on and compiled for VB6. However, the source code is included in the download, so it can be easily recompiled for VB5. See readme.htm (included in the zip file) for installation instructions. Enjoy...

Download... (57 kb)

Below is an example of code that the add-in generates:


'********** Sproc Wizard Code Generation START **********

Dim Conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim Params As ADODB.Parameters
Dim Param As ADODB.Parameter
Dim strConnString As String
strConnString = "Provider=MSDASQL;Password=pwd;Persist Security Info=True;User ID=sa;Data Source=Dictionary"

'Set connection properties and open
Conn.ConnectionString = strConnString
Conn.CursorLocation = adUseServer
Conn.Open

'Set command properties
Set cmd.ActiveConnection = Conn
cmd.CommandText = "appextract"
cmd.CommandType = adCmdStoredProc
Set Params = cmd.Parameters

'Define stored procedure parameters and append to command.
Params.Append cmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
Params.Append cmd.CreateParameter("@ApplicationName", adVarChar, adParamInput, 255)

'Specify input parameters
'params("@ApplicationName") = {MyVariable}

'Execute the command
cmd.Execute

'Retrieve stored procedure return value and output parameters
'{MyVariable} = params("RETURN_VALUE")

'Close connection
Conn.Close

'********** Sproc Wizard Code Generation END **********