Datagrid.Select Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there

I have a requirement to filter off 5 records from a datatable in a dataset.
I have a SQL procedure that retrieves a large lump of data from a database -
I then do whatever I'm doing with that data. I have a seperate DataList on my
aspx page that should contain summary details of the latest 5 records
retrieved in the main data pull.

I don't want to re-connect to the SQL database - but am struggling to use
DataView when all I have to work on is a Date field - so the DataList should
show the 5 most recent records - from what I can tell, my filter statement
can only be relatively simple - not "Select TOP 5 Records from X where A='B'"
etc. etc - I don't think this is designed for the purpose, but please tell me
otherwise if I'm wrong.

This leaves me with Datagrid.Select which I have not used before - should I
be able to use a "proper" select Statement ("Select TOP5....") - if so, how
would I then copy this to a new datatable so I can bind my DataList to it ?

I suppose an alternative would be to limit the number of records my DataList
can display... but again, if there is a way to do this, I can't find out
how...

Any thoughs / help is much appreciated

Stuart
 
Hi Stuart,

Although, you can’t use ‘select top records from …’ against a datatable,
there is some way to work around it. Following code snippet shows how to do
it.

DataTable myTable = new DataTable();
SqlDataAdapter dap = new SqlDataAdapter(“SELECT DocDate, other_fields FROM
table_name WHERE CONDITIONâ€, CONNECTION_STRING);
Dap.Fill(myTable);
DataView dv = myTable.DefaultView;
dv.Sort = “DocDate DESCâ€;
// suppose you want to top 10 records
string strBorderDate = ((DateTime)dv[9][“DocDateâ€]).ToString();
dv.RowFilter = “DocDate <=’†+ strBorderDate + “’â€;
// In dv there are should be 10 records.


HTH

Elton Wang
(e-mail address removed)
 
Hi Elton

Thanks very much for taking the time to throw some light on this...

You'll excuse my inability to jump between C# and VB - but could you explain
the filter string in a little more depth for me / provide the VB equivalent.

Thanks again

Elton W said:
Hi Stuart,

Although, you can’t use ‘select top records from …’ against a datatable,
there is some way to work around it. Following code snippet shows how to do
it.

DataTable myTable = new DataTable();
SqlDataAdapter dap = new SqlDataAdapter(“SELECT DocDate, other_fields FROM
table_name WHERE CONDITIONâ€, CONNECTION_STRING);
Dap.Fill(myTable);
DataView dv = myTable.DefaultView;
dv.Sort = “DocDate DESCâ€;
// suppose you want to top 10 records
string strBorderDate = ((DateTime)dv[9][“DocDateâ€]).ToString();
dv.RowFilter = “DocDate <=’†+ strBorderDate + “’â€;
// In dv there are should be 10 records.


HTH

Elton Wang
(e-mail address removed)

Stuart said:
Hi there

I have a requirement to filter off 5 records from a datatable in a dataset.
I have a SQL procedure that retrieves a large lump of data from a database -
I then do whatever I'm doing with that data. I have a seperate DataList on my
aspx page that should contain summary details of the latest 5 records
retrieved in the main data pull.

I don't want to re-connect to the SQL database - but am struggling to use
DataView when all I have to work on is a Date field - so the DataList should
show the 5 most recent records - from what I can tell, my filter statement
can only be relatively simple - not "Select TOP 5 Records from X where A='B'"
etc. etc - I don't think this is designed for the purpose, but please tell me
otherwise if I'm wrong.

This leaves me with Datagrid.Select which I have not used before - should I
be able to use a "proper" select Statement ("Select TOP5....") - if so, how
would I then copy this to a new datatable so I can bind my DataList to it ?

I suppose an alternative would be to limit the number of records my DataList
can display... but again, if there is a way to do this, I can't find out
how...

Any thoughs / help is much appreciated

Stuart
Click to expand...
Click to expand...
 
Hi Stuart,

The basic logic is like following sql query:
SELECT * FROM TABLE_NAME WHERE DocDate >= 'DocDate of 10th record' ORDER BY
DocDate DESC

In DataView, RowFilter property works as Condition (without WHERE) and Sort
property works as ORDER BY.

VB code as follows:
Dim myTable As New DataTable()
Dim dap As new SqlDataAdapter(“SELECT DocDate, other_fields FROM table_name
WHERE CONDITIONâ€, CONNECTION_STRING)
Dap.Fill(myTable)
Dim dv As DataView = myTable.DefaultView
dv.Sort = "DocDate DESC"
Dim strBorderDate As String = CType(dv(9)("DocDate"), Date).ToString
dv.RowFilter = “DocDate >='" + strBorderDate + "'"

HTH

Elton

Stuart said:
Hi Elton

Thanks very much for taking the time to throw some light on this...

You'll excuse my inability to jump between C# and VB - but could you explain
the filter string in a little more depth for me / provide the VB equivalent.

Thanks again

Elton W said:
Hi Stuart,

Although, you can’t use ‘select top records from …’ against a datatable,
there is some way to work around it. Following code snippet shows how to do
it.

DataTable myTable = new DataTable();
SqlDataAdapter dap = new SqlDataAdapter(“SELECT DocDate, other_fields FROM
table_name WHERE CONDITIONâ€, CONNECTION_STRING);
Dap.Fill(myTable);
DataView dv = myTable.DefaultView;
dv.Sort = “DocDate DESCâ€;
// suppose you want to top 10 records
string strBorderDate = ((DateTime)dv[9][“DocDateâ€]).ToString();
dv.RowFilter = “DocDate <=’†+ strBorderDate + “’â€;
// In dv there are should be 10 records.


HTH

Elton Wang
(e-mail address removed)

Stuart said:
Hi there

I have a requirement to filter off 5 records from a datatable in a dataset.
I have a SQL procedure that retrieves a large lump of data from a database -
I then do whatever I'm doing with that data. I have a seperate DataList on my
aspx page that should contain summary details of the latest 5 records
retrieved in the main data pull.

I don't want to re-connect to the SQL database - but am struggling to use
DataView when all I have to work on is a Date field - so the DataList should
show the 5 most recent records - from what I can tell, my filter statement
can only be relatively simple - not "Select TOP 5 Records from X where A='B'"
etc. etc - I don't think this is designed for the purpose, but please tell me
otherwise if I'm wrong.

This leaves me with Datagrid.Select which I have not used before - should I
be able to use a "proper" select Statement ("Select TOP5....") - if so, how
would I then copy this to a new datatable so I can bind my DataList to it ?

I suppose an alternative would be to limit the number of records my DataList
can display... but again, if there is a way to do this, I can't find out
how...

Any thoughs / help is much appreciated

Stuart
Click to expand...
Click to expand...
Click to expand...
 
Back
Top