error al llamar un sp

darkgeo orlando
26 de Octubre del 2009
ola ktal espero su ayuda
lo k pasa es k al acer llamado a la execucion de un store procedure me marca error
operations is not allowed because the object is closed
pero lo raro es k cheko el sp con el query analyzer y me funciona bn
el programa lo tenia funcionando con otro sp y tambn funcionaba
entonces mi pregunta es que es lo que puede estar fallando?
aki dejo el kodigo y los sp

Dim n As Integer
Dim serie(1000) As String
Dim fecha(1000) As String
Dim pnc(1000) As String
Dim serie1(1000) As String
Dim fecha1(1000) As String
Dim pnc1(1000) As String
Dim formato(1000) As String
Dim formato1(1000) As String

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strquery
Dim Linea1 As String
Dim Arch1 As String
Dim I1 As Integer

Dim linea As String
Dim Arch As String
Dim I As Integer
Public Sub Exp1()

//creacion de archivos
Arch = "Sal_prod.txt"
Open Arch For Output Access Write As #1
For I = 0 To List1.ListCount
linea = ""
linea = linea & List1.List(I)
Print #1, linea
Next I
Close #1
End Sub
Public Sub Exp2()
Arch = "Prod_sal.dat"
Open Arch For Output Access Write As #1
For I = 0 To List1.ListCount
linea = ""
linea = linea & List2.List(I)
Print #1, linea
Next I
Close #1
End Sub
Private Sub Form_Load()
ConectarSQLServer
List1.Clear
List1.AddItem formato(0)
For I = 1 To n
List1.AddItem formato(I)
Next
List2.Clear
For j = 0 To n
List2.AddItem formato1(j)
Next
Exp1
Exp2
End Sub

//conecta a sp
Public Sub ConectarSQLServer()
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim fechaa As Date
Dim cf As String
Dim a, b, c, m, j As Integer
Dim año, mes, dia, minuto, segundo, hora As String
Dim a1, m1, d1, min, seg, hor As String

año = Year(Now)
mes = Month(Now)
dia = Day(Now)
hora = Hour(Now)
minuto = Minute(Now)
segundo = Second(Now)
a1 = CStr(año)
m1 = CStr(mes)
d1 = CStr(dia)
minuto = CStr(min)
hora = CStr(hor)
segundo = CStr(seg)

n = 1
m = 0
formato(0) = "310DATE " + a1 + m1 + d1 + hora + min + seg
conn.Open "Provider=sqloledb;" & _
"Data Source=SRVSALT1;" & _
"Initial Catalog=cptdryers;" & _
"User Id=sa;Password=avsal"

strQuery1 = "cptSecProdEmbarGeo"
Set rs1 = conn.Execute(strQuery1) //ejecuta el sp

While Not rs1.EOF And n < 100 /// marca error operations is not allowed because the object is closed


pnc(n) = rs1.Fields("prod")
serie(n) = rs1.Fields("serie")
fecha(n) = rs1.Fields("fecha")
pnc1(n) = rs1.Fields("prod")
serie1(n) = rs1.Fields("serie")
fecha1(n) = rs1.Fields("fecha")

a = Len(pnc(n))
b = Len(pnc(n))

//formato para llenar los combobox
For j = a To 15
pnc(n) = pnc(n) + " "
Next

c = Len(serie(n))

For j = c To 3
serie1(n) = "0" + serie1(n)

Next

For j = c To 3
serie(n) = "0" + serie(n)

Next

formato(n) = "310" + pnc(n) + "W729" + serie(n) + "W729" + fecha(n) + "1" + fecha(n) + "SAL+0000"

For j = b To 11
pnc1(n) = pnc1(n) + " "
Next
formato1(m) = pnc1(n) + "W729 " + serie1(n) + a1 + fecha1(n) + ""


m = m + 1
n = n + 1
rs1.MoveNext

Wend



End Sub



sp
CREATE PROCEDURE spCptPorduccion01Secadoras
AS

select pro.Prod , count(ser.serie) as serie, right(\'00\'+convert(varchar(2),datepart(mm,getdate( ))),2)+right(\'00\'+convert(varchar(2),datepart(dd,g etdate())),2)as fecha
from cptserie ser, cptproducto pro
where ser.productoid in (1,2,3,4,5,6,14,15,16,17,18,22,64,28,53,63,56,55,2 7)
and ser.productoid = pro.productoid
and ser.timestamp >= getdate()-1
group by pro.ProductoNombreCorto

RETURN
GO


y este es el sp que no me corre


CREATE procedure cptSecProdEmbarGeo as

CREATE TABLE #TEMP
(
Prod varchar(20),
serie integer,
Fecha varchar(20)

)

create table #temp1
(
pr1 varchar(20),
cant1 integer,
fecha1 varchar(20)
)

create table #temp2
(
pr2 varchar(20),
cant2 integer,
fecha2 varchar(20)
)


create table #temp3
(
pr3 varchar(20),
cant3 integer,
fecha3 varchar(20)
)

insert into #temp1(pr1,cant1,fecha1)
select pro.ProductoNombreCorto , count(ser.serie) as serie, right(\'00\'+convert(varchar(2),datepart(mm,getdate( ))),2)+right(\'00\'+convert(varchar(2),datepart(dd,g etdate())),2)as fecha
from cptserie ser,
cptproducto pro
where ser.productoid in (1,2,3,4,5,6,14,15,16,17,18,22,64,28,53,63,56,55,2 7)
and ser.productoid = pro.productoid
and ser.timestamp >= getdate()-1
group by pro.ProductoNombreCorto

insert into #temp2(pr2,cant2,fecha2)
select prod.ProductoNombrecorto , (count(ser.serie)*-1) as serie, right(\'00\'+convert(varchar(2),datepart(mm,getdate( ))),2)+right(\'00\'+convert(varchar(2),datepart(dd,g etdate())),2)as fecha
from cptRequisicionClienteSerieItem crcsi
inner join cptserie ser on crcsi.serieid=ser.serieid
inner join cptProducto prod on ser.productoid=prod.productoid
where crcsi.timestamp>=getdate()-1
and ser.productoid in (1,2,3,4,5,6,14,15,16,17,18,22,64,28,53,63,56,55,2 7)
group by ProductoNombreCorto


insert into #temp3(pr3,cant3,fecha3)
select ent.modelo,(count(ent.serie)*-1) as cantidad, right(\'00\'+convert(varchar(2),datepart(mm,getdate( ))),2)+right(\'00\'+convert(varchar(2),datepart(dd,g etdate())),2)as fecha
from srvsalt13.dbsolicitudpruebassec.dbo.entradalaborat orio ent
where fecha<=getdate()-1
group by ent.modelo

insert into #temp (Prod,serie,fecha)
select t1.pr1,sum(t1.cant1+t2.cant2+t3.cant3),t1.fecha1
FROM #temp1 t1,#temp2 t2,#temp3 t3
where t1.pr1=t2.pr2 and t3.pr3=t1.pr1
group by t1.pr1,t1.fecha1

select prod,serie,fecha
from #temp

RETURN
GO

pero en sql si me manda un resultado y poes no entiendo porque


bueno espero su respuesta gracias