Re: Procedure with Table Var Returns State Closed
From: thejamie (thejamie_at_discussions.microsoft.com)
Date: 08/28/04
- Next message: Peter: "SQL Relations vs. DataRelations?"
- Previous message: Michael D. Long: "Re: Creating a dataset filled with random records"
- In reply to: William \(Bill\) Vaughn: "Re: Procedure with Table Var Returns State Closed"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 28 Aug 2004 05:07:01 -0700
Bill,
Much obliged.
Jamie
"William (Bill) Vaughn" wrote:
> 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
> >
>
>
>
- Next message: Peter: "SQL Relations vs. DataRelations?"
- Previous message: Michael D. Long: "Re: Creating a dataset filled with random records"
- In reply to: William \(Bill\) Vaughn: "Re: Procedure with Table Var Returns State Closed"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
