Re: Help again
- From: "RobinS" <RobinS@xxxxxxxxxxxxxxx>
- Date: Mon, 29 Jan 2007 16:56:01 -0800
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" <noneavailable@xxxxxxxxxxx> wrote in message
news:OTTxoa9QHHA.2252@xxxxxxxxxxxxxxxxxxxxxxx
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" <RobinS@xxxxxxxxxxxxxxx> wrote in message
news:5rGdnfVx66EjpiPYnZ2dnUVZ_t6qnZ2d@xxxxxxxxxxxxxx
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" <noneavailable@xxxxxxxxxxx> wrote in message
news:OBeowB8QHHA.2252@xxxxxxxxxxxxxxxxxxxxxxx
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
.
- References:
- Help again
- From: Darth Ferret
- Re: Help again
- From: RobinS
- Re: Help again
- From: Darth Ferret
- Help again
- Prev by Date: Re: VS2005 code editor stays in runtime
- Next by Date: Re: Working
- Previous by thread: Re: Help again
- Next by thread: Get user info from AD
- Index(es):
Relevant Pages
|
