strange error when trying to call a CL from vb.net

  • Thread starter Thread starter Amaryllis
  • Start date Start date
A

Amaryllis

I'm trying to call a CL which is located on our AS400 from a Windows
application. I've tried to code it in different ways, but I seem to
get the same error every time. Does anyone have any clue as to what
this means? I am not trying to alter a table. This particular CL
merely generates the next voucher number in a sequence.


"SQL0204: HRCU030P in HRZNCUSOBJ type *N not found.
Cause . . . . . : HRCU030P in HRZNCUSOBJ type *N was not found. If
this is an ALTER TABLE statement and the type is *N, a constraint was
not found. If this is not an ALTER TABLE statement and the type is *N,
a function, procedure, or trigger was not found. Recovery . . . :
Change the name and try the request again. If the object is a node
group, ensure that the DB2 Multisystem product is installed on your
system and create a nodegroup with the CRTNODGRP CL command."


Thanks
 
Hi Amaryllis,

I thought that I now knew every shortcut of my name in computer language
however the CL with AS400 I do not know, because of that Jay does every
AS400 in this newsgroup I have added (AS400) to this message. That does not
mean he can help you, however if he can he mostly does.

(I have looked at Google so now I know an extra CL)

Cor
I'm trying to call a CL which is located on our AS400 from a Windows
application. I've tried to code it in different ways, but I seem to
get the same error every time. Does anyone have any clue as to what
this means? I am not trying to alter a table. This particular CL
merely generates the next voucher number in a sequence.


"SQL0204: HRCU030P in HRZNCUSOBJ type *N not found.
Cause . . . . . : HRCU030P in HRZNCUSOBJ type *N was not found. If
this is an ALTER TABLE statement and the type is *N, a constraint was
not found. If this is not an ALTER TABLE statement and the type is *N,
a function, procedure, or trigger was not found. Recovery . . . :
Change the name and try the request again. If the object is a node
group, ensure that the DB2 Multisystem product is installed on your
system and create a nodegroup with the CRTNODGRP CL command."


Thanks
Click to expand...
 
Amaryllis,
What did you use for OleDbCommand.CommandType? It sounds like you used Text,
you need to use StoredProcedure and ensure that the OleDbCommand.CommandText
is in the correct call syntax.

Its best to be on Client Access V5R2 or later (with current all PTFs) to be
fully supported from .NET. (I understand that you can remain on an older
OS/400 such as V4R5, its the client that needs to be current).

http://www-1.ibm.com/servers/eserver/iseries/access/oledb/index.html

http://www-912.ibm.com/n_dir/nas4ap...6be4003c8957?OpenDocument&Highlight=2,ii13341

The above links are from June 3rd, not sure if there are more current
versions with better support...

If you look around the SQL Thing site (http://www.sqlthing.com) and the
above sites you should come across a number of samples and further
information.

I understand that Client Access V5R3 will include a "native" .NET Data
Provider, which should see an improvement in performance!

http://www.iseriesnetwork.com/news/nwn/story.cfm?ID=18476&channel=news


Hope this helps
Jay


Amaryllis said:
I'm trying to call a CL which is located on our AS400 from a Windows
application. I've tried to code it in different ways, but I seem to
get the same error every time. Does anyone have any clue as to what
this means? I am not trying to alter a table. This particular CL
merely generates the next voucher number in a sequence.


"SQL0204: HRCU030P in HRZNCUSOBJ type *N not found.
Cause . . . . . : HRCU030P in HRZNCUSOBJ type *N was not found. If
this is an ALTER TABLE statement and the type is *N, a constraint was
not found. If this is not an ALTER TABLE statement and the type is *N,
a function, procedure, or trigger was not found. Recovery . . . :
Change the name and try the request again. If the object is a node
group, ensure that the DB2 Multisystem product is installed on your
system and create a nodegroup with the CRTNODGRP CL command."


Thanks
Click to expand...
 
Unfortunately, we are such a small ompany that not everyone has the
most updated versions of Client Access, so the stored procedure thing
will not work for everyone, so I was forced to get it to work another
way. Below is a copy of my code. The CL runs if I hard-code the
parameters into the call string. I just can't seem to get the output
values back from the call.


myCommand.CommandType = ADODB.CommandTypeEnum.adCmdText
myCommand.ActiveConnection = conn400

Dim myParm As ADODB.Parameter
myParm = myCommand.CreateParameter("VoucherNum",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamOutput, 10, " ")
Call myCommand.Parameters.Append(myParm)

Dim myParm1 As ADODB.Parameter
myParm1 = myCommand.CreateParameter("BatchPrf",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 3, "CDA")
Call myCommand.Parameters.Append(myParm1)

Dim myParm2 As ADODB.Parameter
myParm2 = myCommand.CreateParameter("ErrorNum",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamOutput, 2, " ")
Call myCommand.Parameters.Append(myParm2)

Dim myParm3 As ADODB.Parameter
myParm3 = myCommand.CreateParameter("ErrorFlag",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamOutput, 1, " ")
Call myCommand.Parameters.Append(myParm3)

myCommand.CommandText = "Call HRZNCUSOBJ.HRCU030P('" &
myParm.Value & "', '" & myParm1.Value & "', '" &
myParm2.Value & "', '" & myParm3.Value & "')"

myCommand.Execute()




I tried this method to get the values back, but I get an "Unknown
error"


Dim strVoucherNum As String =
strcvtr.FromBytes(myCommand.Parameters("VoucherNum").Value)
Dim strErrNum As String =
strcvtr.FromBytes(myCommand.Parameters("ErrorNum").Value)
Dim strErrFlg As String =
strcvtr.FromBytes(myCommand.Parameters("ErrorFlag").Value)
 
Amaryllis,
myCommand.CommandType = ADODB.CommandTypeEnum.adCmdText
Click to expand...

NOTE: This is a VB.NET newsgroup! The code you gave appears to be VB6 code,
you may have better luck in one of the VB6 newsgroups or on the
iseriesnetwork.
Unfortunately, we are such a small ompany that not everyone has the
most updated versions of Client Access, so the stored procedure thing
Click to expand...
The answer I gave is VB.NET specific, if you have VB6 you can be on an older
version of Client Access...

VB.NET, more specifically ADO.NET prefers current versions, if you are using
ADO (ADODB) you can use older versions of Client access with either VB6 or
VB.NET.
myCommand.CommandText = "Call HRZNCUSOBJ.HRCU030P('" &
myParm.Value & "', '" & myParm1.Value & "', '" &
myParm2.Value & "', '" & myParm3.Value & "')"
Click to expand...
Why are you adding the parameters, without using parameter markers, you only
need to use one, build the entire string, or use parameters. I would
recommend using parameters.

With ADODB you can use the following which should work.
myCommand.CommandType = ADODB.CommandTypeEnum.adStoredProc
Click to expand...
myCommand.CommandText = "HRZNCUSOBJ.HRCU030P"
Dim myParm As ADODB.Parameter
myParm = myCommand.CreateParameter("VoucherNum",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamOutput, 10, " ")
Call myCommand.Parameters.Append(myParm)
Click to expand...

...

If you watch the CommandText property it will be converted to property Call
syntax for you, with the required number of parameter markers.

In your example the correct Call syntax, with parameter markers, is "{ call
HRZNCUSOBJ.HRCU030P(?, ?, ?, ?) }" Again if you set CommandType =
adStoredProc, ADODB will build the correct Call syntax for you.
Unfortunately ADO.NET does not...

Hope this helps
Jay

Amaryllis said:
Unfortunately, we are such a small ompany that not everyone has the
most updated versions of Client Access, so the stored procedure thing
will not work for everyone, so I was forced to get it to work another
way. Below is a copy of my code. The CL runs if I hard-code the
parameters into the call string. I just can't seem to get the output
values back from the call.


myCommand.CommandType = ADODB.CommandTypeEnum.adCmdText
myCommand.ActiveConnection = conn400

Dim myParm As ADODB.Parameter
myParm = myCommand.CreateParameter("VoucherNum",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamOutput, 10, " ")
Call myCommand.Parameters.Append(myParm)

Dim myParm1 As ADODB.Parameter
myParm1 = myCommand.CreateParameter("BatchPrf",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 3, "CDA")
Call myCommand.Parameters.Append(myParm1)

Dim myParm2 As ADODB.Parameter
myParm2 = myCommand.CreateParameter("ErrorNum",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamOutput, 2, " ")
Call myCommand.Parameters.Append(myParm2)

Dim myParm3 As ADODB.Parameter
myParm3 = myCommand.CreateParameter("ErrorFlag",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamOutput, 1, " ")
Call myCommand.Parameters.Append(myParm3)

myCommand.CommandText = "Call HRZNCUSOBJ.HRCU030P('" &
myParm.Value & "', '" & myParm1.Value & "', '" &
myParm2.Value & "', '" & myParm3.Value & "')"

myCommand.Execute()




I tried this method to get the values back, but I get an "Unknown
error"


Dim strVoucherNum As String =
strcvtr.FromBytes(myCommand.Parameters("VoucherNum").Value)
Dim strErrNum As String =
strcvtr.FromBytes(myCommand.Parameters("ErrorNum").Value)
Dim strErrFlg As String =
strcvtr.FromBytes(myCommand.Parameters("ErrorFlag").Value)
Click to expand...
 
I know it's VB6 code, but it's the only thing I can get to work :?
Everything else I have tried has bombed on me. I'll try anything at
this point. Thanks for the help.
 
Back
Top