NO error, but I got only part of what I want to get.

NO error, but I got only part of what I want to get.

• J. Clark

  you are right, thks.

  now I got an error:

  System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at database.DBClass.connDb() at database.DBClass.exeSql() at calendar.calendar.InsertAMonth(Int32 x)

  How to handle this
  

  
  
  

• Johnny606

  You could actually be getting an exception within your InsertAMonth method, but not be aware of it because you have coded it to catch and store the detail in _errorInfo, but because you are not re-throwing the exception it will never get up to InsertAYear and hence cause the program to terminate with false. Try writing out the value of _errorInfo for each attempted call of InsertAMonth or change the error handler in InsertAMonth to be:
  
  catch (System.Exception E)
  {
  throw new Exception("Error in InsertAMonth", E);
  //_errorInfo = E.ToString();
  }
  
  
  

  
  

• Indigox3

  You don't need the loop and switch statement. You should replace the entire construct with:
  
   InsertAMonth(31)
   InsertAMonth(28)
   InsertAMonth(31)
   InsertAMonth(30)
   InsertAMonth(31)
   InsertAMonth(30)
   InsertAMonth(31)
   InsertAMonth(31)
   InsertAMonth(30)
   InsertAMonth(31)
   InsertAMonth(30)
   InsertAMonth(31)
  
  But I don't know why you're only getting part of the output you expect...
  

• MikeMalone

  You are right, the for and select case loop is not necessary.

  But my problem is that I can get only 3 or 4 months' content in the MSSQL table. What is the problem

  
  
  

• Neal Hudson

  This problem is solved now.

  exeSql method of DBClass resulted in the error, because it opened a new connection to DB each time the SQL command was executed but the connection was not properly closed, thus ate up all the available DB connection.

  Now I have changed the code, it works smoothly now.

  Thank you all who has helped me in this problem in various ways. All of your generous help has done me much good.

  Thank you all again!

  
  
  

• brad0999

  That won't work because you are calling return in your catch block. What you could do is have a bool indicated success and set it to false in you catch block, or to true at the end of the try, then return it outside of the try, catch, finally. i.e
  
  bool isSuccessful;
  
  try
  {
      .... do stuff ...
      isSuccessful = true;
  }
  catch (Exception e)
  {
      .... log error info ...
      isSuccessful = false;
  }
  finally
  {
      ... clean up stuff ...
  }
  
  return isSuccessful;

  Better still, get your DBClass to implement IDisposable, making sure the necessary cleanup code is in its Dispose method, and then wrap you InsertAMonth logic in a using() clause..... down side to that being that you won't explicitly return a flag indicating success, you'll have to infer that based on the fact you had an exception raised from the method - but that shouldn't ruin your day :-)
  
  using (DBClass db = new DBClass())
  {
      .... logic to insert a month ...
      db.clear();
  }

  
  

  
  

• MMMalik

  Thanks a lot.

  I already changed InsertAMonth code as this, but the error still exists:

  private bool InsertAMonth(int x)
   {
    DBClass db = new DBClass();
    try
    {
     db.ConnStr = ConfigurationSettings.AppSettings["cstr_calendar"];
     for (int i = 1; i<=x; i++)
     {
      _ri = i;   
      db.Sql = "insert into solar (nian,yue,ri,xingqi) values ('"+_nian+"','"+_yue+"','"+_ri+"','"+_xingqi+"')";
      db.exeSql();
      if (_xingqi < 7)
       {_xingqi += 1;}
      else
       {_xingqi = 1;}
     }
     return true;
    }
    catch (System.Exception E)
    {
     _errorInfo = E.ToString();
     return false;
    }
    finally
    {
     db.clear();   //  Here I put db.clear in finally bracket, so it will surely be executed, right
     db = null;
    }
   }

   

  database.dll is as follows:

  using System;
  using System.Data;
  using System.Data.SqlClient;

  namespace database
  {
   public class DBClass
   {
    private SqlConnection conn;
    private SqlCommand comm;
    public SqlDataReader dr;
    public DataSet ds;
    public SqlDataAdapter dad;
    public DataView dv;
    private string sql;
    private string connStr;
    private string errInfo;
    
    public void database()
    {
     errInfo = "";
    }
    
    public string ErrInfo
    {
     get {return errInfo;}
    }
    public string Sql
    {
     get {return sql;}
     set {sql=value;}
    }
    public string ConnStr
    {
     get {return connStr;}
     set {connStr=value;}
    }
    
    private void connDb()
    {
     conn = new SqlConnection(connStr);
     try
     {
      conn.Open();
     }
     catch(SqlException e)
     {
      for(int i=0;i<e.Errors.Count;i++)
      {
       errInfo+="Error Sequential Number:" +i+ "\n" + "Error Info:" +e.Errors.Message+ "\n" + "Error Source:" +e.Errors.Source+ "\n" + "Program:" +e.Errors.Procedure;
      }
      conn.Close();
     }
     
    }
    
    public void dataView()
    {
     connDb();
     dad = new SqlDataAdapter(sql,conn);
     ds = new DataSet();
     dad.Fill(ds,"article");
     dv = new DataView(ds.Tables["article"]);
    }
    
    public void readerData()
    {
     connDb();
     comm =  new SqlCommand(sql,conn);
     dr = comm.ExecuteReader();
    }
    
    public void exeSql()
    {
     connDb();
     comm = new SqlCommand(sql,conn);
     comm.ExecuteNonQuery();
    }
    
    public void clear()  // clear method should be able to ensure a connection be closed once InsertAMonth is completed
    {
     conn.Close();
     comm = null;
     dr = null;
     ds = null;
     dad = null;
     dv = null;  
    }
   }

  }

  
  
  

• looplocal

  Sounds like you not explicitly closing your SQLConnection.... when it goes out of scope it does not close the connection, and hence after a while you are hitting the max pool size (you are pulling a new one from the pool each time you are insertng a month) and running out of connections. You can do this either by calling Close() - the preferred method - or Dispose() on the SQLConnection object that it appears you have wrapped with your DBClass.
  
• NO error, but I got only part of what I want to get.