March 21, 2005 at 7:59 am
Hi all,
I developed a store proc that returns a ResultSet (as a result of a SELECT statement) and an INT value (as a result of a SELECT COUNT(pKey) ... statement)
Now the problem I'm facing is with the front-end application. I'm using ASP and I wrote this code just to test what version of the output parameter works OK:
'The Connection and Command objects are created succefully, since I can display the records, and the store proc works OK too because I tested it from Query Analyzer displaying the expected results.
...
cmd.CommandText="spGetAds_OO"
cmd.Parameters.Append cmd.CreateParameter("@RETURN_VALUE1", adInteger, adParamReturnValue)
cmd.Parameters.Append cmd.CreateParameter("@LtCoName", adVarWChar, adParamInput, 255, LtCoName)
... 'Other input parameters here
cmd.Parameters.Append cmd.CreateParameter("@RETURN_VALUE2", adInteger, adParamOutput)
Set rs=cmd.Execute 'Test # 1 here
Response.Write("**" & cmd.Parameters("@RETURN_VALUE1") & "**")
'This displays ****, which means that the output parameter is empty string.
...'Test # 2, some procesing with the recordser here. After it reach EOF and before I close it:
Response.Write("**" & cmd.Parameters("@RETURN_VALUE1") & "**") %>
'This displays ****, which means that the output parameter is empty string.
...
rs.close() 'Test # 3, testing output parameter after the rs is closed:
Response.Write("**" & cmd.Parameters("@RETURN_VALUE1") & "**") %>
Again empty string
The same results are displayed if I use @RETURN_VALUE2 version.
How can I set up ASP environment(ADO objects) in order to use any kind of the output parameter feature?
Regards,
Orlando Otero
P.S. Sorry about my English
March 21, 2005 at 8:11 am
adInteger and adParamOutput... are meaningless in asp.
You must replace them with their equivalent int value.
Ex adParamInput = 1.
There's also a workaround where you must include a vb file but I don't remember how to do this. This file allows you to use the vb enums instead of their values.
March 21, 2005 at 8:18 am
I set up a Connection, Command and RecordSet Objects in a file that is included in all .asp that calls store proc. That file includes too "adovbs.asp" file, which contains all constants declaration. I don't think that the use of adInteger, etc. would be the problem.
Regards,
Orlando Otero
March 21, 2005 at 8:29 am
I could be wrong, but it seems to me that if the problem were an unknown constant he would be getting runtime errors instead of just an empty output parameter result. I guess my question would be does your stored procedure actually place the value within the output parameter? It seems too obvious, but from what you stated isn't necessarily true. From Query Analyzer if you do the following (adjusted for reality):
declare @ReturnValue1 int
exec MyTestProc @Input1 = 'A', @Input2 = 'B', @RETURN_VALUE1 = @ReturnValue1 OUTPUT
print ISNULL( CONVERT(varchar, @ReturnValue1), 'NULL' )
Do you get the expected value, or do you get NULL? If NULL you need to assign the value to the variable prior to exiting the stored procedure. The way you described it you are just returning two resultsets instead of generating output parameter values.
March 21, 2005 at 8:42 am
Here is how I tested the stored proc:
Declare @Regs1 INT, @Regs2 INT
exec @Regs1=spGetAds_OO '', '', NULL, '=', NULL, NULL, NULL, 25, 1, @Regs2 OUTPUT
Print @Regs1
Print @Regs2
//Store proc here
CREATE PROCEDURE spGetAds_OO
@LtCoName nvarchar(255)=NULL,
@LtCoPhone nvarchar(50)=NULL,
@SaleAmount money=NULL,
@Expr nvarchar(10)=NULL,
@CCOrChNo nvarchar(50)=NULL,
@CheckAcctNo nvarchar(50)=NULL,
@IdOwner int=NULL,
@PageSize INT=25,
@PageIndex INT=1,
@RETURN_VALUE INT OUTPUT
AS
...
declare @rv int
Exec @RETURN_VALUE=spGetPagedData_OO 'tblRecorder R', 'R.rc_AdNo', 'R.rc_AdNo', @PageSize, @PageIndex, @Where, @rv Output
Return @RETURN_VALUE
And in both versions of the output parameter it works OK through Query Analyzer.
Regards,
Orlando Otero
March 21, 2005 at 8:47 am
I'm using OLEDB provider for ODBC. Does this could be the problem?
OO
March 21, 2005 at 1:55 pm
I doubt it. Try the following to see if making your code reflect these examples more closely helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg02_525v.asp
I've never used output parameters like this, but it may be that cmd.Parameters("@RETURN_VALUE1") doesn't access the output parameter. It may be in a different collection (I have no idea). The MSDN examples keep using enumerated vs named access. So, try enumerated access as they do and if that works try looking for a different collection other than Parameters that may be holding the result values.
Actually, I think this last sentence from the second link is the answer:
After each parameter is added to the Parameters collection, executing the query string creates a recordset. After the recordset is closed, the values for the return code and output parameters are available.
March 21, 2005 at 3:17 pm
I found NO WAY to make it works.
And now Short Circuit evaluations in ASP's IF statements doesn't work too.
If rs.State=0 Or rs.EOF ... gave me error at this line when I know that rs.State=0
This was one of the problems I faced 5 years ago and I decided to develop with Servlet/JSP technologies.
Thanks anyway, I'll see what to do.
Regards,
OO
March 22, 2005 at 1:25 am
With a default serverside firehose cursor I indeed wouldn't expect the output params to be populated until after all the records are read and the recordset closed, but I would however expect the last Response.Write to work though...
What you might want to try is to make this a client side recordset. In that case you should be able to access the output param anywhere you wish. To do so add
cmd.ActiveConnection.CursorLocation = adUseClient
before executing your sproc
Good luck!
March 22, 2005 at 6:35 am
It looks as though Remi's answer may be correct. ASP is late bound and dosen't know about the ADO constants unless you are including adovbs.inc OR added the ADO typlibe meta tags in global.asa.
1. Replace the command parameter constants with their like numeric values
2. or Hard-code the values in your own include
3. or do one of the above in the top paragraph and you should be ok.
March 22, 2005 at 7:22 am
I think your problem is to with you parameter declarations for @RETURN_VALUE1 and @RETURN_VALUE2.
ADO uses @RETURN_VALUE for the return value and even though you used @RETURN_VALUE as an output parameter in your proc the command object will contain two parameters named @RETURN_VALUE (not @RETURN_VALUE1 and @RETURN_VALUE2) and these will be tied to your proc.
When you appended your two parameters (@RETURN_VALUE1 and @RETURN_VALUE2) they will not be used and therefore contain nothing.
Far away is close at hand in the images of elsewhere.
Anon.
March 22, 2005 at 9:28 am
Hi guys,
I included "connTest.asp" which includes "adovb.asp" which includes all ADO constants definition, as mentioned earlier, if the adSomeLiteral=SomeValue constants didn't load the correct value I would get a RUNTIME Error, so the connection is setted up correctly, the command and parameters are created succesfully, the store proc works fine since I tested with ALL cases from Query Analyzer, even I tested the store proc from ASP environment, I call the SP as follow:
'At this point conn stores Connection object, cmd stores Command Object and rs stores the only one RecordSet I create during the entire lifecycle of the Web application, and at this point too, rs hasn't been opened although I create it using CreateObject(ADODB.RecordSet)
'Code changed for n time in the last 2 days.
rs.CursorLocation=adUseClient
cmd.ActiveConnection.CursorLocation = adUseClient 'just as some of you suggested
cmd.Parameters.Append cmd.CreateParameter("@LtCoName", adVarWChar, adParamInput, 255, LtCoName) 'Param1
cmd.Parameters.Append cmd.CreateParameter("@LtCoPhone", adVarWChar, adParamInput, 50, LtCoPhone) 'Param2
cmd.Parameters.Append cmd.CreateParameter("@SaleAmount", adCurrency, adParamInput, , SaleAmount) 'Param3
cmd.Parameters.Append cmd.CreateParameter("@Expr", adVarWChar, adParamInput, 10, CondExpr) 'Param4
cmd.Parameters.Append cmd.CreateParameter("@CCOrChNo", adVarWChar, adParamInput, 50, AcCheck) 'Param5
cmd.Parameters.Append cmd.CreateParameter("@CheckAcctNo", adVarWChar, adParamInput, 50, AcCC) 'Param6
cmd.Parameters.Append cmd.CreateParameter("@IdOwner", adInteger, adParamInput, , NULL) 'Param7
If IdUser<>"" Then cmd.Parameters("@IdOwner").Value=IdUser
cmd.Parameters.Append cmd.CreateParameter("@PageSize", adInteger, adParamInput, , intRows) 'Param8
cmd.Parameters.Append cmd.CreateParameter("@PageIndex", adInteger, adParamInput, , intPage) 'Param9
'OUTPUT Parameter
Set paramOutput=cmd.CreateParameter("@Output", adInteger, adParamOutput) 'Param10 the OUTPUT PARAMETER
cmd.Parameters.Append paramOutput
cmd.CommandText="spGetAds_OO"
Now what comes is interesting, if I write:
cmd.Execute
and I use the output parameter(s) here, there is no problem, I can read it with the expected values, the PROBLEM is when I work with the RecordSet:
Set rs=cmd.Execute
'Output paramater doesn't get updated here
....
rs.close()
'Output paramater doesn't get updated here
Set rs=Nothing
'Output paramater doesn't get updated here
I don't know HOW TO MAKE WORK something as simple as a set of rows and an output parameter returned by the same store procedure.
Any idea?
Regards,
OO
March 22, 2005 at 1:16 pm
Ok guys, finally the problem was solved.
Attempting to access the output parameter without having closed the RecordSet probably through an Exception that due to the fact that some included file as the On Error Resume Next statement inside, the Exception was ignored and the execution continues, and when the execution reachs the parameter access statement, it never got updated. I think this was what happend becasue I removed the parameter access statement that follows the Set rss=cmd.Execute and the parameter gets update after rs is closed.
Thanks a lot to all of you
Regards,
OO
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply