SQL Server Performance Forum – Threads Archive

problem using a stoed procedure

i have 2 DB
and i have a USER that defined as a db_owner of both of the DB’s.
when i try to activate a SP on the second Db like this :
set cm_delOrder = Server.CreateObject("ADODB.Command")
cm_delOrder.ActiveConnection = Application("ConnStr")
cm_delOrder.CommandType = adcmdstoredproc
cm_delOrder.CommandText = "DB2.dbo.sp_DeleteOrder"
response.write request("id") ‘ isee that i do have a value in "id"
cm_delOrder.Parameters("@ID") = cint(request("id"))
cm_delOrder.Execute
when i activate this SP with an id from the query analzyzer everything works great!
when i do the same in asp(with the above code) i get the errorr : quote:
——————————————————————————–
ADODB.Command error ‘800a0cc1’ Item cannot be found in the collection corresponding to the requested name or ordinal.
orders.asp, line 10 ——————————————————————————– what can be the reason?
thnaks in advance
peleg

Don’t you have to append a parameter to the Parameters collection of your command object, before executing the command?

Hi, Please check your stored procedure. Is this input or input/output parameter in ur sp? cm_delOrder.Parameters("@ID") = cint(request("id")) Please check the @ID with ur sp. It might be input or input/output parameter. If it is only output, then it will give an error i think…. ):-Saravanan.

well in my SP its like this
….
@Id int
as …..
that all

CREATE PROCEDURE dbo.MyProc (@Id int output)
AS ………. Your ADO object may also need to mark the parameter as an "input/output" parameter – check appropriate literature on ASP and ADO for that. Does your procedure by any chance end with the line:
SELECT @Id
or
PRINT @Id … then I can understand why you’re seeing a result in QA.

Hi ya, As Adriaan mentioned you can’t just refer to a parameter like that, you need to create it and add it to the collection e.g. Set oParam = cm_delOrder.CreateParameter("@ID", adInteger, adParamInput)
cm_delOrder.Parameters.Append oParam
oParam.Value = cint(request("id")) also if id is a query string parameter then be aware that google won’t index any url that contains &id= Cheers
Twan

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> that google won’t index any url that contains &id= <hr height="1" noshade id="quote"></blockquote id="quote"> Not true anymore. But usually you can’t expect a high ranking. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

google’s guidelines say otherwise… >>
Don’t use "&id=" as a parameter in your URLs, as we do not include these pages in our index
<< http://www.google.com/webmasters/guidelines.html Cheers
Twan

http://www.google.de/search?q=%26id&start=0&ie=utf-8&oe=utf-8&meta=lr%3Dlang_de Strange. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

Your ADO object may also need to mark the parameter as an "input/output" parameter i dont need tio mark it as input/output i have lot of sored procudures with list of variables that i send to the SP
withought any need to append or create A new paramter
the thing is that i have played with it more and foun out something strane :
if i do
cm_delOrder.CommandText = "DB1.dbo.sp_DeleteOrder" –> it dosnet work!!! if i do cm_delOrder.CommandText = "dbo.sp_DeleteOrder" it workes!!!! when i do on DB2 which has the same SP like this
cm_delOrder.CommandText = "DB2.dbo.sp_DeleteOrder"
it wont work!!!
why can i access a SP by telling it on chich db to run the SP from?
why doing "DB1.dbo.sp_DeleteOrder" wont work but the same
"dbo.sp_DeleteOrder" does work?
thnaks in advance
peleg

]]>