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 - Page 4

By Danny Lesandrini

Utility Output for SP ems_GetNextCounters

Here is a screen shot of the form that displays the results.
(The full text of the code follows.)

Click to Enlarge

Option Explicit

'  This code may be tweaked and pasted into your ASP Page to instantiate
'  the class that calls your Stored Proc.
'  You will need to change the names of the DLL, Class and functions as 
'  well as the parameters for the connection string.
'    Dim objDataCls
'    Dim rstReturn
'    Dim sConnect
'    Dim lRecCount
'    Dim sError

'    Set objDataClass = Server.CreateObject("YourDLL.YourClass")
'    Set rstReturn = Server.CreateObject("ADODB.Recordset")
'    sConnect = "Provider=SQLOLEDB.1;User ID=sa;PWD=;Initial Catalog=YourDB;Data Source=YourServer"
'    Set rstReturn = objDataCls.Exec_ems_MyProc(sConnect, Param_1, ... Param_n)
'    lRecCount = objDataCls.RecordsAffected
'    sError = objDataCls.SQLError
'    If Not rstReturn.BOF Then
'        Do Until rstReturn.EOF ...
' ------------------- END CODE SAMPLE ------------------- 

Private lngRecordsAffected As Long
Private strSQL7Error As String

Property Get RecordsAffected() As String
    RecordsAffected = lngRecordsAffected
End Property

Property Get SQLError() As String
    SQLError = strSQL7Error
End Property

'//////////// Code for:  ems_GetNextCounters ////////////
'  Created on 10/6/2000
'  Created by Danny Lesandrini
'  Created for Dean Evans and Associates, Inc.
'  Parameters expected by this Stored Proc
'  ----------------------------------------
'    @TableName varchar(75)
'    @HowMany int
'    @NextCounter int OUTPUT
'  ----------------------------------------

Public Function Exec_ems_GetNextCounters( _ 
    ByVal strConnect As String, _ 
    ByVal TableName As String, _ 
    ByVal HowMany As Long, _ 
    ByVal  As Long ) As ADODB.Recordset

On Error Resume Next

Dim cnn As New ADODB.Connection
Dim com As New ADODB.Command

    cnn.Open strConnect
    Set com.ActiveConnection = cnn

    With com

        .CommandText = "ems_GetNextCounters"
        .CommandType = adCmdStoredProc

        .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, , 0)
        .Parameters.Append .CreateParameter("@TableName", adVarChar, adParamInput, 75, TableName)
        .Parameters.Append .CreateParameter("@HowMany", adInteger, adParamInput, 8, HowMany)
        .Parameters.Append .CreateParameter("@NextCounter", adInteger, adParamOutput)

        Set Exec_ems_GetNextCounters = .Execute(lngRecordsAffected)
        strSQL7Error = Err.Description

    End With

    Set com.ActiveConnection = Nothing
    Set cnn = Nothing

End Function

See All Articles by Columnist Danny Lesandrini

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