logo
vbRad Home
Source Code
Book Reviews
Forum
Links
About Us
Contribute

Compare Databases with SQL Effects Clarity
 
 ADO Stored Procedure Generator add-in

Posted on
6/8/2006
Author:
Robert Gelb
Email:
Not Shown
Applies To OS:
Windows
Product:
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 **********    

Add Your Comment  

Name: Email Address: all fields optional
Notify me via email when someone responds to this message (valid email required).

Enter the word:
 



Comments
#1. By Marvin. Posted on 10/1/2006 8:57:06 PM
Saved my life with this DLL! Thanks!

#2. By http://www.counterhit.de/cgi-b. Posted on 3/18/2007 3:28:11 AM
http://www.counterhit.de/cgi-bin/gb.cgi?id=buycialis

#3. By http://www.gaestebuch-umsonst.. Posted on 3/19/2007 3:38:43 PM
http://www.gaestebuch-umsonst.ws/n/nachalos.htm

#4. By http://www.gaestebuch-umsonst.. Posted on 3/21/2007 5:06:14 AM
http://www.gaestebuch-umsonst.ws/b/buycials.htm

#5. By Very nice site!. Posted on 4/25/2007 12:28:36 PM
Very nice site!

#6. By Phocas Marlon. Posted on 4/25/2007 8:51:18 PM
Ya stanu toli plachem to li penje. Phocas Marlon.

#7. By Masozi Koldo. Posted on 5/4/2007 12:41:30 PM
Da delo dazhe ne v goda. Masozi Koldo.

#8. By wdimqfitgp. Posted on 6/19/2007 2:53:53 AM
Hello! Good Site! Thanks you! apbcrndwtc

#9. By Visitor936. Posted on 8/6/2007 11:01:44 AM
I have visited your site 487-times

#10. By Cherette Jojo. Posted on 8/13/2007 8:27:26 AM
Man I had a dreadful fligh. Cherette Jojo.

#11. By Visitor912. Posted on 9/7/2007 12:13:11 PM
I have visited your site 167-times

#12. By John. Posted on 1/28/2008 7:43:41 PM
W872EP 94gbdkli720dv

#13. By john. Posted on 1/30/2008 4:17:07 AM
site very I'm good happy

#14. By Jiggy. Posted on 6/26/2010 6:08:09 AM
Check out T4S - Stored Procedure Generator (www.tools4sql.net). It is very interesting. It saves your development time & speeds up development task by generating commonly used stored procedures for all tables of sql server database in no time. It provides very advance configurations to generate stored procedure as per your coding standards.

#15. By John Book. Posted on 2/9/2011 4:41:47 PM
Same thing can be done better way using Tools4SQL - Stored Procedure Generator.

#16. By Stored Procedure Generator. Posted on 2/15/2011 8:10:23 AM
I agree with Jiggy
Tools4SQL is really nice SP Generator