Parameter expected?

1 Apr 2005 11:07 PM

andrewbb

It keeps saying the stored procedure expects the parameter '@entity'
which was not suppplied, but I'm supplying it:

OdbcConnection cn = new OdbcConnection(CNSTR);
OdbcCommand cmd = new OdbcCommand("GetMyList", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@entity", OdbcType.VarChar, 4);
cmd.Parameters.Add("@type", OdbcType.VarChar, 4);
cmd.Parameters["@entity"].Value = "PRAC";
cmd.Parameters["@type"].Value = "TYPE";
cn.Open();
OdbcDataReader reader = cmd.ExecuteReader();
cn.Close();

If I substitute a SQL statement (no parameters) all is good. What I am
I doing wrong on the parameter creation/adding?

Thanks in advance.

3 Apr 2005 4:09 PM

Val Mazur (MVP)

Hi,

Could be something related to the reserved words. If you have any reserved
words as a column names in your SQL statement you could get this type of
error. What is your SP?

--
Val Mazur
Microsoft MVP

http://xport.mvps.org

<andre***@gmail.com> wrote in message

Show quote

news:1112396856.328442.49980@o13g2000cwo.googlegroups.com...
> It keeps saying the stored procedure expects the parameter '@entity'
> which was not suppplied, but I'm supplying it:
>
> OdbcConnection cn = new OdbcConnection(CNSTR);
> OdbcCommand cmd = new OdbcCommand("GetMyList", cn);
> cmd.CommandType = CommandType.StoredProcedure;
> cmd.Parameters.Add("@entity", OdbcType.VarChar, 4);
> cmd.Parameters.Add("@type", OdbcType.VarChar, 4);
> cmd.Parameters["@entity"].Value = "PRAC";
> cmd.Parameters["@type"].Value = "TYPE";
> cn.Open();
> OdbcDataReader reader = cmd.ExecuteReader();
> cn.Close();
>
> If I substitute a SQL statement (no parameters) all is good. What I am
> I doing wrong on the parameter creation/adding?
>
> Thanks in advance.
>

4 Apr 2005 2:15 PM

andrewbb

I changed all the parameter column names to make sure it's not a
reserved word, but it still gives the same error. Here's the SP with
the modified names:

alter procedure dbo.GetMCTRList (
    @aentity varchar(4),
    @atype    varchar(4)
) as

    select
        MCTR_VALUE as vVal,
        MCTR_DESC as vDescr
    from
        mihsreport..CMC_MCTR_CD_TRANS
    where
        MCTR_ENTITY = @aentity
        and MCTR_TYPE = @atype

4 Apr 2005 2:58 PM

andrewbb

Interesting that when I change the code to use SqlClient rather than
Odbc, everything works.

We're moving to Sybase soon, so using the SqlClient objects isn't a
long term solution.

5 Apr 2005 12:06 AM

Val Mazur (MVP)

Your code looks fine. Could be some sort of a bug. Actually moving to Sybase
could solve the problem, because you will use another ODBC driver. If it
does not solve, then you could be in a trouble even with OLEDB for Sybase. I
have pretty bad experience to work with Sybase with .NET. OLEDB provider for
Sybase has a number of bugs and in some cases you just do not have
workaround for them. .NET Managed provider for Sybase is extremely slow. I
have found that in some cases it works about 20 times slower than OLEDB
provider

--
Val Mazur
Microsoft MVP

http://xport.mvps.org

<andre***@gmail.com> wrote in message

Show quote

news:1112624158.698094.58810@z14g2000cwz.googlegroups.com...
>I changed all the parameter column names to make sure it's not a
> reserved word, but it still gives the same error. Here's the SP with
> the modified names:
>
> alter procedure dbo.GetMCTRList (
> @aentity varchar(4),
> @atype varchar(4)
> ) as
>
> select
> MCTR_VALUE as vVal,
> MCTR_DESC as vDescr
> from
> mihsreport..CMC_MCTR_CD_TRANS
> where
> MCTR_ENTITY = @aentity
> and MCTR_TYPE = @atype
>