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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy