Visual Basic - error sp

Life is soft - evento anual de software empresarial
   
Vista:

error sp

Publicado por orlando (3 intervenciones) el 26/10/2009 16:34:40
Gracias por la ayuda nos vemos despues saludos
aunque mi duda es por que con otro store si funcionaba esta conexion y con este aparece este error?
que es lo que pued provocar el error?
operations is not allowed because the object is closed saludos
te dejo toda la conexion 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,getdate())),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,27)
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,getdate())),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,27)
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,getdate())),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,27)
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,getdate())),2)as fecha
from srvsalt13.dbsolicitudpruebassec.dbo.entradalaboratorio 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
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder