Cannot find table 0 error in C#

Question

I get This error when i try to execute stored procedure and get null dataset.

private SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["learnaspConnectionString"].ConnectionString);

protected void btnregister_Click(object sender, EventArgs e)
{
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("dbo.RegisterInsert", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p1 = new SqlParameter("@fname", txtfname.Text);
            SqlParameter p2 = new SqlParameter("@lname", txtlname.Text);
            SqlParameter p3 = new SqlParameter("@company", txtcompany.Text);
            SqlParameter p4 = new SqlParameter("@phone", txtphone.Text);
            SqlParameter p5 = new SqlParameter("@address", txtaddress.Text);
            SqlParameter p6 = new SqlParameter("@email", txtemail.Text);
            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);
            cmd.Parameters.Add(p3);
            cmd.Parameters.Add(p4);
            cmd.Parameters.Add(p5);
            cmd.Parameters.Add(p6);
            cmd.ExecuteNonQuery();

            con.Close();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            ListViewDetails.DataSource = ds;
            ListViewDetails.DataBind();
            lblmessage.Visible = true;
            lblmessage.Text = "Registration Completed Successfully!";
        }
        catch (SqlException ex)
        {
        }
}

My stored Procedure is:

   CREATE PROCEDURE [dbo].[RegisterInsert] 
    -- Add the parameters for the stored procedure here
    @fname nvarchar(50),
    @lname nvarchar(50),
    @company nvarchar(50),
    @phone nvarchar(50),
    @address nvarchar(250),
    @email  nvarchar(250)   
       SET NOCOUNT ON;
        insert into [dbo].[register]
    ([fname],
     [lname],
     [company],
     [phone],
     [address],
     [email])
     values
     (@fname,
      @lname,
      @company,
      @phone,
      @address,
      @email)    
END

I am not able to get display data in Listview control when i run my code.Button Click event stores values in Database twice and gives error'Cannot find table 0'.

Please help me to let me know where i am wrong in my code.

Thanks in Advance.

Answer 1

The problem is the Dataset does not contain any table objects. Therefore DS.Table[0] does not exist and will thrown an error. Add a check to ensure the dataset has tables before proceeding.

    // check if ds has a table
    if(DS.TABLES.COUNT>0)
    {
       //check if ds.table[0] has any rows  
       if(DS.TABLES[0].ROWS.COUNT>0){
       //do something
    }
 }

Answer 2

             con.Open();
        SqlCommand cmd = new SqlCommand("dbo.RegisterInsert", con);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter p1 = new SqlParameter("@fname", txtfname.Text);
        SqlParameter p2 = new SqlParameter("@lname", txtlname.Text);
        SqlParameter p3 = new SqlParameter("@company", txtcompany.Text);
        SqlParameter p4 = new SqlParameter("@phone", txtphone.Text);
        SqlParameter p5 = new SqlParameter("@address", txtaddress.Text);
        SqlParameter p6 = new SqlParameter("@email", txtemail.Text);
        cmd.Parameters.Add(p1);
        cmd.Parameters.Add(p2);
        cmd.Parameters.Add(p3);
        cmd.Parameters.Add(p4);
        cmd.Parameters.Add(p5);
        cmd.Parameters.Add(p6);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        DataTable dt = new DataTable();
        dt = ds.Tables[0];

        ListViewDetails.DataSource = dt;
        ListViewDetails.DataBind();
        con.close();
        lblmessage.Visible = true;
        lblmessage.Text = "Registration Completed Successfully!";

you have not properly defined datatable.

DataTable dt = new DataTable();

Bind of datasource in this way

 ListViewDetails.DataSource = dt;

Now close the connection after binding the data.

con.close(); 

This code will work.

Answer 3

The issue lies in the line: SqlDataAdapter da = new SqlDataAdapter(cmd);

Its good that you created an adapter. But two issue are there in above line:

1.) The above method is actually used to specify the SelectCommand of SqlDataAdapter . Check MSDN here.

2.) You havent specified what SqlConnection to use to this adapter. So do this way:

SqlDataAdapter da = new SqlDataAdapter(cmd, conn);

where conn is your SqlConnection object.

Seeing the last 8 lines of your code, it seems basically you want to display the data.

So once you execute your query: the Line: cmd.ExecuteNonQuery(); , now change your cmd and assign either a stored procedure that does the Select Query

OR

You can specif Select query in cmd as : Select * from register , but then you need to change the command type also as: cmd.CommandType=CommandType.Text;

Answer 4

You are using insert query so you are getting null DataTable.
Write another select statement after insert statement.

    CREATE PROCEDURE [dbo].[RegisterInsert] 
-- Add the parameters for the stored procedure here
@fname nvarchar(50),
@lname nvarchar(50),
@company nvarchar(50),
@phone nvarchar(50),
@address nvarchar(250),
@email  nvarchar(250)   
   SET NOCOUNT ON;
    insert into [dbo].[register]
([fname],
 [lname],
 [company],
 [phone],
 [address],
 [email])
 values
 (@fname,
  @lname,
  @company,
  @phone,
  @address,
  @email)

select * from [dbo].[register]

Answer 5

Totally Wrong .

You want store values from database , need insert query

You want read values from database , need select query

But you have used insert query for select query . and you have to call the query in 2 time ( dataadapter and executenonquery),So the data inserted twice .

first read this article Msdn

Answer 6

Try this

    private SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["learnaspConnectionString"].ConnectionString);

    protected void btnregister_Click(object sender, EventArgs e)
    {
        try
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("dbo.RegisterInsert", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p1 = new SqlParameter("@fname", txtfname.Text);
            SqlParameter p2 = new SqlParameter("@lname", txtlname.Text);
            SqlParameter p3 = new SqlParameter("@company", txtcompany.Text);
            SqlParameter p4 = new SqlParameter("@phone", txtphone.Text);
            SqlParameter p5 = new SqlParameter("@address", txtaddress.Text);
            SqlParameter p6 = new SqlParameter("@email", txtemail.Text);
            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);
            cmd.Parameters.Add(p3);
            cmd.Parameters.Add(p4);
            cmd.Parameters.Add(p5);
            cmd.Parameters.Add(p6);
            cmd.ExecuteNonQuery();

            SqlDataAdapter da = new SqlDataAdapter("Select * from register",con);//Here  you are using same cmd that is for insert so you call insert procedure twice and that's why insert data twice 
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            ListViewDetails.DataSource = ds;
            ListViewDetails.DataBind();
            lblmessage.Visible = true;
            lblmessage.Text = "Registration Completed Successfully!";


            con.Close();

        }
        catch (SqlException ex)
        {
        }
    }