Procedure with Table Var Returns State Closed
From: thejamie (thejamie_at_discussions.microsoft.com)
Date: 08/27/04
- Next message: Sarah Bram: "Re: XP SP2 and MDAC 2.8"
- Previous message: Jerry Pisk: "Re: Updating to MDAC 2.8"
- Next in thread: William \(Bill\) Vaughn: "Re: Procedure with Table Var Returns State Closed"
- Reply: William \(Bill\) Vaughn: "Re: Procedure with Table Var Returns State Closed"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 27 Aug 2004 13:17:01 -0700
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: Sarah Bram: "Re: XP SP2 and MDAC 2.8"
- Previous message: Jerry Pisk: "Re: Updating to MDAC 2.8"
- Next in thread: William \(Bill\) Vaughn: "Re: Procedure with Table Var Returns State Closed"
- Reply: William \(Bill\) Vaughn: "Re: Procedure with Table Var Returns State Closed"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
