Please Help before I pull out the last of my hair

This is on the longer side so please bear with me.

I have written the following stored procedure:

CREATE PROCEDURE dbo.usrGet_ContactSearchPRODUCT_CATEGORY
@ConsultantID Int,
@LanguageID Int,
@RequestedPage Int,
@PerPage Int,
@FromDate VarChar(10),
@ToDate VarChar(10),
@ProductCategory Char(3)

AS
DECLARE @LowValue Int
DECLARE @HighValue Int
DECLARE @Pages Int

DECLARE @SelectFrom Table
( RecordNumber Int NOT NULL IDENTITY(1,1),
ContactID Int NULL,
FirstName VarChar(50) NULL,
LastName VarChar(50) NULL,
Address1 VarChar(50) NULL,
Address2 VarChar(50) NULL,
City VarChar(50) NULL,
State Char(2) NULL,
Zip Char(10) NULL,
CellPhone Char(15) NULL,
WorkPhone Char(15) NULL,
HomePhone Char(15) NULL,
Email VarChar(100) NULL )

SET NOCOUNT ON

-- Insert unique rows into @SelectFrom
INSERT INTO @SelectFrom
( ContactID, FirstName, LastName, Address1, Address2, City, State,
Zip, CellPhone, WorkPhone, HomePhone, Email )
SELECT DISTINCT
C.ContactId, C.FirstName, C.LastName, C.Address1, C.Address2,
C.City, C.State, C.Zip, C.CellPhone, C.WorkPhone, C.HomePhone,
C.Email
FROM Contact AS C WITH (NOLOCK)
INNER JOIN PartyGuestLineItem AS PGLI WITH (NOLOCK)
ON (C.ContactId = PGLI.ContactID)
INNER JOIN ProductMaster AS PM WITH (NOLOCK)
ON (PGLI.ProductID = PM.ProductID)
INNER JOIN ProductDescriptionMaster AS PDM WITH (NOLOCK)
ON (PM.ProductID = PDM.ProductID)
INNER JOIN PARTY AS P WITH (NOLOCK)
ON (PGLI.PartyID = P.PartyID)
WHERE ((C.ConsultantID = @ConsultantID) AND
(C.Status <> 'D' OR C.Status is NULL) AND
(PM.ProductTreeBrand = @ProductCategory) AND
(PDM.LanguageID = @LanguageID) AND
(P.PartyDate BETWEEN @FromDate AND DATEADD(Day,1, @ToDate) ))
GROUP BY
PGLI.PartyID, P.PartyDate, C.LastName, C.FirstName, C.ContactID,
PGLI.ProductID, C.Address1, C.Address2, C.City, C.State, C.Zip,
C.CellPhone, C.WorkPhone, C.HomePhone, C.Email
ORDER BY
C.LastName, C.FirstName, C.ContactID

-- Get the number of pages required to display all contacts that met the
search criteria
SELECT @Pages = CAST( CEILING(CAST(@@Identity AS FLOAT) / CAST(@PerPage AS
FLOAT)) AS Int)

-- Return the records for the @Requested Page
SELECT @LowValue = ((@RequestedPage - 1) * @PerPage) + 1
SELECT @HighValue = @RequestedPage * @PerPage

-- WHEN RUN IN QUERY ANALYZER THIS IS THE STATEMENT THAT RETURNS THE FIRST
RESULT SET
SELECT ContactID, FirstName, LastName, Address1, Address2, City, State,
Zip, CellPhone, WorkPhone, HomePhone, Email
FROM @SelectFrom
WHERE RecordNumber BETWEEN @LowValue and @HighValue
ORDER BY RecordNumber

-- RETURN a recordset with a summary of the products purchased by the
selected
-- contacts during the time period
-- WHEN RUN IN QUERY ANALYZER THIS IS THE STATEMENT THAT RETURNS THE SECOND
RESULT SET
SELECT DISTINCT
C.LastName, C.FirstName, C.ContactID, PGLI.ProductID,
PDM.ProductDescription,
SUM(PGLI.Quantity) AS Quantity
FROM @SelectFrom AS C
INNER JOIN PartyGuestLineItem AS PGLI WITH (NOLOCK)
ON (C.ContactId = PGLI.ContactID)
INNER JOIN ProductMaster AS PM WITH (NOLOCK)
ON (PGLI.ProductID = PM.ProductID)
INNER JOIN ProductDescriptionMaster AS PDM WITH (NOLOCK)
ON (PM.ProductID = PDM.ProductID)
INNER JOIN PARTY AS P WITH (NOLOCK)
ON (PGLI.PartyID = P.PartyID)
WHERE (
(C.RecordNumber BETWEEN @LowValue AND @HighValue) AND
(PM.ProductTreeBrand = @ProductCategory) AND
(PDM.LanguageID = @LanguageID) AND
(P.PartyDate BETWEEN @FromDate AND DATEADD(Day,1, @ToDate))
)
GROUP BY C.LastName, C.FirstName, C.ContactID, PGLI.ProductID,
PDM.ProductDescription
ORDER BY C.LastName, C.FirstName, C.ContactID, PGLI.ProductID

-- Return a recordset that has the total pages
-- WHEN RUN IN QUERY ANALYZER THIS IS THE STATEMENT THAT RETURNS THE THIRD
AND FINAL RESULT SET
SELECT @Pages

GO

I expect this procedure to return 3 result sets. When I run it in Query
Analyzer it does exactly what I expect. The first result set should be the
contacts to be displayed on @RequestedPage when there are @PerPage displayed
on each page. The second result set should be a summary of the purchases
made by the contacts in the first set during the requested time period. The
third result set should be simply one row with the number of pages required
to display all of the contacts that meet the selection criteria.

However when I run the stored procedure as shown here in an ASP page I get
errors. I will describe them after the page code.

<%@ Language=VBScript %>

<%
on error resume next

Dim objConn
Dim objRS
Dim vntContacts
Dim vntPurchase
Dim vntPages

strCommand = "EXEC dbo.usrGet_ContactSearchPRODUCT_CATEGORY 3839, 0, 1, 10,
'1/1/2000', '3/10/2006', 'HC'"

Set objConn = Server.CreateObject("ADODB.Connection")
With objConn
.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
Info=True;Initial Catalog=DBName;UID=user;PWD=password;Data Source=server"
.CursorLocation = adUseClient
.Open
Set objRS = .Execute(strCommand)
Response.Write("err 1 = " & err.number & err.Description & "
")
End With


vntContacts = objRS.GetRows()
Response.Write("err 2 = " & err.number & err.Description & "
")

objRS.NextRecordset
Response.Write("err 3 = " & err.number & err.Description & "
")

vntPurchase = objRS.GetRows()
Response.Write("err 4 = " & err.number & err.Description & "
")

objRS.NextRecordset
Response.Write("err 5 = " & err.number & err.Description & "
")

vntPages = objRS.GetRows()
Response.Write("err 6 = " & err.number & err.Description & "
")

Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

The first Response.Write after the .Execute says err.number is 0. So far so
good. However each of the response.writes after I try to use objRS return
the following error:

err = 3704 Operation is not allowed when the object is closed.

I had originally tried setting up the last recordset as an output parameter
on the stored procedure and instead of just using a connection object to
execute the stored proc I was using a command and had intended to just
retrieve the parameter value in addition to the first two recordsets. Since
I've been going through various attempts to get this to work it got switched
to a final recordset.

Am I doing something wrong or is do I need to split this into multiple
stored procedures, one to populate a work table and then a second to only
retrieve records from the work table?

Thanks,

Evan Nelson

Re: Please Help before I pull out the last of my hair

Evan Nelson wrote:
> This is on the longer side so please bear with me.
>
> I have written the following stored procedure:
>
> CREATE PROCEDURE dbo.usrGet_ContactSearchPRODUCT_CATEGORY
> @ConsultantID Int,
> @LanguageID Int,
> @RequestedPage Int,
> @PerPage Int,
> @FromDate VarChar(10),
> @ToDate VarChar(10),
> @ProductCategory Char(3)
>

> SET NOCOUNT ON
>

Good!


>
> I expect this procedure to return 3 result sets. When I run it in
> Query Analyzer it does exactly what I expect. The first result set
> should be the contacts to be displayed on @RequestedPage when there
> are @PerPage displayed on each page. The second result set should be
> a summary of the purchases made by the contacts in the first set
> during the requested time period. The third result set should be
> simply one row with the number of pages required to display all of
> the contacts that meet the selection criteria.
>
> However when I run the stored procedure as shown here in an ASP page
> I get errors. I will describe them after the page code.
>

>
> strCommand = "EXEC dbo.usrGet_ContactSearchPRODUCT_CATEGORY 3839, 0,
> 1, 10, '1/1/2000', '3/10/2006', 'HC'"
>


objRS = .Execute(strCommand)
> Response.Write("err 1 = " & err.number & err.Description & "
")
> End With
>
>
> vntContacts = objRS.GetRows()
> Response.Write("err 2 = " & err.number & err.Description & "
")
>
> objRS.NextRecordset

This should be:
Set objRS= objRS.NextRecordset



You may wish to consider this more secure and efficient method for executing
your procedure:
http://tinyurl.com/jyy0

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Please Help before I pull out the last of my hair

Bob Barrows [MVP] wrote:
>
>
> objRS = .Execute(strCommand)

Oops, too much snippage.The original correctly read:
Set objRS = .Execute(strCommand)

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Please Help before I pull out the last of my hair

Bob,

I think maybe I wrote the post the wrong way. I probably should have put
all the code at the bottom and gotten to my questions first. So, referring
back to the code in my initial post here is my problem.

The first Response.Write after the .Execute says err.number is 0. So far so
good. However each of the response.writes after I try to use objRS return
the following error:

err = 3704 Operation is not allowed when the object is closed.

I received the same results using the method in the code samples I provided
and also using a command object. So my question is - have I done something
wrong in my stored proc that it making it so that the statements:

set objRS = objConn.execute(strSQLCommand) or set objRS = objCmd.execute

are returning a closed recordset? I'm assuming the error message means that
the recordset is closed.

Thanks




"Bob Barrows [MVP]" wrote:

> Evan Nelson wrote:
> > This is on the longer side so please bear with me.
> >
> > I have written the following stored procedure:
> >
> > CREATE PROCEDURE dbo.usrGet_ContactSearchPRODUCT_CATEGORY
> > @ConsultantID Int,
> > @LanguageID Int,
> > @RequestedPage Int,
> > @PerPage Int,
> > @FromDate VarChar(10),
> > @ToDate VarChar(10),
> > @ProductCategory Char(3)
> >
>
> > SET NOCOUNT ON
> >
>
> Good!
>
>
> >
> > I expect this procedure to return 3 result sets. When I run it in
> > Query Analyzer it does exactly what I expect. The first result set
> > should be the contacts to be displayed on @RequestedPage when there
> > are @PerPage displayed on each page. The second result set should be
> > a summary of the purchases made by the contacts in the first set
> > during the requested time period. The third result set should be
> > simply one row with the number of pages required to display all of
> > the contacts that meet the selection criteria.
> >
> > However when I run the stored procedure as shown here in an ASP page
> > I get errors. I will describe them after the page code.
> >
>
> >
> > strCommand = "EXEC dbo.usrGet_ContactSearchPRODUCT_CATEGORY 3839, 0,
> > 1, 10, '1/1/2000', '3/10/2006', 'HC'"
> >
>
>
> objRS = .Execute(strCommand)
> > Response.Write("err 1 = " & err.number & err.Description & "
")
> > End With
> >
> >
> > vntContacts = objRS.GetRows()
> > Response.Write("err 2 = " & err.number & err.Description & "
")
> >
> > objRS.NextRecordset
>
> This should be:
> Set objRS= objRS.NextRecordset
>
>
>
> You may wish to consider this more secure and efficient method for executing
> your procedure:
> http://tinyurl.com/jyy0
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Please Help before I pull out the last of my hair

Evan Nelson wrote:
> Bob,
>
> I think maybe I wrote the post the wrong way. I probably should have
> put all the code at the bottom and gotten to my questions first. So,
> referring back to the code in my initial post here is my problem.
>
> The first Response.Write after the .Execute says err.number is 0. So
> far so good. However each of the response.writes after I try to use
> objRS return the following error:
>
> err = 3704 Operation is not allowed when the object is closed.

That's because you did not say:
Set objRS - objRS.NextRecordset

..

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Please Help before I pull out the last of my hair

Bob,

Do I have to do a .NextRecordset just to get the first recordset?

Evan


"Bob Barrows [MVP]" wrote:

> Evan Nelson wrote:
> > Bob,
> >
> > I think maybe I wrote the post the wrong way. I probably should have
> > put all the code at the bottom and gotten to my questions first. So,
> > referring back to the code in my initial post here is my problem.
> >
> > The first Response.Write after the .Execute says err.number is 0. So
> > far so good. However each of the response.writes after I try to use
> > objRS return the following error:
> >
> > err = 3704 Operation is not allowed when the object is closed.
>
> That's because you did not say:
> Set objRS - objRS.NextRecordset
>
> ..
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Please Help before I pull out the last of my hair

Bob,

My code is

with objConn
Several lines opening the connection
Set objRS = .Execute(strCommand)
Response.Write("err 1 = " & err.number & err.Description & "
")
End With
vntContacts = objRS.GetRows()
Response.Write("err 2 = " & err.number & err.Description & "
")
objRS.NextRecordset
Response.Write("err 3 = " & err.number & err.Description & "
")
vntPurchase = objRS.GetRows()
Response.Write("err 4 = " & err.number & err.Description & "
")
objRS.NextRecordset
Response.Write("err 5 = " & err.number & err.Description & "
")
vntPages = objRS.GetRows()
Response.Write("err 6 = " & err.number & err.Description & "
")

The page displays
err 1 = 0
err 2 = = 3704 Operation is not allowed when the object is closed.
err 3 = 3704 Operation is not allowed when the object is closed.
err 4 = 3704 Operation is not allowed when the object is closed.
and so on

Evan
"Bob Barrows [MVP]" wrote:

> Evan Nelson wrote:
> > Bob,
> >
> > I think maybe I wrote the post the wrong way. I probably should have
> > put all the code at the bottom and gotten to my questions first. So,
> > referring back to the code in my initial post here is my problem.
> >
> > The first Response.Write after the .Execute says err.number is 0. So
> > far so good. However each of the response.writes after I try to use
> > objRS return the following error:
> >
> > err = 3704 Operation is not allowed when the object is closed.
>
> That's because you did not say:
> Set objRS - objRS.NextRecordset
>
> ..
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Please Help before I pull out the last of my hair

Evan Nelson wrote:
> Bob,
>
> Do I have to do a .NextRecordset just to get the first recordset?
>

No. Only the subsequent resultsets. Look, your code says:

Set objRS = .Execute(strCommand)
.....
objRS.NextRecordset
....

It needs to say

Set objRS = .Execute(strCommand)
.....
Set objRS =objRS.NextRecordset


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Please Help before I pull out the last of my hair

Bob Barrows [MVP] wrote:
> Evan Nelson wrote:
>> Bob,
>>
>> Do I have to do a .NextRecordset just to get the first recordset?
>>
>
> No. Only the subsequent resultsets. Look, your code says:
>
> Set objRS = .Execute(strCommand)
> ....
> objRS.NextRecordset
> ...
>
> It needs to say
>
> Set objRS = .Execute(strCommand)
> ....
> Set objRS =objRS.NextRecordset
>
>
Just to clarify, NextRecordset is a method that RETURNS a NEW recordset
object. When the method is called, the original recordset object is
"cleared".

You need to set a variable to the recordset object returned by the method.
It can be the same variable, as shown above, or a new variable.
See http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthnext rec.asp

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Please Help before I pull out the last of my hair

I am getting the error on the first .getRows when I am trying to get the
first recordset rows.

"Bob Barrows [MVP]" wrote:

> Bob Barrows [MVP] wrote:
> > Evan Nelson wrote:
> >> Bob,
> >>
> >> Do I have to do a .NextRecordset just to get the first recordset?
> >>
> >
> > No. Only the subsequent resultsets. Look, your code says:
> >
> > Set objRS = .Execute(strCommand)
> > ....
> > objRS.NextRecordset
> > ...
> >
> > It needs to say
> >
> > Set objRS = .Execute(strCommand)
> > ....
> > Set objRS =objRS.NextRecordset
> >
> >
> Just to clarify, NextRecordset is a method that RETURNS a NEW recordset
> object. When the method is called, the original recordset object is
> "cleared".
>
> You need to set a variable to the recordset object returned by the method.
> It can be the same variable, as shown above, or a new variable.
> See http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthnext rec.asp
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Please Help before I pull out the last of my hair

Evan Nelson wrote:
> I am getting the error on the first .getRows when I am trying to get
> the first recordset rows.
>
You are?? I thought you said the first one was all right.

Two things:
1. Your on error resume next statement may be masking an earlier error. Try
moving the statement to immediately before opening the recordset (for now of
course)

2. I see that you are setting the connection's CursorLocation property to
adUseClient ... but you are doing that before opening it. That may prevent
the property setting from "taking". I prefer to set that property on the
recordset object itself, but there is no need to use a bulky client-side
cursor for this. Here is how I would run your procedure. Give it a try:

'open your connection as before (without touching CursorLocation), then:
Set objRS = CreateObject("ADODB.Recordset")
objConn.usrGet_ContactSearchPRODUCT_CATEGORY 3839, _
0, 1, 10,"1/1/2000", "3/10/2006", "HC", objRS

if not objRS.EOF Then vntContacts = objRS.GetRows()
Set objRS =objRS.NextRecordset
If not objRS is Nothing then
if not objRS.EOF Then vntPurchase = objRS.GetRows()
Set objRS =objRS.NextRecordset
If not objRS is Nothing then
if not objRS.EOF Then vntPages= objRS.GetRows()
End if
End if
Set objRS = nothing
objConn.close: Set objConn = Nothing
If IsArray(vntContacts) then
'process your array
else
'handle the situation where no records were returned
end if
etc.

PS. I would probably have used an output parameter for the Pages value, but
no big deal.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Please Help before I pull out the last of my hair

Bob,

I think I've changed the page to work the way you suggested, but I am still
getting an error on the first "If NOT objRS.EOF Then... " line. Since I took
out the "on error.." completely I am not getting the following error:

Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/test.asp, line 7

Line 7 is the line with "If Not objRS.EOF Then vntContacts = objRS.GetRows()

Here is the full modified ASP page. For now I'm not even trying to do
anything with the arrays that should be returned. I just want to see if I
can get them.

<% Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.ConnectionString = "Provider=SQLOLEDB.1;.........."
objConn.Open
objConn.usrGet_ContactSearchPRODUCT_CATEGORY 3839, 0, 1, 10,"1/1/2000",
"3/10/2006", "HC", objRS

If Not objRS.EOF Then vntContacts = objRS.GetRows()
Set objRS = objRS.NextRecordset

If Not objRS Is Nothing Then
If Not objRS.EOF Then vntPurchase = objRS.GetRows()
Set objRS = objRS.NextRecordset

If Not objRS Is Nothing Then
If Not objRS.EOF Then vntPages = objRS.GetRows()
End If
End If

Set objRS = Nothing
objConn.Close
Set objConn = Nothing %>

Thanks for helping me work thru this.

Evan

"Bob Barrows [MVP]" wrote:

> Evan Nelson wrote:
> > I am getting the error on the first .getRows when I am trying to get
> > the first recordset rows.
> >
> You are?? I thought you said the first one was all right.
>
> Two things:
> 1. Your on error resume next statement may be masking an earlier error. Try
> moving the statement to immediately before opening the recordset (for now of
> course)
>
> 2. I see that you are setting the connection's CursorLocation property to
> adUseClient ... but you are doing that before opening it. That may prevent
> the property setting from "taking". I prefer to set that property on the
> recordset object itself, but there is no need to use a bulky client-side
> cursor for this. Here is how I would run your procedure. Give it a try:
>
> 'open your connection as before (without touching CursorLocation), then:
> Set objRS = CreateObject("ADODB.Recordset")
> objConn.usrGet_ContactSearchPRODUCT_CATEGORY 3839, _
> 0, 1, 10,"1/1/2000", "3/10/2006", "HC", objRS
>
> if not objRS.EOF Then vntContacts = objRS.GetRows()
> Set objRS =objRS.NextRecordset
> If not objRS is Nothing then
> if not objRS.EOF Then vntPurchase = objRS.GetRows()
> Set objRS =objRS.NextRecordset
> If not objRS is Nothing then
> if not objRS.EOF Then vntPages= objRS.GetRows()
> End if
> End if
> Set objRS = nothing
> objConn.close: Set objConn = Nothing
> If IsArray(vntContacts) then
> 'process your array
> else
> 'handle the situation where no records were returned
> end if
> etc.
>
> PS. I would probably have used an output parameter for the Pages value, but
> no big deal.
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Please Help before I pull out the last of my hair

Evan Nelson wrote:
> Bob,
>
> I think I've changed the page to work the way you suggested, but I am
> still getting an error on the first "If NOT objRS.EOF Then... " line.
> Since I took out the "on error.." completely I am not getting the
> following error:
>
> Error Type:
> ADODB.Recordset (0x800A0E78)
> Operation is not allowed when the object is closed.
> /test.asp, line 7

Well, given that your procedure contains the "SET NOCOUNT ON" line, the
exclusion of which is the usual cause of this erroe, I am extremely puzzled,
to say the least.

I would suggest stripping down your procedure to make it as simple as
possible to see if it still fails. something like this:

CREATE PROCEDURE dbo.usrGet_ContactSearchPRODUCT_CATEGORY
@ConsultantID Int,
@LanguageID Int,
@RequestedPage Int,
@PerPage Int,
@FromDate VarChar(10),
@ToDate VarChar(10),
@ProductCategory Char(3)

AS
SET NOCOUNT ON
SELECT @ConsultantID as test
SELECT @LanguageID as test
SELECT @RequestedPage as test

And see if the page still raises the error.

Maybe it's as simple as moving the "SET NOCOUNT ON" line to before the table
variable declaration ...


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Please Help before I pull out the last of my hair

Evan Nelson wrote:
> Bob,
>
> I think I've changed the page to work the way you suggested, but I am
> still getting an error on the first "If NOT objRS.EOF Then... " line.
> Since I took out the "on error.." completely I am not getting the
> following error:
>
> Error Type:
> ADODB.Recordset (0x800A0E78)
> Operation is not allowed when the object is closed.
> /test.asp, line 7
>
> Line 7 is the line with "If Not objRS.EOF Then vntContacts =
> objRS.GetRows()
>
Is there anything in the connection's errors collection?

dim ADOerrors
Set ADOerrors = objConn.Errors
for i = 0 ADOerrors .Count -1
Response.Write ADOerrors(i).Number & ": " & _
ADOerrors(i).Description & "
"
next


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Please Help before I pull out the last of my hair

Bob,

Thanks alot. Moving the SET NOCOUNT ON was all it took. I just moved it to
before the line that was populating the table variable. And now everything
works the way I expected it too.

Evan

"Bob Barrows [MVP]" wrote:

> Evan Nelson wrote:
> > Bob,
> >
> > I think I've changed the page to work the way you suggested, but I am
> > still getting an error on the first "If NOT objRS.EOF Then... " line.
> > Since I took out the "on error.." completely I am not getting the
> > following error:
> >
> > Error Type:
> > ADODB.Recordset (0x800A0E78)
> > Operation is not allowed when the object is closed.
> > /test.asp, line 7
> >
> > Line 7 is the line with "If Not objRS.EOF Then vntContacts =
> > objRS.GetRows()
> >
> Is there anything in the connection's errors collection?
>
> dim ADOerrors
> Set ADOerrors = objConn.Errors
> for i = 0 ADOerrors .Count -1
> Response.Write ADOerrors(i).Number & ": " & _
> ADOerrors(i).Description & "
"
> next
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>

Re: Please Help before I pull out the last of my hair

Evan Nelson wrote:
> Bob,
>
> Thanks alot. Moving the SET NOCOUNT ON was all it took. I just
> moved it to before the line that was populating the table variable.
> And now everything works the way I expected it too.
>
Unbelievable. I need to remember this one...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.