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.spcrunqry 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.iDB2VarChar, 9)
cmdparm1.Value = "SUNTR401A"
cmd.Parameters.Add(cmdparm1)
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "hteusrj/sprunqry"
Try
cmd.ExecuteNonQuery()
Catch exc As iDB2Exception
MsgBox("execute did not work", MsgBoxStyle.OKOnly, "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.comwrote in message
news:OB**************@TK2MSFTNGP02.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.iDB2VarChar, 9)
cmdparm1.Value = "SUNTR401A"
cmdparm1.Direction = ParameterDirection.Output
cmd.Parameters.Add(cmdparm1)
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "{CALL hteusrj/sprunqry ( ? )}"
Try
cmd.ExecuteNonQuery()
Catch exc As iDB2Exception
MsgBox("execute did not work", MsgBoxStyle.OKOnly, "OK")
Exit Sub
End Try
This catches an error when it tries to execute the command The error
says: {IBM.Data.DB2.iSeries.iDB2SQLErrorException}
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