ADO Closes recordset! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ADO Closes recordset!

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-10-05 : 20:12:34

harraz writes "Hello friends,

I have a SP that does not return recordsets when called from VB6/ADO, wherease
when I run it on the SQL Server 2000 query analyzer everything seems to work fine and I
get records.

When I call this sp from a VB/ADO command object the recorset gets closed!!! I
attached the sp source code followed by the VB/ADO source code too.

any help is appreciated.

thanks
harraz

HINT : If I break this SP into two stored procedures at first and second parts as shown in
the comments and then call them consecutively it will work !! but I need to know why
can't they be together :)

--------------- Stored Procedire code follows --------------------------------------
Create Procedure PROC_9 @mm varchar(4000), @yy smallint, @billingstatus bit AS

/* first part of the procedure */
DELETE rate_amnt_summary
INSERT INTO rate_amnt_summary
(Billing_Code,
Rate,
total_hours,
total_amount)

SELECT tblProjects.Billing_Code,
tblRates.Rate,
SUM(tblWorks_ON.Hours) AS [total hours]
, SUM(tblWorks_ON.Hours) * tblRates.Rate AS [total amount]

FROM tblWorks_ON INNER JOIN
tblProjects ON tblWorks_ON.PROJ_ID = tblProjects.PROJ_ID INNER JOIN
tblEmployees ON tblWorks_ON.EMP_ID = tblEmployees.EMP_ID INNER
JOIN
tblRates ON tblProjects.PROJ_ID = tblRates.PROJ_ID AND
tblEmployees.EMP_ID = tblRates.EMP_ID

WHERE month(tblWorks_ON.[Date]) IN (SELECT * FROM intlist_to_tbl(9))
AND year(tblWorks_ON.[Date])=2001 AND tblWorks_ON.Billing_Status=0

GROUP BY tblProjects.Billing_Code, tblEmployees.Initials, tblRates.Rate
ORDER BY tblProjects.Billing_Code

/* second part of the procedure */
SELECT Billing_Code, SUM(total_hours) AS Hours, SUM(total_amount) AS Amount
FROM rate_amnt_summary

GROUP BY Billing_Code
ORDER BY Billing_Code

--------------- VB/ADO source code follows --------------------------------------
Dim objCmd As New ADODB.Command
Dim objPar As New ADODB.Parameter
Dim recset As ADODB.Recordset

With objCmd
Set .ActiveConnection = mconn
.CommandText = "PROC_9"
.CommandType = adCmdStoredProc
.CommandTimeout = 15
End With

Set objPar = objCmd.CreateParameter("@mm", adVarChar, adParamInput, 4000)
objCmd.Parameters.Append objPar

Set objPar = objCmd.CreateParameter("@yy", adSmallInt, adParamInput)
objCmd.Parameters.Append objPar

Set objPar = objCmd.CreateParameter("@billingstatus", adBoolean, adParamInput)
objCmd.Parameters.Append objPar

objCmd.Parameters("@mm") = mm
objCmd.Parameters("@yy") = yy
objCmd.Parameters("@billingstatus") = bs

Set recset = New ADODB.Recordset

' I THINK THE PROBLEM IS HERE
recset.Open objCmd, , adOpenStatic, adLockReadOnly

Set RunQuery5 = recset
"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources