Getting off the ground with orace parameters

[Guest]

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 Browne]

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

 

[David Sceppa]

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.