Stored Procedure : Returnning Back values

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

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure : Returnning Back values

Author

Topic

think
Starting Member

3 Posts

Posted - 2004-03-30 : 08:24:46

This is the Proc :

CREATE proc DelDepartment(@DepID smallint )
AS
Declare @Result tinyint
set @Result=0
set nocount on
if not exists ( select DepartmentID
from Employee
where DepartmentID=@DepID)
and not exists ( select DepartmentID
from Departments
where ParentID=@DepID)
begin
set nocount off
Delete Departments
where DepartmentID=@DepID

IF @@ROWCOUNT > 0
set @Result=1
end
else
set @Result=2 --It has Employees or sub Dep.

select MyResult = @Result
GO

,In my peogram .. I have to use Recordset instead of command ( sure command is the best solution to get back the result ) ;because the DB proivder doesn't support creating command object ( I have found this comment on MSDN ;there are some providers cause errors with command ) .
Anyway.. here is my code :

Dim strSQL As String
Dim Rs As New ADODB.Recordset

strSQL = "EXECUTE DelDepartment " & 120

Set Rs = con.Execute(strSQL)

MsgBox Rs(0)

The error: ( Num :3265 )
item cannot be found in the collection corresponding to the requested name or ordinal

Note :
I have checked the same way for adding proc. ,and I got the same result even the last select stat. to get the new added Dep. (By the @newDepName - arg. in the adding proc. - )

thanks in advance

To live ,you have to rsik

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2004-03-30 : 13:27:00

Shouldn't it be:

MsgBox Rs(MyResult) since MyResult is what is being returned?

In fact, you might have to use MsgBox (Rs!Fields("MyResult").Value) or something like that. I can't remember the exact syntax.

Aj

think
Starting Member

3 Posts

Posted - 2004-03-30 : 18:55:10

The problem isn't the msgbox or the name of feild ( I can use several ways ) ,but the problem is that recordset doesn't contain the field !!

I hope you got me now

To live ,you have to rsik

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-31 : 00:37:09

Change the first line in this snippet to SET NOCOUNT ON


...
set nocount on
Delete Departments
where DepartmentID=@DepID

IF @@ROWCOUNT > 0
set @Result=1
end
else
set @Result=2 --It has Employees or sub Dep.
...

What DB Provider are you using that doesn't support a Command Object? Because this looks like SQL Server to me, and both the OLEDB and ODBC providers for SQL Server do support Command objects (the ODBC driver has a few limitations though)

OS

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2004-03-31 : 01:47:16

Hi!

I have made small change in ur sp. Please run in it and let me know.

Hope it works.

Regards
Sachin

CREATE proc DelDepartment(@DepID smallint )
AS
set nocount on

Declare @Result tinyint
set @Result=0

if not exists ( select DepartmentID
from Employee
where DepartmentID=@DepID)
and not exists ( select DepartmentID
from Departments
where ParentID=@DepID)

begin

Delete Departments
where DepartmentID=@DepID

IF @@ROWCOUNT > 0
set @Result=1
end
else
set @Result=2 --It has Employees or sub Dep.

select @Result

set nocount off
GO

think
Starting Member

3 Posts

Posted - 2004-04-01 : 09:30:31

yes , it is sql server provider , the command problem has been fixed by reinstallng MDAC26sp .

And I have checked your change ,but still the same problem .

the fast solution I made is :

select MyResult=1 -- replace it instead of ( set @Result=1 )

select MyResult=2 -- replace it instead of ( set @Result=2 )

It works and I can get Rs(0)

Thanks for you efforts

To live ,you have to risk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources