Hi,
I have a large table. Normally, the user will need to see only the data
from the past two years, but sometimes, they will need to go back further.
I have a form with a datagrid, a DateTimePicker and a command button.
What I'd like is when the user selects a new date and clicks the button,
the grid would display data from the value in the datapicker through today.
I have it working, but only if I select everything in the data adapter,
then I fill a dataset with all the records (this takes 20-30 seconds),
then I create a dataview that is the datasource for the datagrid.
When the user picks a new date, I get the value and use the dataview's
rowfilter to update the range displayed in the datagrid.
This works well, but it is slow and I read here that it is best to work
with small sets of data and then update when necessary.
There must be a better way to somehow update the data adapter and then
update the dataset, dataview and datagrid but I can't seem to get it.
Can someone please give me some ideas to test out?
Thanks
Michael 9 3546
Micheal:
When the user selects a new date range, are you requerying for data that you
already have? If so, you are eating up a lot of resources for no benefit.
So if the date range is smaller for instance, or includes data that you
already have, you can just change your SQL Statement to get the new data
that you want and then just fill the existing datatable. Each time, you can
just use the Adapter to fill rows that you don't have. Since you are
binding to a dataview, you can filter there. So if you had rows 1-10000 and
needed 5000-15000.. .you could just querty the db for 10001 to 15000 filling
the datatable that's already filled, This will give you a table with 15000
records. Now in your grid, you can specify the rowfilter of the dataview to
show only what you want so you don't have to requery. If for instance the
next selection was rows 1-3000 you wouldn't even need to go to the db again.
Does this sound like a viable solution?
--
W.G. Ryan, MVP www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Michael" <no****@aol.com> wrote in message
news:eQ*************@TK2MSFTNGP10.phx.gbl... Hi,
I have a large table. Normally, the user will need to see only the data from the past two years, but sometimes, they will need to go back further.
I have a form with a datagrid, a DateTimePicker and a command button. What I'd like is when the user selects a new date and clicks the button, the grid would display data from the value in the datapicker through today.
I have it working, but only if I select everything in the data adapter, then I fill a dataset with all the records (this takes 20-30 seconds), then I create a dataview that is the datasource for the datagrid.
When the user picks a new date, I get the value and use the dataview's rowfilter to update the range displayed in the datagrid.
This works well, but it is slow and I read here that it is best to work with small sets of data and then update when necessary.
There must be a better way to somehow update the data adapter and then update the dataset, dataview and datagrid but I can't seem to get it.
Can someone please give me some ideas to test out?
Thanks
Michael
Michael,
It are users so you can not force them to do something except if you tell
that you will not return more than let say a page full of data from your
datagrid let say 30 rows.
You can do that using the Select Top with a Where clause http://msdn.microsoft.com/library/de...a-ses_9sfo.asp
When you can than use the data from your last selected row for the next
Where, than you can do paging through your database using buttons by storing
the proper data for that (by instance the data from the first row everytime)
Another method when you cannot get the exact starting points is of course
much easier by doing this paging date by date.
Just some idea's I hope they help,
Cor
Bill, Does this sound like a viable solution?
--
Bill,
I was reading full interest your solutions. However I see some problems. How
do you recoginize the gabs. If you give a "when" it will load all the rows
newly so you have to merge those. I don't think that it than will be a
solution however creates more overhead.
However, the biggest disadvantage from your solution is for me, that you
don't know if the already retrieved data in the datatable is complete.
Or do I oversee something?
Cor
Hi Ryan,
I like your solution, but I'm not sure how to implement 'changing you
sql command' part. I'm not sure how to use the data adapter and dataset
to update what is being displayed in the datagrid.
I thought I'd have to requery everything with the new ranges. You mean
I can add more rows to what is already in the dataset?
This is what I am doing now...
.....
sqlConn = New sqlConnection("...")
sqlCmd = New SqlCommand("Select * from SALES where date>'2002/1/1'",
sqlConn)
daSales = New SqlDataAdapter(sqlCmd)
daSales.Fill(ds, "SALES")
dvSales = New DataView(ds.Tables("SALES"))
dgSales.DataSource = dvSales
.....
'User selects a new date range below
d = DateTimePicker1.Value.Date 'gets date user selected
s = CStr(d)
daSales.SelectCommand.CommandText = "select * from SALES where date > " & s
daSales.Fill(ds, "SALES")
dvSales = New DataView(ds.Tables("SALES"))
dgSales.DataSource = dvSales
'Note this doesn't work :(
What does work is having this initial data adapter query ALL the rows,
then later use the dataview's rowfilter to narrow the range. The only
problem is that initial query of gettting ALL the rows can take 20-30sec
and it will only get worse as they enter more data. I thought it best
to limit the default to 2yrs of data and then if needed, I can requery
and get more rows, but I am not sure how to do this.
Thanks for you idea.
Michael
W.G. Ryan eMVP wrote: Micheal:
When the user selects a new date range, are you requerying for data that you already have? If so, you are eating up a lot of resources for no benefit. So if the date range is smaller for instance, or includes data that you already have, you can just change your SQL Statement to get the new data that you want and then just fill the existing datatable. Each time, you can just use the Adapter to fill rows that you don't have. Since you are binding to a dataview, you can filter there. So if you had rows 1-10000 and needed 5000-15000.. .you could just querty the db for 10001 to 15000 filling the datatable that's already filled, This will give you a table with 15000 records. Now in your grid, you can specify the rowfilter of the dataview to show only what you want so you don't have to requery. If for instance the next selection was rows 1-3000 you wouldn't even need to go to the db again.
Does this sound like a viable solution?
Hi Cor,
Yes, that gives me a few ideas to try out. My only problem is I am not
sure how to re-query a dataset. I thought I would update the sqlcommand
in the data adapter and then refill the dataset. I tried this but the
grid doesn't update.
Am I missing some refresh() method somewhere?
If someone could please show me the steps (data adapter -> dataset ->
dataview -> datagrid) I'd really appreciate it.
What I do now is: (in the form load event)
1) define a data adapter, where clause with the last two years data
2) data adapter fill method fills a dataset
3) create a dataview using the above dataset
4) have the datagrid's datasource set to the dataview above
This works, but I don't know how to update (or re-query) the rows I need
Thanks
Richard
Cor Ligthert wrote: Michael,
It are users so you can not force them to do something except if you tell that you will not return more than let say a page full of data from your datagrid let say 30 rows.
You can do that using the Select Top with a Where clause
http://msdn.microsoft.com/library/de...a-ses_9sfo.asp
When you can than use the data from your last selected row for the next Where, than you can do paging through your database using buttons by storing the proper data for that (by instance the data from the first row everytime)
Another method when you cannot get the exact starting points is of course much easier by doing this paging date by date.
Just some idea's I hope they help,
Cor
Michael, What I do now is: (in the form load event)
This sound strange for me, I assumed that a datatimepicker event did do
this.
And than clear the dataset everytime in advance.
And than another event with a button to let the user page back in future
until now.
Cor
Hi Cor,
Sorry for the mixup. I didn't want to cut and paste a lot of code.
I simplified my form so that it has just 3 controls:
- DateTimePicker (used to specify range of rows to show in datagrid)
- Button (when clicked, I'd like the datagrid rows updated)
- DataGrid (initially set with 2 years rows)
At the top of the form (below the 'Windows Form Designer generated
code'), I declared some public variables:
Dim sqlConn As New SqlConnection
Dim daSales As New SqlDataAdapter
Dim ds As New DataSet
Dim sqlCmd As New SqlCommand
Dim dvSales As New DataView
Then in the form load event, I defined the SqlConnection, the Data
Adapter (with select choosing the last two year's rows), the DataSet,
the DataView and then I set the DataGrid's source to the DataView.
sqlConn = New SqlConnection("server=...")
sqlCmd = New SqlCommand("Select * from SALES where date>'2003/1/1'",
sqlConn)
daSales = New SqlDataAdapter(sqlCmd)
daSales.Fill(ds, "SALES")
dvSales = New DataView(ds.Tables("SALES"))
dgSales.DataSource = dvSales
Later in the code, I have a button_click event that gets the value of a
DateTimePicker control that the user changes. It is here that I am
having trouble re-querying. If I use a DataView.rowfilter in this
event, it works fine, but I must have the data adapter select ALL (that
takes a long time) defined in the form load event.
' Button click event
Dim d As Date
Dim s As String
Dim strSql As String
d = DateTimePicker1.Value.Date
s = CStr(d)
' The commented lines below don't work?
'strSql = daSales.SelectCommand.CommandText
'daSales.SelectCommand.CommandText = "select * from SALES where date > " & s
'daSales.Fill(ds, "SALES")
'dvSales = New DataView(ds.Tables("SALES"))
'dgSales.DataSource = dvSales
dvSales.RowFilter = "date> '" & s & "'" '<- this works
I hope this makes it clearer what I am trying to do.
Richard
Cor Ligthert wrote: Michael,
What I do now is: (in the form load event)
This sound strange for me, I assumed that a datatimepicker event did do this. And than clear the dataset everytime in advance.
And than another event with a button to let the user page back in future until now.
Cor
Michael,
I made a sample how I would probably do your problem. I have not completly
tested yet however I thought that it was working as I expected and otherwise
it gives a good idea how you could do it.
\\\needs a datetimepicker, a button and a datagrid and the NorthWind
database
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Me.DateTimePicker1.Value = New DateTime(1996, 7, 1)
End Sub
Private Sub DateTimePicker1_ValueChanged(ByVal sender _
As System.Object, ByVal e As System.EventArgs) Handles
DateTimePicker1.ValueChanged
FillGrid(DateTimePicker1.Value)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
FillGrid(Nothing)
End Sub
Private Sub FillGrid(ByVal dt As DateTime)
Dim conn As New SqlClient.SqlConnection("Server=(Local);" & _
"DataBase=Northwind; Integrated Security=SSPI")
Static lastDate As DateTime = New DateTime(1996, 7, 1)
Dim ds As New DataSet
If dt = Nothing Then
dt = lastDate.AddDays(1)
End If
Dim da As New SqlClient.SqlDataAdapter _
("Select * from Orders Where orderdate = @orderdate", conn)
da.SelectCommand.Parameters.Add("@orderdate", CDate(dt))
da.Fill(ds)
Do While ds.Tables(0).Rows.Count = 0
dt = dt.AddDays(1)
da.SelectCommand.Parameters("@orderdate").Value = dt
da.Fill(ds)
If dt.Equals(New DateTime(1998, 6, 30)) Then
Exit Do
End If
Loop
DataGrid1.DataSource = Nothing
DataGrid1.DataSource = ds.Tables(0)
lastDate = dt
End Sub
///
I hope this helps a little bit?
Cor
Hi Cor,
Thanks so much for taking the time to show me how to do it. This helps
A LOT!
Your example also showed me how to do other things on my list such at
public functions and parameter passing to SQL select statements.
I copied your code into a new example and it finally changes the grid!
I will slowly go over everything and this will definitely teach me what
I need to know.
Thanks again!
Michael
Cor Ligthert wrote: Michael,
I made a sample how I would probably do your problem. I have not completly tested yet however I thought that it was working as I expected and otherwise it gives a good idea how you could do it.
\\\needs a datetimepicker, a button and a datagrid and the NorthWind database Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Me.DateTimePicker1.Value = New DateTime(1996, 7, 1) End Sub Private Sub DateTimePicker1_ValueChanged(ByVal sender _ As System.Object, ByVal e As System.EventArgs) Handles DateTimePicker1.ValueChanged FillGrid(DateTimePicker1.Value) End Sub Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click FillGrid(Nothing) End Sub Private Sub FillGrid(ByVal dt As DateTime) Dim conn As New SqlClient.SqlConnection("Server=(Local);" & _ "DataBase=Northwind; Integrated Security=SSPI") Static lastDate As DateTime = New DateTime(1996, 7, 1) Dim ds As New DataSet If dt = Nothing Then dt = lastDate.AddDays(1) End If Dim da As New SqlClient.SqlDataAdapter _ ("Select * from Orders Where orderdate = @orderdate", conn) da.SelectCommand.Parameters.Add("@orderdate", CDate(dt)) da.Fill(ds) Do While ds.Tables(0).Rows.Count = 0 dt = dt.AddDays(1) da.SelectCommand.Parameters("@orderdate").Value = dt da.Fill(ds) If dt.Equals(New DateTime(1998, 6, 30)) Then Exit Do End If Loop DataGrid1.DataSource = Nothing DataGrid1.DataSource = ds.Tables(0) lastDate = dt End Sub /// I hope this helps a little bit?
Cor
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Paulb1us |
last post by:
I want to update records from a csv file.
I do this in a button click:
//Create Adapters
da = new OdbcDataAdapter("Select * FROM test.csv", conn);
//Fill a data table
da.Fill(dt);
|
by: Wing |
last post by:
Hi all,
I have created 2 tables in sql database and join these 2 tables before
assign the result to the dataset, and display the result in datagrid.
Everything is fine up to this point. The...
|
by: gl |
last post by:
I'm currently planning out a project and I wanted to make sure it's easy to
do something (or at least possible). Basically we're going to have a grid
reporting off of dataset (with a relative...
|
by: Simon |
last post by:
Hi everyone,
I have a small problem regarding a wizard that I'm making on my website.
The wizard is obviously a series of pages that take values from the user. My
question is:
- Should I...
|
by: Jean Christophe Avard |
last post by:
Hi! I have a dataset that retreive all the item information from the
database. I need to be able to edit them, in the dataset and in the
database.
I have this code, could anyone tell me if I'm...
|
by: Adam |
last post by:
This is kind of a tough question, but, I'm working on a
program now and using sql data adapters with sql server to
generate data tables.
I'm updating the tables and then using the...
|
by: Stephen Plotnick |
last post by:
I'm very new to VB.NET 2003
Here is what I have accomplished:
MainSelectForm - Selects an item
In a public class I pass a DataViewRow to
ItemInformation1 Form
ItemInformation2 Form
|
by: Simon |
last post by:
Hi all,
Is it somehow possible to do do batched updates with table adapters in
the same way you can with data adapters?
I expected the methods to be much the same but apparently not. If it...
|
by: Miro |
last post by:
vb2005 express
(Im trying to figure out how to "Update databases manually" )
I created a screen, and have created a button.
I have a file that has 3 fields:
StaffID -Primary Key
Edit_User ...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |