Solved

oracle odbc parameterize query with resultset

Posted on 2004-08-09
6
726 Views
Last Modified: 2010-08-05
Dear all,
   I had a ERROR System.Data.Odbc.OdbcException: ERROR [HY105] [Microsoft][ODBC driver for Oracle]Invalid parameter type . Can anyone tell me what is the cause of such error ?
problem encountered at this line
-             myAdapter.Fill(ds)
here is wat i do.. .

CREATE OR REPLACE PACKAGE Pkg_MYweb
AS
TYPE tblString IS TABLE OF VARCHAR(50)INDEX BY BINARY_INTEGER;
TYPE tblNumber IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
PROCEDURE GET_RPT
(
       i_ADDRESSKEY IN NUMBER,
        o_FIELD01 OUT tblString,
        o_FIELD02 OUT tblString,
        o_FIELD03 OUT tblNumber,
        o_FIELD04 OUT tblNumber
        o_FIELD05 OUT tblString,
        o_FIELD06 OUT tbString,
        o_FIELD07 OUT tblNumber,
        o_FIELD08 OUT tblNumber,
        o_FIELD09 OUT tblString,
        o_FIELD10 OUT tblNumber,
        o_FIELD11 OUT tblNumber,
        o_FIELD12 OUT tblString,
        o_FIELD13 OUT tblNumber,
        o_FIELD14 OUT tblNumber,
        o_FIELD15 OUT tblString
 );
 END Pkg_MYweb;

this is my code to access it

            myCommand = new OdbcCommand()
            myAdapter = new OdbcDataAdapter()
            myAdapter.SelectCommand = myCommand

            myCommand.CommandText = SqlString
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.Connection = myConnection

            prm = myCommand.Parameters.Add(new OdbcParameter("ADDRESSKEY", OdbcType.Int))
            prm.Direction = ParameterDirection.Input
            prm.Value = CInt(SiteCode)

            myCommand.Parameters.Add(new OdbcParameter("FIELD01", OdbcType.VarChar, 50, ParameterDirection.Output))
            myCommand.Parameters.Add(new OdbcParameter("FIELD02", OdbcType.VarChar, 50, ParameterDirection.Output))

            prm = myCommand.Parameters.Add(new OdbcParameter("FIELD03", OdbcType.Numeric))
            prm.Direction = ParameterDirection.Output

            prm = myCommand.Parameters.Add(new OdbcParameter("FIELD04", OdbcType.Numeric))
            prm.Direction = ParameterDirection.Output

            myCommand.Parameters.Add(new OdbcParameter("FIELD05", OdbcType.VarChar, 50, parameterDirection.Output))
            myCommand.Parameters.Add(new OdbcParameter("FIELD06", OdbcType.VarChar, 50, ParameterDirection.Output))

            prm = myCommand.Parameters.Add(new OdbcParameter("FIELD07", OdbcType.Numeric))
            prm.Direction = ParameterDirection.Output
            prm = myCommand.Parameters.Add(new OdbcParameter("FIELD08", OdbcType.Numeric))
            prm.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(new OdbcParameter("FIELD09", OdbcType.VarChar, 50, ParameterDirection.Output))
            prm = myCommand.Parameters.Add(new OdbcParameter("FIELD10", OdbcType.Numeric))
            prm.Direction = ParameterDirection.Output
            prm = myCommand.Parameters.Add(new OdbcParameter("FIELD11", OdbcType.Numeric))
            prm.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(new OdbcParameter("FIELD12", OdbcType.VarChar, 50, ParameterDirection.Output))
            prm = myCommand.Parameters.Add(new OdbcParameter("FIELD13", OdbcType.Numeric))
            prm.Direction = ParameterDirection.Output
            prm = myCommand.Parameters.Add(new OdbcParameter("FIELD14", OdbcType.Numeric))
            prm.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(new OdbcParameter("FIELD15", OdbcType.VarChar, 50, ParameterDirection.Output))

            myAdapter.Fill(ds)
            DbGrid.DataSource = ds
            DbGrid.DataBind()


thank you very much
0
Comment
Question by:kahwoo
  • 3
6 Comments
 
LVL 2

Author Comment

by:kahwoo
ID: 11751373
the calling query

{ call Pkg_MYweb.GET__RPT(? , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
0
 
LVL 3

Accepted Solution

by:
realrael earned 500 total points
ID: 11759118

in order to debug the problem, our first step is to determine which particular parameter type is causing the error (NUMBER, tblString or tblNumeric). i suggest you create a simple procedure that first test these, then work from there. - rael
0
 
LVL 2

Author Comment

by:kahwoo
ID: 11760506
i had found out the problem

that is the line tat coz the error prm = myCommand.Parameters.Add(new OdbcParameter("ADDRESSKEY", OdbcType.Int))
in my procedure I declare it as NUMBER.

and I had abandone all the parameterDirection.Output

instead I use this
{call (?, {Resultset 1000, FIELD01, FIELD02....})}

cheers,
chan kah woo
0
 
LVL 2

Author Comment

by:kahwoo
ID: 11760518
realrael,
    I wish to delete this question as I had work it out myself,
however I will give u some points for the good advice that u gave me.

regards

kahwoo ^___.^
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question