Click here to Skip to main content
15,896,557 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
OleDbConnection conn = new OleDbConnection();
    OleDbCommand cmd = new OleDbCommand();
 
    OleDbDataAdapter da = new OleDbDataAdapter();
    DataSet ds = new DataSet();
    System.Data.DataTable dt = null;
    //string query = null;
    string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;""";
   
    protected void Page_Load(object sender, EventArgs e)
    {
        //ConfigurationSettings.AppSettings[FileExtension];
        
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string validFileExtensions = Config.Get("ValidFileExtension");


        int uploadSize = Convert.ToInt32( Config.Get("DocUploadValidationSize"));
        string validationMEssage = Config.Get("DocUploadValidationMessage");

        connectionString = string.Format(connectionString, @"d:\R and D\Book1.xlsx");
          if (FileUpload1.HasFile)
          {
            string fileExt =
               System.IO.Path.GetExtension(FileUpload1.FileName);

            if (validFileExtensions.IndexOf(fileExt)>=0)
            {
                try
                {

                  if (FileUpload1.PostedFile.ContentLength >= uploadSize)
                    {

                        Literal1.Text = validationMEssage;
                        return;
                    }
        
                        FileUpload1.SaveAs(@"C:\Users\user5\Desktop" +
                        FileUpload1.FileName);
                        Label1.Text = "File name: " +
                        FileUpload1.PostedFile.FileName + "<br>" +
                        FileUpload1.PostedFile.ContentLength + " kb<br>" +
                        "Content type: " +
                        FileUpload1.PostedFile.ContentType;


                        using (OleDbConnection conn = new OleDbConnection(connectionString))
                        { 
                            conn.Open();

                            OleDbCommand cmd = conn.CreateCommand();
                            dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                             
                            

                            String[] excelSheets = new String[dt.Rows.Count];
                            int i = 0;

                            // Add the sheet name to the string array.

                            foreach (DataRow row in dt.Rows)
                            {
                                  excelSheets[i] = row["TABLE_NAME"].ToString();
                                i++;
                            }

                            // Loop through all of the sheets if you want too...

                            for (int j = 1; j < excelSheets.Length; j++)
                            {

                                if (j == 1)
                                {
                                    cmd.CommandText = "SELECT ID,UserName,Designation FROM [" + excelSheets[1] + "]";
                                }
                                
                                else
                                    if (j == 2)
                                    {
                                        cmd.CommandText = "SELECT ID,CITY,STATE FROM [" + excelSheets[2] + "]";
                                    }


                                else
                                        if (j == 3)
                                        {
                                            cmd.CommandText = "SELECT ID,Client,City FROM [" + excelSheets[3] + "]";
                                        }
                                 
                                //cmd.CommandText = "SELECT ID,City,State FROM [WorkSheet2$]";
                                //cmd.CommandText = "SELECT ID,City,State FROM [WorkSheet3$]";





                                //using (OleDbCommand cmd = conn.CreateCommand())
                                // {


                                using (OleDbDataReader reader = cmd.ExecuteReader())
                                {
                                    while (reader.Read())
                                    {
                                        Console.WriteLine(reader["WorkSheet1"].ToString());
                                        Console.WriteLine(reader["WorkSheet2"].ToString());
                                        Console.WriteLine(reader["WorkSheet3"].ToString());
                                    }

                                }
                            }
                                Console.Read();
                            //}
                        }


                }

                catch (Exception ex)
                {
                    Label1.Text = "ERROR: " + ex.Message.ToString();
                }
            }
            else
            {
                Label1.Text = "Upload Valid File!";
            }
        }
        else
        {
            Label1.Text = "You have not specified a file.";
        }

 }




}




have this code for reading multiple tag form excelsheet when i debuging code it not goes in following part

C#
while (reader.Read())
 {
 Console.WriteLine(reader["WorkSheet1"].ToString());
 Console.WriteLine(reader["WorkSheet2"].ToString());
 Console.WriteLine(reader["WorkSheet3"].ToString());
 }
 }
 }


and gives following errors No value given for one or more required parameters.
if correct the above code
Posted
Updated 1-Jul-11 8:29am
v4
Comments
DaveAuld 1-Jul-11 14:30pm    
Edit: fixed formatting, the triple quote broke the parser. replaced them with & q u o t ;

1 solution

Put a try/catch block around your code, and run it under the debugger. Also, try this instead:

C#
Console.WriteLine(reader.GetString(reader["WorkSheet1"]));
 
Share this answer
 
v2

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