Stored procedure parameter trouble 
Author Message
 Stored procedure parameter trouble

Hello, I'm trying to implement stored procedures - but experience some
difficulties.

My procedure is a simple lookup-type of thing:


AS


GO

********************************************'
I call the procedure from VB6(ADO 2.7)

Public Sub GetBilletInfo(BILLET_SEQ_NO As Integer)

Dim rs As Recordset
Set rs = New ADODB.Recordset
Dim Cmd As New ADODB.Command

Dim params As ADODB.Parameters
Dim param As ADODB.Parameter

With Cmd
    Set .ActiveConnection = cnDatabase
    .CommandText = "MTS_GET_CAST_ID"
    .CommandType = adCmdStoredProc
    Set params = .Parameters
End With

params.Refresh
' Define stored procedure params and append to command.


0)

'The error occurs on the 2. params.appen:
'Error 3708 Parameter object is improperly defined. Inconsisten or incomlete
information was provided.

' Specify input parameter values

Cmd.Execute , , adExecuteNoRecords


Debug.Print CAST_ID

Set Cmd = Nothing

End Sub

********************************************''

The table that the procedure reads looks like this.

CREATE TABLE [BILLET_LOG] (
 [BILLET_SEQ_NO] [int] IDENTITY (1, 1) NOT NULL ,
 [INSERTED_TIME] [datetime] NULL ,
 [DISP_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [CAST_ID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

********************************************

Any suggestions to solve my problem will be greatly appreciated.

Regards,

Bent



Mon, 15 Aug 2005 03:50:26 GMT  
 Stored procedure parameter trouble
Hello,

this i strange; I removed the params.append lines - and the VB code worked!

Should I stick with this method - not declaring the parameters?

Bent



Quote:
> Hello, I'm trying to implement stored procedures - but experience some
> difficulties.

> My procedure is a simple lookup-type of thing:

> CREATE PROCEDURE [MTS_GET_CAST_ID]


> AS


> GO

> ********************************************'
> I call the procedure from VB6(ADO 2.7)

> Public Sub GetBilletInfo(BILLET_SEQ_NO As Integer)

> Dim rs As Recordset
> Set rs = New ADODB.Recordset
> Dim Cmd As New ADODB.Command

> Dim params As ADODB.Parameters
> Dim param As ADODB.Parameter

> With Cmd
>     Set .ActiveConnection = cnDatabase
>     .CommandText = "MTS_GET_CAST_ID"
>     .CommandType = adCmdStoredProc
>     Set params = .Parameters
> End With

> params.Refresh
> ' Define stored procedure params and append to command.


adParamInput,
> 0)

> 'The error occurs on the 2. params.appen:
> 'Error 3708 Parameter object is improperly defined. Inconsisten or
incomlete
> information was provided.

> ' Specify input parameter values

> Cmd.Execute , , adExecuteNoRecords


> Debug.Print CAST_ID

> Set Cmd = Nothing

> End Sub

> ********************************************''

> The table that the procedure reads looks like this.

> CREATE TABLE [BILLET_LOG] (
>  [BILLET_SEQ_NO] [int] IDENTITY (1, 1) NOT NULL ,
>  [INSERTED_TIME] [datetime] NULL ,
>  [DISP_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [CAST_ID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO

> ********************************************

> Any suggestions to solve my problem will be greatly appreciated.

> Regards,

> Bent



Mon, 15 Aug 2005 04:06:40 GMT  
 Stored procedure parameter trouble
Bent,

If you call Refresh method of parameters, then ADO will create collection of
those parameters based on information from SP and provider. That means you
do not need to add them again. Otherwise you will have duplicated parameters
(in your case you will pass four parameters instead of two). If you do not
call Refresh, then you need to declare those parameters as you do right now
after Refresh

--
Val Mazur
Microsoft MVP



Quote:
> Hello, I'm trying to implement stored procedures - but experience some
> difficulties.

> My procedure is a simple lookup-type of thing:

> CREATE PROCEDURE [MTS_GET_CAST_ID]


> AS


> GO

> ********************************************'
> I call the procedure from VB6(ADO 2.7)

> Public Sub GetBilletInfo(BILLET_SEQ_NO As Integer)

> Dim rs As Recordset
> Set rs = New ADODB.Recordset
> Dim Cmd As New ADODB.Command

> Dim params As ADODB.Parameters
> Dim param As ADODB.Parameter

> With Cmd
>     Set .ActiveConnection = cnDatabase
>     .CommandText = "MTS_GET_CAST_ID"
>     .CommandType = adCmdStoredProc
>     Set params = .Parameters
> End With

> params.Refresh
> ' Define stored procedure params and append to command.


adParamInput,
> 0)

> 'The error occurs on the 2. params.appen:
> 'Error 3708 Parameter object is improperly defined. Inconsisten or
incomlete
> information was provided.

> ' Specify input parameter values

> Cmd.Execute , , adExecuteNoRecords


> Debug.Print CAST_ID

> Set Cmd = Nothing

> End Sub

> ********************************************''

> The table that the procedure reads looks like this.

> CREATE TABLE [BILLET_LOG] (
>  [BILLET_SEQ_NO] [int] IDENTITY (1, 1) NOT NULL ,
>  [INSERTED_TIME] [datetime] NULL ,
>  [DISP_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [CAST_ID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO

> ********************************************

> Any suggestions to solve my problem will be greatly appreciated.

> Regards,

> Bent



Mon, 15 Aug 2005 07:01:57 GMT  
 Stored procedure parameter trouble
Hello,

so then I only need to call parameters.refresh, not adding parameters?

regards

Bent

Quote:
> Bent,

> If you call Refresh method of parameters, then ADO will create collection
of
> those parameters based on information from SP and provider. That means you
> do not need to add them again. Otherwise you will have duplicated
parameters
> (in your case you will pass four parameters instead of two). If you do not
> call Refresh, then you need to declare those parameters as you do right
now
> after Refresh

> --
> Val Mazur
> Microsoft MVP



> > Hello, I'm trying to implement stored procedures - but experience some
> > difficulties.

> > My procedure is a simple lookup-type of thing:

> > CREATE PROCEDURE [MTS_GET_CAST_ID]


> > AS


> > GO

> > ********************************************'
> > I call the procedure from VB6(ADO 2.7)

> > Public Sub GetBilletInfo(BILLET_SEQ_NO As Integer)

> > Dim rs As Recordset
> > Set rs = New ADODB.Recordset
> > Dim Cmd As New ADODB.Command

> > Dim params As ADODB.Parameters
> > Dim param As ADODB.Parameter

> > With Cmd
> >     Set .ActiveConnection = cnDatabase
> >     .CommandText = "MTS_GET_CAST_ID"
> >     .CommandType = adCmdStoredProc
> >     Set params = .Parameters
> > End With

> > params.Refresh
> > ' Define stored procedure params and append to command.


> adParamInput,
> > 0)

> > 'The error occurs on the 2. params.appen:
> > 'Error 3708 Parameter object is improperly defined. Inconsisten or
> incomlete
> > information was provided.

> > ' Specify input parameter values

> > Cmd.Execute , , adExecuteNoRecords


> > Debug.Print CAST_ID

> > Set Cmd = Nothing

> > End Sub

> > ********************************************''

> > The table that the procedure reads looks like this.

> > CREATE TABLE [BILLET_LOG] (
> >  [BILLET_SEQ_NO] [int] IDENTITY (1, 1) NOT NULL ,
> >  [INSERTED_TIME] [datetime] NULL ,
> >  [DISP_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >  [CAST_ID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO

> > ********************************************

> > Any suggestions to solve my problem will be greatly appreciated.

> > Regards,

> > Bent



Mon, 15 Aug 2005 17:27:33 GMT  
 Stored procedure parameter trouble
Right,

if you call Refresh, then no definition required. If you do not call
Refresh, then you declare and append all the parameters from the code

--
Val Mazur
Microsoft MVP



Quote:
> Hello,

> so then I only need to call parameters.refresh, not adding parameters?

> regards

> Bent


> > Bent,

> > If you call Refresh method of parameters, then ADO will create
collection
> of
> > those parameters based on information from SP and provider. That means
you
> > do not need to add them again. Otherwise you will have duplicated
> parameters
> > (in your case you will pass four parameters instead of two). If you do
not
> > call Refresh, then you need to declare those parameters as you do right
> now
> > after Refresh

> > --
> > Val Mazur
> > Microsoft MVP



> > > Hello, I'm trying to implement stored procedures - but experience some
> > > difficulties.

> > > My procedure is a simple lookup-type of thing:

> > > CREATE PROCEDURE [MTS_GET_CAST_ID]


> > > AS


> > > GO

> > > ********************************************'
> > > I call the procedure from VB6(ADO 2.7)

> > > Public Sub GetBilletInfo(BILLET_SEQ_NO As Integer)

> > > Dim rs As Recordset
> > > Set rs = New ADODB.Recordset
> > > Dim Cmd As New ADODB.Command

> > > Dim params As ADODB.Parameters
> > > Dim param As ADODB.Parameter

> > > With Cmd
> > >     Set .ActiveConnection = cnDatabase
> > >     .CommandText = "MTS_GET_CAST_ID"
> > >     .CommandType = adCmdStoredProc
> > >     Set params = .Parameters
> > > End With

> > > params.Refresh
> > > ' Define stored procedure params and append to command.


> > adParamInput,
> > > 0)

adParamOutput)

> > > 'The error occurs on the 2. params.appen:
> > > 'Error 3708 Parameter object is improperly defined. Inconsisten or
> > incomlete
> > > information was provided.

> > > ' Specify input parameter values

> > > Cmd.Execute , , adExecuteNoRecords


> > > Debug.Print CAST_ID

> > > Set Cmd = Nothing

> > > End Sub

> > > ********************************************''

> > > The table that the procedure reads looks like this.

> > > CREATE TABLE [BILLET_LOG] (
> > >  [BILLET_SEQ_NO] [int] IDENTITY (1, 1) NOT NULL ,
> > >  [INSERTED_TIME] [datetime] NULL ,
> > >  [DISP_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> > >  [CAST_ID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > > ) ON [PRIMARY]
> > > GO

> > > ********************************************

> > > Any suggestions to solve my problem will be greatly appreciated.

> > > Regards,

> > > Bent



Mon, 15 Aug 2005 21:16:07 GMT  
 Stored procedure parameter trouble
Thanks -

then I got the grip of it!

Regards,
Bent


Quote:
> Right,

> if you call Refresh, then no definition required. If you do not call
> Refresh, then you declare and append all the parameters from the code

> --
> Val Mazur
> Microsoft MVP



> > Hello,

> > so then I only need to call parameters.refresh, not adding parameters?

> > regards

> > Bent


> > > Bent,

> > > If you call Refresh method of parameters, then ADO will create
> collection
> > of
> > > those parameters based on information from SP and provider. That means
> you
> > > do not need to add them again. Otherwise you will have duplicated
> > parameters
> > > (in your case you will pass four parameters instead of two). If you do
> not
> > > call Refresh, then you need to declare those parameters as you do
right
> > now
> > > after Refresh

> > > --
> > > Val Mazur
> > > Microsoft MVP



> > > > Hello, I'm trying to implement stored procedures - but experience
some
> > > > difficulties.

> > > > My procedure is a simple lookup-type of thing:

> > > > CREATE PROCEDURE [MTS_GET_CAST_ID]


> > > > AS


> > > > GO

> > > > ********************************************'
> > > > I call the procedure from VB6(ADO 2.7)

> > > > Public Sub GetBilletInfo(BILLET_SEQ_NO As Integer)

> > > > Dim rs As Recordset
> > > > Set rs = New ADODB.Recordset
> > > > Dim Cmd As New ADODB.Command

> > > > Dim params As ADODB.Parameters
> > > > Dim param As ADODB.Parameter

> > > > With Cmd
> > > >     Set .ActiveConnection = cnDatabase
> > > >     .CommandText = "MTS_GET_CAST_ID"
> > > >     .CommandType = adCmdStoredProc
> > > >     Set params = .Parameters
> > > > End With

> > > > params.Refresh
> > > > ' Define stored procedure params and append to command.


> > > adParamInput,
> > > > 0)

> adParamOutput)

> > > > 'The error occurs on the 2. params.appen:
> > > > 'Error 3708 Parameter object is improperly defined. Inconsisten or
> > > incomlete
> > > > information was provided.

> > > > ' Specify input parameter values

> > > > Cmd.Execute , , adExecuteNoRecords


> > > > Debug.Print CAST_ID

> > > > Set Cmd = Nothing

> > > > End Sub

> > > > ********************************************''

> > > > The table that the procedure reads looks like this.

> > > > CREATE TABLE [BILLET_LOG] (
> > > >  [BILLET_SEQ_NO] [int] IDENTITY (1, 1) NOT NULL ,
> > > >  [INSERTED_TIME] [datetime] NULL ,
> > > >  [DISP_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ,
> > > >  [CAST_ID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > > > ) ON [PRIMARY]
> > > > GO

> > > > ********************************************

> > > > Any suggestions to solve my problem will be greatly appreciated.

> > > > Regards,

> > > > Bent



Tue, 16 Aug 2005 02:55:49 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Crystal 8.5 - troubles with passing strings as stored procedure parameters

2. Trouble passing parameters to a stored procedure in ADO

3. ado recordsets from stored procedures, stored procedures have input parameters

4. Having trouble learning how to call stored procedures.

5. Trouble with stored procedures in data environment

6. Trouble populating recordset from stored procedure in VB

7. To Stored Procedure or not to stored procedure

8. To stored procedure or not to stored procedure

9. sub report changes its source stored procedure to main report's stored procedure

10. To stored procedure or not to stored procedure

11. Passing Parameters to Store Procedures

12. Passing a parameter to a stored procedure from VBA

 

 
Powered by phpBB® Forum Software