Trouble including a string in an SQL query for an Excel Spreadsheet

Question

I am displaying data from an Excel Spreadsheet through an ASP.net web form using C#. I would like to run an SQL query on the data, but am having trouble figuring out how to use a string in my query.

Here is the code I am running in my .aspx.cs file. I am also using a .aspx to display the data in a GridView.

protected void Page_Load(object sender, EventArgs e)
{
    string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelCSTest.xls") + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

    OleDbConnection objConn = new OleDbConnection(sConnectionString);
    objConn.Open();

    string sSQL = "SELECT * FROM [Sheet1$A1:D14]";

    OleDbCommand objCmdSelect = new OleDbCommand(sSQL, objConn);
    OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
    objAdapter1.SelectCommand = objCmdSelect;

    DataSet objDataset1 = new DataSet();
    objAdapter1.Fill(objDataset1, "XLData");

    GridView1.DataSource = objDataset1.Tables[0].DefaultView;
    GridView1.DataBind();

    objConn.Close();
}

Ideally, I would like to add a WHERE clause to my string sSQL = "SELECT * FROM [Sheet1$A1:D14]"; in order to query the current month and display the row of said month from the Excel Spreadsheet.

Excel is about the worst possible data source you could use in a web site. Even a flat file would be better. — Joel Coehoorn, Jan 16 '15 at 04:10
As @TomyVarghese mentioned "what is the error you are getting when you include the where clause?" — Mehrad, Jan 19 '15 at 04:54

score 0 · Answer 1 · edited May 23 '17 at 11:43

I have rewritten your code for you, however, you should seriously consider sticking into some coding standards. Please don't end your variables with numbers and consider formatting your code then it's more readable. Also you need to surround your OleDbConnection() inside a using statement then it gets disposed properly.

Here is the formatted and reworked code

public partial class ExcelAdapter : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelCSTest.xls") + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

        using (OleDbConnection objConn = new OleDbConnection(sConnectionString))
        {
            objConn.Open();
            var sSQL = "SELECT * FROM [Sheet1$A1:D14]";
            OleDbCommand objCmdSelect = new OleDbCommand(sSQL, objConn);
            objCmdSelect.CommandType = CommandType.Text;
            DataSet objDataset = new DataSet();
            OleDbDataAdapter objAdapter = new OleDbDataAdapter(objCmdSelect).Fill(objDataset);
            objConn.Close();
        }
    }
}

I haven't had time to test this code but I am pretty sure it should be spot on. (I hope)

Further more have a look at this tutorial. It's an excellent source to give you an idea of how to do this job properly.

Import Excel File to DataSet

Where Clause

It's very easy to add the where clause to your SQL and even better you can parametrize it to make it more standard.

Your code should look something like

    var sSQL = "SELECT * FROM [Sheet1$A1:D14] WHERE currentDate = ?";
    cmd.Parameter.Add("@Param1", OleDbType.VarChar).Value = todaysDate;

For the full example of how the parametrization should be done have a look at OleDbCommand.Parameters Property and also for even more details on an example you can check my answer's example to this question Missing Required Parameter in Parameterized Query?

Trouble including a string in an SQL query for an Excel Spreadsheet

1 Answers1