Dataset question - need LOTS OF HELP

So you've basically got a single DataGrid on this page, right?

Then we get the data we need based on the OperatorID, and ShiftID.  The DataGrid will show records from DownTimeDetails.

I suppose you'll have a DateTimePicker to select the date and a ComboBox to select the Shift.

You'll have a "Next" button that increments to the next Shift.

Is the Query to the database the tricky part to this for you?

Or can you write the query fine-- it's just the DataSet and the DataGrid that you need help with?

Hmm... "design time databinding"

Are you completely commited to this particular technique?  I normally get the data to a DataSet... then bind to the DataGrid at run time.  The results I've gotten with the "Data Form Wizard" have not been helpful to me in the past.

If you need me to write a working example of this, I can do that.  "Real Code" is actually simpler to work with than the Wizard output.  Just verify to me what you want exactly and I'll cook it up for you.  (There are so many gotchas in this stuff, I personally think the example is the way to go-- as opposed to explaining each step.  The amount of trial and error it took me to get this working the first time-- ai yai yai!)

I don't use the wizard, just set the datasource to the controls.

I can write queries just fine. Just using the dataset and dataview properly is what I'm having trouble with.  I'll try and draw this out better.

Entry Date(dtp)        Shift(combo)  Operator (combo)  
------------------------------------------------------------------
tab page 1 | tab page 2
-------------------------------------------------------------
TAB PAGE 1
---------------------
The user enters a bunch of data throughout the day.  If the machine goes down, they'd click on Tab page 2

TAB PAGE 2
--------------

On selected page, I look at the current date and shift above the tab page, if there is no record in the downtime for that shift and day, I create it.  Use a temp view to search for the record,  If it's there, I get the downtimeid, and change my details view row filter to that id, and show the info on a grid.  This part works great.

But I want the user to be able to search through the downtime records, by date, shift  SO on this tab page, have navigation buttons that the user can move around.  This is the part I'm having trouble with.  

So here's what tab page 2 would look like:

Entrydate (Label)         Shiftname(Label)        Operator Name (Label)           Navigation buttons and save....

Grid with details.
-------------------------------------------------------------------------------------------------------------------------------
my first problem was getting the dataset binding context to point to the right position.  So I could use that to Navigate.
Then I wanted to use hidden combo boxes so that I can get the shiftname and operatorfullname, but when I'd bind them, I'd get an exception thrown. Don't really understand that one.
I have my select procedure ordering the records by date, shift.  

I kind of have it workin, but I have code all over the place. And I'm not even sure that it's working properly.  So I'm looking for an easier way... the proper way.  Because this can't be this Hard.

In VB6, you'd just have to call find on the recordset and you'd be done.  Why did MS have to make this so confusing! :)

Tigger

Go to this site and download my sample entry screen to see if this helps at all.

http://www.labwebdesign.com/sample/sample.zip

Let me know when you get it.

Tigger

I'm looking at it now...

It sounds like two seperate problems:

" I need to position the binding context on that day so that I know where to move from there."

Will being able to move the current row of the DataGrid solve this?  If so, check this link:

http://www.syncfusion.com/FAQ/WinForms/FAQ_c44c.asp#q895q



"How do I show the name and shift.  Can I do everything in a view?"

Would you like the name and shift to show up as combobox columns in the DataGrid solve this?  If so, check this link:

http://www.syncfusion.com/FAQ/WinForms/FAQ_c44c.asp#q480q


(Sorry if I've misunderstood somewhat.)

Ok, the grid is not the problem (details). I'm having trouble with the downtime part.  
Here's the simplest way to describe it.

I have a dataset -> with Downtime table which should be sorted the way I want

On click of tab page 2.  Find the record in the downtime table that has the same date and shift as above.... (from sample)
position the binding context to that row.
All my labels should be updated. (with just id numbers FOR NOW)
Update my row filter of dataview which is linked to my grid to the downtimeid of the found record.

If I can do this,
then I should be able to position the binding context of downtime and be able to move next and previous easily.

The user cannot ADD downtime records, just details.
The user cannot delete downtime records, just details
The user cannot edit downtime records, just details.

So it's basically a visual thing.

Once this works, then I want to be able to display shiftname and operator name instead of id's.

We'll take this one step at a time.

Tigger

Okay--

You've got your main table and your detail table.

Tab One is the data entry screen for the main table.  You are on a single record from the main table and can edit it all you want.

Tab Two is for the details.  When you flip to it, you check to see if there is any detail record-- if there's not any, you make one.

So far we're pretty straight forward.  If we don't try anything fancy, it shouldn't be to hard to implement this.  On both tabs, you are grabbing data for a dataset, talking to it, and then saving the data.

----
" Find the record in the downtime table that has the same date and shift as above.... (from sample)
position the binding context to that row."
----

In order to keep this as simple as possible, I suggest that everytime you look at a set of details, you just grab the ones you need from the database with a query.  That's easier to do than messing with binding contexts.  If they add or edit, you save the changes.  If not, you just pull in fresh data on the next set of records.

A general tip: coding dot.net in the same way that you would do things in the old DAO and ADO world is dangerous.  In general, you should avoid "recordset" type thinking.  ADO.Net is disconnected.  Instead of grabbing a few large chunks of data and doing lots of things to them, it is better in .Net to do lots of small transactions.

(Hope I'm getting closer to addressing your concerns...!)

"In VB6, you'd just have to call find on the recordset and you'd be done.  Why did MS have to make this so confusing! :)"

They did it this way in order to optimize things for _large_ enterprise type programs.

Recordsets require a continuous connection to a database-- that gets expensive as the user count goes up.  ADO.Net is designed to help encourage you to minimize the amount of time you're connected to the database.  

Close:

Tab 1 is for Entries - relating to their machines
Tab 2 is for Downtime - with details.  Has nothing to do with tab one.  The only thing that's kind of in common is the date that is entered, I want to pull up that date when they click the tab. Other than that it's on its own.

"In order to keep this as simple as possible, I suggest that everytime you look at a set of details, you just grab the ones you need from the database with a query.  That's easier to do than messing with binding contexts.  If they add or edit, you save the changes.  If not, you just pull in fresh data on the next set of records."

But if I did a query (using a dataview to show the main downtime info... I need to know what record comes before and next in the sequence.  So the sequence should go something like May 1st day, 1st Night, 2nd Day, 2nd Night.... etc.  So what would be the proper way to Navigate this?

Tigger

" So the sequence should go something like May 1st day, 1st Night, 2nd Day, 2nd Night.... etc.  So what would be the proper way to Navigate this?"

To get the query and the database to do the work, I suggest this way.

Make a table called tblShift with two fields, ShiftID and Shift.  Add two records: 1, "Day" and 2, "Night".

In tblStuff, you have a WorkDate column that's a date/time field and a ShiftID column.

To get the records in the correct order:

SELECT * FROM tblStuff ORDER BY WorkDate, ShiftID

Alternately, you could:

SELECT * FROM tblStuff WHERE WorkDate=[Your Parameter] ORDER BY ShiftID


You may still have some records out of order with this.  I presume tblStuff also has two columns called something like BeginDownTime and EndDownTime.  To accont for these simpply adjust the ORDER BY clause:

ORDER BY WorkDate, ShiftID, BeginDownTime

Or in the second example:

ORDER By ShiftID, BeginDownTime.


Hope this helps!

Ok.

Here's my select query: select * from downtime order by entrydate, shiftid

That's fine.

What I need to know is.  Once I fill the dataset, I need to be able to Find (move to) the current date I need.  Do I create a temporary datatable, or dataview?  
Then once I find this record, my bound labels should be showing the correct data.  
Then if the user clicks next or previous - be able to move from there.

So if my dataset is holding data like

Date     ShiftiD
May 1   1  *
May 1   2
May 2   1
May 2   2

At the start the binding context is where the star is

So if the current entry date is May 2 -  1
then the position should be

Date     ShiftiD
May 1   1  
May 1   2
May 2   1  *
May 2   2

Then if the user clicks previous:
May 1   1  
May 1   2 *
May 2   1  
May 2   2

I need to what and how to do this.  Like I said above, what do I need to create during run-time to accomplish this.  

Have you used the Row object yet?

The DataTable object is good for holding a relatively large amount of data that you will end up accessing randomly or doing lots a stuff with.

The DataView object is what you use to "query" your datatables.

Both of those objects have a rows collection that you access like this:

            Dim r As DataRow
            For Each r In MyDataTable.Rows
                MsgBox(r("MyField"))
            Next


"What I need to know is.  Once I fill the dataset, I need to be able to Find (move to) the current date I need.  Do I create a temporary datatable, or dataview?"

If you already have all the data in a DataTable, create a DataView that shows just the data you want to deal with.  If you haven't got the data or if you want to make sure you have fresh data, pull only the records you need directly from the database into a new DataTable.


"Then once I find this record, my bound labels should be showing the correct data.  
Then if the user clicks next or previous - be able to move from there."

In other words, you want a MoveFirst, MoveNext, MoveLast, MovePrevious, BOF, EOF, CurrentRow type interface on your DataView and DataTable?  I can write something like that pretty quick--  if you'd like me to, just say.

Please.  I need some kind of example.

Module Module1

    Sub Main()
        Dim dt As New FriendlyDataTable()

        AddStructure(dt)
        AddData(1, "Joe", dt)
        AddData(2, "Fred", dt)
        AddData(3, "Bob", dt)
        AddData(4, "Bill", dt)

        dt.MoveFirst()
        Console.WriteLine(dt.CurrentRow("ID") & " " & dt.CurrentRow("Name"))
        dt.MoveNext()
        Console.WriteLine(dt.CurrentRow("ID") & " " & dt.CurrentRow("Name"))
        dt.MoveNext()
        Console.WriteLine(dt.CurrentRow("ID") & " " & dt.CurrentRow("Name"))
        dt.MovePrevious()
        Console.WriteLine(dt.CurrentRow("ID") & " " & dt.CurrentRow("Name"))
        dt.MoveLast()
        Console.WriteLine(dt.CurrentRow("ID") & " " & dt.CurrentRow("Name"))
        dt.MoveFirst()
        Console.WriteLine(dt.CurrentRow("ID") & " " & dt.CurrentRow("Name"))

        Console.Read()
    End Sub

    Sub AddStructure(ByVal dt As DataTable)
        Dim c As New DataColumn("ID", GetType(Integer))
        dt.Columns.Add(c)
        Dim d As New DataColumn("Name", GetType(String))
        dt.Columns.Add(d)
    End Sub

    Sub AddData(ByVal ID As Integer, ByVal Name As String, ByVal dt As DataTable)
        Dim r As DataRow

        r = dt.NewRow

        r("ID") = ID
        r("Name") = Name

        dt.Rows.Add(r)
    End Sub
End Module

Public Class RowException
    Inherits Exception
End Class

Public Class FriendlyDataTable
    Inherits DataTable

    Private _Current As Integer

    Public ReadOnly Property BOF() As Boolean
        Get
            Return (_Current = -1)
        End Get
    End Property

    Public ReadOnly Property EOF() As Boolean
        Get
            Return (_Current = Me.Rows.Count + 1)
        End Get
    End Property

    Public ReadOnly Property CurrentRow() As DataRow
        Get
            If _Current < 0 Or _Current > (Me.Rows.Count - 1) Then
                Throw New RowException()
            Else
                Return Me.Rows(_Current)
            End If
        End Get
    End Property

    Public Sub MoveFirst()
        _Current = 0
    End Sub

    Public Sub MoveNext()
        _Current += 1
        If _Current > Me.Rows.Count Then
            _Current = Me.Rows.Count + 1
        End If
    End Sub

    Public Sub MovePrevious()
        _Current -= 1
        If _Current < 0 Then
            _Current = -1
        End If
    End Sub

    Public Sub MoveLast()
        _Current = Me.Rows.Count - 1
    End Sub
End Class

Ok I think we've gone way off topic.

Maybe some code will help:

My Dataset is filled like this: (order by date, shift)

Downtime ID            Date            Shift
1                            2004-02-25      1
4                            2004-03-13      2
3                            2004-03-14      1
2                            2004-03-14      2

On Click of tab page 2

***This code will not work but can give you an idea ****

filterstring = "entrydate='" & dtpEntryDate.Text & "' and shiftid=" & cmbShift.SelectedValue

dim mydataview as new dataview
mydataview = dsEntries.Tables("Downtime").defaultview

mydataview.Sort = "downtimeid"
i = downSearch.Find(3)           ' Since this re-sorts my view this doesn't return the proper value  

 Me.BindingContext(DsEntries, "downtime").Position = i


Something like that anyway.  I want to find the row position in the dataset for the record I need to find, then set the binding context to the specific position.

Does this clairify things?

I think I have figured it out, I will post code in a little bit.

Tigger

I will reward you the 500 points because you have put a lot of effort into helping me... here's what I did that works:

I call this function only once.  This way When the user clicks on the tab, I can get the current record I need:

Private Function GetPosition() As Integer
       
       Dim downSearch As New DataView

        Dim i As Integer

        downSearch = DsEntries.Tables("downtime").DefaultView
        ' Then find the position of that id in the view and reposition the binding context
        Dim vals(1) As Object
        Dim tempdate As Date
        tempdate = dtpEntryDate.Text
        vals(0) = tempdate
        vals(1) = cmbShift.SelectedValue
        downSearch.Sort = "entrydate,shiftid"
        'dataGrid1.DataSource = downSearch

        i = downSearch.Find(vals)
        GetPosition = i

    End Function

Then I can use the binding context position +/- 1 to scroll through the records.

The problem I was having was because I had created too many views based on the table, and it wasn't removing my row filter.  It was like the views were being combined together.  

Anyway, all is good now.

Thanks again for the help.

You're welcome!  :)

I wish I could have been quicker to pin down exactly what you were wanting to know.  I'm glad to know it's all good now.