How do I select specific cells from my excel spreadsheet for my GridView control.

You could change your query that selects the columns from the spreadsheet so that it only selects the ones you need.

Change this:

cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";

Select allOpen in new window

to something like this:

cmdExcel.CommandText = "SELECT [column1],[column3] From [" + SheetName + "]";

Select allOpen in new window

Where [column1], [column3] are the actual column names for the first and third columns.

So, it could look something like :

cmdExcel.CommandText = "SELECT [Name],[City] From [" + SheetName + "]";

Select allOpen in new window

@zephyr_hex: I knew this should be easier that I was making it. I am struggling with the syntax on how to get the cells / columns that I want. If you have an example that would be great. I'm still taking stabs at it right now.

Please note:  A cell is not a column.  A cell is the intersection of a row and a column.  So it's a little confusing when you use the two terms interchangeably.  I *think* you want to only include certain columns from your spreadsheet.

If that's the case, please tell us the column names you want.  It should be as simple as using those column names in your SELECT statement instead of SELECT *

If that's not the case and you truly do want only certain cells, you need to  tell us how you identify which rows to choose, as well as telling us the names of the columns.

@zephyr_hex: you're right, my mistake. I actually got it to work using the following:

cmdExcel.CommandText = "SELECT [" + SheetName + "].Gender From [" + SheetName + "]";

Select allOpen in new window

   
My problem is that some of the names include a ".". How do I account for that?

Thank you. Lastly: what about concatenating 2 columns?

Also, I thought I was getting an error message telling me that there was an improper use of brackets, when I try to bracket the column name as well. I will double check that.

@zephyr_hex: thank you for the concatenation and adding spacing, that worked. For some reason I'm still getting a funky error when the column name has a period (".") in it?

No value given for one or more required parameters.

See below.

Server Error in '/' Application.
No value given for one or more required parameters.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.

Source Error:


Line 98:         cmdExcel.CommandText = "SELECT [" + SheetName + "].StuNo, [" + SheetName + "].FirstName + ' ' + [" + SheetName + "].LastName AS StudentName, [" + SheetName + "].[Org#ID] FROM [" + SheetName + "]";
Line 99:         oda.SelectCommand = cmdExcel;
Line 100:        oda.Fill(dt);
Line 101:        connExcel.Close();
Line 102:


Source File: c:\Dev\QUPowerHours\LoadStudents.aspx.cs    Line: 100 

Select allOpen in new window

Are you sure that's what's causing the error?  To test, did you remove that column from your query statement?  It works when you don't include that column name?

@zephyr_hex: Yes, I'm pretty sure this is causing it. If I change the column name so that it doesn't have a period "." in it, it works fine. I only get an error when the column name has a period in it, and I put the "#" character in the column name. I've tried adding the "$" above as well.

Line 98:         cmdExcel.CommandText = "SELECT [" + SheetName + "].StuNo, [" + SheetName + "].FirstName + ' ' + [" + SheetName + "].LastName AS StudentName, [" + SheetName + "$].[Org#Desc] FROM [" + SheetName + "]";

Select allOpen in new window

without the "#" I get an: "Invalid bracketing of name..." error.

 Invalid bracketing of name '['August Report#csv$'$].[Org.Desc]'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Invalid bracketing of name '['August Report#csv$'$].[Org.Desc]'.

Source Error:

Line 98:         cmdExcel.CommandText = "SELECT [" + SheetName + "].StuNo, [" + SheetName + "].FirstName + ' ' + [" + SheetName + "].LastName AS StudentName, [" + SheetName + "$].[Org.Desc] FROM [" + SheetName + "]";
Line 99:         oda.SelectCommand = cmdExcel;
Line 100:        oda.Fill(dt);
Line 101:        connExcel.Close();
Line 102:

Select allOpen in new window

One way to debug it is to put a break point on line 100, and change the query back to SELECT *

Inspect the results of oda in debug mode to see how the column name is appearing.  Then use that name in your SELECT query.

@zephyr_hex: haha all it showed was: "Select * FROM ['August Report#csv$']. I laugh because I feel like I tried to trick it and it beat me! ha!

I tried this:

"SELECT [" + SheetName + "].Org#ID FROM [" + SheetName + "]"

Select allOpen in new window

When I put the "#" in, but I get this:

I get: "Syntax error in date in query expression '['August Report#csv$'].Org#ID'."

This is what the oda.SelectCommand text looks like:

SELECT ['August Report#csv$'].Org#ID FROM ['August Report#csv$']

Select allOpen in new window

@zephyr_hex: I figured it out: I needed to put the column name (with the "#" for the ".") in the qualifier like so:

"SELECT [" + SheetName + ".Org#ID] FROM [" + SheetName + "]";

Select allOpen in new window

yay!  glad you got it working.