By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,734 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

stored proc error - ADO and DB2

P: n/a
Greetings!

I am VERY new to DB2 but not Orable, Sybase and SQL Server.

I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I
try to execute
Nov 12 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
"ColdCanuck" <co**@canuck.ca> wrote in message
news:gY6mc.6987$F04.5734@clgrps13...
Greetings!

I am VERY new to DB2 but not Orable, Sybase and SQL Server.

I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I
try to execute


Sorry, I accidently sent this before finishing...

Please excuse the cross-post but I am unsure which group my problem pertains
to.

As I was saying, when I try to execute my stored proc, I get the following
error:
"Parameter type cannot be determined for at least one variant
parameter."

My proc is quite simple:

CREATE PROCEDURE NORM.npn_test2
( IN pContactId character(3),
OUT oName character(20))
LANGUAGE SQL
BEGIN ATOMIC

DECLARE sName CHAR(20);

SET sName = (SELECT ADADTX || ', ' || ADAETX FROM MAILIB.UUADCPP WHERE
ADAANB = pContactId);
SET oName = sName;

END
My VB code looks like:

Public Function Call_sp_test(con As ADODB.Connection, ByVal sContactId As
String) As String
On Error GoTo ErrHandler
Dim sName As String

Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Dim P1 As New ADODB.Parameter
Dim P2 As New ADODB.Parameter

Set cmd.ActiveConnection = con

cmd.CommandText = "{CALL NORM.npn_test2(?,?)}"

cmd.CommandType = adCmdText
'cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh
'cmd.Parameters("pContactId").Value = sContactId
cmd.Prepared = True

sName = " " '40 spaces
P1.Direction = adParamInput
P1.Type = adChar
P1.Size = 6
P1.Name = "ContactId"
P1.Value = "000006"

P1.Direction = adParamReturnValue
P2.Type = adChar
P2.Name = "Name"
P2.Size = 40
P2.Value = sName

cmd.Parameters.Append P1
cmd.Parameters.Append P2

'cmd.Execute
rst.Open cmd <- error occurs here

sName = cmd.Parameters("oName").Value

Call_sp_test = cmd.Parameters("oName").Value

rst.Close
Set rst = Nothing
Set cmd = Nothing

ErrHandler:
MsgBox "Error Code: " & Err.Number & vbNewLine & _
"Description: " & Err.Description & vbNewLine & _
"Source: " & Err.Source, vbOKOnly + vbCritical
Err.Clear
'Set Call_sp_test = Nothing

End Function
Has anyone run into this problem before? What am I missing here?

Thanks for all replies!

cheers,
CC


Nov 12 '05 #2

P: n/a
"ColdCanuck" <co**@canuck.ca> wrote in message
news:gY6mc.6987$F04.5734@clgrps13...
Greetings!

I am VERY new to DB2 but not Orable, Sybase and SQL Server.

I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I
try to execute


Sorry, I accidently sent this before finishing...

Please excuse the cross-post but I am unsure which group my problem pertains
to.

As I was saying, when I try to execute my stored proc, I get the following
error:
"Parameter type cannot be determined for at least one variant
parameter."

My proc is quite simple:

CREATE PROCEDURE NORM.npn_test2
( IN pContactId character(3),
OUT oName character(20))
LANGUAGE SQL
BEGIN ATOMIC

DECLARE sName CHAR(20);

SET sName = (SELECT ADADTX || ', ' || ADAETX FROM MAILIB.UUADCPP WHERE
ADAANB = pContactId);
SET oName = sName;

END
My VB code looks like:

Public Function Call_sp_test(con As ADODB.Connection, ByVal sContactId As
String) As String
On Error GoTo ErrHandler
Dim sName As String

Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Dim P1 As New ADODB.Parameter
Dim P2 As New ADODB.Parameter

Set cmd.ActiveConnection = con

cmd.CommandText = "{CALL NORM.npn_test2(?,?)}"

cmd.CommandType = adCmdText
'cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh
'cmd.Parameters("pContactId").Value = sContactId
cmd.Prepared = True

sName = " " '40 spaces
P1.Direction = adParamInput
P1.Type = adChar
P1.Size = 6
P1.Name = "ContactId"
P1.Value = "000006"

P1.Direction = adParamReturnValue
P2.Type = adChar
P2.Name = "Name"
P2.Size = 40
P2.Value = sName

cmd.Parameters.Append P1
cmd.Parameters.Append P2

'cmd.Execute
rst.Open cmd <- error occurs here

sName = cmd.Parameters("oName").Value

Call_sp_test = cmd.Parameters("oName").Value

rst.Close
Set rst = Nothing
Set cmd = Nothing

ErrHandler:
MsgBox "Error Code: " & Err.Number & vbNewLine & _
"Description: " & Err.Description & vbNewLine & _
"Source: " & Err.Source, vbOKOnly + vbCritical
Err.Clear
'Set Call_sp_test = Nothing

End Function
Has anyone run into this problem before? What am I missing here?

Thanks for all replies!

cheers,
CC


Nov 12 '05 #3

P: n/a
ColdCanuck wrote:
"ColdCanuck" <co**@canuck.ca> wrote in message
news:gY6mc.6987$F04.5734@clgrps13...
Greetings!

I am VERY new to DB2 but not Orable, Sybase and SQL Server.

I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I
try to execute


Sorry, I accidently sent this before finishing...

Please excuse the cross-post but I am unsure which group my problem
pertains to.

As I was saying, when I try to execute my stored proc, I get the following
error:
"Parameter type cannot be determined for at least one variant
parameter."

My proc is quite simple:

CREATE PROCEDURE NORM.npn_test2
( IN pContactId character(3),
OUT oName character(20))
LANGUAGE SQL
BEGIN ATOMIC

DECLARE sName CHAR(20);

SET sName = (SELECT ADADTX || ', ' || ADAETX FROM MAILIB.UUADCPP WHERE
ADAANB = pContactId);
SET oName = sName;

END
My VB code looks like:

Public Function Call_sp_test(con As ADODB.Connection, ByVal sContactId As
String) As String
On Error GoTo ErrHandler
Dim sName As String

Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Dim P1 As New ADODB.Parameter
Dim P2 As New ADODB.Parameter

Set cmd.ActiveConnection = con

cmd.CommandText = "{CALL NORM.npn_test2(?,?)}"

cmd.CommandType = adCmdText
'cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh
'cmd.Parameters("pContactId").Value = sContactId
cmd.Prepared = True

sName = " " '40 spaces
P1.Direction = adParamInput
P1.Type = adChar
P1.Size = 6
P1.Name = "ContactId"
P1.Value = "000006"

P1.Direction = adParamReturnValue
P2.Type = adChar
P2.Name = "Name"
P2.Size = 40
P2.Value = sName

cmd.Parameters.Append P1
cmd.Parameters.Append P2

'cmd.Execute
rst.Open cmd <- error occurs here

sName = cmd.Parameters("oName").Value

Call_sp_test = cmd.Parameters("oName").Value

rst.Close
Set rst = Nothing
Set cmd = Nothing

ErrHandler:
MsgBox "Error Code: " & Err.Number & vbNewLine & _
"Description: " & Err.Description & vbNewLine & _
"Source: " & Err.Source, vbOKOnly + vbCritical
Err.Clear
'Set Call_sp_test = Nothing

End Function
Has anyone run into this problem before? What am I missing here?

Thanks for all replies!

cheers,
CC


CC,

In the stored procedure definition you want to specify a SPECIFIC NAME as
well as the procedure name.

You really do want to be using the enumeration adCmdStoredProc, which you
have commented out in favour of adCmdText.

You'll find a couple of good samples in -

C:\PROGRAM FILES\SQLLIB\SAMPLES\ADO\VB

From memory they are something like spcall.* (simple call) and rssp.*
(returning a result set).

I was just working on an example piece of code for one of our developers
today and the sample code works !!!

HTH

Phil
Nov 12 '05 #4

P: n/a
ColdCanuck wrote:
"ColdCanuck" <co**@canuck.ca> wrote in message
news:gY6mc.6987$F04.5734@clgrps13...
Greetings!

I am VERY new to DB2 but not Orable, Sybase and SQL Server.

I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I
try to execute


Sorry, I accidently sent this before finishing...

Please excuse the cross-post but I am unsure which group my problem
pertains to.

As I was saying, when I try to execute my stored proc, I get the following
error:
"Parameter type cannot be determined for at least one variant
parameter."

My proc is quite simple:

CREATE PROCEDURE NORM.npn_test2
( IN pContactId character(3),
OUT oName character(20))
LANGUAGE SQL
BEGIN ATOMIC

DECLARE sName CHAR(20);

SET sName = (SELECT ADADTX || ', ' || ADAETX FROM MAILIB.UUADCPP WHERE
ADAANB = pContactId);
SET oName = sName;

END
My VB code looks like:

Public Function Call_sp_test(con As ADODB.Connection, ByVal sContactId As
String) As String
On Error GoTo ErrHandler
Dim sName As String

Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Dim P1 As New ADODB.Parameter
Dim P2 As New ADODB.Parameter

Set cmd.ActiveConnection = con

cmd.CommandText = "{CALL NORM.npn_test2(?,?)}"

cmd.CommandType = adCmdText
'cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh
'cmd.Parameters("pContactId").Value = sContactId
cmd.Prepared = True

sName = " " '40 spaces
P1.Direction = adParamInput
P1.Type = adChar
P1.Size = 6
P1.Name = "ContactId"
P1.Value = "000006"

P1.Direction = adParamReturnValue
P2.Type = adChar
P2.Name = "Name"
P2.Size = 40
P2.Value = sName

cmd.Parameters.Append P1
cmd.Parameters.Append P2

'cmd.Execute
rst.Open cmd <- error occurs here

sName = cmd.Parameters("oName").Value

Call_sp_test = cmd.Parameters("oName").Value

rst.Close
Set rst = Nothing
Set cmd = Nothing

ErrHandler:
MsgBox "Error Code: " & Err.Number & vbNewLine & _
"Description: " & Err.Description & vbNewLine & _
"Source: " & Err.Source, vbOKOnly + vbCritical
Err.Clear
'Set Call_sp_test = Nothing

End Function
Has anyone run into this problem before? What am I missing here?

Thanks for all replies!

cheers,
CC


CC,

In the stored procedure definition you want to specify a SPECIFIC NAME as
well as the procedure name.

You really do want to be using the enumeration adCmdStoredProc, which you
have commented out in favour of adCmdText.

You'll find a couple of good samples in -

C:\PROGRAM FILES\SQLLIB\SAMPLES\ADO\VB

From memory they are something like spcall.* (simple call) and rssp.*
(returning a result set).

I was just working on an example piece of code for one of our developers
today and the sample code works !!!

HTH

Phil
Nov 12 '05 #5

P: n/a

"Philip Nelson" <te*****@scotdb.com> wrote in message
news:Mq***********************@news.easynews.com.. .
In the stored procedure definition you want to specify a SPECIFIC NAME as
well as the procedure name.

You really do want to be using the enumeration adCmdStoredProc, which you
have commented out in favour of adCmdText.

You'll find a couple of good samples in -

C:\PROGRAM FILES\SQLLIB\SAMPLES\ADO\VB

From memory they are something like spcall.* (simple call) and rssp.*
(returning a result set).

I was just working on an example piece of code for one of our developers
today and the sample code works !!!

HTH

Phil


Thanks for that info Phil!

I have stepped back from the VB part and am focussing on the DB2 part. I
have added SPECIFIC NAME to the procedure npn_test2, but when I try to run
it via Ops Navigator: call npn_test2('000006', ?);

I get the following error: "The number of parameter values set or registered
does not match the number of parameters."

Yet when I run:

SELECT SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_MODE,
PARAMETER_NAME
FROM qsys2.sysparms
WHERE specific_name ='NORM';

I get:
NORM, NPN_TEST2, 1, IN, PCONTACTID

NORM, NPN_TEST2, 2, OUT, ONAME

So what's up with this error message?

cheers,
Norm
Nov 12 '05 #6

P: n/a

"Philip Nelson" <te*****@scotdb.com> wrote in message
news:Mq***********************@news.easynews.com.. .
In the stored procedure definition you want to specify a SPECIFIC NAME as
well as the procedure name.

You really do want to be using the enumeration adCmdStoredProc, which you
have commented out in favour of adCmdText.

You'll find a couple of good samples in -

C:\PROGRAM FILES\SQLLIB\SAMPLES\ADO\VB

From memory they are something like spcall.* (simple call) and rssp.*
(returning a result set).

I was just working on an example piece of code for one of our developers
today and the sample code works !!!

HTH

Phil


Thanks for that info Phil!

I have stepped back from the VB part and am focussing on the DB2 part. I
have added SPECIFIC NAME to the procedure npn_test2, but when I try to run
it via Ops Navigator: call npn_test2('000006', ?);

I get the following error: "The number of parameter values set or registered
does not match the number of parameters."

Yet when I run:

SELECT SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_MODE,
PARAMETER_NAME
FROM qsys2.sysparms
WHERE specific_name ='NORM';

I get:
NORM, NPN_TEST2, 1, IN, PCONTACTID

NORM, NPN_TEST2, 2, OUT, ONAME

So what's up with this error message?

cheers,
Norm
Nov 12 '05 #7

P: n/a

"ColdCanuck" <co**@canuck.ca> wrote in message
news:577mc.7147$F04.4921@clgrps13...
"ColdCanuck" <co**@canuck.ca> wrote in message
news:gY6mc.6987$F04.5734@clgrps13...
Greetings!

I am VERY new to DB2 but not Orable, Sybase and SQL Server.

I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I try to execute

Sorry, I accidently sent this before finishing...

Please excuse the cross-post but I am unsure which group my problem

pertains to.

As I was saying, when I try to execute my stored proc, I get the following
error:
"Parameter type cannot be determined for at least one variant
parameter."

My proc is quite simple:

CREATE PROCEDURE NORM.npn_test2
( IN pContactId character(3),
OUT oName character(20))
LANGUAGE SQL
BEGIN ATOMIC

DECLARE sName CHAR(20);

SET sName = (SELECT ADADTX || ', ' || ADAETX FROM MAILIB.UUADCPP WHERE
ADAANB = pContactId);
SET oName = sName;

END
My VB code looks like:

Public Function Call_sp_test(con As ADODB.Connection, ByVal sContactId As
String) As String
On Error GoTo ErrHandler
Dim sName As String

Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Dim P1 As New ADODB.Parameter
Dim P2 As New ADODB.Parameter

Set cmd.ActiveConnection = con

cmd.CommandText = "{CALL NORM.npn_test2(?,?)}"

cmd.CommandType = adCmdText
'cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh
'cmd.Parameters("pContactId").Value = sContactId
cmd.Prepared = True

sName = " " '40 spaces
P1.Direction = adParamInput
P1.Type = adChar
P1.Size = 6
P1.Name = "ContactId"
P1.Value = "000006"

P1.Direction = adParamReturnValue
P2.Type = adChar
P2.Name = "Name"
P2.Size = 40
P2.Value = sName

cmd.Parameters.Append P1
cmd.Parameters.Append P2

'cmd.Execute
rst.Open cmd <- error occurs here

sName = cmd.Parameters("oName").Value

Call_sp_test = cmd.Parameters("oName").Value

rst.Close
Set rst = Nothing
Set cmd = Nothing

ErrHandler:
MsgBox "Error Code: " & Err.Number & vbNewLine & _
"Description: " & Err.Description & vbNewLine & _
"Source: " & Err.Source, vbOKOnly + vbCritical
Err.Clear
'Set Call_sp_test = Nothing

End Function
Has anyone run into this problem before? What am I missing here?

Thanks for all replies!

cheers,
CC

Hey CC,
Just judging by the error message you're getting (never having to use DB2
before) I would guess that your problem is that you are calling the
cmd.Parameters.Refresh method, which should auto-populate your Parameter
collection in the Command object, and then incorrectly creating 2 other
parameters to add to the collection.

The two new Parameters that you are creating as new.
This is an incorrect way to create parameters. You should call the
Cmd.CreateParameter method to create the parameter, set the values, and then
append it to the Paramters collection.

The bottom line is that you should only do one of the two-
Cmd.Parameters.Refresh (Which is inefficient if the parameter list doesn't
change), or Cmd.Parameters.Append(Cmd.CreateParameter(...)) (my personal
favorite).

--Buddy Robbins
MCSD, MCSE, MCDBA
Nov 12 '05 #8

P: n/a

"ColdCanuck" <co**@canuck.ca> wrote in message
news:577mc.7147$F04.4921@clgrps13...
"ColdCanuck" <co**@canuck.ca> wrote in message
news:gY6mc.6987$F04.5734@clgrps13...
Greetings!

I am VERY new to DB2 but not Orable, Sybase and SQL Server.

I am trying to call a stored procedure via VB 6 and ADO/OLEDB. But when I try to execute

Sorry, I accidently sent this before finishing...

Please excuse the cross-post but I am unsure which group my problem

pertains to.

As I was saying, when I try to execute my stored proc, I get the following
error:
"Parameter type cannot be determined for at least one variant
parameter."

My proc is quite simple:

CREATE PROCEDURE NORM.npn_test2
( IN pContactId character(3),
OUT oName character(20))
LANGUAGE SQL
BEGIN ATOMIC

DECLARE sName CHAR(20);

SET sName = (SELECT ADADTX || ', ' || ADAETX FROM MAILIB.UUADCPP WHERE
ADAANB = pContactId);
SET oName = sName;

END
My VB code looks like:

Public Function Call_sp_test(con As ADODB.Connection, ByVal sContactId As
String) As String
On Error GoTo ErrHandler
Dim sName As String

Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

Dim P1 As New ADODB.Parameter
Dim P2 As New ADODB.Parameter

Set cmd.ActiveConnection = con

cmd.CommandText = "{CALL NORM.npn_test2(?,?)}"

cmd.CommandType = adCmdText
'cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh
'cmd.Parameters("pContactId").Value = sContactId
cmd.Prepared = True

sName = " " '40 spaces
P1.Direction = adParamInput
P1.Type = adChar
P1.Size = 6
P1.Name = "ContactId"
P1.Value = "000006"

P1.Direction = adParamReturnValue
P2.Type = adChar
P2.Name = "Name"
P2.Size = 40
P2.Value = sName

cmd.Parameters.Append P1
cmd.Parameters.Append P2

'cmd.Execute
rst.Open cmd <- error occurs here

sName = cmd.Parameters("oName").Value

Call_sp_test = cmd.Parameters("oName").Value

rst.Close
Set rst = Nothing
Set cmd = Nothing

ErrHandler:
MsgBox "Error Code: " & Err.Number & vbNewLine & _
"Description: " & Err.Description & vbNewLine & _
"Source: " & Err.Source, vbOKOnly + vbCritical
Err.Clear
'Set Call_sp_test = Nothing

End Function
Has anyone run into this problem before? What am I missing here?

Thanks for all replies!

cheers,
CC

Hey CC,
Just judging by the error message you're getting (never having to use DB2
before) I would guess that your problem is that you are calling the
cmd.Parameters.Refresh method, which should auto-populate your Parameter
collection in the Command object, and then incorrectly creating 2 other
parameters to add to the collection.

The two new Parameters that you are creating as new.
This is an incorrect way to create parameters. You should call the
Cmd.CreateParameter method to create the parameter, set the values, and then
append it to the Paramters collection.

The bottom line is that you should only do one of the two-
Cmd.Parameters.Refresh (Which is inefficient if the parameter list doesn't
change), or Cmd.Parameters.Append(Cmd.CreateParameter(...)) (my personal
favorite).

--Buddy Robbins
MCSD, MCSE, MCDBA
Nov 12 '05 #9

P: n/a

"Buddy Robbins" <Bu***********@sbcglobal.net> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

Hey CC,
Just judging by the error message you're getting (never having to use DB2
before) I would guess that your problem is that you are calling the
cmd.Parameters.Refresh method, which should auto-populate your Parameter
collection in the Command object, and then incorrectly creating 2 other
parameters to add to the collection.

The two new Parameters that you are creating as new.
This is an incorrect way to create parameters. You should call the
Cmd.CreateParameter method to create the parameter, set the values, and then append it to the Paramters collection.

The bottom line is that you should only do one of the two-
Cmd.Parameters.Refresh (Which is inefficient if the parameter list doesn't
change), or Cmd.Parameters.Append(Cmd.CreateParameter(...)) (my personal
favorite).

--Buddy Robbins
MCSD, MCSE, MCDBA


Thanks for that Buddy!

I've taken this back to the DB2 where I'm getting errors just trying to call
the stored proc.

cheers,
Norm
Nov 12 '05 #10

P: n/a

"Buddy Robbins" <Bu***********@sbcglobal.net> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

Hey CC,
Just judging by the error message you're getting (never having to use DB2
before) I would guess that your problem is that you are calling the
cmd.Parameters.Refresh method, which should auto-populate your Parameter
collection in the Command object, and then incorrectly creating 2 other
parameters to add to the collection.

The two new Parameters that you are creating as new.
This is an incorrect way to create parameters. You should call the
Cmd.CreateParameter method to create the parameter, set the values, and then append it to the Paramters collection.

The bottom line is that you should only do one of the two-
Cmd.Parameters.Refresh (Which is inefficient if the parameter list doesn't
change), or Cmd.Parameters.Append(Cmd.CreateParameter(...)) (my personal
favorite).

--Buddy Robbins
MCSD, MCSE, MCDBA


Thanks for that Buddy!

I've taken this back to the DB2 where I'm getting errors just trying to call
the stored proc.

cheers,
Norm
Nov 12 '05 #11

P: n/a
The bottom line is that you should only do one of the two-
Cmd.Parameters.Refresh (Which is inefficient if the parameter list doesn't change), or Cmd.Parameters.Append(Cmd.CreateParameter(...)) (my personal favorite).

--Buddy Robbins
MCSD, MCSE, MCDBA


You were spot on here Buddy! That did it!

many thanks...
Norm
Nov 12 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.