Solved

closing the connection

Posted on 2006-06-16
8
236 Views
Last Modified: 2010-05-19
private bool insertTaxPaid(SqlConnection adcDb,OleDbConnection payrollDb)
            {
                  
                    
                  System.Data.OleDb.OleDbDataReader dataReader;
                  int eeIdColId,forenameColId,surnameColId,eerefColId,taxColId;
                  bool errorIndicator = false;
                  Random randInt = new Random();
                  SqlParameter[] paramsArray = new SqlParameter[11];
                  SqlParameter[] errorList = new SqlParameter[5];
                  OleDbCommand cmd;
            
                  period = this.ddTaxPeriod.SelectedValue;
                  employerId = this.ddEmployers.SelectedValue;
                 payrollDb =        new OleDbConnection(ConfigurationSettings.AppSettings[employerId]);
                        adcDb = new SqlConnection(ConfigurationSettings.AppSettings["connectLocal"]);

         
                  Compensation compensation = new Compensation();
                  ArrayList allErrors;
                  IEnumerator iEnum;
                  CompensationDB db = new CompensationDB(ConfigurationSettings.AppSettings["connectLocal"]);
                  
                  int i =0;

                  try
                  {
                               payrollDb.Open();
                                    adcDb.Open();
                        if (payrollDb == null) this.lblErrorDisplay.Text = "The payroll file was not found";
                        else
                        {
                  
                              
                        cmd = new OleDbCommand("SELECT EeDetails.EeID, EeDetails.EeRef, Sum(EeRunBals.Tax) AS SumOfTax, EeDetails.Forename, EeDetails.Surname FROM EeDetails INNER JOIN EeRunBals ON EeDetails.EeID = EeRunBals.EeID GROUP BY EeDetails.EeID, EeDetails.EeRef, EeDetails.Forename, EeDetails.Surname",payrollDb);
                        dataReader=cmd.ExecuteReader();      

                              
                              if (dataReader.HasRows)
                              {
                                    
                                    eeIdColId = dataReader.GetOrdinal("EEid");
                                    eerefColId = dataReader.GetOrdinal("EeRef");
                                    forenameColId = dataReader.GetOrdinal("Forename");
                                    surnameColId = dataReader.GetOrdinal("Surname");
                                    taxColId = dataReader.GetOrdinal("SumOfTax");                                    
                                    
                                    
                                    while (dataReader.Read())
                                    {
                                                            
                                                      //check surname forename and description are not numbers
                                                 compensation = new Compensation();
                                                      try
                                                      {
                                                      
      
                                                            if (!dataReader.IsDBNull(eerefColId))
                                                      compensation.PayrollId = Convert.ToString(dataReader.GetValue(eerefColId));
                                                            else

                                                      if (util.isDecimal(Convert.ToString(dataReader.GetValue(taxColId))))
                                                            compensation.Amount = Convert.ToDecimal(dataReader.GetValue(taxColId));
                                                        compensation.Description = "Tax";
                                                            compensation.Firstname = Convert.ToString(dataReader.GetValue(forenameColId));
                                                            compensation.Lastname = Convert.ToString(dataReader.GetValue(surnameColId));
                                                            compensation.Source = "payrolldb";
                                                            compensation.Currency = "GBP";
                                                            compensation.Employer = employerId;
                                                            paramsArray[9] = new SqlParameter("@periodId",period);
                                                            compensation.Period = int.Parse(period);
                                                            if (!db.insertCompensation(compensation,"WriteCompensation"))
                                                            {
                                                                  allErrors = db.ErrorLog;

                                                                                                                        
                                                                  if (allErrors.Count > 0)
                                                                  {
                                                                        errorIndicator = true;
                                                                        iEnum = allErrors.GetEnumerator();
                                                                        while (iEnum.MoveNext())
                                                                        {                                                                              
                                                                              errorList[0] = new SqlParameter();
                                                                              errorList[0].ParameterName = "@returnValue";
                                                                              errorList[0].Direction = ParameterDirection.ReturnValue;                                                                              
                                                                              errorList[1] = new SqlParameter("@employerId",employerId);                                                                              
                                                                              errorList[2] = new SqlParameter("@id",randInt.Next(1,100000));
                                                                              errorList[3] = new SqlParameter("@page","Payroll.aspx");
                                                                              errorList[4] = new SqlParameter("@detail",iEnum.Current.ToString());
                                                                              SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings["connectLocal"],CommandType.StoredProcedure,"spWriteError",errorList);
                                                                              this.lblErrorDisplay.Text = "Errors have been written to the log file.  Click the error link to view.";
                                                                        
                                                                        }
                                                                  }  

                                                            }
                                                            else
                                                                  errorIndicator = true;
                                                            
                                                                  
                                                                                                      
                                                            
                                                      }
                                                      catch(Exception ex)
                                                      {
                                                            Response.Write("Unable to write errors");                  
                                                      }
                                                
                                                                                                                                                                                                                        
                                                                                                
                                    } // end while

                              }            
                              else
                              {
                                    Response.Write("No tax rows found");
                              }                  
                        } // end else  payrolldb not null
                                                      
                  } //end try
                  catch(Exception ex)
                  {
                        this.lblErrorDisplay.Text = ex.Message;
                        Response.Write(ex.Message);
                                    
                              
                  }
                  

                  finally
                        
                  {
                        
                                    
                        if (payrollDb != null)
                              payrollDb = null;
                        adcDb = null;                              
                        
                        
                  } // end finally
                  return errorIndicator;

            }
      
0
Comment
Question by:SirReadAlot
  • 5
  • 3
8 Comments
 

Author Comment

by:SirReadAlot
ID: 16920123
Object reference not set to an instance of an object.
is the error I got when i TRIED TO CLOSE THE connection

payroll.close()
adc.close()

thanks
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 16920213
Then one of them is null...  

Where are you closing them in your original code?  Can you check for null first?

if(payroll != null)
  payroll.Close();
0
 

Author Comment

by:SirReadAlot
ID: 16920303
will do that
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:SirReadAlot
ID: 16920316
I am changing them in the original code,

but by looking at the code,
 where will be the best place to close connection

thanks
0
 
LVL 25

Expert Comment

by:dstanley9
ID: 16920389
In your finally block.  That way it is closed even if an exception is thrown:

               finally
                   
               {
                    if (payrollDb != null)
                         payrollDb.Close();
                    if (adcDb != null)
                         adcDb.Close();
               } // end finally

0
 
LVL 25

Accepted Solution

by:
dstanley9 earned 500 total points
ID: 16920402
Also, this block:

                          payrollDb.Open();
                              adcDb.Open();
                    if (payrollDb == null) this.lblErrorDisplay.Text = "The payroll file was not found";


is pointless as the third line will never execute if payrollDB is null because it will throw an exception.
0
 

Author Comment

by:SirReadAlot
ID: 16920485
will do this
0
 

Author Comment

by:SirReadAlot
ID: 16920503
thanks
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question