array with sql server 2000 - SQL Server Forums

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

Transact-SQL (2000)

array with sql server 2000

Author

Topic

alekos66
Starting Member

2 Posts

Posted - 2006-05-11 : 09:42:30

hello
I have created one store procedures that return a table variable

'CREATE PROCEDURE sptcondconsiglieri @immobile_id varchar(6)
as
DECLARE @tbl table(condomio_id Varchar(6),titlo varchar(5),nominativo varchar(256),stato int)
DECLARE @colA nvarchar(50)
DECLARE @MyCursor CURSOR
/*declare @mycursor1 cursor*/

SET @MyCursor = CURSOR FAST_FORWARD
FOR
Select nome_consigliere
From t_immoconsiglieri
where immobile_id=@immobile_id
order by posizione
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ColA
WHILE @@FETCH_STATUS = 0
BEGIN

Insert @tbl
SELECT dbo.T_Condomini.Condomino_id,dbo.T_Condomini.titolo,dbo.T_Condomini.Nominativo,dbo.T_UniCond.StCon_id
FROM dbo.T_Condomini INNER JOIN
dbo.T_UniCond ON dbo.T_Condomini.Condomino_id = dbo.T_UniCond.Condomino_id INNER JOIN
dbo.T_Unita ON dbo.T_UniCond.Unita_id = dbo.T_Unita.Unita_id
WHERE (dbo.T_Condomini.Nominativo = @ColA) AND (dbo.T_UniCond.Dta_fine = '21001231') AND (dbo.T_Unita.Immobile_id =@immobile_id) and dbo.T_UniCond.StCon_id<>3
FETCH NEXT FROM @MyCursor
INTO @ColA
END

CLOSE @MyCursor
DEALLOCATE @MyCursor

select * from @tbl

/*SET QUOTED_IDENTIFIER OFF*/
GO
,
When i call store procedure with vb6
'Dim rs As Variant
Set cmd = New ADODB.Command
Dim pm As New ADODB.Parameter
' conn.BeginTrans
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
Set pm = cmd.CreateParameter("immobile_id", adVarChar, adParamInput, 6, immobile_id)
cmd.Parameters.Append pm
cmd.CommandText = "sptcondconsiglieri"
Set rs = cmd.Execute
If Not rs.EOF Then
'
Rs is close
I dont undestand why
Tank you

tomy74
Starting Member

32 Posts

Posted - 2006-05-11 : 10:09:39

Hi,

what do you mean exactly by RS is close , can you exaplain more please ?
if you called this procedure from query analyser and pass it a parameter did it work properly ?

Thanks,

Tarek Ghazali
SQL Server MVP

alekos66
Starting Member

2 Posts

Posted - 2006-05-11 : 10:16:17

Hi
From query analyser work properly.
Rs is adodb.recordset
In vb when i call "if rs.eof then" i get error because vb say that it is close

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources