SQLDataAdapter.Fill error

Question

I am getting an error when used below code. This is not happening on all the records. But at least 10 times out of 5000 transactions I am getting below error. I cross checked the data in the database and data is good but I am not sure why I am getting this error. Request you to help me showing proper path of code implementation.


Service Method:


public List GetStockPIN(string sInput)
{
    List listStockInfo = new List();

    try
    {

    DEBUG("Info from GetStockPIN : ", sInput);
    // Parse request string 
    Parseparams(sInput);
    string sTerminalId = GetValue("TerminalId");
    string sMerchantId = GetValue("MerchantId");
    string sBankCode= GetValue("BankCode");
    string sAppId = GetValue("AppId");
    string sProductName = GetValue("ProductName");
    string sDenomName = GetValue("DenomName");
    string sQTY = GetValue("QTY");

    StockRepository objStockRepository = new StockRepository(sConString) { DenomName = sDenomName, ProductName = sProductName, TerminalId = sTerminalId, Qty = Convert.ToInt32(sQTY) };
    listStockInfo = objStockRepository.GetStockPIN();
    return listStockInfo;
    }
    catch (Exception ex)
    {
    DEBUG("Error from GetStockPIN : ", ex);
    return listStockInfo;
    }
}



Database Access Method:


public List  GetStockPIN()
       {
           DataTable dtrec = new DataTable();
           List objStockInfo = new List();
           SqlConnection conn = null;
           try
           {
               using (conn = new SqlConnection(sConnectionString))
               {
                   using (SqlCommand cmd = conn.CreateCommand())
                   {


                       cmd.CommandType = CommandType.StoredProcedure;
                       cmd.CommandText = "prc_GetStockPIN";
                       cmd.Parameters.Add(new SqlParameter("@TerminalId", SqlDbType.NVarChar));
                       cmd.Parameters["@TerminalId"].Value = TerminalId;

                       cmd.Parameters.Add(new SqlParameter("@ProductName", SqlDbType.VarChar));
                       cmd.Parameters["@ProductName"].Value = ProductName;

                       cmd.Parameters.Add(new SqlParameter("@DenomName", SqlDbType.VarChar));
                       cmd.Parameters["@DenomName"].Value = DenomName;

                       cmd.Parameters.Add(new SqlParameter("@Qty", SqlDbType.Int));
                       cmd.Parameters["@Qty"].Value = Qty;

                       conn.Open();
                       using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                       {
                          da.Fill(dtrec);                          
                       }

               //Some additional code that generates List

                   }
               }
           }
           catch (Exception error)
           {
               throw error;
           }
           finally
           {
               if (conn != null)
               {
                   try
                   {
                       conn.Close();
                   }
                   catch (Exception ex)
                   {
                   }
               }
           }
           return objStockInfo;
       }
    }

Here is the exception output:

 DEBUG  : Error from GetStockPIN :  
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.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(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at TAMSBulkPIN.DAL.StockRepository.GetStockPIN()
   at TAMSBulkPIN.BulkPINSVC.GetStockPIN(String sInput)

Could you also provide the actual error message you are receiving, as the stack trace that you have provided does not actually show this. — Martin
@MartinParkin I am providing Exception details that I am getting using the code that I have. — Sudhakar Chavali

Chirag Sutariya Chirag Sutariya · Accepted Answer · 2014-02-25T09:32:22

pass connection name to dataadapter object

using (SqlDataAdapter da = new SqlDataAdapter(cmd,/* your connection object*/))
{
   try
   {
      da.Fill(dtrec);
   }
}

SQLDataAdapter.Fill error

3 Answers