January 5, 2003 at 10:11 pm
I have a userlogin accessing more than 1 databases.
I need to execute a stored procedure by using exec db..procname syntax by VB / ADO.
When i am using following code in VB i get an error message.
VB Code is
Dim adcmd As New ADODB.Command, intParamNum As Integer
With adcmd
.ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "db..procname"
.Parameters.Refresh
end with
Error message is
Item cannot be found in the collection corresponding to the requested name or ordinal.
Any solutions ?
Amit
January 6, 2003 at 4:16 am
When the message popsup under debug you should get a button to debug and it point to the line that is an issue? However, did you remember to set the references for the correct library so you can use ADODB and if getting this error on another machine have you verified they are at the same MDAC version as it was written under since differences can be the root of this issue? Other than that I have no issues with this snippet at all.
January 6, 2003 at 5:00 am
You sure the proc name is correct? Could have wrong db, wrong owner (remember the default tries the specified owner (or dbo), then you, if not found, quites), proc name actually spelled wrong.
Andy
January 6, 2003 at 5:07 am
i have checked. the error is on line .Parameters.Refresh
MDAC 2.6 is the version of ADO in VB Project reference.
also the procedure gets executed from SQL Query Analyzer perfectly by
syntax exec db..procname param1 , param2
i even tried db.owner.procname
but it fails from VB only.
January 6, 2003 at 5:30 am
Can you post your SP header
CREATE PROCEDURE SPNAME
PARAMS
AS
so I can compare better.
I have tried and cannot duplicate the error. Also, when you don't do the Parameters Refresh it shoudl call itself anyway the first time the Parameters collection is addressed for the command. What happens without that line? And finally can you provide a bit more of the code section for testing please.
January 6, 2003 at 11:30 pm
i went into exact details of code, and found that error is not caused by .Parameters.Refresh command.
In my next code statement, i am refering to first parameter and assigning the value. that time it gives me error
i.e. .Parameters(1).Value = 10
however the Parameters Collection does not get refresh from VB code.
I changed the code slightly as follows. and it worked. I had to change my current database for getting the parameters collections filled up by ADO. does anyone know how to do this without changing current DB ?
Dim adcmd As New ADODB.Command, intParamNum As Integer
cn.Execute "use db"
With adcmd
.ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "procname"
.Parameters.Refresh
end with
January 7, 2003 at 4:20 am
Unfortunately unless you set the default database to the one for the SP and not need to go back then this is your option as it is a limitation with the ADO Command object itself. If you need to remain in a particular DB you might try setting up an SP in that DB to call the one in the other DB with all the same parameters.
January 7, 2003 at 5:05 am
Thats new to me! I always code the params collection, never use refresh - you save a round trip and that is always worth doing.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply