Click here to Skip to main content
14,695,443 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
See more:
I could not able to fetch 7th column value from excel it throws an error as "An unhandled exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll"

and Additional information: Cannot find column 7.

But iam having values in excel for 7th column, Even I couldn't able to fetch after 7th value. I have attached my excelsheet and coding.

Kindly do the needful.

int j=0;
private void ImportExcel(string strFilePath)
       {
           if (File.Exists(strFilePath))
           {
               String strExcelConn = "Provider=Microsoft.ACE.OLEDB.12.0;"
           + "Data Source=" + strFilePath + ";"
           + "Extended Properties='Excel 8.0;HDR=Yes'";
               connExcel = new OleDbConnection(strExcelConn);
               try
               {
                   cmdExcel.Connection = connExcel;
                   //Check if the Sheet Exists
                   connExcel.Open();
                   DataTable dtExcelSchema;
                   //Get the Schema of the WorkBook
                   dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                   connExcel.Close();
                   //Read Data from Sheet1
                   connExcel.Open();
                   OleDbDataAdapter da = new OleDbDataAdapter();
                   string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                   cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
                   da.SelectCommand = cmdExcel;
                   da.Fill(ds);
                    DisplayText();//Method
               }
               catch (Exception ex)
               {
                   MessageBox.Show(ex.Message, "ImportExcel");
               }
               finally
               {
                   cmdExcel.Dispose();
                   connExcel.Dispose();
               }
           }


private void DisplayText()
      {
          BAngle();
          BFAngle();
      }
      private void BAngle()
      {
          int Angle = 0;
          for (int i = j; i <= ds.Tables[0].Rows.Count; i++)
          {
              string sname = ds.Tables[0].Rows[i][Angle].ToString();
              txt_BSA.Text = sname.ToString();
              txt_BSA.Focus();
           }
      }
      private void BFAngle()
      {
          int Fore = 7;
          for (int i = j; i <= ds.Tables[0].Rows.Count; i++)
          {
              string bname = ds.Tables[0].Rows[i][Fore].ToString();//It throws an error
              txt_BFA.Text = bname.ToString();
              txt_BFA.Focus();
            
          }
      }


I have attached my excel Sheet in below link.

Excel.xls - Google Drive[^]

What I have tried:

private void BFAngle()
{
int Fore = 7;
for (int i = j; i <= ds.Tables[0].Rows.Count; i++)
{
string bname = ds.Tables[0].Rows[i][Fore].ToString();
txt_BFA.Text = bname.ToString();
txt_BFA.Focus();

}
Posted
Updated 25-Aug-16 21:59pm
v5
Comments
The Praveen Singh 26-Aug-16 2:21am
   
in your excel there is 11 colomn and in your loop you have set 7.

1 solution

Try to replace
for (int i = j; i <= ds.Tables[0].Rows.Count; i++)

with
for (int i = j; i < ds.Tables[0].Rows.Count; i++)

With a 0 based list, the last element is Count-1

[Update]
Test if
ds.Tables[0].Rows[i].Count
is at lest 8 because you try to read element 7.

[Update]
Quote:
Compiler shows error as "Error 1 Operator '<=' cannot be applied to operands of type 'int' and 'method group'"
Looks like you try to access ds.Tables[0].Rows as a 2D array when it is in fact a 1D array.

ds.Tables[0].Rows[i][Fore] do not exist because
ds.Tables[0].Rows[i] is an int and
ds.Tables[0].Rows is an array
   
v4
Comments
vinodh muthusamy 26-Aug-16 2:42am
   
I have tried with code which you have posted, same error it throws.

Any other solution please
vinodh muthusamy 26-Aug-16 3:04am
   
ds.Tables[0].Rows[i].Count

Compiler shows error as "Error 1 Operator '<=' cannot be applied to operands of type 'int' and 'method group'"
vinodh muthusamy 26-Aug-16 3:18am
   
K what is the solution for this
vinodh muthusamy 26-Aug-16 3:21am
   
Kindly refer the excel which i have given link, Iam not able to fetch from 7th column values.

Till 6 column values i can get values
Patrice T 26-Aug-16 3:42am
   
May be you can tell what is ds
vinodh muthusamy 26-Aug-16 4:12am
   
ds is data set

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900