Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 9, 2000

Utility to Build VB Class to Match SQL Stored Procedures

By Danny Lesandrini

What is SP2VB?

SP2VB (Stored Proc To VB Class) is a Visual Basic utility that will create the class module code necessary to execute SQL Server Stored Procs with an ADO Command object. In addition to preparing the necessary ADO calls, the utility uses the SQLDMO and SQLNS libraries to query your SQL Server for the names and parameters of all the Stored Procs in your database.

Why the Need for this Utility?

  • I hate to type!
  • I make mistakes when I type!
  • Typing long lists of parameters is boring!

What more need be said?

Executing a SQL Server Stored Proc from MS Access or from an ASP page is relatively trivial, but either way, you have to deal with the sometimes long list of input and output parameters. Even when parameters are few, you still want to keep your code consistent accross your entire application, employing a common naming scheme and including at least simple error handling.

Boring? Sometimes! But very necessary and always beneficial.

Accordingly, I came up with a plan to automate the task -- a simple code generator. The pages that follow will explain (at relatively high level) what the utility does and how you might modify the code to meet your specific needs. As usual, the source code is available for download so that you can test out the utility and adapt it as you please.

Before we get into the code itself, here's a screen shot of the main form. When we are finished, the tool will be able to perform the following functions:

  1. Connect to any SQL Server Database
  2. Optionally filter out System Stored Procs (Prefixed with dt_ or sp_)
  3. Optionally process only one user-specified procedure
  4. Assign the Data Type that the Function returns (Long or ADO Recordset)
  5. Display results so user can cut and paste into VB Class module

Future enhancements to this utility could include the following

  1. Automatically output results to VB Class Module
  2. Generate corresponding ASP Class Method execution code
  3. Automatically determine proper Data Type for Function return value

Page 2: Code the Utility

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM