call Stored Procedure from VB

[mutley]

Hi


I tried call 2 Storeds Procedures from VB wich front-end


The first Stored Procedure give an error message


RUN-TIME ERROR '-2147217900'

PROCEDURE MEUTESTE HAS NO PARAMETERS AND ARGUMENTS WERE SUPPLIED

-------------------------
CREATE PROCEDURE meuteste AS
select ag02_hr_ini,ag02_hr_fim
from ag02

------------------------ x---------------------------

The other Procedure give error :

ADO CAN NOT FOUND COLLECTION OBJECT CORRESPONDING NAME OR REFERENCE ORDINAL


CREATE PROCEDURE sp2_filtramain (@Pdata datetime ,
@Pcd_proc varchar(08))
AS

declare @whr_ini datetime /* Horário Inicial - Cursor */
declare @vhr_ini datetime /* Cópia do horário Inicial */
declare @whr_fim datetime /* Horário Final - Cursor */
declare @vhr_fim datetime /* Cópia Do horário Final */
declare @hora_agenda datetime /* Hora Agendada */
declare @wdata_ini datetime
declare @wdata_fim datetime
declare @wlocal int /* Código do Local - Cursor */
declare @vlocal int /* Cópia do código do local */
declare @wnom_loc varchar(30) /* Nome do Local - Cursor */
declare @vnom_loc varchar(30) /* Cópia do nome do local */
declare @Vstat integer /* Status de retorno */
declare @Whr_dur varchar(6) /* duração */
declare @Vhr_dur integer /* Duração do Exame */
declare @Vcd_rgr integer /* regra */
declare @Vintervalo datetime /* Guarda o Intervalo de cada período */
declare @Vhora integer /*Guarda Hora e Minuto */
declare @Vminuto integer
declare @wccu varchar(4)
declare @Vachou integer
declare @Vret integer
declare @Vdia varchar(02)
declare @Vmes varchar(02)
declare @Vrows integer
declare @Vchar varchar(30)
declare @lflag integer
declare cr_Agenda insensitive cursor /* CURSOR */

FOR
select distinct convert(datetime, convert(varchar, ag02_hr_ini, 14)),
convert(datetime, convert(varchar, ag02_hr_fim, 14)),
ag01_cd_loc,
ag01_nm_loc,
ag01_cd_ccu
from ag02
join ag07
on ag02_cd_rgr = ag07_cd_rgr
join ag18
on ag07_cd_rgr = ag18_cd_rgr and
ag07_cd_ccu = ag18_cd_ccu and
ag07_cd_loc = ag18_cd_loc
join ag01
on ag07_cd_ccu = ag01_cd_ccu and
ag07_cd_loc = ag01_cd_loc
where (ag07_cd_proc = @Pcd_proc) and (ag02_dt_ini_vig <= getdate())
and (ag02_dt_fim_vig>=getdate() or ag02_dt_fim_vig is null)
and (ag02_dia_sem = 2)
order by 3,1

create table #tmp_se_ag08_02 (
hr_ini datetime not null,
hr_fim datetime not null,
sala varchar(30)
)

select @Whr_dur = ag06_hr_dur
from ag06
where ag06_cd_proc = @Pcd_proc

select @Vhora = convert(integer,substring(@Whr_dur,1,2))
select @Vminuto = convert(integer,substring(@Whr_dur,4,2))
select @Vhr_dur = @Vhora*60 + @Vminuto


/* if (@@ROWCOUNT = 0)
begin
select @Vstat = 6
goto ERR_AGENDA
end */
select @Vachou = 0
while @Vachou = 0
begin
--------------------------------------------
-- Verifica se eh um feriado
-------------------------------------------
select @Vchar = convert(varchar, @Pdata)
select @Vdia = convert(varchar, datepart(day, @Pdata))
select @Vmes = convert(varchar, datepart(month, @Pdata))
exec @Vret = sp2_se_ag16_02 @Vdia, @Vmes, NULL, NULL
if (@Vret = 0)
begin
select @Vrows = 0
select @Pdata = dateadd(day, 1, @Pdata)
continue
end
open cr_Agenda

fetch next from cr_Agenda into @Whr_ini, @Whr_fim,@wlocal,@wnom_loc,@Wccu




