jackal

jackal

  • NA
  • 3
  • 0

Error message : "RollbackTransaction requires an open and available Connection. The connection's current state is Open, Fetching."

Nov 7 2005 3:41 AM

This below is source code  :
public static bool IsExistCustomerID(string customerid,OdbcConnection conn,OdbcTransaction trans)
  {
   bool ret=false;
   string sql="select customerid from customers"+
    " where customerid=?";
   OdbcDataReader reader=null;
   try
   {
    using(OdbcCommand cmd=new OdbcCommand(sql,conn,trans))
    {
     cmd.Parameters.Add("customerid",OdbcType.VarChar).Value=customerid;
     reader=cmd.ExecuteReader();
     if(reader.Read())ret=true;
    }
   }
   finally
   {
    if(reader!=null) reader.Close();
   }
   return ret;
}

public static void Insert2Customers(string custid,string comname,OdbcConnection conn,OdbcTransaction trans)
  {
   string sql="Insert into customers(customerid,companyname)"+
       " values(?,?)";
    using(OdbcCommand cmd=new OdbcCommand(sql,conn,trans))
   {
    cmd.Parameters.Add("custid",OdbcType.VarChar).Value=custid;
    cmd.Parameters.Add("comname",OdbcType.VarChar).Value=comname;
    cmd.ExecuteNonQuery();
   }
}
public static Test(){
try
   {
    conn=new OdbcConnection(con_string);
    conIn.Open();
    ArrayList arr=new ArrayList();
    
    arr.Add("One");
    arr.Add("Two");
    arr.Add("Three");
    if(arr.Count>0)
    {
     trans=conn.BeginTransaction(IsolationLevel.ReadCommitted);
     for(int i=0;i<=arr.Count;i++){
      string customerid=arr[i].ToString();
      ret=IsExistCustomerID(customerid,conn,trans);   //Select statement
      if(ret)         // line 1
      {                // line 2
       if(trans!=null)trans.Rollback();    // line 3
       throw new TrapException("duplicate customer id");  //line 4
      } //line 5
      Insert2Customers(customerid,customerid,conn,trans);
     }
     trans.Commit();
    }
   }
   catch(TrapException e)
   {
    if(trans!=null)trans.Rollback();
    Console.WriteLine("error : {0}",e.Message);
   }
   finally
   {
    if(conn!=null)conn.Close();
    Console.WriteLine();
   }
}
I got error message in line 3 with message :
      "RollbackTransaction requires an open and available Connection. The connection's current state is Open, Fetching."
I don't know what it happen if anybody know about this problem tell me at [email protected]
Thanks for advance
     soundex....