C＃windows应用程序，找不到表0 [英] C# windows application, cannot find table 0

问题描述

我正在尝试使用数据集从存储过程中获取SQL数据库中的数据，并在组合框中填充该数据。它给出了错误，因为找不到表0.

 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， ）; 
 尝试 
 {
 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 ]。行[a] [ 0 ]。ToString（））
 {
 comboBox1.Items.Add（comboBox1.Items [z] +    -  + ds5.Tables [ 0 ]。行[a] [ 1 ]。ToString（））; 
 comboBox1.Items.Remove（comboBox1.Items [z]）; 
} 
} 
} 
} 
} 
  catch （例外情况）
 {
  if （lblerror.Text ==  ）
 {
 lblerror.Text = ex.Message; 
} 
  else  
 {
 lblerror.Text = lblerror.Text + ex.Message; 
} 
} 
 最后 
 {
 conn.Close（）; 
 conn.Dispose（）; 
} 

存储过程：

   -    + ========================== ================================================== ==================  
  @ BATCH_NAME   VARCHAR （ 12 ） OUTPUT ，
  @ BATCH_STATUS_FLG   CHAR （ 1 ）输出，
  @ BATCH_STATUS_DT   DATETIME  输出，
  @ BEGIN_SFN   VARCHAR （ 13 ）输出， 
  @ END_SFN   VARCHAR （ 13 ） OUTPUT ，
  @ TOTAL_SFN   INT   OUTPUT ，
  @ TOTAL_AMEND   INT  输出，
  @ 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（ @ BAT CH_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_STATU） S_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  
  A. ND  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 ））
 返回 
  END  
 
 < span class =code-keyword> SET   NOCOUNT   OFF  
  RETURN  

我的尝试：



我尝试过使用数据集，如果我通过普通的select语句来获取批处理名称和标志statuts

决方案

1。始终确保您的存储过程从后端返回结果集。即数据库方面。至少要有一个返回所需结果集的 SELECT 语句。还要检查如何编写和使用存储过程 [ ^ ]。



2.尝试理解ADO.NET对象如 DataSet [ ^ ]， DataAdapter [ ^ ]并关注它们如何使用返回结果集的不同SQL语句。



3. DO在您的应用程序中根本没有尝试使用 DataSet.Tables.Count！= 0 。请参阅下面的解释。

如果您期望结果集，甚至无法获得单个表，上述代码将阻止您不要通过进一步隐藏它来获得实际问题（例外）。

技术上，

1.你有一个返回结果集。

2.您使用 DataAdaper 和 DataSet 要获得结果集，那么你应该至少在 DataSet 对象中看到一个表模式。



为什么 DataSet.Tables.Count！= 0 在这种情况下是不好的。

让我拿< a href =http://www.codeproject.com/script/Membership/View.aspx?mid=34187> Richard Deeming [ ^ ]在评论中建议代码。

 使用（ var  connection =  new  SqlConnection（  server =。; integrated security = SSPI;））
 使用（ var  command =  new  SqlCommand（  PRINT'Hello '，connection））
 {
  var  ds =  new  DataSet（）; 
  var  da =  new  SqlDataAdapter（command）; 
 da.Fill（ds）; 
 
 Console.WriteLine（ds.Tables.Count）; 
} 

此代码完美无缺。开心！没那么快。让我们看看它隐藏的危险问题导致逻辑错误 [ ^ ]



问题1 。输入SQL语句 PRINT'Hello'是不是可以返回结果集的SQL语句，如 SELECT SQL语句，因此您永远不会得到结果集。对于 DELETE ， UPDATE 以及不能够执行的其他SQL语句也是如此返回结果集。

问题2 。要求 DataAdapter 对象（ da ）填充 DataSet 。即

 da.Fill（ds）; 

问题1 之上的另一个错误，A DataAdapter 其中有一个sql语句，该语句不返回结果集并告知填充 DataSet 。这是另一个错误何时在 DataAdapter 和 DataSet 这两个对象>什么 sql语句。

问题3 ，这是最危险的一个。在前两个问题之上使用 DataSet.Tables.Count 。这包括整个错误，为什么无法正常工作为什么我没有得到结果集，为什么范围索引通过假设你没有桌子



一般来说上面的代码总是有效但最后给出了意想不到的结果，现在发现它会更加困难实际的错误。



什么是终极解决方案呢？



1.了解上面提到的前两点。简而言之，知道如何使用存储过程或SQL语句， DataAdapter 和 DataSet 和相关对象。知道 WHEN 在 WHAT SQL语句中使用它们，例如 SELECT ， DELETE ， UPDATE 等等

2.如果您期望结果集并且无法在 DataSet 对象中看到它，那么请勿ATTEMPT 使用 DataSet.Tables.Count == 0 或 DataSet.Tables.Count！= 0 。请参阅问题1,2,3 说明。 面对错误，弄清楚为什么它没有返回结果集，不要进一步隐藏它。




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.



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:



-- +==============================================================================================
@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

解决方案

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.

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

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.




这篇关于C＃windows应用程序，找不到表0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！