UNION QUERY AND ADO - 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)

UNION QUERY AND ADO

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-05-13 : 13:33:19

I have a union query in a stored procedure which I call through a command object and assign the results to a recordset.

I have made sure to include SET NOCOUNT ON in the sp.

I run the query in the query analyzer I get no records.
I run the query in ASP/ADO and when it gets to the While Loop

"While Not objRs.EOF"

It goes into the loop once.
It's as if the Union Query is returning a gost record.

Any suggestions would be appreciated.

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-05-13 : 13:48:13

I dont think ADO would be able to tell how the result set was produced by the sproc, UNION or otherwise. Do you get the same behaviour if you use just the first half of the UNION in the sproc?

Do you have a return value from your sproc?

Can you post the DDL for the sproc, and I realise this isnt an ASP/ADO board but perhaps graz could overlook it if you posted the peritinent portion of your ASP code.

Justin

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-05-13 : 14:14:31

This is the store procedure
-----------------------------------
CREATE PROCEDURE GetSelectedItems

@strRequestID NVARCHAR(255)

AS

SET NOCOUNT ON

SELECT A.RequestID,B.ToolID As ItemID, B.ItemType, C.ToolName As ItemName
FROM dbo.Requests As A LEFT OUTER JOIN
dbo.RequestTools As B ON A.RequestID = B.RequestID LEFT OUTER JOIN
dbo.Tools As C On B.ToolID = C.ToolID
WHERE A.RequestID = @strRequestID
UNION
SELECT A.RequestID, B.ProcessID As ItemID, B.ItemType, C.ProcessName As ItemName
FROM dbo.Requests As A LEFT OUTER JOIN
dbo.RequestProcesses As B ON A.RequestID = B.RequestID LEFT OUTER JOIN
dbo.Processes As C ON B.ProcessID = C.ProcessID
WHERE A.RequestID = @strRequestID
ORDER BY ItemType
GO
-----------------------------------
And this is the code that calls the store procedure

dim objConnection
dim objCommand
dim objRs

Set objCommand = Server.CreateObject("ADODB.Command")
Set objConnection = Server.CreateObject("ADODB.Connection")
set objRs = Server.CreateObject("ADODB.Recordset")
Set objCommand = Server.CreateObject("ADODB.Command")

'If Request("Mode")="1" Then
objCommand.Parameters.Append objCommand.CreateParameter ("@RequestID",adWChar,adParamInput,255,Session("RequestID"))
Set objRs = ExecuteSP(objConnection, objCommand, "GetSelectedItems", Application("BelSourceConnectString"))
Dim i
i=1
While Not objRs.EOF

Response.Write "<OPTION>" & objRs("ItemType") & objRs("ItemID") & "-" & objRs("ItemName") & "</OPTION>"

objRs.MoveNext
i=i+1
Wend

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources