Solved

closing the connection

Posted on 2006-06-16
8
231 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now