kahwoo
asked on
oracle odbc parameterize query with resultset
Dear all,
I had a ERROR System.Data.Odbc.OdbcExcep tion: 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.StoredProcedur e
myCommand.Connection = myConnection
prm = myCommand.Parameters.Add(n ew OdbcParameter("ADDRESSKEY" , OdbcType.Int))
prm.Direction = ParameterDirection.Input
prm.Value = CInt(SiteCode)
myCommand.Parameters.Add(n ew OdbcParameter("FIELD01", OdbcType.VarChar, 50, ParameterDirection.Output) )
myCommand.Parameters.Add(n ew OdbcParameter("FIELD02", OdbcType.VarChar, 50, ParameterDirection.Output) )
prm = myCommand.Parameters.Add(n ew OdbcParameter("FIELD03", OdbcType.Numeric))
prm.Direction = ParameterDirection.Output
prm = myCommand.Parameters.Add(n ew OdbcParameter("FIELD04", OdbcType.Numeric))
prm.Direction = ParameterDirection.Output
myCommand.Parameters.Add(n ew OdbcParameter("FIELD05", OdbcType.VarChar, 50, parameterDirection.Output) )
myCommand.Parameters.Add(n ew OdbcParameter("FIELD06", OdbcType.VarChar, 50, ParameterDirection.Output) )
prm = myCommand.Parameters.Add(n ew OdbcParameter("FIELD07", OdbcType.Numeric))
prm.Direction = ParameterDirection.Output
prm = myCommand.Parameters.Add(n ew OdbcParameter("FIELD08", OdbcType.Numeric))
prm.Direction = ParameterDirection.Output
myCommand.Parameters.Add(n ew OdbcParameter("FIELD09", OdbcType.VarChar, 50, ParameterDirection.Output) )
prm = myCommand.Parameters.Add(n ew OdbcParameter("FIELD10", OdbcType.Numeric))
prm.Direction = ParameterDirection.Output
prm = myCommand.Parameters.Add(n ew OdbcParameter("FIELD11", OdbcType.Numeric))
prm.Direction = ParameterDirection.Output
myCommand.Parameters.Add(n ew OdbcParameter("FIELD12", OdbcType.VarChar, 50, ParameterDirection.Output) )
prm = myCommand.Parameters.Add(n ew OdbcParameter("FIELD13", OdbcType.Numeric))
prm.Direction = ParameterDirection.Output
prm = myCommand.Parameters.Add(n ew OdbcParameter("FIELD14", OdbcType.Numeric))
prm.Direction = ParameterDirection.Output
myCommand.Parameters.Add(n ew OdbcParameter("FIELD15", OdbcType.VarChar, 50, ParameterDirection.Output) )
myAdapter.Fill(ds)
DbGrid.DataSource = ds
DbGrid.DataBind()
thank you very much
I had a ERROR System.Data.Odbc.OdbcExcep
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.StoredProcedur
myCommand.Connection = myConnection
prm = myCommand.Parameters.Add(n
prm.Direction = ParameterDirection.Input
prm.Value = CInt(SiteCode)
myCommand.Parameters.Add(n
myCommand.Parameters.Add(n
prm = myCommand.Parameters.Add(n
prm.Direction = ParameterDirection.Output
prm = myCommand.Parameters.Add(n
prm.Direction = ParameterDirection.Output
myCommand.Parameters.Add(n
myCommand.Parameters.Add(n
prm = myCommand.Parameters.Add(n
prm.Direction = ParameterDirection.Output
prm = myCommand.Parameters.Add(n
prm.Direction = ParameterDirection.Output
myCommand.Parameters.Add(n
prm = myCommand.Parameters.Add(n
prm.Direction = ParameterDirection.Output
prm = myCommand.Parameters.Add(n
prm.Direction = ParameterDirection.Output
myCommand.Parameters.Add(n
prm = myCommand.Parameters.Add(n
prm.Direction = ParameterDirection.Output
prm = myCommand.Parameters.Add(n
prm.Direction = ParameterDirection.Output
myCommand.Parameters.Add(n
myAdapter.Fill(ds)
DbGrid.DataSource = ds
DbGrid.DataBind()
thank you very much
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i had found out the problem
that is the line tat coz the error prm = myCommand.Parameters.Add(n ew 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
that is the line tat coz the error prm = myCommand.Parameters.Add(n
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
ASKER
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 ^___.^
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 ^___.^
ASKER
{ call Pkg_MYweb.GET__RPT(? , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}