select @lflag = 0
while (@@FETCH_STATUS = 0)
begin
select @wdata_ini = convert(varchar, @Pdata, 111) + ' ' + convert(varchar, @Whr_ini, 14)
select @wdata_fim = convert(varchar, @Pdata, 111) + ' ' + convert(varchar, @Whr_fim, 14)
select @Vrows = count(ag03_cd_loc) from ag03 where ag03_cd_ccu = @wccu
and ag03_cd_loc = @Wlocal and ((@Wdata_ini between ag03_dth_ini and ag03_dth_fim) or
@Wdata_fim between ag03_dth_ini and ag03_dth_fim)
if @vrows > 0
begin
fetch next from cr_Agenda into @Whr_ini, @Whr_fim,@wlocal,@wnom_loc,@wccu
continue
end
if @lflag<>0
begin
while @vlocal = @wlocal and (@@FETCH_STATUS = 0)
begin
if abs(datediff(n,@vhr_fim,@Whr_ini))<=1
select @Vhr_fim = @Whr_fim
else
begin
select @Vhr_fim = dateadd(n,1,@Vhr_fim)
select @Vintervalo = dateadd(n,@Vhr_dur,@vhr_ini)
while (@Vintervalo <= @Vhr_fim)
begin
select @Vachou =1
insert into #tmp_se_ag08_02 values (@Vhr_ini,@Vintervalo,@vnom_loc)
select @Vhr_ini = @Vintervalo
select @Vintervalo = dateadd(n,@vhr_dur,@Vintervalo)
end -- while intervalo
select @vhr_ini = @whr_ini
select @vhr_fim = @whr_fim
end -- else datediff
fetch next from cr_Agenda into @Whr_ini, @Whr_fim,@wlocal,@wnom_loc,@wccu
end -- while local
select @Vhr_fim = dateadd(n,1,@Vhr_fim)
select @Vintervalo = dateadd(n,@Vhr_dur,@vhr_ini)
while (@Vintervalo <= @Vhr_fim)
begin
select @Vachou = 1
insert into #tmp_se_ag08_02 values (@Vhr_ini,@Vintervalo,@vnom_loc)
select @Vhr_ini = @Vintervalo
select @Vintervalo = dateadd(n,@vhr_dur,@Vintervalo)
end -- while intervalo 2
select @Vhr_ini = @Whr_ini
select @Vhr_fim = @Whr_ini
select @Vnom_loc = @Wnom_loc
select @Vlocal = @Wlocal
end
else
begin
select @Vhr_ini = @Whr_ini
select @Vhr_fim = @Whr_fim
select @Vnom_loc = @Wnom_loc
select @Vlocal = @Wlocal
select @lflag = 1
fetch next from cr_Agenda into @Whr_ini, @Whr_fim,@wlocal,@wnom_loc,@wccu
end
end
close cr_Agenda
deallocate cr_Agenda
select @Pdata = dateadd(day, 1, @Pdata)
end
select substring(convert(varchar,hr_ini,14),1,5) as hora_ini,
substring(convert(varchar, hr_fim,14 ),1,5) as hora_fim, sala from #tmp_se_ag08_02

drop table #tmp_se_ag08_02
---------------------------------------X---------------------------
The 2 Stored Procedures run normal from Query Analyzer

I call Stored Procedures from VB as:

newdata = Format(cdata, "yyyy-mm-dd")
cproc = "ABDS"

sql = "sp2_filtramain('" & newdata & "', '" & cproc & "')"
' or sql = "meuteste()"
With ocmd
' .ActiveConnection = G_Database.ConnectionString
.ActiveConnection = SQLSERVER.ConnectionString

.CommandText = sql
.CommandType = adCmdStoredProc
.Prepared = True
End With
'Executa a procedure
Set ors = New ADODB.Recordset
Set ors = ocmd.Execute


Thank You In Advance

[Mongo]

I play in a different RDBMS, so the params ordinals may be off by one, each, i.e., this is a quick SWAG, but it may help. Try replacing this in your VB code:

.
.
.
With ocmd 
  .ActiveConnection = SQLSERVER.ConnectionString 
  '  sp1
  'cmd.CommandText = "meuteste"
  'cmd.Name = "meuteste" 

  '   sp2 with params
  .CommandText = "sp2_filtramain"
  .CommandType = adCmdStoredProc
  .Parameters.Refresh
  .Parameters(0).Value = Format(cdata, "yyyy-mm-dd")
  .Parameters(1).Value = "ABDS"
End With
.
.
.

[mutley]

Hi



I tried , but do not work

[Mongo]

With the same run-time errors?

[Marcelo Velasquez]

Hi

The same erros, Do I believe that it is because of the Cursor, or doesn't It have anything there to be?


thank you in advance