title: how to query oracle database based on user input using asp.net c#
body: <p>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 :-</p>
  
  <pre><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"))
       {
              &lt;&lt;(1st method)&gt;&gt;
              string query = "SELECT idcard from CallCardTable where idcard= :pCallCardNo";                
  
              &lt;&lt;(2nd method)&gt;&gt;
              string query = "SELECT idcard from CallCardTable where idcard=@CallCardNo";
  
              OracleCommand cmd = new OracleCommand(query, conn);
  
              cmd.CommandType = CommandType.Text;
              conn.Open();
  
              &lt;&lt;(1st method parameter -)&gt;&gt;
              OracleParameter pCallCardNo = new OracleParameter();
              pCallCardNo.Value = CallCardNo;
              cmd.Parameters.Add(pCallCardNo);
  
              &lt;&lt;(2nd method parameter - )&gt;&gt;
              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();
  
   }
  
  
  &lt;&lt;1st exception&gt;&gt;
  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&amp; rowidDescriptor, ArrayList&amp; resultParameterOrdinals)
  at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList&amp; 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)
  
  &lt;&lt;2nd exception&gt;&gt;
  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&amp; rowidDescriptor, ArrayList&amp; resultParameterOrdinals)
  at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList&amp; 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)
  </code></pre>
  
  <p>&lt;&lt;3rd exception after follow the answer from AVD>>
      System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number</p>
  
  <pre><code>at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
  at System.Data.OracleClient.OracleParameterBinding.Bind(OciStatementHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection, Boolean&amp; mustRelease, SafeHandle&amp; handleToBind)
  at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor&amp; rowidDescriptor, ArrayList&amp; resultParameterOrdinals)
  at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList&amp; 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)
  </code></pre>