June 3, 2002 at 10:20 am
Hi everybody!
I have this SP that is returning a recordset a in a output variable the rowcount. But somehow the output variable is empty. Can somebody give me a hint?
Thanks,Durug
CREATE PROCEDURE [sp_inventory_Class_click]
(
@clickclass char(2),
@countNr int OUTPUT
)
AS
BEGIN
SELECT item_class,
sum(quantity_on_hand * frozen_unit_cost)
AS Item_Valuation
FROM Item_Master AS a INNER JOIN
Item_frozen_cost AS b ON
a.location_code = b.location_code AND
a.item_number = b.item_number
where Inactive_code <> 'I'
and substring(item_class,1,2) = @clickclass
group by item_class
ORDER BY Item_valuation desc
select @countNr = @@rowcount
END
June 3, 2002 at 10:36 am
How are you retrieving your value, and can you post the relavent code?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 10:56 am
set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = "Provider=SQLOLEDB;Server=local;Database=INTRA;UID=sa;PWD="
cmd.CommandText = "sp_inventory_Class_click"
cmd.CommandType = 4
cmd.Parameters.Append cmd.CreateParameter("@clickclass",adChar,adParamInput,2, Label)
cmd.Parameters.Append cmd.CreateParameter("@countnr",adInteger,adParamOutput)
set objrs = cmd.Execute
response.write cmd.parameters("@countnr")
June 3, 2002 at 11:36 am
Have to close the recordset to get the parameter.
Andy
June 3, 2002 at 11:40 am
Try response.write cmd.parameters(1) (may be 0 but try 1 first).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 12:03 pm
Apparently is has to do something with the cursor location.
I changed the code to this
set cn = Server.CreateObject("ADODB.Connection")
set cmd = Server.CreateObject("ADODB.Command")
set objrs = Server.CreateObject("ADODB.Recordset")
cn.CursorLocation = adUseClient ' Uncomment this line to get the output parameter early.
cn.Open "Provider=SQLOLEDB;Server=local;Database=INTRA;UID=sa;PWD="
set cmd.ActiveConnection = cn
cmd.CommandText = "sp_inventory_Class_click"
cmd.CommandType = 4
cmd.Parameters.Append cmd.CreateParameter("@clickclass",adChar,adParamInput,2, Label)
cmd.Parameters.Append cmd.CreateParameter("@countnr",adInteger,adParamOutput)
set objrs = cmd.Execute
And it's working. As you can see from the code if you comment this line cn.CursorLocation = adUseClient (then the location will be server side) the parameter is empty again.
Anyway,
thanks guys!
June 3, 2002 at 5:55 pm
Glad to hear you found a solution.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply