Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

asp error

Status
Not open for further replies.
Guest_imported

Guest_imported

New member
Jan 1, 1970
0
I am sure someone must have run into this problem.
When I run the code in SQL Server, it works.
However, it tells me that no records is found which is not true.
I have a code that inserts records into my oracle and sql server dbs, depending on which database I am using.
So part of the record that are inserted I am trying to retrieve with this code.
Like I said, in sql server, it runs the code without error but tells me no record found.
In Oracle, it just gives me this error:
"unable to perform operation when object is closed".
I used a commit in my stored proc just in case it is not finding any records in the database.
Please help!!!

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

which is this code:

<% If Not objRs.EOF And objRs.BOF Then
While not objRs.Eof %>

Here is the rest of the code:

<% Language=VBScript %>
<html>
<head>
<meta NAME=&quot;GENERATOR&quot; Content=&quot;Microsoft Visual
Studio 6.0&quot;>
</head>
<body>

<%

const ArraySep = &quot;,&quot;
Dim objConn,objCmd,objRS
Set objConn =server.CreateObject(&quot;ADODB.Connection&quot;)
Set objCmd= server.CreateObject(&quot;ADODB.Command&quot;)
Set objRs=server.CreateObject(&quot;ADODB.Recordset&quot;)

objConn.CursorLocation = 3
objConn.Open &quot;DSN=mydb;UID=scott;PWD=tiger&quot;

objRs.CursorLocation = 3
objRs.CursorType = 3
objRs.LockType = 4

dim StRaData , StData
StRaData = Request.Form(&quot;txtData&quot;) & ArraySep
StData = Split(straData, ArraySep )

With objCmd
.CommandType = &H0004
.ActiveConnection = objConn
.CommandText = &quot;stp_TEST_GetCV&quot;
.Parameters.Append .CreateParameter(&quot;P_FirstName&quot;,200, &H0003, 20, stData(0))
.Parameters.Append .CreateParameter(&quot;P_LastName&quot;,200, &H0003, 20, stData(1))
.Parameters.Append .CreateParameter(&quot;P_Phone&quot;, 200,&H0003, 20, stData(2))
End With

objRs.Open objCmd
%>


<TABLE cellSpacing=0 cellPadding=10 width=&quot;58%&quot;
border=1

align=&quot;center&quot;>

<TR>
<TD align=middle>
CV Search Result
</TD></TR>
<TR>
<% If Not objRs.EOF And objRs.BOF Then
While not objRs.Eof %>
<TR><TD><%= objRs.Fields(1).value%></TD></TR>
<TR><TD><%= objRs.Fields(2).value%></TD></TR>
<TR><TD><%= objRs.Fields(3).value%></TD></TR>
<TR><TD><%= objRs.Fields(4).value%></TD></TR>
<% obRs.moveNext
Wend
else
response.write stData(0) & stData(1) & stData(2) %>
<TR><TD> No Records Found...</TD></TR>
<% end if

Set objCmd=nothing
objRs.close
Set objRs=nothing
Set Conn = nothing

%>

</TR>

</TABLE>
</body>
</html>
 
Sort by date Sort by votes
Can't be certain without seeing the SP, but it sounds like no data is being returned from the SP. Is the last action performed in the SP a SELECT statement? codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
<insert witticism here>
 
Upvote 0 Downvote
Thanks codestorm for your reply.
First,Yes, the last action perform in the SP is a select statement.
Actually, there are two SPs. One does the insert and another does the select.
The insert works fine in both oracle and sql server because after executigng the insert SP with asp, I can do a select in either an oracle database or sql server database and have them return records,
It is the select side that, I guess is causing the problem.
The SP written in Sql server by a good guy called Nouman will give a message that says no records found while the SP written in Oracle will just give the error given my prior thread.
Here is a copy of both Oracle SP and SqL server SP.
Oracle first, followed by Sql server.

CREATE OR REPLACE PROCEDURE stp_TEST_GetCV(
P_FirstName IN OUT CVSubmission.FirstName%TYPE,
P_LastName IN OUT CVSubmission.LastName%TYPE,
P_Phone IN OUT CVSubmission.Phone%TYPE
)

