Help again

[Darth Ferret]

This thing is about to drive me crazy. I have about 50 queries in the AS400
that I need to put on a menu. Once I conquer this I have a bunch more rpg
reports that I need to pass a date to. In the AS400 I have a stored
procedure (SPRUNQRY) that runs the RUNQRY command with the name of the query
as a parameter. In the AS400 I would type "RUNQRY SUNTR401A" on a command
line to run this query. My connection is opening, and I can run some reports
that do not have any parameters. My VB.net 2003 is:

'create command object

Dim cmd As New iDB2Command(" ", cn)

'setup the parameters

Dim cmdparm1 As New iDB2Parameter(" cmdparm1", iDB2DbType.iDB2 VarChar, 9)

cmdparm1.Value = "SUNTR401A"

cmdparm1.Direct ion = ParameterDirect ion.Output

cmd.Parameters. Add(cmdparm1)

cmd.CommandType = CommandType.Sto redProcedure

cmd.CommandText = "{CALL hteusrj/sprunqry ( ? )}"

Try

cmd.ExecuteNonQ uery()

Catch exc As iDB2Exception

MsgBox("execute did not work", MsgBoxStyle.OKO nly, "OK")

Exit Sub

End Try

This catches an error when it tries to execute the command The error says:
{IBM.Data.DB2.i Series.iDB2SQLE rrorException}

and also: message code -104 and message: "SQL0104 Token {was not valid.
Valid tokens : : <IDENTIFIER>. "

I can't figure out exactly what it wants. I could sure use some help, I've
spun my wheels for several days on this.

Thanks in advance,

Joe in Florida

[RobinS]

The parameter direction should be input. (That's the default, so I would
just eliminate that line). That indicates whether you are sending into *in*
to the SP or if you're getting something back. Output Parameters are
usually used for getting back autoincrement values assigned to inserted
records.

You command text needs to *not* have curly braces in it; it looks like
that's what the AS400 is kicking back. The CommandText needs to be the
*name* of the stored procedure that you are running, so if
"hteusrj/sprunqry" is really the name of the stored procedure in the
database, I think this will work:

cmd.CommandText = "hteusrj/sprunqry"

Does that work?

Robin S.
---------------------------------------------
"Darth Ferret" <no***********@ nowhere.comwrot e in message
news:OB******** ******@TK2MSFTN GP02.phx.gbl...

This thing is about to drive me crazy. I have about 50 queries in the
AS400 that I need to put on a menu. Once I conquer this I have a bunch
more rpg reports that I need to pass a date to. In the AS400 I have a
stored procedure (SPRUNQRY) that runs the RUNQRY command with the name of
the query as a parameter. In the AS400 I would type "RUNQRY SUNTR401A" on
a command line to run this query. My connection is opening, and I can run
some reports that do not have any parameters. My VB.net 2003 is:

'create command object

Dim cmd As New iDB2Command(" ", cn)

'setup the parameters

Dim cmdparm1 As New iDB2Parameter(" cmdparm1", iDB2DbType.iDB2 VarChar, 9)

cmdparm1.Value = "SUNTR401A"

cmdparm1.Direct ion = ParameterDirect ion.Output

cmd.Parameters. Add(cmdparm1)

cmd.CommandType = CommandType.Sto redProcedure

cmd.CommandText = "{CALL hteusrj/sprunqry ( ? )}"

Try

cmd.ExecuteNonQ uery()

Catch exc As iDB2Exception

MsgBox("execute did not work", MsgBoxStyle.OKO nly, "OK")

Exit Sub

End Try

This catches an error when it tries to execute the command The error
says: {IBM.Data.DB2.i Series.iDB2SQLE rrorException}

and also: message code -104 and message: "SQL0104 Token {was not valid.
Valid tokens : : <IDENTIFIER>. "

I can't figure out exactly what it wants. I could sure use some help,
I've spun my wheels for several days on this.

Thanks in advance,

Joe in Florida

[Darth Ferret]

Thank you for your help Robin,

I made the changes and received the message below the code about a qualified
object. On a lark I tried using hteusrj.spcrunq ry with a period instead of a
slash and it just hangs:
'create command object

Dim cmd As New iDB2Command(" ", cn)

'setup the parameters

Dim cmdparm1 As New iDB2Parameter(" cmdparm1", iDB2DbType.iDB2 VarChar, 9)

cmdparm1.Value = "SUNTR401A"

