ptslv
asked on
How to load datagrid with only consecutive records
Hi everyone!
I have a ASP.NET website using C# in the code behind. I am using Access as a datasource. I am working with live data that is constantly changing. I am loading a grid with results from my query. I need to change my query or rather the way I am loading my datagrid so that only consecutive records load. If there is a break in the consecutive Entry_Order numbers of the record, I don't want anything beyond that point to load into my datagrid.
For example, this is my query result:
ID Thread MSEL Entry_Order
1 1 A. 1 1
2 1 A. 2 2
4 1 A.5 4
5 1 B.1 5
Since there is a break between Entry_Order 2 and 4, I only want to display records 1 and 2, not record 4 or 5. Can someone show me how to display only consecutive records?
I am currently using this to load my grid:
+++++++++++++
ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(strSQL,co n);
try
{
count=da.Fill(ds);
if(!bIsError)
{
if(count!=0)
{
DataGrid1.DataSource=ds;
DataGrid1.DataBind();
}
else
SendUserAMessage("There are no tasks for you at this time. Please recheck your tasks at a later time.");
if(con.State.ToString() == "Open")
{ con.Close(); }
}
}
Thanks. ptslv
I have a ASP.NET website using C# in the code behind. I am using Access as a datasource. I am working with live data that is constantly changing. I am loading a grid with results from my query. I need to change my query or rather the way I am loading my datagrid so that only consecutive records load. If there is a break in the consecutive Entry_Order numbers of the record, I don't want anything beyond that point to load into my datagrid.
For example, this is my query result:
ID Thread MSEL Entry_Order
1 1 A. 1 1
2 1 A. 2 2
4 1 A.5 4
5 1 B.1 5
Since there is a break between Entry_Order 2 and 4, I only want to display records 1 and 2, not record 4 or 5. Can someone show me how to display only consecutive records?
I am currently using this to load my grid:
+++++++++++++
ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(strSQL,co
try
{
count=da.Fill(ds);
if(!bIsError)
{
if(count!=0)
{
DataGrid1.DataSource=ds;
DataGrid1.DataBind();
}
else
SendUserAMessage("There are no tasks for you at this time. Please recheck your tasks at a later time.");
if(con.State.ToString() == "Open")
{ con.Close(); }
}
}
Thanks. ptslv
Fill a data table instead of a data set, create another data table, copy the records you want from your original data table to the new data table, then attach that data table to your data set (or simply create a view on the new data table, then bind that view to the data grid). That second suggestion would mean you have to implement the update/insert handlers yourself.
DataRow drow = siparisHareketDetayDataTab le.Rows[0] ;
int counter =(Convert.ToInt32(drow["En try_Order" ].ToString ()))
for (int i = 0; i < siparisHareketDetayDataTab le.Rows.Co unt; i++)
{
counter++;
DataRow drow = siparisHareketDetayDataTab le.Rows[i] ;
if (counter) == (Convert.ToInt32(drow["Ent ry_Order"] .ToString( )))
{
//add to datagrid(fill a dataset then bind) or a listview
ListViewItem lvi = new ListViewItem(drow["ID "].ToString());
lvi.SubItems.Add(drow["Thr ead "].ToString());
lvi.SubItems.Add(drow["MSE L "].ToString());
lvi.SubItems.Add(drow["Ent ry_Order"] .ToString( ));
myListview.Items.Add(lvi);
}
}
or in sql look the link below
http://www.sqlteam.com/item.asp?ItemID=1491
HTH,
Kosturdur
int counter =(Convert.ToInt32(drow["En
for (int i = 0; i < siparisHareketDetayDataTab
{
counter++;
DataRow drow = siparisHareketDetayDataTab
if (counter) == (Convert.ToInt32(drow["Ent
{
//add to datagrid(fill a dataset then bind) or a listview
ListViewItem lvi = new ListViewItem(drow["ID "].ToString());
lvi.SubItems.Add(drow["Thr
lvi.SubItems.Add(drow["MSE
lvi.SubItems.Add(drow["Ent
myListview.Items.Add(lvi);
}
}
or in sql look the link below
http://www.sqlteam.com/item.asp?ItemID=1491
HTH,
Kosturdur
ASKER
Thanks for the quick responses. I will try them and see what works best for me.
ptslv
ptslv
In http://www.sqlteam.com/item.asp?ItemID=1491
if you get the row number, you can make select top xxx sql statement.
Kosturdur
if you get the row number, you can make select top xxx sql statement.
Kosturdur
ASKER
kosturdur-
I have tried your method, but it does not limit the list to only records with consecutive Entry_Order numbers. Entire query results are showing.
I have tried setting up craskin's code but am getting errors; Here's what I have:
da.Fill(ds,dataTableName);
DataTable myDataTable = ds.Tables[dataTableName];
DataRow drow = myDataTable.Rows[0];
int i = 0;
while (i < myDataTable.Rows.Count -1)
{
if( myDataTable.Rows.Item["Ent ry_Order"] != i+1)
{
myDataTable.Rows.Remove();
}
else
i+=1;
}
myDataTable.Rows.Item["Ent ry_Order"] throws the error: System.Data.DataRow Collection does not contain a definition for 'Item'
myDataTable.Rows.Remove(); throws an error - invalid arguments and cannot convert from int to System.Data.DataRow
any suggestions?
ptslv
I have tried your method, but it does not limit the list to only records with consecutive Entry_Order numbers. Entire query results are showing.
I have tried setting up craskin's code but am getting errors; Here's what I have:
da.Fill(ds,dataTableName);
DataTable myDataTable = ds.Tables[dataTableName];
DataRow drow = myDataTable.Rows[0];
int i = 0;
while (i < myDataTable.Rows.Count -1)
{
if( myDataTable.Rows.Item["Ent
{
myDataTable.Rows.Remove();
}
else
i+=1;
}
myDataTable.Rows.Item["Ent
myDataTable.Rows.Remove();
any suggestions?
ptslv
if you don't want to see the non-consecutive don't add to the listview. Sorry but I couldn't understand what you really mean?
If your data is like;
1 2 4 5 you can only show 1-2
Could you write the code that you tried my solution?
------------
In craskin's code if you delete(remove) a row, I think you forgot to increase i.
Kosturdur
If your data is like;
1 2 4 5 you can only show 1-2
Could you write the code that you tried my solution?
------------
In craskin's code if you delete(remove) a row, I think you forgot to increase i.
Kosturdur
ASKER
Kosturdur-
This is what I have from your code, but the Listview stuff throws errors: I think I have too much stuff here but I am trying to weed it out. I had it working at one point to show all the rows but I broke it somewhere when I tried to remove the unwanted records:
string dataTableName = "UserTasks";
DataTable myDataTable = ds.Tables[dataTableName];
try
{
count=da.Fill(ds); // trying something different - still good
//************************ ********** ********** ********** ********** ********** **
//new datatable stuff added 4/9/07
DataRow drow = myDataTable.Rows[0];
int counter =(Convert.ToInt32(drow["En try_Order" ].ToString ()));
for (int i = 0; i < myDataTable.Rows.Count; i++)
{
counter++;
drow = myDataTable.Rows[i];
if ((counter) == (Convert.ToInt32(drow["Ent ry_Order"] .ToString( ))))
{
//add to datagrid(fill a dataset then bind) or a listview
ListViewItem lvi = new ListViewItem(drow["ID"].To String());
lvi.SubItems.Add(drow["Thr ead"].ToSt ring());
lvi.SubItems.Add(drow["MSE L"].ToStri ng());
lvi.SubItems.Add(drow["Ent ry_Order"] .ToString( ));
myListview.Items.Add(lvi);
}
}
if(!bIsError)
{
if(count!=0)
{
DataGrid1.DataSource=ds;
DataGrid1.DataBind();
}
else
SendUserAMessage("There are no tasks at this time. Please recheck your tasks at a later time.");
if(con.State.ToString() == "Open")
{con.Close();}
}
......the erst of the try/catch
ptslv
This is what I have from your code, but the Listview stuff throws errors: I think I have too much stuff here but I am trying to weed it out. I had it working at one point to show all the rows but I broke it somewhere when I tried to remove the unwanted records:
string dataTableName = "UserTasks";
DataTable myDataTable = ds.Tables[dataTableName];
try
{
count=da.Fill(ds); // trying something different - still good
//************************
//new datatable stuff added 4/9/07
DataRow drow = myDataTable.Rows[0];
int counter =(Convert.ToInt32(drow["En
for (int i = 0; i < myDataTable.Rows.Count; i++)
{
counter++;
drow = myDataTable.Rows[i];
if ((counter) == (Convert.ToInt32(drow["Ent
{
//add to datagrid(fill a dataset then bind) or a listview
ListViewItem lvi = new ListViewItem(drow["ID"].To
lvi.SubItems.Add(drow["Thr
lvi.SubItems.Add(drow["MSE
lvi.SubItems.Add(drow["Ent
myListview.Items.Add(lvi);
}
}
if(!bIsError)
{
if(count!=0)
{
DataGrid1.DataSource=ds;
DataGrid1.DataBind();
}
else
SendUserAMessage("There are no tasks at this time. Please recheck your tasks at a later time.");
if(con.State.ToString() == "Open")
{con.Close();}
}
......the erst of the try/catch
ptslv
hi ptslv,
--If your table is like that;
*********************Table crate SQL BEGINS******************** **********
/****** Object: Table [dbo].[UserTasks] Script Date: 04/10/2007 20:13:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserTasks](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Thread] [int] NOT NULL,
[MSEL] [nchar](10) COLLATE Turkish_CI_AS NOT NULL,
[Entry_Order] [int] NOT NULL,
CONSTRAINT [PK_UserTasks] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
*********************Table crate SQL ENDS********************** ********** *
--And your data is like;
*********************Data BEGINS******************** ********** ********** *****
ID Thread MSEL Entry_Order
1 1 A. 1 1
2 1 A. 2 2
4 1 A.5 4
5 1 B.1 5
*********************Data ENDS********************** ********** ********** *****
--The code below works;
--You must have a form with a button1 and datagridview1(do not set it's datasource just drop a datagridview) and a dataset2 of your table named UserTasks;
--On button1_Click event of button1 write this code.
************************CO DE BEGINS******************** ********** *******
private void button1_Click(object sender, EventArgs e)
{
DataSet2TableAdapters.User TasksTable Adapter da = new WindowsApplication4.DataSe t2TableAda pters.User TasksTable Adapter();
DataSet2.UserTasksDataTabl e dtUserTasks = new DataSet2.UserTasksDataTabl e();
da.Fill(dtUserTasks);
DataTable dt = dtUserTasks.Copy();
DataRow drow = dt.Rows[0];
int value = Convert.ToInt32(drow["Entr y_Order"]. ToString() );
bool consecutiveRecordFound = false;
int i = 1;
while (i < dt.Rows.Count)
{
drow = dt.Rows[i];
if (Convert.ToInt32(drow["Ent ry_Order"] .ToString( )) != value + 1)
{
if (consecutiveRecordFound)
{
dt.Rows[i].Delete();
}
else
{
value = Convert.ToInt32(drow["Entr y_Order"]. ToString() );
dt.Rows[i-1].Delete();
}
}
else
{
consecutiveRecordFound = true;
value = Convert.ToInt32(drow["Entr y_Order"]. ToString() );
}
i++;
}
if (consecutiveRecordFound)
{
dataGridView1.DataSource = dt;
}
else
{
SendUserAMessage("There are no tasks at this time. Please recheck your tasks at a later time.");
}
}
************************CO DE ENDS********************** ********** *****
The error In craskin's code if you delete(remove) a row, the row still there with no data that means you forgot to increase i.
Kosturdur
--If your table is like that;
*********************Table
/****** Object: Table [dbo].[UserTasks] Script Date: 04/10/2007 20:13:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserTasks](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Thread] [int] NOT NULL,
[MSEL] [nchar](10) COLLATE Turkish_CI_AS NOT NULL,
[Entry_Order] [int] NOT NULL,
CONSTRAINT [PK_UserTasks] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
*********************Table
--And your data is like;
*********************Data BEGINS********************
ID Thread MSEL Entry_Order
1 1 A. 1 1
2 1 A. 2 2
4 1 A.5 4
5 1 B.1 5
*********************Data ENDS**********************
--The code below works;
--You must have a form with a button1 and datagridview1(do not set it's datasource just drop a datagridview) and a dataset2 of your table named UserTasks;
--On button1_Click event of button1 write this code.
************************CO
private void button1_Click(object sender, EventArgs e)
{
DataSet2TableAdapters.User
DataSet2.UserTasksDataTabl
da.Fill(dtUserTasks);
DataTable dt = dtUserTasks.Copy();
DataRow drow = dt.Rows[0];
int value = Convert.ToInt32(drow["Entr
bool consecutiveRecordFound = false;
int i = 1;
while (i < dt.Rows.Count)
{
drow = dt.Rows[i];
if (Convert.ToInt32(drow["Ent
{
if (consecutiveRecordFound)
{
dt.Rows[i].Delete();
}
else
{
value = Convert.ToInt32(drow["Entr
dt.Rows[i-1].Delete();
}
}
else
{
consecutiveRecordFound = true;
value = Convert.ToInt32(drow["Entr
}
i++;
}
if (consecutiveRecordFound)
{
dataGridView1.DataSource = dt;
}
else
{
SendUserAMessage("There are no tasks at this time. Please recheck your tasks at a later time.");
}
}
************************CO
The error In craskin's code if you delete(remove) a row, the row still there with no data that means you forgot to increase i.
Kosturdur
ASKER
My table has more fields to it. Those are the major fields I look for in my query. I have them in an editable datagrid. My customer has users who are assigned tasks, and only wants the users to see consecutive tasks. For instance, if User1 has Entry_Orders 1,2,4,5, and User2 has Entry_Order 5, User1 can only view and do the first 2 tasks. Then User 2 does his task. Then User1 goes back in and sees his next 2 tasks and completes them. That is the cycle. There may be tasks in between which are assigned to a different user, and that task has to be completed before the next can be done.
So the code I need has to be able to work with an editable datagrid.
Thanks. ptslv
So the code I need has to be able to work with an editable datagrid.
Thanks. ptslv
ASKER
By the way, this is an ASP.NET web application, not a Windows application. Your code is throwing a bunch of errors!
ptslv
ptslv
ASKER
Kosturdur-
I adjusted your code and ran it but only the first record loaded into the grid. This is my code now:
OleDbConnection con = new OleDbConnection(strConnect ion2);
strSQL = "Select.....";
OleDbDataAdapter da2 = new OleDbDataAdapter(strSQL,co n);
ds2 = new DataSet();
DataTable dtUserTasks = new DataTable();
da2.Fill(dtUserTasks);
DataTable dt = dtUserTasks.Copy();
DataRow drow = dt.Rows[0];
int value = Convert.ToInt32(drow["Entr y_Order"]. ToString() );
bool consecutiveRecordFound = false;
int i = 1;
while (i < dt.Rows.Count)
{
drow = dt.Rows[i];
if (Convert.ToInt32(drow["Ent ry_Order"] .ToString( )) != value + 1)
{
if (consecutiveRecordFound)
{
dt.Rows[i].Delete();
}
else
{
value = Convert.ToInt32(drow["Entr y_Order"]. ToString() );
dt.Rows[i-1].Delete();
}
}
else
{
consecutiveRecordFound = true;
value = Convert.ToInt32(drow["Entr y_Order"]. ToString() );
}
i++;
}
if (consecutiveRecordFound)
{
DataGrid1.DataSource = dt;
}
else
{
SendUserAMessage("There are no tasks at this time. Please recheck your tasks at a later time.");
}
ptslv
I adjusted your code and ran it but only the first record loaded into the grid. This is my code now:
OleDbConnection con = new OleDbConnection(strConnect
strSQL = "Select.....";
OleDbDataAdapter da2 = new OleDbDataAdapter(strSQL,co
ds2 = new DataSet();
DataTable dtUserTasks = new DataTable();
da2.Fill(dtUserTasks);
DataTable dt = dtUserTasks.Copy();
DataRow drow = dt.Rows[0];
int value = Convert.ToInt32(drow["Entr
bool consecutiveRecordFound = false;
int i = 1;
while (i < dt.Rows.Count)
{
drow = dt.Rows[i];
if (Convert.ToInt32(drow["Ent
{
if (consecutiveRecordFound)
{
dt.Rows[i].Delete();
}
else
{
value = Convert.ToInt32(drow["Entr
dt.Rows[i-1].Delete();
}
}
else
{
consecutiveRecordFound = true;
value = Convert.ToInt32(drow["Entr
}
i++;
}
if (consecutiveRecordFound)
{
DataGrid1.DataSource = dt;
}
else
{
SendUserAMessage("There are no tasks at this time. Please recheck your tasks at a later time.");
}
ptslv
I tried my code and worked for windows application. The code looks ok so, maybe the data is wrong. And maybe the connections aren't ok. Why don't you use typed datasets in ASP.Net. Please looked the link below http://www.asp.net/learn/dataaccess/default.aspx?tabid=63 and find the "Creating a Data Access Layer" part http://www.asp.net/learn/dataaccess/tutorial01vb.aspx?tabid=63.
Neverthless, I want to try your code with typed dataset in ASP.Net.
Kosturdur
Neverthless, I want to try your code with typed dataset in ASP.Net.
Kosturdur
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am still working on this - it was pushed to the back burner! I will comment when I have a chance! Thanks.
ptslv
ptslv
I think I gived the right answer for both windows and web application. I also tried the codes and both are working.
after you fill your dataset (though you should just use a datatable to save memory, loop through the records and remove the extra rows
Dim i as Integer = 0
Do while i < myDataTable.Rows.Count -1
If myDataTable.Rows(i).Item("
myDataTable.Rows(i).Remove
Else
i+= 1
End If
Loop
something like that should work.