Solved

closing the connection

Posted on 2006-06-16
8
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
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.

739 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