asked on

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

SOLUTION

x77

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bob Learned

.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

ASKER CERTIFIED SOLUTION

Edward Joell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DataAdapter fill causes &quot;Operation is not valid due to the current state of the object&quot; error

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