Solved

oracle odbc parameterize query with resultset

Posted on 2004-08-09
6
728 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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