objCmd.CommandType = adCmdText work fine but objCmd.CommandType = adCmdStoredProc no

[espartaco]

Me again

I continue trying to fix the problem, and for adCmdText execute open the command, but for adCmdStoredProc not.
Have someone an idea what i am doing wrong?

Some other property i need to configure to call a stored procedure?

Blessings

Leonardo






'BeginNewConnection
Private Function GetNewConnection() As ADODB.Connection
Dim oCn As New ADODB.Connection
Dim sCnStr As String

sCnStr = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Merchant;Integrated Security=SSPI;Trusted_Connection=Yes;"
oCn.Open sCnStr

If oCn.State = adStateOpen Then
Set GetNewConnection = oCn
End If

End Function


Sub Test()

On Error GoTo ErrHandler:

Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
'Dim objParm1 As New ADODB.Parameter
Dim objRs As New ADODB.Recordset

' Set CommandText equal to the stored procedure name.

'Is working
objCmd.CommandText = "Select * from Forma"
objCmd.CommandType = adCmdText

' It is not working
' objCmd.CommandText = "RepTotalesRemVenMon"
' objCmd.CommandType = adCmdStoredProc

' Connect to the data source.
Set objConn = GetNewConnection
objCmd.ActiveConnection = objConn

' Automatically fill in parameter info from stored procedure.
'objCmd.Parameters.Refresh

' Set the param value.
'objCmd(1) = "ALFKI"

' Execute once and display...
Set objRs = objCmd.Execute
' objRs.Open objCmd.Execute



'Debug.Print objParm1.Value
'Do While Not objRs.EOF
' Debug.Print vbTab & objRs(0) & vbTab & objRs(1) & vbTab & _
' objRs(2) & vbTab & objRs(3)
' objRs.MoveNext
'Loop

' ...then set new param value, re-execute command, and display.
'objCmd(1) = "CACTU"
'Set objRs = objCmd.Execute

'Debug.Print objParm1.Value
'Do While Not objRs.EOF
' Debug.Print vbTab & objRs(0) & vbTab & objRs(1) & vbTab & _
' objRs(2) & vbTab & objRs(3)
' objRs.MoveNext
'Loop

'clean up
objRs.Close
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
'Set objParm1 = Nothing
Exit Sub

ErrHandler:
'clean up
If objRs.State = adStateOpen Then
objRs.Close
End If

If objConn.State = adStateOpen Then
objConn.Close
End If

Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
'Set objParm1 = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
'EndAutoParamCmd



End Sub


USE [Merchant]
GO
/****** Object: StoredProcedure [dbo].[REPORTE TOTALES DE REMISIONES POR VENDEDOR Y MONEDA] Script Date: 05/23/15 11:14:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[RepTotalesRemVenMon]
AS
BEGIN
DECLARE @Remision real,@Cliente int,@ClieNomComercial varchar(90),@Clienomfiscal varchar(90),@VenNombre varchar(40),@Remtotal decimal(18,2),@Moneda char(1),@RemFecha datetime
DECLARE @Dolartotal decimal(18,2),@Pesostotal decimal(18,2),@Buffervendedor varchar(40)

CREATE TABLE #RepTotalesRemVenMon (
Remision real,
Cliente int,
ClieNomComercial varchar(90),
Clienomfiscal varchar(90),
VenNombre varchar(40),
Remtotal decimal(18,2),
Moneda char(1),
RemFecha datetime,
Vendedor varchar(40),
Dolar char(1),
TotalDolar decimal(18,2),
Pesos char(1),
TotalPesos decimal(18,2)
);

INSERT INTO #RepTotalesRemVenMon(Remision,Cliente,ClieNomComercial,Clienomfiscal,VenNombre,Remtotal,Moneda,RemFecha)
select Remision,fr.cliente,gc.ClieNomComercial, gc.clienomfiscal, fv.VenNombre,remtotal,fr.moneda,RemFecha
from FurRemision fr
join GenCliente gc on Fr.Cliente = gc.Cliente
join FurVendedor fv on fr.Vendedor = fv.Vendedor
where RemFecCancela is not null
order by fv.VenNombre, RemFecha

DECLARE CALCULO_TOT_VEND_MON CURSOR FOR select distinct VenNombre from #RepTotalesRemVenMon
OPEN CALCULO_TOT_VEND_MON
FETCH NEXT FROM CALCULO_TOT_VEND_MON INTO @VenNombre
WHILE @@FETCH_STATUS = 0
BEGIN
select @Remision = max(Remision) from #RepTotalesRemVenMon where VenNombre = @VenNombre
select @Dolartotal = sum(Remtotal) from #RepTotalesRemVenMon where VenNombre = @VenNombre and Moneda = 'D'
select @Pesostotal = sum(Remtotal) from #RepTotalesRemVenMon where VenNombre = @VenNombre and Moneda = 'P'

UPDATE #RepTotalesRemVenMon
SET Vendedor = @VenNombre, Dolar = 'D', TotalDolar = @Dolartotal, Pesos = 'P', TotalPesos = @Pesostotal
WHERE Remision = @Remision

FETCH NEXT FROM CALCULO_TOT_VEND_MON INTO @VenNombre
END
CLOSE CALCULO_TOT_VEND_MON
DEALLOCATE CALCULO_TOT_VEND_MON

select * from #RepTotalesRemVenMon

END