|
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
| |
Share this Question
|
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 | |
|
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 | |
|
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 | |
|
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 | |
|
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 | |
|
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 | |
|
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 | |
|
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 | |
|
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 | |
|
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 | |
|
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 | | This discussion thread is closed Replies have been disabled for this discussion. | |  Question stats - viewed: 2951
- replies: 11
- date asked: Nov 12 '05
|