cmd.Parameters. Add(cmdparm1)

cmd.CommandType = CommandType.Sto redProcedure

cmd.CommandText = "hteusrj/sprunqry"

Try

cmd.ExecuteNonQ uery()

Catch exc As iDB2Exception

MsgBox("execute did not work", MsgBoxStyle.OKO nly, "OK")

Exit Sub

End Try

=============== ==========

Message "SQL5016 Qualified object name SPRUNQRY not valid." String

It's getting closer. MessageDetails "Cause . . . . . : One of the
following has occurred: -- The syntax used for the qualified object name is
not valid for the naming option specified. With system naming, the
qualified form of an object name is schema-name/object-name. With SQL
naming the qualified form of an object name is
authorization-name.object-name. -- The syntax used for the qualified object
name is not allowed. User-defined types cannot be qualified with the schema
in the system naming convention on parameters and SQL variables of an SQL
procedure or function. Recovery . . . : Do one of the following and try
the request again: -- If you want to use the SQL naming convention, verify
the SQL naming option in the appropriate SQL command and qualify the object
names in the form authorization-id.object-name. -- If you want to use the
system naming convention, specify the system naming option in the
appropriate SQL command and qualify the object names in the form
schema-name/object-name. -- With the system naming convention, ensure the
user-defined types specified for parameters and variables in an SQL routine
can be found in the current path." String

"RobinS" <Ro****@NoSpam. yah.nonewrote in message
news:5r******** *************** *******@comcast .com...

>
The parameter direction should be input. (That's the default, so I would
just eliminate that line). That indicates whether you are sending into
*in* to the SP or if you're getting something back. Output Parameters are
usually used for getting back autoincrement values assigned to inserted
records.

You command text needs to *not* have curly braces in it; it looks like
that's what the AS400 is kicking back. The CommandText needs to be the
*name* of the stored procedure that you are running, so if
"hteusrj/sprunqry" is really the name of the stored procedure in the
database, I think this will work:

cmd.CommandText = "hteusrj/sprunqry"

Does that work?

Robin S.
---------------------------------------------
"Darth Ferret" <no***********@ nowhere.comwrot e in message
news:OB******** ******@TK2MSFTN GP02.phx.gbl...

>This thing is about to drive me crazy. I have about 50 queries in the
AS400 that I need to put on a menu. Once I conquer this I have a bunch
more rpg reports that I need to pass a date to. In the AS400 I have a
stored procedure (SPRUNQRY) that runs the RUNQRY command with the name of
the query as a parameter. In the AS400 I would type "RUNQRY SUNTR401A" on
a command line to run this query. My connection is opening, and I can run
some reports that do not have any parameters. My VB.net 2003 is:

'create command object

Dim cmd As New iDB2Command(" ", cn)

'setup the parameters

Dim cmdparm1 As New iDB2Parameter(" cmdparm1", iDB2DbType.iDB2 VarChar, 9)

cmdparm1.Val ue = "SUNTR401A"

cmdparm1.Direc tion = ParameterDirect ion.Output

cmd.Parameters .Add(cmdparm1)

cmd.CommandTyp e = CommandType.Sto redProcedure

cmd.CommandTex t = "{CALL hteusrj/sprunqry ( ? )}"

Try

cmd.ExecuteNon Query()

Catch exc As iDB2Exception

MsgBox("execut e did not work", MsgBoxStyle.OKO nly, "OK")

Exit Sub

End Try

This catches an error when it tries to execute the command The error
says: {IBM.Data.DB2.i Series.iDB2SQLE rrorException}

and also: message code -104 and message: "SQL0104 Token {was not valid.
Valid tokens : : <IDENTIFIER>. "

I can't figure out exactly what it wants. I could sure use some help,
I've spun my wheels for several days on this.

Thanks in advance,

Joe in Florida

[RobinS]

What is the name of the parameter in the stored procedure? For example,
this is a stored procedure from SQLServer, and the parameter name is
@CustomerID. So when I add a parameter to my parameterlist containing the
value for this, I have to call the parameter @CustomerID. Is yours really
called "cmdparm1" on the database side?

SELECT CustomerID,
LastName,
FirstName,
Address,
City,
State,
Zip,
Phone
FROM Customer
WHERE (CustomerID = @CustomerID)

And is the stored procedure *really* called "hteusrv/spcrunqry" ? I ask
because I've never seen a stored procedure name with a slash in it.

Robin S.
--------------------------------------------

