Re: Procedure with Table Var Returns State Closed

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 08/27/04 

Date: Fri, 27 Aug 2004 16:31:45 -0700

When a query executes that returns no rowset in the resultset, the
Recordset.State is set to adStateClosed. This is perfectly normal. It means
that the action queries you're executing (and you're executing several in
the SP) are simply returning their resultsets--they don't contain SELECT
statements so they don't have rowsets.
Try adding SET NOCOUNT ON in the SP. This tells SS to drop these rowsetless
resultsets.\

hth 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"thejamie" <thejamie@discussions.microsoft.com> wrote in message 
news:2AC6E309-B942-461E-8BD8-056033628BD7@microsoft.com...
> I'm getting a closed state status when I use the RS.OPEN oCMD method.  I 
> see
> the difference between this method and others I've made successfully as
> pertaining to the use of a Table Variable in the stored procedure.  I'm 
> not
> sure though so I've included the code below.  Suggestions appreciated.
> -- 
> Regards,
> Jamie
>
> Dim oConn As ADODB.Connection
> Private Sub Form_Load()
>    MakeNew_ID "Ventilation", 16, 268, 1401, 45, 218
> End Sub
> Public Function ExecStordProc(oCmd As ADODB.Command) As ADODB.Recordset
>    Dim oConn As ADODB.Connection
>    Dim oRS As ADODB.Recordset
>    Dim sKey As String
>
>    On Error GoTo ErrHandler
>    Const cFunctionName As String = "ExecStordProc"
>
>    ' get the connection from the pool
>    Set oConn = New ADODB.Connection
>    oConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=Quotes;Data
> Source=(local)\valco"
>
>    oConn.Open
>
> '    ' set the command options
>    oCmd.CommandType = adCmdStoredProc
>
>    ' attach the connection
>    Set oCmd.ActiveConnection = oConn
>
>    ' set the recordset properties - we want proc to return
>    'records so cursor and locking must be set RIGHT
>    Set oRS = createRecordset
>
>    ' open the RS using the command
>    oRS.Open oCmd
>
> 'NOT SURE WHY THIS RETURNS WITH A STATE OF adStateClosed
>    ' disconnect the RS
>    Set oCmd.ActiveConnection = Nothing
>
> '    ' return the RS
>    Set ExecStordProc = oRS
>
> EndFunction:
>    ' clean up and exit
>    Call oQ.objDA.ReturnConnection(sKey, True)
>    If Not oCmd Is Nothing Then
>        If Not oCmd.ActiveConnection Is Nothing Then
>        Set oCmd.ActiveConnection = Nothing
>        End If
> '        If Not ors.ActiveConnection Is Nothing Then
> '        Set ors.ActiveConnection = Nothing
> '        End If
>        Set ExecStordProc = oRS
>    End If
>    Set oCmd = Nothing
>    Set oConn = Nothing
>    Set oRS = Nothing
>    Exit Function
>
> ErrHandler:
>    ' clean up and exit
>    Call oQ.objDA.ReturnConnection(sKey, True)
>    If Not oCmd Is Nothing Then
>        Set oCmd.ActiveConnection = Nothing
>    End If
>    If Err.Number <> 0 Then Err.Raise Err.Number, m_cModuleName &
> "::ExecStordProc", Err.Description
>    Set oCmd = Nothing
>    Set oConn = Nothing
>    Set oRS = Nothing
>
>
> End Function
> Public Function createRecordset() As ADODB.Recordset
>    Dim oRS As ADODB.Recordset
>
>    ' *** set up error trapping ***
>    Const cFunctionName As String = "createRecordset"
>    On Error GoTo ErrorTrap
>
>    ' *** create a recordset object and set its properties ***
>    Set oRS = New ADODB.Recordset
>    oRS.CacheSize = 100
>    oRS.CursorLocation = adUseClient
>    oRS.CursorType = adOpenStatic
>    oRS.LockType = adLockBatchOptimistic
> '    oRS.Open sSQL, oConn, adOpenStatic,adLockBatchOptimistic
>
>    ' *** return the RS ***
>    Set createRecordset = oRS
>
> EndFunction:
>    ' *** clean up ***
>    Set oRS = Nothing
>
>    If Err.Number <> 0 Then Err.Raise Err.Number, m_cModuleName & "." &
> cFunctionName, Err.Description
>    Exit Function
>
> ErrorTrap:
>    GoTo EndFunction
> End Function
> Private Function MakeNew_ID(sTableName As String, iFrameID As Integer,
> QuoteID As Long, CusID As Long, SLSID As Long, BLDGID As Long)
>    'SET UP THIS PARTICULAR PROCEDURE
>    Dim oConn As ADODB.Connection
>    Dim oCmd As ADODB.Command
>    Dim oRS As ADODB.Recordset
>
>
>    Dim sSprocName As String
>    Dim p1 As ADODB.Parameter
>    Dim p2 As ADODB.Parameter
>    Dim p3 As ADODB.Parameter
>    Dim p4 As ADODB.Parameter
>    Dim p5 As ADODB.Parameter
>    Dim p6 As ADODB.Parameter
>    On Error GoTo ErrHandler
>
>
>
>    Set oCmd = createCommand("MakeNew_Frame")
>
>     Set p1 = oCmd.CreateParameter("FormName", adVarChar, adParamInput, 50,
> sTableName)
>     Set p2 = oCmd.CreateParameter("FrameNumber", adSmallInt, adParamInput,
> , iFrameID): p2.Precision = 5
>     Set p3 = oCmd.CreateParameter("QuoteKEY", adBigInt, adParamInput, ,
> QuoteID): p3.Precision = 20
>     Set p4 = oCmd.CreateParameter("CUSID", adInteger, adParamInput, ,
> CusID): p4.Precision = 10
>     Set p5 = oCmd.CreateParameter("SLSID", adInteger, adParamInput, ,
> SLSID): p5.Precision = 10
>     Set p6 = oCmd.CreateParameter("BLDGID", adInteger, adParamInput, ,
> BLDGID): p6.Precision = 10
>     oCmd.Parameters.Append p1
>     oCmd.Parameters.Append p2
>     oCmd.Parameters.Append p3
>     oCmd.Parameters.Append p4
>     oCmd.Parameters.Append p5
>     oCmd.Parameters.Append p6
>
>     ' execute
>     Set oRS = ExecStordProc(oCmd)
>
>    ' clean up
>    Set oCmd = Nothing
>    Set p1 = Nothing
>    Set p2 = Nothing
>    Set p3 = Nothing
>    Set p4 = Nothing
>    Set p5 = Nothing
>    Set p6 = Nothing
>
>    Exit Function
>
> ErrHandler:
>
>    ' clean up
>    Set oCmd = Nothing
>    Set p1 = Nothing
>    Set p2 = Nothing
>    Set p3 = Nothing
>    Set p4 = Nothing
>    Set p5 = Nothing
>    Set p6 = Nothing
>
>    If Err.Number <> 0 Then Err.Raise Err.Number, m_cModuleName &
> "::MakeNew_ID", Err.Description
> End Function
>
>
> Public Function createCommand(sSprocName As String) As ADODB.Command
>    Dim oCmd As ADODB.Command
>
>    ' allocate it
>    Set oCmd = New ADODB.Command
>
>    ' set it up
>    oCmd.CommandType = adCmdStoredProc
>    oCmd.CommandText = sSprocName
>    oCmd.CommandTimeout = m_cCommandTimeout
>
>    ' return it
>    Set createCommand = oCmd
>
>    ' clean up
>    Set oCmd = Nothing
> End Function
>
> '--------------------PROCEDURE--------------------------------
>
> /*CREATE PROCEDURE MakeNew_Frame
> (
> @FormName varchar(50), -- Parent Form name
> @FrameNumber int, -- Frame Number
> @QuoteKey bigint,  -- QuoteKEY
> @CusID int, -- Customer ID
> @SlsID int, -- SalesPerson ID
> @Bldg int -- Building ID
> )
> AS
> */
> --debug
> SET NOCOUNT ON
> --DROP PROCEDURE MakeNew_Frame
> DECLARE
> --EXEC MakeNew_Frame 'Ventilation', 16, 268, 1401, 45, 218
> --{ call MakeNew_Frame('Ventilation', 13, 268, 1401, 45, 218) }
> @FormName varchar(50), -- Parent Form name
> @FrameNumber int, -- Frame Number
> @QuoteKey bigint,  -- QuoteKEY
> @CusID int, -- Customer ID
> @SlsID int, -- SalesPerson ID
> @Bldg int -- Building ID
>
> SET @FormName='Ventilation'
> SET @FrameNumber=16
> SET @QuoteKey=268
>
> SET @CusID=1401
> SET @SlsID=45
> SET @Bldg=218
>
> --end debug
>
>
> --RETURN A TABLE CONTAINING ALL KEY INFORMATION
> DECLARE @NewTbl TABLE
> (
> QuoteID bigint,
>
> FrameKey int NULL,
> FrameName varchar(50) NULL,
>
> FormKey int NULL,
> FormName varchar(50) NULL
> )
>
>
>
>
> DECLARE @SQL nvarchar(1000), --use to run query if necessary
>
>  @@temp numeric, --use temp var to create the key
> @stemp varchar(9), --Store the temp var
> @TableName varchar(50), --Convert the table name to it's numeric ref table
> @FrameName varchar(50), --STORE NAME OF THIS FRAME
>
> @sFrameNumber varchar(9), --Store the framenumber to string
> @sFormNumber varchar(9),--Store the formnumber to string
> @sQuoteID varchar(19), --Store the QuoteID to string
> @QuoteAbbrevID varchar(10), --SHORTCUT NAME IN QUOTE TABLE FOR FORM ID
>
> @sName varchar(50), --used in the CURSOR DECLARATION
> @NewID numeric , --Store the new KEY FOR THE FRAME
> @sNewID varchar(9),
>
> @FormKey int, --Get the FORM's Key ID
> @sFormKey varchar(9), --Store the FORM's Key ID
> @FormNumber int, --Store Form Number
> @sCusID varchar(9), --Store Customer ID
> @sSlsID varchar(9), --Store SalesPerson ID
> @sBldg varchar(9), --Store Building ID
> @sNameID varchar(500), --Store sname and the suffix "ID"
> @sConversion nvarchar(1150), --Convert the select to nvarchar then to int
> @FrameNameIDName varchar(50),--name for keyframename with "suffix ID"
> @FormNameIDName varchar(50) --name for keyformname with "suffix ID"
>
> -- IF WE NEED TO DO AN INSERT, THEN SET THIS NOW
>
> --STORE THE QUOTEID
> SELECT @sQuoteID=CONVERT(varchar(19),@QuoteKey)
>
> --IF NO CUSID USE 99999
> IF @CusID=0 or @CusID=99999
> SELECT @sCusID='99999'
> ELSE
> SELECT @sCusID=CONVERT(varchar(9),@CusID)
>
> SELECT @sSlsID=Convert(varchar(9),@SlsID)
> SELECT @sBldg=Convert(varchar(9),@Bldg)
>
> --GET THE FORMKEY
> IF @FormName='Feeding'
> SELECT @FormKey=(SELECT FeedID FROM Quote WHERE QuoteID=@sQuoteID)
> SELECT @QuoteAbbrevID='FeedID'
> IF @FormName='Watering'
> SELECT @FormKey=(SELECT WaterID FROM Quote WHERE QuoteID=@sQuoteID)
> SELECT @QuoteAbbrevID='WaterID'
> IF @FormName='Cooling'
> SELECT @FormKey=(SELECT CoolID FROM Quote WHERE QuoteID=@sQuoteID)
> SELECT @QuoteAbbrevID='CoolID'
> IF @FormName='Heating'
> SELECT @FormKey=(SELECT BroodID FROM Quote WHERE QuoteID=@sQuoteID)
> SELECT @QuoteAbbrevID='BroodID'
> IF @FormName='Ventilation'
> SELECT @FormKey=(SELECT VentID FROM Quote WHERE QuoteID=@sQuoteID)
> SELECT @QuoteAbbrevID='VentID'
>
> --STORE THE FORMKEY
> IF @FormKey IS NULL
> SELECT @sFormKey='0'
> ELSE
> SELECT @sFormKey=CONVERT(varchar(9),@FormKey)
>
>
> --UPDATE THE QUOTE TABLE WITH A KEY FOR THIS FORM
> --AND CREATE A NEW REFERENCE IN ITS TABLE
> SELECT @TableName=N'_'+LTRIM(@FormName)+N'Number'
>
> IF @sFormKey='0'
> BEGIN
>
> SELECT @SQL= N'SELECT @stemp=(SELECT MAX(CurrentHighValue) FROM
> '+@TableName+N')'
> EXEC sp_executesql @SQL, N'@stemp varchar(9) OUTPUT', @stemp OUTPUT
>
> SET @FormKey=CONVERT(int,@stemp)
>
> SELECT @sFormKey=CONVERT(varchar(9),@FormKey+1)
>
> --UPDATE THE QUOTE
> SELECT @SQL= N'UPDATE QUOTE SET
> '+@QuoteAbbrevID+N'='+CONVERT(varchar(9),@FormKey)+N',CUSID='+CONVERT(varchar(9),@sCusID)+N',SLSMNID='+CONVERT(varchar(9),@sSlsID)+N',BUILDINGID='+CONVERT(varchar(9),@sBldg)+N' 
> WHERE QUOTEID='+@sQUOTEID
>
> EXEC (@SQL)
>
> --CREATE THE TABLE
> SELECT @sql=N'SET IDENTITY_INSERT '+@FormName+N' ON INSERT INTO
> '+@FormName+N'('+@FormName+'ID,QUOTEID,BUILDINGID) VALUES
> ('+@sFormKey+N','+@sQuoteID+N','+@sBldg+N') SET IDENTITY_INSERT
> '+@FormName+N' OFF'
> EXEC (@sql)
>
> --MAKE THIS VALUE PERMANENT
> SET @@temp=CONVERT(int,@sFormKey)
>
> --ROLL THE UPDATE INTO THE REFERENCE TABLE
> BEGIN
> IF @@temp is null
> SELECT @sql=N'INSERT INTO '+@TableName+N' Values(1);'
> ELSE
> SELECT @sql=N'UPDATE '+@TableName+N' SET CurrentHighValue='+@sFormKey
> END
> EXEC (@sql)
> END
> ELSE --IF @sFormKey='0'
> BEGIN
> --IN HERE CHECK FOR SAME AS ABOVE BUT IF IN PARENT RATHER THAN QUOTE TABLE
> SELECT @SQL= N'SELECT @stemp=(SELECT '+@FormName+'ID FROM '+@FormName+N'
> WHERE QuoteID=' +@sQuoteID+N')'
> EXEC sp_executesql @SQL, N'@stemp varchar(9) OUTPUT', @stemp OUTPUT
> SELECT @FormKey=CONVERT(int,@stemp)
>
> END
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> --  FORM KEY IS ESTABLISHED     --
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
>
>
> SELECT @FormNumber= (SELECT FormID FROM FRAMES WHERE sName=@FormName)
> SELECT @sFormNumber= CONVERT(varchar(9),@FormNumber)
> SELECT @sFormKey= CONVERT(varchar(9),@FormKey) --Redundant but necessary
> SELECT @sFrameNumber= CONVERT(varchar(5),@FrameNumber)
>
> SELECT @sql=''
>
> --GET A FRAME NAME
> SELECT @SQL= N'SELECT @sName=(SELECT sname from Frames WHERE
> ID='+@sFrameNumber+N')'
>
> EXEC sp_executesql @SQL, N'@sName varchar(50) OUTPUT', @sName OUTPUT
>
> --CHECK TO BE CERTAIN THIS ONE DOESN'T ALREADY EXIST
>
> SELECT @sNameID=N'(SELECT ' +@sName+'ID FROM '+@FormName+' WHERE
> QuoteID='+convert(nvarchar,@sQuoteID)+N')'
>
> SELECT @sConversion=convert(nvarchar(500),@sNameID)
>
> SELECT @SQL=N'SELECT @sNewID='+@sConversion
> EXEC sp_executesql @SQL, N'@sNewID varchar(9) OUTPUT', @sNewID OUTPUT
>
> SELECT @NewID=CONVERT(int,@sNewID)
>
> --CREATE A REFERENCE TO THE FRAME KEY CURRENT HIGH NUMBER TABLE
> SELECT @TableName=N'_'+LTRIM(@sName)+N'Number'
>
>
> BEGIN
> IF @NewID IS NULL
> BEGIN
> BEGIN
> --create a variable holding the max id in the system
> SELECT @SQL= N'SELECT @stemp=(SELECT MAX(CurrentHighValue) FROM
> '+@TableName+N')'
> EXEC sp_executesql @SQL, N'@stemp varchar(9) OUTPUT', @stemp OUTPUT
> END
>
> SELECT @@temp=CONVERT(int,@stemp)
>
> BEGIN
> If @@temp is null
> Set @NewID = 1
> Else
> Set @NewID =  @@temp + 1;
> END
>
> BEGIN
> IF @@temp is null
> SELECT @sql=N'Insert into '+@TableName+' Values(1)'
>
> ELSE
> SELECT @sql=N'Update '+@TableName+' Set CurrentHighValue =
> CurrentHighValue + 1'
> END
>
> Exec(@SQL)
> SELECT @sNewID=CONVERT(varchar(9),@NewID)
> SELECT @FrameNameIDName = @sname +'ID'
> SELECT @FormNameIDName = @FormName +'ID'
>
> --CREATE THE TABLE
>
> SELECT @sql=N'SET IDENTITY_INSERT '+@sname+N' ON INSERT INTO
> '+@sname+N'('+@FrameNameIDName+',QUOTEID,FORMID,BUILDINGID,'+@FormNameIDName+')
> VALUES
> ('+@sNewID+N','+@sQuoteID+N','+@sFormNumber+N','+@sBldg+N','+@sFormKey+N')
> SET IDENTITY_INSERT '+@sname+N' OFF'
> EXEC (@sql)
>
> --UPDATE THE PARENT FORM
> SELECT @sql=N'UPDATE '+@Formname+' SET '+@FrameNameIDName+'='+@sNewID+'
> WHERE QuoteID='+@sQuoteID
>
> EXEC (@sql)
> END
> ELSE --IF @NewID IS NULL
> BEGIN
> SELECT @FrameNameIDName = @sname +'ID'
> SELECT @FormNameIDName = @FormName +'ID'
>
> --CHECK TO SEE IF THE CHILD EXISTS
> SELECT @SQL=N'SELECT @stemp=(SELECT '+@Formname+'ID FROM '+@sName+N' WHERE
> '+@Formname+'ID='+@sFormKey+N')'
>
> EXEC sp_executesql @SQL, N' @stemp varchar(9) OUTPUT', @stemp OUTPUT
>
> --IF THE CHILD DOESNT EXISTS, @@temp will NULL
> SELECT @@temp=CONVERT(int,@stemp)
> IF @@temp IS NULL
> BEGIN
> SELECT @sql=N'SET IDENTITY_INSERT '+@sname+N' ON INSERT INTO
> '+@sname+N'('+@FrameNameIDName+',QUOTEID,FORMID,BUILDINGID,'+@FormNameIDName+')
> VALUES
> ('+@sNewID+N','+@sQuoteID+N','+@sFormNumber+N','+@sBldg+N','+@sFormKey+N')
> SET IDENTITY_INSERT '+@sname+N' OFF'
> EXEC (@sql)
> SELECT @NewID=CONVERT(int,@sNewID)
> END
>
> END
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> --  FRAME KEY IS ESTABLISHED     --
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
>
> END
>
> INSERT INTO @NewTbl(FrameKey,FrameName,QuoteID,FormName,FormKey) VALUES
> (CONVERT(varchar(9),@NewID),quotename(@sName,''''),@sQuoteID,quotename(@FormName,''''),@sFormKey)
> SELECT * FROM @NewTbl
> GO
>

Relevant Pages

  • Re: How to use VBA to update a SQL Server Table from a Spreadsheet
    ... looked at the data in the sql database it all showed up as "0's". ... Dim oConn As Object ... Dim sSQL As String ... Set oRS = CreateObject ...
    (microsoft.public.excel.programming)
  • Procedure with Table Var Returns State Closed
    ... I'm getting a closed state status when I use the RS.OPEN oCMD method. ... Dim oConn As ADODB.Connection ... Set oRS = Nothing ... EXEC sp_executesql @SQL, N'@stemp varcharOUTPUT', @stemp OUTPUT ...
    (microsoft.public.data.ado)
  • Any Ideas, AnyOne, Hello!
    ... Dim oCon As New ADODB.Connection ... Set oCmd = CreateObject ... Set oRS = CreateObject ...
    (microsoft.public.data.ado)
  • Large ADO/LDAP/AD RecordSet problems/errors
    ... Dim oCon As New ADODB.Connection ... Set oCmd = CreateObject ... Set oRS = CreateObject ...
    (microsoft.public.data.ado)
  • Re: Which identity?
    ... > Some postings I read suggested using MAXto retrieve the inserted record ... > each of these 3 SQL features? ... Another way to do it if using the .AddNew method on a Jet based ADO ... Dim rsTest As ADODB.Recordset ...
    (microsoft.public.access.queries)