Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle odbc parameterize query with resultset

Posted on 2004-08-09
6
Medium Priority
?
742 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

722 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