AS
P_Fax CVSubmission.Fax%TYPE ;
P_Address1 CVSubmission.Address1%TYPE;
P_Address2 CVSubmission.Address2%TYPE;
P_City CVSubmission.City%TYPE;
P_State CVSubmission.State%TYPE;
P_ZipCode CVSubmission.ZipCode%TYPE;
P_Country CVSubmission.Country%TYPE;
P_EducationDetails CVSubmission.EducationDetails%TYPE;
P_YearExperience CVSubmission.YearExperience%TYPE ;
P_ExperienceDetails CVSubmission.ExperienceDetails%TYPE;
P_Email CVSubmission.Email%TYPE;

cursor cv IS SELECT * FROM CvSubmission
WHERE LastName = P_LastName
AND FirstName =P_FirstName
AND Phone = P_Phone;
BEGIN
open cv;
FETCH cv INTO
P_FirstName ,
P_LastName,
P_Email,
P_Phone ,
P_Fax ,
P_Address1 ,
P_Address2 ,
P_City ,
P_State,
P_ZipCode,
P_Country,
P_EducationDetails,
P_YearExperience,
P_ExperienceDetails;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_Error(-2001,'No records found');
END;

This sql server version:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stp_TEST_GetCV]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[stp_TEST_GetCV]
GO

CREATE PROCEDURE stp_TEST_GetCV

@P_FirstName varchar(20) ,
@P_LastName varchar(20) ,
@P_Phone varchar(20)

/********************************************************
*Procedure Name : stp_TEST_GetCV
*
*Auther: Nouman
*
*_______________________________________________
*Param Name Type Length
*_______________________________________________
*
*@P_FirstName varchar 20
*@P_LastName varchar 20
*@P_Phone varchar 20
*________________________________________________
*
*Note:
*
*Tables Accessed: CVSubmission
*
*Stored Procedure Accessed: -None-
*
*Temp tables created and used: -None-
*
*Reture Value: RecordsSet
*
*Any Special Notes:
*
*Revision#1
*
*
***************************************************************/

AS

BEGIN

Declare @STR_SQL nvarchar(4000)