"Darth Ferret" <no***********@ nowhere.comwrot e in message
news:OT******** ******@TK2MSFTN GP02.phx.gbl...

Thank you for your help Robin,

I made the changes and received the message below the code about a
qualified object. On a lark I tried using hteusrj.spcrunq ry with a period
instead of a slash and it just hangs:
'create command object

Dim cmd As New iDB2Command(" ", cn)

'setup the parameters

Dim cmdparm1 As New iDB2Parameter(" cmdparm1", iDB2DbType.iDB2 VarChar, 9)

cmdparm1.Value = "SUNTR401A"

cmd.Parameters. Add(cmdparm1)

cmd.CommandType = CommandType.Sto redProcedure

cmd.CommandText = "hteusrj/sprunqry"

Try

cmd.ExecuteNonQ uery()

Catch exc As iDB2Exception

MsgBox("execute did not work", MsgBoxStyle.OKO nly, "OK")

Exit Sub

End Try

=============== ==========

Message "SQL5016 Qualified object name SPRUNQRY not valid." String

It's getting closer. MessageDetails "Cause . . . . . : One of the
following has occurred: -- The syntax used for the qualified object name
is not valid for the naming option specified. With system naming, the
qualified form of an object name is schema-name/object-name. With SQL
naming the qualified form of an object name is
authorization-name.object-name. -- The syntax used for the qualified
object name is not allowed. User-defined types cannot be qualified with
the schema in the system naming convention on parameters and SQL
variables of an SQL procedure or function. Recovery . . . : Do one of
the following and try the request again: -- If you want to use the SQL
naming convention, verify the SQL naming option in the appropriate SQL
command and qualify the object names in the form
authorization-id.object-name. -- If you want to use the system naming
convention, specify the system naming option in the appropriate SQL
command and qualify the object names in the form
schema-name/object-name. -- With the system naming convention, ensure the
user-defined types specified for parameters and variables in an SQL
routine can be found in the current path." String

"RobinS" <Ro****@NoSpam. yah.nonewrote in message
news:5r******** *************** *******@comcast .com...

>>
The parameter direction should be input. (That's the default, so I would
just eliminate that line). That indicates whether you are sending into
*in* to the SP or if you're getting something back. Output Parameters
are usually used for getting back autoincrement values assigned to
inserted records.

You command text needs to *not* have curly braces in it; it looks like
that's what the AS400 is kicking back. The CommandText needs to be the
*name* of the stored procedure that you are running, so if
"hteusrj/sprunqry" is really the name of the stored procedure in the
database, I think this will work:

cmd.CommandText = "hteusrj/sprunqry"

Does that work?

Robin S.
---------------------------------------------
"Darth Ferret" <no***********@ nowhere.comwrot e in message
news:OB******* *******@TK2MSFT NGP02.phx.gbl.. .

>>This thing is about to drive me crazy. I have about 50 queries in the
AS400 that I need to put on a menu. Once I conquer this I have a bunch
more rpg reports that I need to pass a date to. In the AS400 I have a
stored procedure (SPRUNQRY) that runs the RUNQRY command with the name
of the query as a parameter. In the AS400 I would type "RUNQRY
SUNTR401A" on a command line to run this query. My connection is
opening, and I can run some reports that do not have any parameters. My
VB.net 2003 is:

'create command object

Dim cmd As New iDB2Command(" ", cn)

'setup the parameters

Dim cmdparm1 As New iDB2Parameter(" cmdparm1", iDB2DbType.iDB2 VarChar,
9)

cmdparm1.Valu e = "SUNTR401A"

cmdparm1.Dire ction = ParameterDirect ion.Output

cmd.Parameter s.Add(cmdparm1)

cmd.CommandTy pe = CommandType.Sto redProcedure

cmd.CommandTe xt = "{CALL hteusrj/sprunqry ( ? )}"

Try

cmd.ExecuteNo nQuery()

Catch exc As iDB2Exception

MsgBox("execu te did not work", MsgBoxStyle.OKO nly, "OK")

Exit Sub

End Try

This catches an error when it tries to execute the command The error
says: {IBM.Data.DB2.i Series.iDB2SQLE rrorException}

and also: message code -104 and message: "SQL0104 Token {was not valid.
Valid tokens : : <IDENTIFIER>. "

I can't figure out exactly what it wants. I could sure use some help,
I've spun my wheels for several days on this.

Thanks in advance,

Joe in Florida