Solved

Dataset question - need LOTS OF HELP

Posted on 2004-04-23
21
799 Views
Last Modified: 2010-04-24
I hope someone can help me because I am So LOST!

I'm using design time databinding ...etc.  
So I have a few tables I need to work with:

Downtime                  Shift             Operator                                   downtimedetails
-----------                 ---------         ------------                                  --------------------
downtimeid                shiftid            operatorid                                downdetailsid  (autonumber)
entrydate                  shiftname       operatorName                           downtimeid     (link to downtime table)
operatorid                                                                                     all other info
shiftid


I'm using a tab page. On tab page 1 The user enters records during the day/shift... so many entry's during a shift.  So during the day they are going to enter downtime information.
When the user selects the downtime tab, if a downtime record exists for a particular date and shift that they are currently entering, then I set my detail dataview   to the appropriate settings.If it doesn't exists it creates a record for that day and shift. Then I have a datagrid bound to my dataview to show the downtime details for the current day/shift they are entering.

However, I want the user wants to be able to manually go through the downtime records and see the different days and shifts details, I'm having trouble doing this part.

So on the second tab page I want to show Operator Name, Shift Name, Entry Date, as Labels ... and have the grid show the details.  When they click next they should be able to go from let's say May 1st Night shift to May 2nd day shift ... and so on.  

If someone can help me ASAP I'll even reward an extra 500 points.

TIA
Tigger996
0
Comment
Question by:Tigger996
  • 12
  • 9
21 Comments
 
LVL 2

Expert Comment

by:Jeffr0
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:Jeffr0
Comment Utility
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?
0
 
LVL 2

Expert Comment

by:Jeffr0
Comment Utility
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!)
0
 

Author Comment

by:Tigger996
Comment Utility
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
0
 
LVL 2

Accepted Solution

by:
Jeffr0 earned 500 total points
Comment Utility
"Then I wanted to use hidden combo boxes so that I can get the shiftname and operatorfullname"

Do you use DataViews?  Doesn't sound like it here-- if your using comboboxes to look things up like this.

Here's some code from one of my aps where I'm looking up the text that goes along with a Project ID:


    Public Function GetProjectName(ByVal ProjectID As Integer) As String
        Try
            Dim dv As DataView = New DataView(MyDataSet.Tables("tblProjects"))
            dv.RowFilter = "ProjectID = '" & ProjectID & "'"

            If dv.Count = 0 Then
                Return 0
            Else
                Return dv.Item(0)("Project")
            End If
        Catch
            Return 0
        End Try
    End Function
0
 

Author Comment

by:Tigger996
Comment Utility
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
0
 
LVL 2

Expert Comment

by:Jeffr0
Comment Utility
I'm looking at it now...
0
 
LVL 2

Expert Comment

by:Jeffr0
Comment Utility
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.)
0
 

Author Comment

by:Tigger996
Comment Utility
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


0
 
LVL 2

Expert Comment

by:Jeffr0
Comment Utility
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...!)

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Expert Comment

by:Jeffr0
Comment Utility
"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.  


0
 

Author Comment

by:Tigger996
Comment Utility
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
0
 
LVL 2

Expert Comment

by:Jeffr0
Comment Utility
" 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!
0
 

Author Comment

by:Tigger996
Comment Utility
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.  


0
 
LVL 2

Expert Comment

by:Jeffr0
Comment Utility
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.
0
 

Author Comment

by:Tigger996
Comment Utility
Please.  I need some kind of example.

0
 
LVL 2

Expert Comment

by:Jeffr0
Comment Utility
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
0
 

Author Comment

by:Tigger996
Comment Utility
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?

0
 

Author Comment

by:Tigger996
Comment Utility
I think I have figured it out, I will post code in a little bit.

Tigger
0
 

Author Comment

by:Tigger996
Comment Utility
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.
0
 
LVL 2

Expert Comment

by:Jeffr0
Comment Utility
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now