Problem with empty result from storeprocedure

Trixz · Post by **Trixz** » Wed 02 Jun 2010 11:15

Hi

I have a WinForm application with a OracleDataTable (called dt from now on). The dt is configured to call a storeprocedure and when a button is pressed i do the followin

Code: Select all

dt.Active = false;
dt.SelectCommand.Parameters["InParam"].Value = editBox.Text;
dt.Select();
dt.FetchAll = true;
dt.Active = true;

myGrid.DataSource = dt;

This works fine, as long as the store procedure returns any rows. When the sp returns a cursor with only columnheaders (no rows) the dt still have the same row as before the command.

I've tried dt.Clear() and dt.Rows.Clear()
dt.Clear() resultet in a errormessage saying that the datareader was closed when doing the 3rd call to datatable.
dt.Rows.Clear() cleared the rows, but when the active = true was set the old result was loaded back into the dt.

It seems like the problem lies in that the dt has a datasource behind and when the storeprocedure returns en empty result, the datasource is not cleared and refilled.

What I'm I doing wrong?

Shalex · Post by **Shalex** » Wed 02 Jun 2010 15:12

Setting the Active property to false does not clear the data, but frees the resources allocated on the server, if any. Please try the following code:

Code: Select all

dt.Clear();
dt.SelectCommand.Parameters["p1"].Value = editBox.Text;         
dt.Active = true;
myGrid.DataSource = dt;

If the problem persists, please specify your current version (x.xx.xxx) of dotConnect for Oracle (the Tools>Oracle>About menu of VS) and the exact Grid control you are using (is it System.Windows.Forms.DataGridView?).

Trixz · Post by **Trixz** » Wed 02 Jun 2010 15:41

I made an example project....
DevArt version 5.0.40.0

I run the project and clear does not work.
The first time I press the button and set the selectparameter, I get the result.
Second, also a result (it work even if it's empty, thats a progress)
But the third time I always get this exception.

Code: Select all

An unhandled exception of type 'System.InvalidOperationException' occurred in Devart.Data.dll

Additional information: Invalid attempt to GetSchemaTable when reader is closed.

I use the oracledatatable control from toolbar to the form and configure it.
Here is some code generated

Code: Select all

            // 
            // oracleDataTable1
            // 
            this.oracleDataTable1.Columns.AddRange(new System.Data.DataColumn[] {
            this.oracleDataTable1_LANDKOD,
            this.oracleDataTable1_LAND_BENAMNING,
            this.oracleDataTable1_ORDERNR,
            this.oracleDataTable1_ORDERFAKTURASTATUS_ID,
            this.oracleDataTable1_ORDERFAKTURASTATUS_BENAMNING,
            this.oracleDataTable1_ORDERLEVERANSSTATUS_ID,
            this.oracleDataTable1_ORDERLEVERANSSTATUS_BENAMNING,
            this.oracleDataTable1_VARREFERENS,
            this.oracleDataTable1_BETALNINGSVILLKOR_ID,
            this.oracleDataTable1_BETALNINGSVILLKOR_VILLKOR,
            this.oracleDataTable1_LAGERSTALLE_ID,
            this.oracleDataTable1_LAGERSTALLE_BENAMNING,
            this.oracleDataTable1_FRAKT,
            this.oracleDataTable1_ANTAL_SLUTKUNDER,
            this.oracleDataTable1_TOTAL_VOLYM,
            this.oracleDataTable1_TOTAL_VIKT,
            this.oracleDataTable1_CHECKPREMIEVAL,
            this.oracleDataTable1_ORDERVARDEFP,
            this.oracleDataTable1_PROVISION,
            this.oracleDataTable1_ORDERVARDE,
            this.oracleDataTable1_BIDRAG,
            this.oracleDataTable1_BUNTNR,
            this.oracleDataTable1_BUNTNRSMART,
            this.oracleDataTable1_PRIORITET_ID,
            this.oracleDataTable1_PRIORITET_BENAMNING,
            this.oracleDataTable1_ORDERTYP_ID,
            this.oracleDataTable1_ORDERTYP_BENAMNING,
            this.oracleDataTable1_ORDERDATUM,
            this.oracleDataTable1_ORDER_PLATS});
            this.oracleDataTable1.Connection = this.oracleConnection1;
            this.oracleDataTable1.Name = "oracleDataTable1";
            this.oracleDataTable1.SelectCommand = this.oracleSelectCommand1;
            this.oracleDataTable1.TableName = "oracleDataTable1";
            this.oracleDataTable1.Owner = this;
            this.oracleDataTable1.TableMapping.DataSetTable = "oracleDataTable1";
            this.oracleDataTable1.TableMapping.SourceTable = "oracleDataTable1";


            // 
            // oracleSelectCommand1
            // 
            this.oracleSelectCommand1.CommandType = System.Data.CommandType.StoredProcedure;
            this.oracleSelectCommand1.CommandText = "jfp.jfp_forms.orderinfo_getorders";
            this.oracleSelectCommand1.Connection = this.oracleConnection1;
            this.oracleSelectCommand1.Name = "oracleSelectCommand1";
            this.oracleSelectCommand1.Parameters.Add(new Devart.Data.Oracle.OracleParameter("IN_LANDKOD", Devart.Data.Oracle.OracleDbType.VarChar, 2, System.Data.ParameterDirection.Input, false, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, "SE"));
            this.oracleSelectCommand1.Parameters.Add(new Devart.Data.Oracle.OracleParameter("IN_KUNDNR", Devart.Data.Oracle.OracleDbType.Number, 0, System.Data.ParameterDirection.Input, false, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, null));
            this.oracleSelectCommand1.Parameters.Add(new Devart.Data.Oracle.OracleParameter("OUT_C", Devart.Data.Oracle.OracleDbType.Cursor, 0, System.Data.ParameterDirection.Output, false, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, null));
            this.oracleSelectCommand1.TableValuedResultType = null;
            this.oracleSelectCommand1.Owner = this;

Code: Select all

            // 
            // oracleDataTable1_LANDKOD
            // 
            this.oracleDataTable1_LANDKOD.AllowDBNull = false;
            this.oracleDataTable1_LANDKOD.ColumnName = "LANDKOD";
            // 
            // oracleDataTable1_LAND_BENAMNING
            // 
            this.oracleDataTable1_LAND_BENAMNING.ColumnName = "LAND_BENAMNING";
            // 
            // oracleDataTable1_ORDERNR
            // 
            this.oracleDataTable1_ORDERNR.AllowDBNull = false;
            this.oracleDataTable1_ORDERNR.ColumnName = "ORDERNR";
            this.oracleDataTable1_ORDERNR.DataType = typeof(double);
            // 
            // oracleDataTable1_ORDERFAKTURASTATUS_ID
            // 
            this.oracleDataTable1_ORDERFAKTURASTATUS_ID.ColumnName = "ORDERFAKTURASTATUS_ID";
            this.oracleDataTable1_ORDERFAKTURASTATUS_ID.DataType = typeof(double);
            // 
            // oracleDataTable1_ORDERFAKTURASTATUS_BENAMNING
            // 
            this.oracleDataTable1_ORDERFAKTURASTATUS_BENAMNING.ColumnName = "ORDERFAKTURASTATUS_BENAMNING";

...

Trixz · Post by **Trixz** » Wed 02 Jun 2010 15:43

Version update: 5.35.79.0

I used the devexpress control originaly, but sample made with standard System.Window.Forms.DataGridView

Shalex · Post by **Shalex** » Thu 03 Jun 2010 11:15

We have reproduced the "Invalid attempt to GetSchemaTable when reader is closed." error with the latest (5.70.140) build of dotConnect for Oracle. It is fixed. Look forward to the next build. I will post here when it is available for download. As a temporary workaround, please use the Close() method. This method disconnects DbDataTable from the data source and frees server resources allocated for the query.

Code: Select all

private void button1_Click(object sender, EventArgs e) {
    //dt.Clear();
    dt.Close();
    //the next line overcomes the issue when DataGridView tracks the table state NOT correctly
    myGrid.DataSource = null;
    dt.SelectCommand.Parameters["p1"].Value = editBox.Text;
    dt.Active = true;
    myGrid.DataSource = dt; 
}

Trixz · Post by **Trixz** » Fri 18 Jun 2010 14:21

This fixed the schema problem... but there is another problem that may be related, so I post in in same thread.

I have a storeprocedure. When I call it with parameter "A" set, (B and C is empty) it returns a resultset with 1 row.
I change parameter "A" to a new value, another row is returned.

I clear parameter "A" and set "B". Tests of the storeprocedure returns 20 rows, but the datatable holds the result from "A" still.

Did the other way around. Started with "B", 20 rows returned. Changed "B", new 15 rows displayed. Tested to clear the "B" parameter, and set the "A", the single row was displayed. Tried to change back, datatable only has the "A".

As you probably can figure out, A is a primary key and always returns 1 row, B is for example a name.
I figured that this may have something to do with the earlier problem and the generated schema.

Between all searches I do a

Code: Select all

dtSearchAgent.Close();
gridAgentList.DataSource = null;
dtSearchAgent.SelectCommand.Parameters["IN_KUNDNR"].Value = txtSearchAgent.Text;
dtSearchAgent.SelectCommand.Parameters["In_SaljareFornamn"].Value = txtSearchFirstname.Text;
dtSearchAgent.Active = true;
gcAgentList.DataSource = dtSearchAgent;

When doing test against the storeprocedure from pl/sql it works fine.
the storeprocedure sends a ref_cursor out.

Shalex · Post by **Shalex** » Tue 22 Jun 2010 11:59

Thank you for your report. We have reproduced the problem.

Shalex · Post by **Shalex** » Wed 23 Jun 2010 09:59

The problem is fixed. Look forward to the next build. As a temporary workaround, please use the following code:

Code: Select all

dtSearchAgent.Close();
gridAgentList.DataSource = null;
//assuming that KUNDNR is your primary key
dtSearchAgent.SelectCommand.Parameters["IN_KUNDNR"].Value = (txtSearchAgent.Text == "") ? null : txtSearchAgent.Text;
dtSearchAgent.SelectCommand.Parameters["In_SaljareFornamn"].Value = txtSearchFirstname.Text;
dtSearchAgent.Active = true;
gcAgentList.DataSource = dtSearchAgent;

Shalex · Post by **Shalex** » Wed 07 Jul 2010 17:53

New build of dotConnect for Oracle 5.70.146 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=18424 .