Programming Help

Web Development



What's Here?

  • Members: 136,562
  • Replies: 479,687
  • Topics: 74,754
  • Snippets: 2,567
  • Tutorials: 677
  • Total Online: 1,900
  • Members: 75
  • Guests: 1,825

Welcome to Dream.In.Code
Getting C# Help is Easy!

Join 136,562 C# Programmers for FREE! Get instant access to thousands of C# experts, tutorials, code snippets, and more! There are 1,900 people online right now. Registration is fast and FREE... Join Now!




Advanced OLEDB Select for Excel Sheet

 
 Reply to this topicStart new topic

Advanced OLEDB Select for Excel Sheet, Trying to perfrom a select statement with a WHERE clause
someone1
28 Apr, 2008 - 10:18 AM
Post #1

New D.I.C Head
*

Joined: 28 Apr, 2008
Posts: 7
I'm trying to load in an XLS sheet and filter the results. I have a DeletedFlag column and want to only select those rows in which this column is set to "False"

here is my code:

CODE

private DataTable LoadXLS(string strFile, String sheetName, String column, String value)
        {
            DataTable dtXLS = new DataTable("XLS");
            try
            {
                string strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);
                OleDbConnection SQLConn = new OleDbConnection(strConnectionString);
                SQLConn.Open();
                OleDbDataAdapter SQLAdapter = new OleDbDataAdapter();
                OleDbCommand selectCMD = new OleDbCommand("SELECT * FROM [" + sheetName + "$] WHERE " + column + " = @" + column, SQLConn);
                selectCMD.Parameters.Add("@" + column, OleDbType.VarChar).Value = value;
                SQLAdapter.SelectCommand = selectCMD;
                
                SQLAdapter.Fill(dtXLS);
                SQLConn.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            return dtXLS;
        }


It keeps breaking when trying to Fill by dataTable:
A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Nationwide_QUpdate.NWQUpdate.LoadXLS(String strFile, String sheetName, String column, String value) in C:\...\NWQUpdate.cs:line 440

I'm not sure what is wrong! Please help.
User is offlineProfile CardPM
+Quote Post

naiairex
RE: Advanced OLEDB Select For Excel Sheet
25 Aug, 2008 - 10:26 AM
Post #2

New D.I.C Head
*

Joined: 25 Aug, 2008
Posts: 1
I got the same excpetion when I try to read Excel 4.0 format files with these code.
CODE

            ...
            System.Data.DataTable dbSchema = new System.Data.DataTable();
            OleDbConnection conn;
            OleDbDataAdapter da;
            DataSet ds;

            string sfile = "C:\\a.xls";
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sfile + ";" + "Extended Properties=Excel 8.0;";

            conn = new OleDbConnection(strConn);
            conn.Open();
            // Get all sheetnames from an excel file into data table
            dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dbSchema != null || dbSchema.Rows.Count > 0)
            {
                // Loop through all worksheets
                for (int i = 0; i < dbSchema.Rows.Count; i++)
                {
                    string sheetname = dbSchema.Rows[i]["TABLE_NAME"].ToString();

                    da = new OleDbDataAdapter("SELECT * FROM [" + sheetname + "]", strConn);
                    da.TableMappings.Add("Table", "dt_Excel");

                    ds = new DataSet();
                  [color=#FF0000] da.Fill(ds);[/color]
                   ....
                }
            }
            conn.Close();

        }


Regards



QUOTE(someone1 @ 28 Apr, 2008 - 11:18 AM) *

I'm trying to load in an XLS sheet and filter the results. I have a DeletedFlag column and want to only select those rows in which this column is set to "False"

here is my code:

CODE

private DataTable LoadXLS(string strFile, String sheetName, String column, String value)
        {
            DataTable dtXLS = new DataTable("XLS");
            try
            {
                string strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);
                OleDbConnection SQLConn = new OleDbConnection(strConnectionString);
                SQLConn.Open();
                OleDbDataAdapter SQLAdapter = new OleDbDataAdapter();
                OleDbCommand selectCMD = new OleDbCommand("SELECT * FROM [" + sheetName + "$] WHERE " + column + " = @" + column, SQLConn);
                selectCMD.Parameters.Add("@" + column, OleDbType.VarChar).Value = value;
                SQLAdapter.SelectCommand = selectCMD;
                
                SQLAdapter.Fill(dtXLS);
                SQLConn.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            return dtXLS;
        }


It keeps breaking when trying to Fill by dataTable:
A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Nationwide_QUpdate.NWQUpdate.LoadXLS(String strFile, String sheetName, String column, String value) in C:\...\NWQUpdate.cs:line 440

I'm not sure what is wrong! Please help.
User is offlineProfile CardPM
+Quote Post

someone1
RE: Advanced OLEDB Select For Excel Sheet
25 Aug, 2008 - 10:52 AM
Post #3

New D.I.C Head
*

Joined: 28 Apr, 2008
Posts: 7
Hey,

Its been a while but i got this to work:

CODE

private DataTable LoadXLS(string strFile, String sheetName, String column, String value)
        {
            DataTable dtXLS = new DataTable(sheetName);
            try
            {
                string strConnectionString = "";
                if(strFile.Trim().EndsWith(".xlsx")) {
                    strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", strFile);
                } else if(strFile.Trim().EndsWith(".xls")) {
                    strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);
                }
                OleDbConnection SQLConn = new OleDbConnection(strConnectionString);
                SQLConn.Open();
                OleDbDataAdapter SQLAdapter = new OleDbDataAdapter();
                string sql = "SELECT * FROM [" + sheetName + "$] WHERE " + column + " = " + value;
                OleDbCommand selectCMD = new OleDbCommand(sql, SQLConn);
                SQLAdapter.SelectCommand = selectCMD;
                
                SQLAdapter.Fill(dtXLS);
                SQLConn.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            return dtXLS;
        }
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic
Time is now: 12/2/08 11:40PM
Invision Power Board v2.1.7 © 2008  IPS, Inc.
Licensed to: MediaGroup1

Live C# Help!

C# Tutorials

Reference Sheets

C# Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month