C# windows application, cannot find table 0

Question

2.00/5 (1 vote)

See more:

I am trying to get data from the SQL database with stored procedure using dataset and populate that data in combobox. It gives error as cannot find table 0.

C#

SqlConnection conn = new SqlConnection();
                    conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
                    SqlCommand cmd2 = new SqlCommand("GetBatch", conn);
                    cmd2.CommandType = CommandType.StoredProcedure;
                    cmd2.CommandText = "usp_CAMR_GetBatch_Status";
                    cmd2.Connection = conn;
                    cmd2.Parameters.AddWithValue("@batch_name", comboBox1.Text.ToString().Substring(0, 12));
                    cmd2.Parameters.AddWithValue("@batch_status_flg", "");
                    cmd2.Parameters.AddWithValue("@BATCH_STATUS_DT", "");
                    cmd2.Parameters.AddWithValue("@BEGIN_SFN", "");
                    cmd2.Parameters.AddWithValue("@END_SFN", "");
                    cmd2.Parameters.AddWithValue("@TOTAL_SFN", "");
                    cmd2.Parameters.AddWithValue("@TOTAL_AMEND", "");
                    cmd2.Parameters.AddWithValue("@TOTAL_PAGES", "");
                    cmd2.Parameters.AddWithValue("@lst_mod_userid", "");
                    cmd2.Parameters.AddWithValue("@lst_mod_dt","");
                    cmd2.Parameters.AddWithValue("@ERR_CODE", 0);
                    cmd2.Parameters.AddWithValue("@ERR_MSG", 0);
                    cmd2.Parameters.AddWithValue("@TABLE_NAME", "");
                    cmd2.Parameters.AddWithValue("@batch_status_msg", "");
                    try
                    {
                        conn.Open();
                        SqlDataAdapter dapt = new SqlDataAdapter(cmd2);
                        dapt.Fill(ds5);
                            for (int z = 0; z < comboBox1.Items.Count; z++)
                            {
                                for (int a = 0; a < ds5.Tables[0].Rows.Count; a++)
                                {
                                    if (comboBox1.Items[z].ToString() == ds5.Tables[0].Rows[a][0].ToString())
                                    {
                                        comboBox1.Items.Add(comboBox1.Items[z] + " - " + ds5.Tables[0].Rows[a][1].ToString());
                                        comboBox1.Items.Remove(comboBox1.Items[z]);
                                    }
                                } 
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        if (lblerror.Text == "")
                        {
                            lblerror.Text = ex.Message;
                        }
                        else
                        {
                            lblerror.Text = lblerror.Text + ex.Message;
                        }
                    }
                    finally
                    {
                        conn.Close();
                        conn.Dispose();
                    }

Stored Procedure:

SQL

-- +==============================================================================================
@BATCH_NAME						VARCHAR(12)        OUTPUT,            
@BATCH_STATUS_FLG				CHAR(1)				OUTPUT,                     
@BATCH_STATUS_DT				DATETIME			OUTPUT, 
@BEGIN_SFN						VARCHAR(13)			OUTPUT, 
@END_SFN						VARCHAR(13)			OUTPUT, 
@TOTAL_SFN						INT					OUTPUT, 
@TOTAL_AMEND					INT					OUTPUT, 
@TOTAL_PAGES					INT					OUTPUT, 
@LST_MOD_USERID					VARCHAR(10)			OUTPUT, 
@LST_MOD_DT						DATETIME			OUTPUT,
@BATCH_STATUS_MSG				VARCHAR(256)        OUTPUT,
@ERR_CODE              			INT             	OUTPUT ,
@ERR_MSG               			VARCHAR(256)  		OUTPUT,
@TABLE_NAME         			VARCHAR(50)    		OUTPUT 
AS
	
SET NOCOUNT ON

SET @BATCH_NAME = UPPER(LTRIM(RTRIM(@BATCH_NAME)))  
SET	@BATCH_STATUS_FLG = ''            
SET @BEGIN_SFN	= ''
SET @END_SFN	= ''
SET @TOTAL_SFN	= 0
SET @TOTAL_AMEND = 0
SET @TOTAL_PAGES = 0
SET	@LST_MOD_USERID = ''
SET	@BATCH_STATUS_MSG = ''
SET @ERR_CODE   = 0
SET @ERR_MSG    = ''
SET @TABLE_NAME = 'TABLE_NAME'

SELECT @BATCH_NAME = BATCH_NAME,
@BATCH_STATUS_FLG = BATCH_STATUS_FLG,
--UPPER(RTRIM(LTRIM(BATCH_STATUS_FLG))), 
       @LST_MOD_USERID = LST_MOD_USERID,
	   @LST_MOD_DT = LST_MOD_DT
FROM DBO.CAMR_BATCH_CONTROL 
WHERE BATCH_NAME = @BATCH_NAME

SELECT   @BEGIN_SFN = ISNULL(MIN(SFN), ''), @END_SFN = ISNULL(MAX(SFN), ''),  
			@TOTAL_SFN = ISNULL(COUNT(*), 0) 
FROM  DBO.CAMR_DOCUMENT_CONTROL 
WHERE BATCH_NAME = @BATCH_NAME
AND DOC_SEQ_NUM = 1

SELECT   @TOTAL_AMEND = ISNULL(COUNT(*), 0) 
FROM  DBO.CAMR_DOCUMENT_CONTROL 
WHERE BATCH_NAME = @BATCH_NAME
AND DOC_SEQ_NUM > 1

SET @TOTAL_PAGES = @TOTAL_SFN + @TOTAL_AMEND

UPDATE DBO.CAMR_BATCH_CONTROL
SET	BEGIN_SFN 			= @BEGIN_SFN,
  	END_SFN 			= @END_SFN,
  	TOTAL_SFN			= @TOTAL_SFN,
  	TOTAL_AMEND			= @TOTAL_AMEND,
  	TOTAL_PAGES			= @TOTAL_PAGES  	
WHERE BATCH_NAME = @BATCH_NAME    

SET @ERR_CODE = @@ERROR
IF @ERR_CODE <> 0
	BEGIN
		SELECT @TABLE_NAME = 'TableName'
		SET @ERR_MSG    = CAST(@ERR_CODE AS VARCHAR(256))
		RETURN
	END

SET NOCOUNT OFF
RETURN

What I have tried:

I have tried using dataset, it works fine if I pass plain select statement to get batch name and flag statuts

Posted 12-Feb-16 8:17am

Member 12076824

Updated 12-Feb-16 10:33am

Chris Maunder772.7K

v3

Add a Solution

Comments

Richard Deeming 12-Feb-16 15:35pm

The stored procedure you've posted is not returning any results. If that's what you're calling, what are you expecting the DataSet to contain?

koolprasad2003 13-Feb-16 1:05am

As Richard explained, you need to "RETURN" something to fetch it back, you re return nothing and trying to access table[0] from dataset (which is basically not exist)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wonde Tadesse · Accepted Answer · 2016-02-12T08:26:00

Solution 1

1. Always make sure your Stored Procedure returns result set from back end. i.e Database side. At least to have a SELECT Statement that returns the desired result set. And also check how to write and work with Stored Procedure[^].

2. Try to understand how ADO.NET objects such as DataSet[^], DataAdapter [^] and focus on how they work with different SQL Statements that returns result set.

3. DO NOT EVEN ATTEMPT TO USE DataSet.Tables.Count != 0 at all in your application. See the explanation below.
If you are expecting result set(s) and couldn't able to get even a single table, The above code will prevent you not to get the actual problem(exception) by further hiding it.
Technically,
1. You have a SQL Statement/Stored Procedure that returns result set(s).
2. And you used DataAdaper and DataSet to get the result set then you should at least see a single table schema inside the DataSet object.

Why is DataSet.Tables.Count != 0 is BAD in this case.
Let me take Richard Deeming[^] suggested code down in the comment.

C#

using (var connection = new SqlConnection("server=.;integrated security=SSPI;"))
using (var command = new SqlCommand("PRINT 'Hello'", connection))
{
    var ds = new DataSet();
    var da = new SqlDataAdapter(command);
    da.Fill(ds);
    
    Console.WriteLine(ds.Tables.Count);
}

This code works perfectly without issue. Happy ! Not so fast. Let see its hidden dangerous issues which leads to Logic error[^]

Issue 1. The input SQL Statement PRINT 'Hello' is not SQL Statement that can return result set(s) like SELECT SQL Statement and hence you will never ever get result set(s). This is also true for DELETE, UPDATE and other SQL Statements which are not capable of returning result set.
Issue 2. The DataAdapter object(da) was asked to fill the DataSet. i.e

C#

da.Fill(ds);

Another mistake on top of Issue 1, A DataAdapter which has a sql statement that doesn't return a result set and told to fill DataSet. This is another mistake When to use the two objects, DataAdapter and DataSet in What sql statement.
Issue 3, This is the most dangerous one. To use DataSet.Tables.Count on top of the first two issues. This eluding the entire error, the Why is not working Why I'm not getting result set, Why index of range issues by assuming you don't have table

Generally the above code always works but give unexpected result at the end, and it will be even more hard to spot the actual error.

What is the Ultimate solution then ?

1. Know the first two points suggested above. In short, Know HOW to work with Stored Procedures or SQL Statement, DataAdapter and DataSet and related objects. Know WHEN to use them in WHAT SQL Statement such as SELECT, DELETE, UPDATE etc.
2. If you are expecting result set(s) and couldn't see it in DataSet object then DO NOT ATTEMPT to hide further by using DataSet.Tables.Count == 0 or DataSet.Tables.Count != 0. See Issue 1,2,3 explanation. Face the error, figure out why it's not returning the result set, don't further hide it.

Posted 12-Feb-16 8:26am

Wonde Tadesse

Updated 15-Feb-16 15:40pm

v5

Comments

Richard Deeming 12-Feb-16 14:46pm

If the DataSet doesn't contain any tables, ds5.Tables[0] will throw an exception, not return null.

The condition needs to be:
if (ds5.Tables.Count != 0 && ds5.Tables[0].Rows.Count != 0)

Wonde Tadesse 12-Feb-16 15:23pm

Not necessary. If DataSet doesn't contain any tables, dapt.Fill(ds5) will throw exception. Since it couldn't fill any tables. That's why I skip that condition. Try it before commenting.

Richard Deeming 12-Feb-16 15:26pm

No it won't. Try it yourself before commenting! :)

Wonde Tadesse 12-Feb-16 15:30pm

If you traverse the code as is and assuming that dapt.Fill(ds5) works properly then there is at least one table. That is what it won't throw exception.

Richard Deeming 12-Feb-16 15:33pm

Either one or more tables are returned by the query, in which case ds5.Tables[0] will never be null; or no tables are returned by the query, in which case ds5.Tables[0] will throw an exception.

Either way, checking for ds5.Tables[0] != null is not correct.

Wonde Tadesse 12-Feb-16 15:38pm

Checking ds5.Tables[0] != null never be false. Having there doesn't harm though. Do you agree ?

Richard Deeming 12-Feb-16 15:40pm

There's no point in having it there, because the condition will never be ~~true~~ false. If you want to fix the exception the OP reported, you need to replace that check with ds5.Tables.Count != 0 - as I suggested 50 minutes ago! :)

Wonde Tadesse 12-Feb-16 15:43pm

It's always true as long as dapt.Fill(ds5) executed. I think you're mixing Tables.Count with Rows.Count

Richard Deeming 12-Feb-16 15:44pm

Yes, I meant to say it will never be false. The rest of the comment still stands - you need to check whether there are any tables in the DataSet before you try to access them.

Wonde Tadesse 12-Feb-16 15:47pm

If that is the case then ds5.Tables.Count !=0 is same as ds.Tables[0] !=null since both always be true. In this case.

Richard Deeming 12-Feb-16 15:49pm

No, it isn't!!!

If there are no tables in the DataSet, then ds.Tables.Count != 0 will be false.
If there are no tables in the DataSet, then ds.Tables[0] != null will THROW AN EXCEPTION.

That is NOT the same thing at all!!!

Wonde Tadesse 12-Feb-16 15:52pm

Back to square one. There is always be a Table(at least one) in DataSet as long as dapt.Fill(ds5) executed. Do you agree ?

Richard Deeming 12-Feb-16 15:55pm

No!!!

As I mentioned 26 minutes ago, and as the documentation clearly states, if the query doesn't return any tables, the Fill method will not add any tables to the DataSet, and it will not throw an exception.

DbDataAdapter.Fill method[^]:
If a command does not return any rows, no tables are added to the DataSet, and no exception is raised.

Wonde Tadesse 12-Feb-16 16:05pm

That what I was saying you confused with Table.Count vs Rows.Count. Let me rephrase it again. If a command does not return any rows, no tables are added to the DataSet, and no exception is raised. But there will be table schema with empty record. Which means you have a Table but no rows

Careful here.

A command may not return a record/rows based on the query, but there is still a schema of a table.E.g

Select * From Order Where Order ID = 1000. Mind you this may returnempty record/row, However you still have Order Table

Richard Deeming 12-Feb-16 16:11pm

No, you're missing the point.

Look carefully at the stored procedure the OP has posted. There is no SELECT statement that returns any resultset to the caller. The only SELECT statements in the procedure are used to set local variables.

Since the query does not return any resultsets to the caller, the DataAdapter will not add any tables to the DataSet, and will not throw an exception.

You will end up with a DataSet where Tables.Count == 0, and your attempt to access the first table with ds5.Tables[0] will throw an exception.

In this case, it's clearly a mistake. The query should be returning a resultset. However, it's quite possible for this to happen for perfectly valid reasons, if the stored procedure changes the resultsets it returns based on the parameters.

Once again: checking for ds5.Tables[0] != null will not solve the exception that the OP reported. You need to check for ds5.Tables.Count != 0 instead.

Wonde Tadesse 12-Feb-16 16:15pm

"You will end up with a DataSet where Tables.Count == 0, and your attempt to access the first table with ds5.Tables[0] will throw an exception."

NO!

You won't get exception as long as dapt.Fill(ds5) executed properly. I already said to many times. Why don't you try it.

Richard Deeming 12-Feb-16 16:18pm

I have tried it. Why don't you try it yourself, instead of continuing this argument?!

using (var connection = new SqlConnection("server=.;integrated security=SSPI;"))
using (var command = new SqlCommand("PRINT 'Hello'", connection))
{
	var ds = new DataSet();
	var da = new SqlDataAdapter(command);
	da.Fill(ds);
	
	Console.WriteLine(ds.Tables.Count);
	Console.WriteLine(ds.Tables[0]);
}

Output:

0
IndexOutOfRangeException: Cannot find table 0

Wonde Tadesse 12-Feb-16 16:25pm

Your "Print Hello" query won't fill the DataSet. You just simply arguing here. The where you get exception.

Richard Deeming 12-Feb-16 16:27pm

That is the point I am trying to make!!!

This is not rocket science. If your query does not return any resultsets, then you will not have any tables in your DataSet, and the Fill method will not throw any exceptions.

Just try the code for yourself before you reply!

Wonde Tadesse 12-Feb-16 16:29pm

This code won't execute. It never reach there.

Console.WriteLine(ds.Tables.Count);
Console.WriteLine(ds.Tables[0]);

Richard Deeming 12-Feb-16 16:31pm

JUST TRY THE DAMN CODE!

Stop posting comments telling me that the behaviour I HAVE OBSERVED is wrong, and the behaviour YOU ARE IMAGINING is correct.

Try the code before you post another pointless comment!

Wonde Tadesse 12-Feb-16 16:39pm


using (var command = new SqlCommand("Select * From Order1", connection)) // Table doesn't exist.
            {
                var ds = new DataSet();
                var da = new SqlDataAdapter(command);
                da.Fill(ds);

                Console.WriteLine(ds.Tables.Count);
                Console.WriteLine(ds.Tables[0]);
            }

Richard Deeming 12-Feb-16 16:45pm

You are STILL missing the point!

The query the OP posted DOES NOT RETURN ANY TABLES!

* If you execute a query against a table that doesn't exist, you will get an exception from the Fill method.

* If you execute a query that has a resultset, then you will get at least one table.

* If you execute a query that does not have a resultset, then you will not get any tables, and the Fill method will not throw an exception.

I don't know how I can make this explanation any simpler!

I posted a friendly suggestion on how to improve your answer. Rather than acting on that suggestion, you've spent the last 90 minutes telling me that:

A) If you do something other than what the OP is doing, you'll get different behaviour; and

B) The behaviour I am looking at RIGHT THIS VERY SECOND doesn't happen, and you refuse to even try the code sample that very clearly demonstrates it because it doesn't happen.

DO NOT reply again until you have tried the code I posted. You will see the exception is thrown from the ds.Tables[0] line, and NOT from the da.Fill(ds) line.

Wonde Tadesse 12-Feb-16 17:06pm

Back from work. Are you happy now ?

Richard Deeming 12-Feb-16 17:07pm

Did you try the code block I posted? Or are you going to continue to tell me that I'm imagining things, and the behaviour I have directly observed doesn't happen?

Wonde Tadesse 12-Feb-16 17:10pm

Your suggestion is not correct and No argument please. We trying to help here not to argue. If you see my profile, there is no such long conversation. I appreciate if we end up here.

Cheers

Richard Deeming 12-Feb-16 17:17pm

I'm not arguing - I'm explaining my position forcefully! :)

To sum up:

* We both agree that if you execute a query with an error, or try to access a table that doesn't exist, the Fill method will throw an exception.

* We both agree that if your query returns a result-set, even if there are no rows in it, you will still get at least one table in the DataSet.

* The disagreement / communication problem: If you execute a query which doesn't return any result-sets - as the OP is doing, and as my demo code-block is doing - the Fill method will not throw an exception, and you will not have any tables in your DataSet. When you try to access the first table with ds.Tables[0], you will get an IndexOutOfRangeException:

System.IndexOutOfRangeException: Cannot find table 0.
at System.Data.DataTableCollection.get_Item(Int32 index)

This exception is the error that the OP reported in the question.

Wonde Tadesse 12-Feb-16 17:21pm

You see that why I say you like arguing before looking :)

Richard Deeming 12-Feb-16 17:23pm

Obligatory XKCD[^] :)

Wonde Tadesse 14-Feb-16 13:30pm

A comprehensive answer is given. Enjoy

Richard Deeming 12-Feb-16 17:18pm

I see you've updated your answer. I've updated my vote to a 5. :)

Richard Deeming 12-Feb-16 15:34pm

If you look at the stored procedure which the OP posted 9 minutes ago, you'll see that it's not returning any tables.

Richard Deeming 12-Feb-16 15:27pm

DbDataAdapter.Fill method[^]:
If a command does not return any rows, no tables are added to the DataSet, and no exception is raised.

Member 12076824 12-Feb-16 15:20pm

Thanks for response. I tried this looks like I am missing something that's failing to fill dataset. At this moment it returns no records in the dataset. SP gets me data at SQL Level