none
"Connection must be valid and open" RRS feed

  • Question

  • Hi,

         I am using DataReader to return the records  from Mysql database. When i using the cast operation in sqlquery, it gives the exception "Connection must be valid and open" and exits, but for all other queries except cast it works fine.


     if (DataReader.HasRows)
                    {
                        while (DataReader.Read())
                        {
                        StrRetMessage =Convert.ToString(DataReader.GetValue(0));
                        }
                    }
    when execute the while condition, first time StrRetMessage gets the value, but at the next time get the exceptional error.


    Thanks,
    Sujith
    Friday, February 22, 2008 5:07 AM

All replies

  •  

    Moved from the SQL Server Integration Services forum to somewhere I hope is more appropriate.

     

    It might also be helpful if you posted the section of code that deals with creating and opening the connection, as the message indicates to me that this is the problem area.

     

    Have you confirmed that the connection is open?

    Can you confirm the line that the exception is raised?

    Can you highlight what you mean by “using the cast operation in sqlquery”? The only cast I can see is the use of the ConvertTo function, but the connection must already be open for you to interrogate the HasRows property or the Read method.

     

    Friday, February 22, 2008 8:42 AM
  • Hi,
        the connection is open and the line where i get the error is :
     while (DataReader.Read())
                        {
                        StrRetMessage =Convert.ToString(DataReader.GetValue(0));
                        }
                        funretStatus = true;
               }
    The sqlquery i have used is :"Select max(cast(fPCode as UNSIGNED ))+1 as fPCode from tblparameter where fPType='CAT'". Actually the fPCode is of varchar type.
    This sqlquery is work fine in Mysql databse.

    The following are the code i am using in the program, and error occurs at- public String funRetSingleRecord(string strQuery) procedure.

     public void open_Con()
            {
                ConString = "userid=root;pwd=;data source=localhost; database=dbpainayilC#";
                objMycon = new MySqlConnection(ConString);
                objMycon.Open();
            }

            public void close_Con()
            {
                objMycon.Close();
                objMycon.Dispose();
            }


    public String funRetSingleRecord(string strQuery)
            {
              try
                {
                    funretStatus = true;
                    open_Con();
                    objMycommand = new MySqlCommand(strQuery, objMycon);
                    DataReader = objMycommand.ExecuteReader();
                    if (DataReader.HasRows)
                    {
                        while (DataReader.Read())
                        {
                        StrRetMessage =Convert.ToString(DataReader.GetValue(0));
                        }
                        funretStatus = true;
                    }
                    else
                    {
                        funretStatus=false;
                        StrRetMessage = "";
                    }
                    DataReader.Close();
                }
                catch (Exception ex)
                {
                   
                    funretStatus = false;
                    StrRetMessage = ex.Message;
                }
                finally
                {
                    close_Con();
                }
                return StrRetMessage;
            }


    Thanks,
    Sujith.
    Friday, February 22, 2008 9:36 AM
  • Sujith.

    Sorry but i am 99% sure the exception is not thrown here

    StrRetMessage =Convert.ToString(DataReader.GetValue(0));

     

    It should be in


    DataReader = objMycommand.ExecuteReader(); this statement

     

    Could you modify the code like this and see what you are getting.

     

    open_Con();

    if( objMycon.State == ConnectionState.Open)

    {
     objMycommand = new MySqlCommand(strQuery, objMycon);
                    DataReader = objMycommand.ExecuteReader();
                    if (DataReader.HasRows)
                    {
                        while (DataReader.Read())
                        {
                        StrRetMessage =Convert.ToString(DataReader.GetValue(0));
                        }
                        funretStatus = true;
                    }
                    else
                    {
                        funretStatus=false;
                        StrRetMessage = "";
                    }
                    DataReader.Close();
                }

    }

    else

    {

    Console.Writeline("No Open connection available");

    }

     

     

    Laddie

    Friday, February 22, 2008 12:53 PM
  • Hi,

        The same error get even after that modification. Connection state is still open after calling that open_con().



    Thnks,
    Sujith
    Monday, February 25, 2008 8:49 AM