DataAdapter fill causes "Operation is not valid due to the current state of the object" error

I've got a problem with the dataadapter.fill method
 
Everytime I run the code it reports "Operation is not valid due to the current state of the object."
 
Here is the code:
 
OracleConnection cnUnitEvent = new OracleConnection();
String cs = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
OracleCommand cmdUnitEvent = new OracleCommand();
cnUnitEvent.ConnectionString = cs;
cnUnitEvent.Open();
String ct = "SELECT LK.PRELIMESTCOST, U.UNITNAME, LK.EVENTDETDESC ";
ct += "FROM ERFO.EVENTS_UNITSLINK LK JOIN ERFO.EVENTS E ON LK.EVENTID = E.EVENTID ";
ct += "JOIN ERFO.UNITS U ON LK.UNITID = U.UNITID WHERE LK.EventID = " + EventID.ToString();
cmdUnitEvent.CommandText = ct;
cmdUnitEvent.CommandType = CommandType.Text;
OracleDataAdapter daUnitEvents = new OracleDataAdapter();
DataSet ds = new DataSet();
//this.UnitEventDetails
DataTable dt = new DataTable();
daUnitEvents.SelectCommand = cmdUnitEvent;
daUnitEvents.Fill(dt);
ds.Tables.Add(dt);

 
I've also tried doing daUnitEvents.Fill(ds); and got the same error.  Mousing over the connection shows the connection state is open.  So I have no idea about the state of which object the error is referencing.
 
Help would be appreciated
Edward JoellAsked:
Who is Participating?
 
Edward JoellConnect With a Mentor Author Commented:
Right got the same answer from co-worker bout  half hour ago.  and it works.  thanks for responding.  will get back about points shortly
0
 
x77Connect With a Mentor Commented:
You need associate OracleCommand to OracleConnection.

    cmdUnitEvent.Connection = cnUnitEvent;

or

   cmdUnitEvent = cnUnitEvent.CreateCommand;
0
 
Bob LearnedCommented:
.NET data adapters can configure their own connections and commands.

Here is a simplified version to illustrate
string connectionString = ConfigurationManager.ConnectionStrings["MainConnStr"].ConnectionString;
        string commandText = "SELECT LK.PRELIMESTCOST, U.UNITNAME, LK.EVENTDETDESC " +
                    "  FROM ERFO.EVENTS_UNITSLINK LK JOIN ERFO.EVENTS E ON LK.EVENTID = E.EVENTID " +
                    "  JOIN ERFO.UNITS U ON LK.UNITID = U.UNITID WHERE LK.EventID = " + EventID.ToString();
        using (OracleDataAdapter daUnitEvents = new OracleDataAdapter(commandText, connectionString))
        {
            DataTable dt = new DataTable();
            daUnitEvents.Fill(dt);
        }

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.