re using a dataset

[ninjaimp]

hi

Im trying to rationalise my code as i feel i am doing the same thing a couple of times with different bits of code.

What i am doing is query a db and putting the result into a dataset and then filling a datagrid with this dataset..Fine. now i have another datagrid which is essentially querying the same db but for data for a day further ahead.

Code at the minute

Private Sub FillToday()
        Dim Searchds As New DataSet
        Dim Searchda As OleDb.OleDbDataAdapter

        sql = ""

        con.Open()
        sql = "SELECT tblStay.ArriveDate, tblStay.Room, tblCustData.CustName FROM tblCustData INNER JOIN tblStay ON tblCustData.CustID = tblStay.CustID WHERE tblStay.ArriveDate = #" & TodaysDate & "#"
        Command = New OleDbCommand(sql, con)
        Command.ExecuteNonQuery()
        Searchda = New OleDb.OleDbDataAdapter(sql, con)
        Searchda.Fill(Searchds)
        dgToday.DataSource = Searchds.Tables(0)
        con.Close()

    End Sub

    Private Sub FillTomorrow()
        Dim Searchds As New DataSet
        Dim Searchda As OleDb.OleDbDataAdapter

        sql = ""

        con.Open()
        sql = "SELECT tblStay.ArriveDate, tblStay.Room, tblCustData.CustName FROM tblCustData INNER JOIN tblStay ON tblCustData.CustID = tblStay.CustID WHERE tblStay.ArriveDate = #" & TomDate & "#"
        Command = New OleDbCommand(sql, con)
        Command.ExecuteNonQuery()
        Searchda = New OleDb.OleDbDataAdapter(sql, con)
        Searchda.Fill(Searchds)
        dgTom.DataSource = Searchds.Tables(0)
        con.Close()

    End Sub

but if i reference the same Dataset within one procedure both datagrids are filled with data from the second search, which of course makes sense but i would like to normalise my code and make it a bit more effiecent!

is there any way i can use a dataset, assign its data to the first datagrid and then re-use it and assign the next one to the second datagrid.

I know this is alot of information, but if anyone could point me in the right direction that would be much appreciated

Regards

 

[Paszt]

First, I would suggest using the DataGridView instead of the DataGrid control.
From the help documentation for the DataGrid control: "The DataGridView control replaces and adds functionality to the DataGrid control; however, the DataGrid control is retained for both backward compatibility and future use, if you choose."

And to answer your question:
First fill a DataTable with all of the data from the database table (or a subset that would suffice). Then create two DataViews from this DataTable and set the DataSource of your DataGridViews to those DataViews. You can then use the RowFilter property of the DataViews to filter records.

 

[ninjaimp]

this makes alot of sense - in short take a snapshot of the db, hold it locally and just apply different queries to it for each element i need to populate.

I will digest all the info you have provided and approach it differently

many thanks for your help

 

[cjard]

hi
I know this is alot of information, but if anyone could point me in the right direction that would be much appreciated

Regards

I would actually only end up pointing you in the direction youre already following.. "Creating a Form to Search Data"

I'd then tell you to either:
have your tableadapter set to ClearBeforeFill = True and refill the datatable for each date, or have CBF set false and fill for 2 dates, using Paszt's advice on filtering. It depends how often you'll be flicking between them vs how often other users will update them