How to execute a dynamic where clause sp in ASP

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

Development Tools

Other Development Tools

How to execute a dynamic where clause sp in ASP

Author

Topic

gbaia
Yak Posting Veteran

52 Posts

Posted - 2004-06-29 : 12:24:01

I just did a stored procedure using the article
http://www.sqlteam.com/item.asp?ItemID=2077

The sp works fine, I've tested it many times in my SQL query analyzer.

The problem is that I don't know how to execute it from my asp page, so that it gets all the parameters and returns my query.

I've done this before when it's just an update or insert using the command.

For now, I have this:

Customer_ID = Request("Customer_ID")
Order_ID_From = Request("Order_ID_From")
Order_ID_To = Request("Order_ID_To")
Order_Date_From = Request("Order_Date_From")
Order_Date_To = Request("Order_Date_To")

set OrderlineRS = Server.CreateObject("ADODB.Recordset")
OrderlineRS.ActiveConnection = MM_site_STRING
OrderlineRS.Source = "{call Invoice_Search(" & Customer_ID & ", " & Order_ID_From & ", " & Order_ID_To & ", '" & Order_Date_From & "', '" & Order_Date_To &"')}"
OrderlineRS.CursorType = 0
OrderlineRS.CursorLocation = 2
OrderlineRS.LockType = 3
OrderlineRS.Open
OrderlineRS_numRows = 0

I get the following error message:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E21)
The requested properties cannot be supported.
/admin/invoice_details1.asp, line 30

And the stored procedure is:

CREATE PROCEDURE admin_get_invoices

@Order_ID_From int = null,
@Order_ID_To int = null,
@Date_From datetime = null,
@Date_To datetime = null,
@Customer_ID int = null

AS

SELECT * FROM Orders
INNER JOIN Orderlines
ON Orders.Order_ID = Orderlines.Order_ID
WHERE Order_Status='P' AND Image_Used=1
AND Orderlines.Order_ID >= COALESCE (@Order_ID_From, Orderlines.Order_ID)
AND Orderlines.Order_ID <= COALESCE (@Order_ID_To, Orderlines.Order_ID)
AND Order_Date >= COALESCE (@Date_From, Order_Date)
AND Order_Date <= COALESCE (@Date_To, Order_Date)
AND Customer_ID = COALESCE (@Customer_ID, Customer_ID)
GO

Thank you so much!!!!!

Grazi

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-29 : 19:31:35

Firstly - your OrderlineRS.Source call is to a different stored procedure....

Secondly, try outputting the command text from your OrderlineRS.Source line and try running this in QA. Maybe some of your formatting is a bit screwy.

Also, you could try changing the way you call your SP from ADO:

1. Direct execution:
dim cn
dim rst
set cn = Server.CreateObject("ADODB.Connection")
cn.ConnectionString = "your conn string"
cn.Open
set rst = cn.admin_get_invoices(param1, param2, param3)

2. Using the ADO Command object (see your ADO documentation for more details)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources