SQL query to select a range and display in datagrid

Question

0.00/5 (No votes)

See more:

I need to write an sql query (in c#) to select excel sheet data in "C" column starting from C19. But i cant specify the ending cell number because more data are getting added to the column. Hence i need to know how to specify the end of the column. Please help. Given below is the code that I'm using. But it doesn't display any value in the datagrid

C#

OleDbCommand ccmd = new OleDbCommand(@"Select * From [SPAT$]", conn);
OleDbDataAdapter da = new OleDbDataAdapter(ccmd);
DataTable dt = new DataTable();
da.Fill(dt)

for (int i = 19; i < dt.Rows.Count; i++)
{
    var value = dt.Rows[i]["C"].ToString();
    dataGridView1.DataSource = value;
    
}

Posted 16-Oct-12 23:39pm

hansikaat

Updated 16-Oct-12 23:53pm

Sushil Mate

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

VIPR@T · Answer 1 · 2012-10-16T23:47:00

Solution 1

Hi,

See the below links.

http://stackoverflow.com/questions/3814065/oledb-command-to-get-data-from-excel-sheet-uing-where-clause[^]
http://www.daniweb.com/software-development/csharp/threads/145027/excel-and-c[^]

you can use where condition with like and you can get only that records which you want.
like this :

C#

OleDbCommand ccmd = new OleDbCommand(@"Select * From [SPAT$] where [column Name] like 'C19*'", conn);

Thanks,
Viprat

Posted 16-Oct-12 23:47pm

VIPR@T

Comments

hansikaat 17-Oct-12 6:44am

Hi, thanks for the reply. What i need to do is to select all the data in the column C after C19 and insert them to a datagrid. I'm bit confused with the solution given to me. Could you please explain

VIPR@T 17-Oct-12 6:52am

you can save your desired records into one DataTable using this query. after that you can give that DataTable as datasource to gridview.

hansikaat · Answer 2 · 2012-10-18T21:59:00

OleDbCommand ccmd = new OleDbCommand(@"Select LEFT(F2,8) From [SPAT$]", conn);
OleDbDataAdapter adapter = new OleDbDataAdapter(ccmd);
DataTable Data = new DataTable();
adapter.Fill(Data);
for (int y = 16; y < Data.Rows.Count; y++)
{
var value = Data.Rows[y][0].ToString();
}
DataTable Oracle = Data.AsEnumerable().Where((row, index) => index >= 16).CopyToDataTable();
dataGridView1.DataSource = Oracle;