Getting off the ground with orace parameters

Having a devil of a time passing a parameter to an oracle command in cnet .
If I substitute the :EXID for a string (Eg 'dogbreath') and remove the parm
stuff it all works. What I was expecting to happen was for the valiue in
exid_txt replace the :EXID. But instead I got the error "ORA-01008: not all
variables bound " at the da.fill line. Apparently I am missing can someone
fill me in?

query = "select ext, active, inactive from cam where ext = :EXID ";

OracleCommand cmd = new OracleCommand(query,con);
cmd.Parameters.Add("EXID", OracleType.VarChar);
cmd.Parameters["EXID"].Value = exid_txt.ToString();

DataSet ds = new DataSet();
OracleDataAdapter da = new OracleDataAdapter(query, con);
da.Fill(ds);

// squeeze a view and feed it the dataset ds for sortability
DataView dv = new DataView(ds.Tables[0]);

David
Tue Jan 04 16:57:26 CST 2005


"jon L" <jonL@discussions.microsoft.com> wrote in message
news:15461C1A-226A-4FDE-BF19-D2A665A7A0C0@microsoft.com...
> Having a devil of a time passing a parameter to an oracle command in cnet
> .
> If I substitute the :EXID for a string (Eg 'dogbreath') and remove the
> parm
> stuff it all works. What I was expecting to happen was for the valiue in
> exid_txt replace the :EXID. But instead I got the error "ORA-01008: not
> all
> variables bound " at the da.fill line. Apparently I am missing can
> someone
> fill me in?
>
> query = "select ext, active, inactive from cam where ext = :EXID ";
>
> OracleCommand cmd = new OracleCommand(query,con);
> cmd.Parameters.Add("EXID", OracleType.VarChar);
> cmd.Parameters["EXID"].Value = exid_txt.ToString();
>
> DataSet ds = new DataSet();
> OracleDataAdapter da = new OracleDataAdapter(query, con);

Should be

OracleDataAdapter da = new OracleDataAdapter(cmd);

Passing the text into the OracleDataAdapter causes it to create its own
OracleCommand, which doesn't have the parameters set.

David

davidsc
Wed Jan 12 22:53:16 CST 2005

Jon,

If I remember correctly, you need to include the ":" in the parameter
name in your .NET code.

query = "select ext, active, inactive from cam where ext = :EXID ";
OracleCommand cmd = new OracleCommand(query,con);
cmd.Parameters.Add(":EXID", OracleType.VarChar);
cmd.Parameters[":EXID"].Value = exid_txt.ToString();

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.