OracleCommand.Executenonquery throws 'System.InvalidOperationException'

The following function throws the System.InvalidOperationException:

internal void executeNonQuery(string connectionString, OracleCommand cmd)
    {
        using (OracleConnection conn = new OracleConnection(connectionString))
        {
            using (cmd)
            {
                conn.Open();
                cmd.ExecuteNonQuery(); //here is the error
                conn.Close();
            }
        }
    }

The additional information is:

Operation is not valid due to the current state of the object.

I try to insert a row into a table. Is there another way to do this or to fix this error?

EDIT: I build the query in the binaryManager class with the following methods:

internal object[] binaryInsert(string tblName, string tblQuery, int conStrgID, int cq)
    {
        object[] retValues = new object[3];
        Stream myStream = null ;
        OracleConnection con = null;
        string conString = qm.getConnectionString("ConnectionStringToMyDB"); //is correct

        byte[] data = GetBytes(tblQuery);

        String sql = "INSERT INTO MYTABLES VALUES (NULL, '" + tblName + "', ':tblQueryBlob', " + conStrgID + ", " + cq + ")";


        OracleCommand cmd = new OracleCommand();
        cmd.CommandText = sql;  // Set the sql-command
        cmd.Connection = con;   //con is an OracleConnection

        OracleParameter param = cmd.Parameters.Add("tblQueryBlob", OracleDbType.Blob); //Add the parameter for the blobcolumn
        param.Direction = ParameterDirection.Input;

        param.Value = data;     //Asign the Byte Array to the parameter

        //command containts the parameter :tblQueryBlob with its value
        retValues[0] = cmd;
        retValues[1] = conString;

        return retValues;
    }

private byte[] GetBytes(string str)
    {
        byte[] bytes = new byte[str.Length * sizeof(char)];
        System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
        return bytes;
    }

I call the binaryInsert method from another class with the following code:

BinaryManager bm = new bBinaryManager();
string sqlQuery = "large string with 5100 characters";
object[] binaryValues = bm.binaryInsert("TextTbl", sqlQuery, 1, 1);
string conString = binaryValues[1].ToString();
OracleCommand cmd = (OracleCommand)binaryValues[0];

QueryManager qm = new QueryManager();
qm.executeNonQuery(conString, cmd);

via Chebli Mohamed

What's the basis to choose SQL over HQL in hibernate for some queries?

I am using hibernate in my project and I have always used HQL. However, I have seen in other projects where at times SQL queries are used by createSqlQueries, rather than HQL. I wanted to know, what could be the deciding factor to choose SQL over HQL in certain scenarios. Also if there are some queries which can't be performed by HQL and we need to choose only SQL, please cite example.

via Chebli Mohamed

Changing the dataset on a server executed SSRS Report

I'm trying to narrow down the results returned from a server generated SSRS report, but the customer is requesting too many fields to do be able to do it easily with parameters into a predefined SQL statement.

Is it possible to pass a statement into the reporting server from .NET that the server will execute as its datasource, instead of the preconfigured one? Either the complete statement or the WHERE clause would be fine.

If not, is it possible to eval a parameter sent into a stored procedure? I'm aware of the security implications.

via Chebli Mohamed

get latest row from foreign key table in left join in sql

i have a table with multiple columns ,and there are 5 more tables ,which have reference foreign key relation .in one table we have more than 5 columns for one ref. but I want only latest one ..can you please tell me how can I take it by single query without using temp ...

via Chebli Mohamed

What are the pitfalls of using a separate SSIS package for centralized error management?

We have a few loosely coupled SSIS packages that are in charge of batch integration. When they have an error (validation issue with data, or an actual OnError error) then they all do the same thing, they email a message to a distribution list. The content of the message varies, and sometimes other people need to be cc'd on the message. But it is basically the same process for everything.

I am thinking of creating a single ErrorHandler package that has a few parameters (error message, cc address, subject line etc) and just getting the parent packages to run an Execute Package Step when they need to send an error message.

The way I see it, we then have one single SSIS package that allows us to manage what we do with the incoming errors. If we decide we want to write stuff into a log file, or call a web service, it only has to be changed in the one place.

Limited testing so far looks fine. Am I missing something obvious here? Why doesn't everybody do this? Is there a transactional or cascading issue that could be a problem?

via Chebli Mohamed

How to copy data in identity column?

I have a table with an identity column in a server and have a other table with same structure in another server.. Now I want to copy all data from one table to other table but I can't help it...

I have already created a linked server..

I use this:

insert into [server].[database].[dbo].[table1]
    select *
    from table2

I also use this query without identity column in the place of *

insert into [server].[database].[dbo].[table1]
   select column1, column2
   from table2

What should I do ?

via Chebli Mohamed

Is it mandatory to use addScalar() in createSqlQuery() in hibernate? Why do we need to specify the Data type to hibernate while executing SQL queries?

String sql = "select Band.band_id bandId from guest_band Band";
   sessionFactory.getCurrentSession().createSQLQuery(sql)
    .addScalar("bandId", Hibernate.LONG)
    .list();

I got to know that addScalar() is used to state hibernate the DataType of the selected item, bandId in this case. But my question is, why do we need to specify the type to hibernate? What does it internally perform? Secondly is it an exception if we don't addScalar()? Lastly, is there any alternate way how this can be achieved?

via Chebli Mohamed