Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 755
  • Last Modified:

oracle odbc parameterize query with resultset

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
kahwoo
Asked:
kahwoo
  • 3
1 Solution
 
kahwooAuthor Commented:
the calling query

{ call Pkg_MYweb.GET__RPT(? , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
0
 
realraelCommented:

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
 
kahwooAuthor Commented:
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
 
kahwooAuthor Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now