how to query oracle database based on user input using asp.net c#

Question

I am facing problem when I try to query oracle database based on user input and fill in dataset for displaying in xml format. Here I wrote my code :-

using System.Data.OracleClient;

[WebMethod]
public string CallCardDetails(string CallCardNo)
{
     //initialize
     DataSet ds = new DataSet();

     //connect
     using (OracleConnection conn = new OracleConnection("User Id=oraDB;Password=ora;Data Source=CCT"))
     {
            <<(1st method)>>
            string query = "SELECT idcard from CallCardTable where idcard= :pCallCardNo";                

            <<(2nd method)>>
            string query = "SELECT idcard from CallCardTable where idcard=@CallCardNo";

            OracleCommand cmd = new OracleCommand(query, conn);

            cmd.CommandType = CommandType.Text;
            conn.Open();

            <<(1st method parameter -)>>
            OracleParameter pCallCardNo = new OracleParameter();
            pCallCardNo.Value = CallCardNo;
            cmd.Parameters.Add(pCallCardNo);

            <<(2nd method parameter - )>>
            cmd.Parameters.Add("@CallCardNo", OracleType.VarChar).Value = CallCardNo;

            OracleDataAdapter dA = new OracleDataAdapter(query, conn);
            dA.Fill(ds);

            cmd.Dispose();
            cmd.Parameters.Clear();
            dA.Dispose();
            conn.Close();
        }
        return ds.GetXml();

 }


<<1st exception>>
System.Data.OracleClient.OracleException: ORA-01008: not all variables bound

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

<<2nd exception>>
System.Data.OracleClient.OracleException: ORA-00936: missing expression

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

<<3rd exception after follow the answer from AVD>> System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleParameterBinding.Bind(OciStatementHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection, Boolean& mustRelease, SafeHandle& handleToBind)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

Cory Grimster · Accepted Answer · 2012-07-05 02:09:01Z

This ought to work:

    public string CallCardDetails(string CallCardNo)
    {
        //initialize
        using (DataSet ds = new DataSet())
        {
            //connect
            using (OracleConnection conn = new OracleConnection("User Id=oraDB;Password=ora;Data Source=CCT"))
            {
                // Oracle uses : for parameters, not @
                string query = "SELECT idcard from CallCardTable where idcard= :pCallCardNo";

                // Let the using block dispose of your OracleCommand
                using (OracleCommand cmd = new OracleCommand(query, conn))
                {
                    // Note: be careful with AddWithValue: if there's a mismatch between the .NET datatype of
                    // CallCardNo and the idcard column you could have an issue.  Cast the value you provide
                    // here to whatever is closest to your database type (String for VARCHAR2, DateTime for DATE, Decimal for NUMBER, etc.)
                    cmd.Parameters.AddWithValue(":pCallCardNo", CallCardNo);
                    conn.Open();

                    // Again, wrap disposables in a using or use try/catch/finally (using will dispose of things in case of exceptions too)
                    using (OracleDataAdapter dA = new OracleDataAdapter(cmd))
                    {
                        dA.Fill(ds);

                        return ds.GetXml();
                    }
                }
            }
        }
    }

Edit: Added using block around the DataSet.

KV Prajapati · Accepted Answer · 2012-07-05 04:50:13Z

2

You have to pass command object instead of passing query to the DataAdapter.

Problem is here => OracleDataAdapter dA = new OracleDataAdapter(query, conn);

Try,

using (OracleConnection conn = new OracleConnection("User Id=oraDB;Password=ora;Data Source=CCT"))
 {
      string query = "SELECT idcard from CallCardTable where idcard=:CallCardNo";
      using(OracleCommand cmd = new OracleCommand(query, conn))
       {
            conn.Open();
            cmd.Parameters.Add(":CallCardNo", OracleType.VarChar,20).Value = CallCardNo;
            OracleDataAdapter dA = new OracleDataAdapter(cmd);
            dA.Fill(ds);
        }
  }
  return ds.GetXml();

edited Jul 5, 2012 at 4:50

answered Jul 4, 2012 at 10:32

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

1 Comment

Siti Over a year ago

Thanks AVD for your answer, I have tried it already. I still got the error..kindly please look at code above <<3rd exception>>.thanks..:)

Collectives™ on Stack Overflow

how to query oracle database based on user input using asp.net c#

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related