ASP.NET runtime error: Cannot find table 0.

[runmd]

I am working on some VB code to connect to an Oracle stored procedure and retrieve data. The stored procedure takes a string as input, constructs a SQL query into a string and uses a cursor to query and pull four fields of data. I have the four fields as an OUT parameter that I would like to store in a as part of a DataSet. I will be using at least one more query to store in this DataSet in the near future. So, it's one query at a time. I get the following error during run-time.

System.IndexOutOfRangeException: Cannot find table 0.

I know what the error means, I have an empty table and that I am not returning any data from the stored procedure. I have tested this procedure and know it works but maybe it's something in the VB code when setting up the pipe. I really don't know what it is. Is there an easy way to debug an oracle stored procedure in Visual Studio 2008 during run-time? The code is below.

 '// Create the connection string
Dim connectionString As String = GetConnectionString()
'// Create the connection
Dim connection As New OracleConnection(connectionString)

'// define the command for the stored procedure
Dim command As New OracleCommand()
command.Connection = connection
command.CommandText = "TRIBES_COUNTY_PKG.GetTribeInfo"

Dim strParam As String
strParam = "91 OR COUNTY_ID = 92)"

'// add the parameters including the two REF CURSOR types to retrieve
'// the two result sets
command.Parameters.Add("qryParam", OracleType.VarChar).Value = strParam
command.Parameters.Add("tribalID", OracleType.Number).Direction = ParameterDirection.Output
command.Parameters.Add("tribalName", OracleType.VarChar, 250).Direction = ParameterDirection.Output
command.Parameters.Add("countyName", OracleType.VarChar, 50).Direction = ParameterDirection.Output
command.Parameters.Add("stateName", OracleType.VarChar, 50).Direction = ParameterDirection.Output
command.CommandType = CommandType.StoredProcedure

'// create the DataAdapter and map tables
Dim adapter As System.Data.OracleClient.OracleDataAdapter
adapter = New System.Data.OracleClient.OracleDataAdapter(command)
adapter.TableMappings.Add("Table", "COUNTIES")
'adapter.TableMappings.Add("Table1", "TRIBES")  'This will be for the 2nd query

'// create and fill the DataSet
Dim dataSet As New DataSet
adapter.Fill(dataSet)

'// Convert DataSet to DataTable
Dim dataTable As New DataTable
'// This If blocks checks if there are any tables, which we know there are none
'If dataSet.Tables.Count > 0 Then
dataTable = dataSet.Tables(0)  'error here
'End If

This is the stored procedure.

-- TRIBES_COUNTY_PKG PACKAGE HEADER
CREATE OR REPLACE PACKAGE TRIBES_COUNTY_PKG AS
  PROCEDURE GetTribeInfo (qryParam IN VARCHAR2,
                          tribalID OUT TRIBE.TRIBAL_ID%TYPE,
                          tribalName OUT TRIBE.TRIBAL_NAME%TYPE,
                          countyName OUT STATE_COUNTY.COUNTY_NAME%TYPE,
                          stateName OUT STATE.STATE_NAME%TYPE);
END TRIBES_COUNTY_PKG;
/

-- TRIBES_COUNTY_PKG PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY TRIBES_COUNTY_PKG AS
  PROCEDURE GetTribeInfo (qryParam IN VARCHAR2,
                          tribalID OUT TRIBE.TRIBAL_ID%TYPE,
                          tribalName OUT TRIBE.TRIBAL_NAME%TYPE,
                          countyName OUT STATE_COUNTY.COUNTY_NAME%TYPE,
                          stateName OUT STATE.STATE_NAME%TYPE)
  IS
    --DECLARE variables here
    curCounties sys_refcursor;  /* cursor variable declaration */
    --curTribes   sys_refcursor;
    query       varchar2(2000);
  BEGIN
    
    query := 'SELECT TRIBAL_ID, TRIBAL_NAME, COUNTY_NAME, STATE_NAME
              FROM TRIBE_COUNTY_VW
              WHERE (COUNTY_ID =' || qryParam ||
              'ORDER BY TRIBAL_NAME';
    -- return all Tribes for Counties
    OPEN curCounties FOR query;
    LOOP
      FETCH curCounties INTO tribalID, tribalName, countyName, stateName;
      EXIT WHEN curCounties%NOTFOUND;
    END LOOP;
    CLOSE curCounties;

    -- return all Tribes Information records
    --OPEN curTribes FOR
    --SELECT *
    --  FROM TRIBE_CONTACT_VW;
      
  END GetTribeInfo;
END TRIBES_COUNTY_PKG;
/

So, is there something that is obvious that I am missing?

 

[jmeckley]

you are trying to load a data table with a non-queryable query. when you call fill it's expecting the ado.net command to return a record set.

your stored proc doesn't return anything. it simply sets output parameters. you need to execute a nonquery, then get the values of the parameters, create a data table, all the values of the parameters to the data table and then add the table to the dataset.

maybe, rather than using output parameters you use multiple selects. within the command object. here is pseudo code

command.commandtext = @"
select * from table1 where...
select * from table2 where...
select * from table3 where...
";
var dataset = new DataSet();
new adapter(command).Fill(dataset);
return dataset;

this will load each result set into a table within the dataset. it will define the the data table schema based on the results of each record set.

also, remember to properly dispose of your objects, either with the using keyword or try finally blocks.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[runmd]

jmeckley,

I took some time to think about my problem and tackle it a slightly different way. I used two REF CURSORs in my test procedure prior to the one I posted here. I got frustrated and came up with the one above. I decided to go back to using those two REF CURSORS as output with two input strings containing the SQL language. I construct the SQL statements in VB, pass them to the stored procedure, use two REF CURSORS to open the query and pass them as output parameters. So, now both queries are working superbly and passing data.

-- TRIBES_COUNTY_PKG PACKAGE HEADER
CREATE OR REPLACE PACKAGE TRIBES_COUNTY_PKG AS
  TYPE T_CURSOR IS REF CURSOR;
  PROCEDURE GetTribeInfo (strQuery         IN VARCHAR2,
			              strQuery2        IN VARCHAR2,
                          curTribeCounties OUT T_CURSOR,
                          curTribeInfo     OUT T_CURSOR);
END TRIBES_COUNTY_PKG;
/

-- TRIBES_COUNTY_PKG PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY TRIBES_COUNTY_PKG AS
  PROCEDURE GetTribeInfo (strQuery          IN VARCHAR2,
			              strQuery2         IN VARCHAR2,
                          curTribeCounties  OUT T_CURSOR,
                          curTribeInfo      OUT T_CURSOR)
  IS
    --DECLARE variables here--
  BEGIN
              
    -- return all Tribes for selected Counties
    OPEN curTribeCounties FOR strQuery;

    -- return all Tribes Information records
    OPEN curTribeInfo FOR strQuery2;    
      
  END GetTribeInfo;
END TRIBES_COUNTY_PKG;
/

For my VB code, I have two DataTables being stored in a DataSet and created a relationship between the two using a common field, TRIBAL_ID. Now, I am setting the DataSource of a heirarchical datagrid (custom control called HierarGrid,

http://aspalliance.com/articleViewer.aspx?aId=21&pId=).

There is a template file (*.ascx) that is used as the expand/contract portion of each record in the datagrid control and contains Labels where the data will be binded to. You can see that from the example in the URL that I posted. I have a new problem but will start a new thread since it's out of the scope of what my previous issue was.