SET @P_FirstName = Replace(@P_FirstName, '''' , '''''')
SET @P_LastName = Replace(@P_LastName, '''' , '''''')
SET @P_Phone = Replace(@P_Phone , '''' , '''''')



Set @STR_SQL = 'SELECT * FROM CVSubmission
WHERE Isnull(LastName,'''') like '''+ @P_LastName+'%''
AND Isnull(FirstName,'''') like '''+ @P_FirstName+'%''
AND isnull(Phone,'''') like '''+ @P_Phone +'%'''

BEGIN

EXEC (@STR_SQL)

END

END

GO

 
Upvote 0 Downvote
Correction codestorm.
I said in my last thread that if I do a select from sql server or oracle database, records are returned.
That statement is partly true.
If I do a wildcard search, eg., select* from cvsubmission,
all records inserted via the other asp are retrieved and displayed but if I do a conditional search such as the one on the SP, no rows are returned. So just in case, I am including the asp that does the insert statement.
<% Language=VBScript %>
<html>
<head>
<meta NAME=&quot;GENERATOR&quot; Content=&quot;Microsoft Visual
Studio 6.0&quot;>
</head>
<body>

<%
const ArraySep = &quot;,&quot;
Dim objConn,objCmd
Set objConn = server.CreateObject(&quot;ADODB.Connection&quot;)
Set objCmd= server.CreateObject(&quot;ADODB.Command&quot;)
objConn.CursorLocation = 3
objConn.Open &quot;DSN=mydb;UID=scott;PWD=tiger&quot;


dim StRaData , stData
StRaData = Request.Form(&quot;txtData&quot;) & ArraySep
StData = Split(straData, ArraySep )
'Response.write stRaData 'Check whether it has correct contents which u passed?

With objCmd
.CommandType = &H0004
.ActiveConnection = objConn
.CommandText = &quot;stp_TEST_InsertCV&quot;
.Parameters.Append .CreateParameter(&quot;P_FirstName&quot;,200, &H0001, 20, stData(0))
.Parameters.Append .CreateParameter(&quot;P_LastName&quot;,200, &H0001, 20, stData(1))
.Parameters.Append .CreateParameter(&quot;P_Email&quot;, 200,&H0001, 50, stData(2))
.Parameters.Append .CreateParameter(&quot;P_Phone&quot;, 200,&H0001, 20, stData(3))
.Parameters.Append .CreateParameter(&quot;P_Fax&quot;, 200,&H0001, 20, stData(4))
.Parameters.Append .CreateParameter(&quot;P_Address1&quot;,200, &H0001, 50, stData(5))
.Parameters.Append .CreateParameter(&quot;P_Address2&quot;,200, &H0001, 50, stData(6))
.Parameters.Append .CreateParameter(&quot;P_City&quot;, 200,&H0001, 20, stData(7))
.Parameters.Append .CreateParameter(&quot;P_State&quot;, 200,&H0001, 20, stData(8))
.Parameters.Append .CreateParameter(&quot;P_ZipCode&quot;,200, &H0001, 20, stData(9))
.Parameters.Append .CreateParameter(&quot;P_Country&quot;,200, &H0001, 20, stData(10))
.Parameters.Append .CreateParameter(&quot;P_EducationDetails&quot;, 200, &H0001,255,stData(11))
.Parameters.Append .CreateParameter(&quot;P_YearExperience&quot;, 200, &H0001, 5,stData(12))
.Parameters.Append .CreateParameter(&quot;P_ExperienceDetails&quot;, 200, &H0001,255,stData(13))
.Execute
End With

Set objCmd=nothing
Set Conn = nothing

%>


<TABLE cellSpacing=0 cellPadding=10 width=&quot;58%&quot; border=1

align=&quot;center&quot; bgcolor=&quot;ffffff&quot;>

<TR>
<TD align=middle bgcolor=&quot;a0b8c8&quot;><b>Kenig Submission Result</b>
</TD></TR>
<TR>
<TR><TD><font color=green face=arial><b><%= Request.Form(&quot;txtData&quot;)(1)%></b></font></TD></TR>
<TR><TD><font color=green face=arial><b><%= Request.Form(&quot;txtData&quot;)(2)%></b></font></TD></TR>
<TR><TD><font color=green face=arial><b><%= Request.Form(&quot;txtData&quot;)(3)%></b></font></TD></TR>
<TR><TD><font color=green face=arial><b><%= Request.Form(&quot;txtData&quot;)(4)%></b></font></TD></TR>
</TR>
<TR><TD align=&quot;middle&quot; bgcolor=&quot;a0b8c8&quot;><b>Thank you for Submitting to Kenig. <br> Your information has been stored in our database</b>
</TD>
</TR>
</TABLE>
</body>
</html>
 
Upvote 0 Downvote
Hi

Not sure if this is the answer to your problem or not, but I've spent hours tearing my hair out over the exact same error before - only to finally realise that I've got a print statement in the SP which basically stops it all from working.

 
Upvote 0 Downvote
You may find that the set statement is being returned as a record set, so in theory you actually have 2 recordsets. Since you are starting at the first recordset as far as the sql is concerned, it doesnt contain the objects you are calling in your sql. You could test this by running the sql code in query analyser (SQL).
If so, try at the start of your code adding

CREATE PROCEDURE stp_TEST_GetCV
@P_FirstName varchar(20) ,
@P_LastName varchar(20) ,
@P_Phone varchar(20)


AS

--******************************
SET NOCOUNT ON
--******************************

BEGIN

Declare @STR_SQL nvarchar(4000)

SET @P_FirstName = Replace(@P_FirstName, '''' , '''''')
SET @P_LastName = Replace(@P_LastName, '''' , '''''')
SET @P_Phone = Replace(@P_Phone , '''' , '''''')



Set @STR_SQL = 'SELECT * FROM CVSubmission
WHERE Isnull(LastName,'''') like '''+ @P_LastName+'%''
AND Isnull(FirstName,'''') like '''+ @P_FirstName+'%''
AND isnull(Phone,'''') like '''+ @P_Phone +'%'''

BEGIN

EXEC (@STR_SQL)

END

END
SET NOCOUNT OFF

GO
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
538
xwb
xwb
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
542
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
446
penguinspeaks
penguinspeaks
how2trainyourdragon
  • Locked
  • Question
Request.Server variables blank for classic ASP
Replies
12
Views
995
GriffMG
GriffMG
Swi
  • Locked
  • Question
JQUERY AJAX POST and how to receive data via Classic ASP
Replies
0
Views
584
Swi
Swi

Part and Inventory Search

Sponsor

Back
Top