Click here to Skip to main content
15,936,709 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Problem

when selecting data from excel sheet and columns name different give error .

so that i need to change my code to accept any alias to columns .

example

in my code column UnitCode represent 'رقم الاستماره'

I need it if he write another name accept without exception as 'الاستماره'

my Code Below :

public DataTable Showdataprint()
        {
            string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", txtpath.Text);

            OleDbConnection con = new OleDbConnection(connectionString);


            con.Open();
            DataTable dt = new DataTable();

            dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            string SheetName = dt.Rows[0]["TABLE_NAME"].ToString();


            OleDbCommand com = new OleDbCommand();
            com.Connection = con;
            //com.CommandText = @"SELECT  CStr([رقم الاستمارة]) as [UnitCode],[قراءة العداد]as[CurrentMeterReading] FROM  [" + SheetName + "] ";
            com.CommandText = @"SELECT  [رقم الاستمارة] as [UnitCode],[رقم العدا د]as[CounterNumber],[قراءة العداد]as[CurrentMeterReading] FROM  [" + SheetName + "] ";
            OleDbDataAdapter oledbda = new OleDbDataAdapter();
            oledbda.SelectCommand = com;
            DataSet ds = new DataSet();
            oledbda.Fill(ds);
            dt = ds.Tables[0];
            con.Close();
            return dt;


        }

To Summarize what i need is dynamic column name alias meaning after

UnitCode as write what i need


What I have tried:

when selecting data from excel sheet and columns name alias different give error
Posted
Comments
Richard Deeming 6-Aug-18 11:28am    
Are your columns always in the same order?

If not, I don't see how you can identify which column is which if the user doesn't provide a consistent column header.

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