Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

oracle odbc parameterize query with resultset

Posted on 2004-08-09
6
Medium Priority
?
750 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 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses
Course of the Month12 days, 14 hours left to enroll

972 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