Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
|
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 5th, 2004, 01:59 PM
Authorized User
 
Join Date: Jul 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default Calling stored procedure from ASP page

Hi!
I have SP set up on my SQL Server. SP has a select statment that selects multiple columns. When I invoke this SP from my ASP page I do not get all the selected values on my ASP page. However, I am execute the same select statement directly from my ASP page I get all the selected values. Here's the snippet of my SP and ASP code

CREATE Procedure AllRecByApp
   @uid int
As
    SELECT a, b, c, d, and so on (about 50 columns)
    FROM tblApps
    WHERE appId = @uid
GO

Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = cn
objCmd.CommandText = "AllRecByApp"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("uid",adInteger,adParamInpu t,,uId)

Set rs = objCmd.Execute
Set objCmd = Nothing

I use rs to plot values on my asp form, e.g. rs("a"), rs("b") etc.

Can anyone help me why it is happening?

Nirav


 
Old February 5th, 2004, 02:34 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Quote:
quote:Originally posted by niravp
When I invoke this SP from my ASP page I do not get all the selected values on my ASP page. However, I am execute the same select statement directly from my ASP page I get all the selected values.
Could you please clarify this? This statement reads to me that it both works and doesn't work.

Peter
------------------------------------------------------
Work smarter, not harder.
 
Old February 5th, 2004, 03:11 PM
Authorized User
 
Join Date: Jul 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry for the confusion:
The SP does not give me all the values selected in my select statement when I display the values on my ASP page. However, when I write the same SQL statement on the ASP and execute it, I'll get all the selected values:

Here is how I've tested:
'Execute sp
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = cn
objCmd.CommandText = "AllRecByApp"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Append objCmd.CreateParameter("uid",adInteger,adParamInpu t,,uId)

Set rs1 = objCmd.Execute
Set objCmd = Nothing
arr1 = rs1.getRows()
FOR row=0 TO UBOUND(arr1, 2)
    FOR col=0 TO UBOUND (arr1, 1)
    response.write arr(col,row)&"<br>"
    NEXT
NEXT
The for loop above does not print all the selected values in SP

'SQL string on ASP page
strSQL = "SELECT a, b, c, d, e, and so on "_
     & " FROM tblApps"_
     & " WHERE appId = '"&uId&"';"

Set rs2 = Server.CreateObject("ADODB.RECORDSET")
rs2.open strSQL, cn, adOpenStatic, adLockReadOnly, adCmdText
arr2 = rs2.getRows()
FOR row=0 TO UBOUND(arr2, 2)
    FOR col=0 TO UBOUND (arr2, 1)
    response.write arr(col,row)&"<br>"
    NEXT
NEXT
This FOR loop print all the selected values and the SQL statement for this recordset is exactly the same as SP.

Hope this clarify what I'm saying.

Nirav



Quote:
quote:Originally posted by planoie
 
Quote:
quote:Originally posted by niravp
Quote:
When I invoke this SP from my ASP page I do not get all the selected values on my ASP page. However, I am execute the same select statement directly from my ASP page I get all the selected values.
Could you please clarify this? This statement reads to me that it both works and doesn't work.

Peter
------------------------------------------------------
Work smarter, not harder.
 
Old February 5th, 2004, 04:38 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

What does the SP look like? It sounds like there is a problem with the SP.
 
Old February 5th, 2004, 05:11 PM
Authorized User
 
Join Date: Jul 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by planoie
 What does the SP look like? It sounds like there is a problem with the SP.
SP looks like as follows:

CREATE Procedure AllRecByApp
   @uid int
As
    SELECT a, b, c, d, and so on (about 50 columns)
    FROM tblApps
    WHERE appId = @uid
GO

I wonder why not all the selected records are not returned from SP. Actually, I have copied and pasted the same SQL select statement of SP on my ASP page, and it works on ASP page.



 
Old March 18th, 2004, 03:23 PM
Registered User
 
Join Date: Mar 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to kbrown2974
Default

Why are you doing objCmd.Parameters.Append objCmd.CreateParameter("uid",adInteger,adParamInpu t,,uId)?

Cant you just use objCmd.CommandText = "AllRecByApp("uid",adInteger,adParamInput,,uId )" and get rid of the CreateParameter line?



 
Old March 19th, 2004, 04:41 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Seems strange. What exactly are the differences between results from command and results from direct SQL? Try putting SET NOCOUNT ON and SET NOCOUNT OFF into your stored procedure. The results from this can be interpreted as a recordset.

--

Joe
 
Old March 19th, 2004, 07:40 PM
Authorized User
 
Join Date: Jul 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by joefawcett
 Seems strange. What exactly are the differences between results from command and results from direct SQL? Try putting SET NOCOUNT ON and SET NOCOUNT OFF into your stored procedure. The results from this can be interpreted as a recordset.

--

Joe
Very strange! I don't have a clue why it would not work. I tried set nocount on and off. Did not work.






Similar Threads
Thread Thread Starter Forum Replies Last Post
calling stored procedure jomet JSP Basics 0 November 23rd, 2007 08:06 AM
Calling Stored Procedure Using vc++ senthil_mano Visual C++ 0 August 30th, 2006 12:20 AM
Calling Stored Procedure with parameters zarina_24 Classic ASP Professional 4 March 2nd, 2006 11:57 AM
Returning Stored Procedure Results from ASP page JennaAckerson Classic ASP Basics 0 December 23rd, 2005 11:44 AM
Calling an Oracle Stored Procedure booksnore2 BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 October 1st, 2004 09:35 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.