locked
DbDataReader NOT working with excel files that have integar as coloumn name RRS feed

  • Question

  • I am reading a excel file and storing it in the DB using DbDataReader.

    The problem is that the column names of the excel file is integar (2,39)

    I notices that DbDataReader is not working as expected when the column name is an integar.

    To verify it i added V to all the column names and things worked fine.So i changed the col name from say 76 to V76

    A

     

    any help appreciated

     

    Snippet of code i am using

     

     

     

    string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties="+"\"Excel 8.0;HDR=YES;\"";

    DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");

    using (DbConnection connection = factory.CreateConnection())

    {

    connection.ConnectionString = excelConnectionString;

    using (DbCommand command = connection.CreateCommand())

    {

    // Cities$ comes from the name of the worksheet

    command.CommandText = "SELECT * FROM [Data$]";

    connection.Open();

    using (DbDataReader dr = command.ExecuteReader())

    {

    while (dr.Read())

    {

    Console.WriteLine("this is {0}",dr.GetName(6).ToString()); 

    }

    }

    }

    }

    Monday, October 15, 2007 9:29 PM

Answers

  • The column name has to be a string and the integral value six is not returning a string six like you want. I recommend that you convert the integers to string such as

    Code Block



    int myValue = 6;
    string myColumnName = myValue.ToString();

    Wednesday, October 17, 2007 2:34 PM