Link to home
Start Free TrialLog in
Avatar of David Megnin
David MegninFlag for United States of America

asked on

How to refresh a DataGridView after database Update.

How do you refresh a DataGridView with the current contents of the database after you do an UPDATE?


Private Sub btnUpdatePosition_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdatePosition.Click
        Me.Validate()
        Me.ApplicantsBindingSource.EndEdit()
        Dim rowsAffected As Integer = 0
        rowsAffected = Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        MessageBox.Show(rowsAffected.ToString + " Rows were affected")

        Dim con As New SqlConnection
        Dim cmd As SqlCommand = New SqlCommand("dbo.spUpdate_JobTitles_PositionsAvailable", con)
        con.ConnectionString = "workingConnectionString"
        cmd.Connection = con
        cmd.CommandType = CommandType.StoredProcedure
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()

This button Updates the database, but the changes are not reflected in the DataGridView, JobTitlesDataGridView, until I exit the application and run it again.
I've searched the web for nearly a week now and none of the solutions work.  Most give me an error  right from the beginning.  I'm using VS2005, VB.NET, SQL Server 2000.

The DataSource for the DataGridView is JobTitlesBindingSource
Avatar of Sancler
Sancler

Although (I assume) your ExecuteNonQuery with dbo.spUpdate_JobTitles_PositionsAvailable will be updating the database table, that update will have no automatic effect on the appplication's local datatable which is the ultimate datasource (via JobTitlesBindingSource) for your JobTitlesDataGridView.  That datatable was (again, I assume) filled by some sort of .Fill command from a tableadapter or dataadapter originally and it will remain like that unless and until you make changes to it in the application or you use the original .Fill command on it again.

Roger
Avatar of David Megnin

ASKER

...and how would I do that?  ...I'm just learning.

Maybe I'm approaching the problem wrong to begin with.

What I'm trying to do is to allow a manager to assign Applicants to JobTitles.

I have a table for JobTitles and another table for the Applicants.  Each JobTitle can have more than one Applicant.  There is PK_JobTitleID in tblJobTitles and FK_JobTitleID in tblApplicants.

It should be a simple matter, but I started out with the two DataGridViews and have not gotten anywhere.
Given that your JobTitlesDataGridView is updated when you "exit the application and run it again", there must be some code which runs on your application's start up and properly gets the data to fill the datagridview.  My guess (based just on the terminology in the bits of code you show) is that in your form_load sub there will be something like

    Me.JobTitlesTableAdapter.Fill(...

with the name of, or a reference to, a datatable where I've put ...

If you copy that code (NB "copy" - that is, leave the original where it is but put a duplicate) at the end of your btnUpdatePosition_Click sub, it might solve the problem.

But I stress that that's a guess.  It's very difficult to know for certain what's going on.  For example, I've no real idea - I've only made an assumption - what your stored procedure is doing.

Roger
Thank you, Roger.

Yes. Here is my form_load sub:
        Me.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.JobTitles)

        Dim cnString As String = "UID=dmegnin;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SYEP2007;Data Source=BETASERVE;Packet Size=4096;"
        Dim cn As SqlConnection = Nothing
        Dim cmd As SqlCommand = Nothing

            cn = New SqlConnection(cnString)
            cn.Open()
            Dim cmd1 As New SqlCommand("SELECT COUNT(*) FROM Applicants", cn)
            Dim count As Integer = Convert.ToInt32(cmd1.ExecuteScalar())
            txtTotalApplicants.Text = String.Format("{0} Applicants", count)
            cn.Close()


Here is my StoredProcedure:
UPDATE    JobTitles
SET              PositionsAvailable =
                          (SELECT     JobTitles_1.txtWorksiteJobTitlePositionsNumber - COUNT(Applicants.keyApplicantID) AS Expr1
                            FROM          Applicants INNER JOIN
                                                   JobTitles AS JobTitles_1 ON JobTitles_1.keyJobTitleID = Applicants.keyJobTitleID
                            WHERE      (JobTitles.keyJobTitleID = JobTitles_1.keyJobTitleID)
                            GROUP BY JobTitles_1.txtWorksiteJobTitlePositionsNumber, JobTitles_1.keyJobTitleID)



My "Update Data" Button:
    Private Sub btnUpdatePosition_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdatePosition.Click
        Me.Validate()
        Me.ApplicantsBindingSource.EndEdit()
        Dim rowsAffected As Integer = 0
        rowsAffected = Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        MessageBox.Show(rowsAffected.ToString + " Rows were affected")

        Dim con As New SqlConnection
        Dim cmd As SqlCommand = New SqlCommand("dbo.spUpdate_JobTitles_PositionsAvailable", con)
        con.ConnectionString = "...my connection string..."
        cmd.Connection = con
        cmd.CommandType = CommandType.StoredProcedure
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
        'JobTitlesBindingSource.ResetBindings(False)  'Doesn't update the DGV



...and here's trying to keep track of how many possitions are still available:
    Private Sub JobTitlesDataGridView_SelectionChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles JobTitlesDataGridView.CellEnter
        'txtAvailable.Text = CStr(cbFindJobTitle.SelectedValue)
        Dim con As New SqlConnection
        Dim cmd2 As New SqlCommand
        Dim cmd3 As New SqlCommand
        Dim cmd4 As New SqlCommand
        Dim available As Integer
        Dim taken As Integer
        Dim total As Integer
        Dim varJobTitleID As String
        If JobTitlesDataGridView.SelectedRows.Count > 0 Then
            varJobTitleID = JobTitlesDataGridView.SelectedRows(0).Cells(5).Value.ToString

            con.ConnectionString = "UID=dmegnin;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SYEP2007;Data Source=BETASERVE;Packet Size=4096;"
            cmd2.Connection = con ' Available
            cmd2.CommandText = "SELECT JobTitles.txtWorksiteJobTitlePositionsNumber - COUNT(Applicants.keyApplicantID) AS availablePositons FROM JobTitles INNER JOIN  Applicants ON JobTitles.keyJobTitleID = Applicants.keyJobTitleID WHERE (JobTitles.keyJobTitleID = '" & varJobTitleID & "') GROUP BY JobTitles.txtWorksiteJobTitlePositionsNumber"
            cmd3.Connection = con ' Taken
            cmd3.CommandText = "SELECT COUNT(Applicants.keyApplicantID) AS Expr1 FROM Applicants INNER JOIN JobTitles ON JobTitles.keyJobTitleID = Applicants.keyJobTitleID WHERE (JobTitles.keyJobTitleID = '" & varJobTitleID & "')"
            cmd4.Connection = con ' Total
            cmd4.CommandText = "SELECT SUM(JobTitles.txtWorksiteJobTitlePositionsNumber) AS Expr1 FROM JobTitles  WHERE (JobTitles.keyJobTitleID = '" & varJobTitleID & "')"
            con.Open()
            available = CInt(cmd2.ExecuteScalar)
            taken = CInt(cmd3.ExecuteScalar)
            total = CInt(cmd4.ExecuteScalar)
            con.Close()
            txtAvailable.Text = available.ToString
            txtTaken.Text = taken.ToString
            txtTotal.Text = total.ToString
        End If
    End Sub


...and here's trying to keep the selected row on the JobTitles DGV matching the selected row of the Applicants DGV so you can tell who's already assigned to where:
    Private Sub ComboBox3_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox3.SelectedIndexChanged
        Dim con As New SqlConnection
        Dim cmd1 As New SqlCommand
        Dim total As Integer
        Dim SelectedMonitor As String
        If WorksitesDataGridView.SelectedRows.Count > 0 Then
            SelectedMonitor = WorksitesDataGridView.SelectedRows(0).Cells(0).Value.ToString

            con.ConnectionString = "... my connection string..."
            cmd1.Connection = con
            cmd1.CommandText = "SELECT SUM(TotalPositions) FROM Worksites WHERE keyMonitorID='" & SelectedMonitor & "'"

            con.Open()
            total = CInt(cmd1.ExecuteScalar)
            con.Close()
            txtPositions.Text = total.ToString
        End If

    End Sub
    End Sub




If you can help me with any of this I would really appreciate it.  I don't really know what I'm doing.  I've tried to piece things together but from what I'm finding this may not be the best way to do what I'm trying to do.

Thanks again.
David
David

It'll be a few hours before I can look at all that.  Someone else might pick it up in the meantime.  If not, I'll come back to it.  So hang in there.

Roger
You can do it either

1) move a code that loads data in Form_Load event to a procedure, and call that procedure from Form_load event and also in Update event to reload data after the update

2) If I understood you correctly (which may not be true, since the code is quite messy), when you update applicants table, you need to refresh a position field in JobTitles table. I still dont understand how this application works, and what is expected from it, and what is a database structure, but you could perhaps udate the position field everytime a applicant is added/removed, which will bring an up-to-date JobTitles datatable. When updating you can call Update on both dataadapters, and no need for dbo.spUpdate_JobTitles_PositionsAvailable sp, cine the postion is maintained regularely

If you didnt understand what I said here, a better explanation and more data will be needed from you, so we can help you.

Goran
David

Is this app operating with the database on a single-user or a multi-user basis?  That is, is it essential that the app keeps going back to the database to refresh data - both ways - so that multi-users can be kept informed of changes that other users are making: or would it be possible, because only one instance of the app would be using the database at any one time, to bring everything necessary over from the database to the app at the start, make all changes in the app, and then send all changes back to the database at the end?

There are, in the code you've posted, pointers both ways.  But the fact that your btnUpdatePosition_Click sub envisages the possibility of a NUMBER of Applicant records being updated at one time makes me feel that a disconnected - do-it-all-in-the-app-then-save - approach might be possible.  If so, it would probably be neater and easier to code.  Indeed, doing it that way may be better even in a multi-user setting: just doing updates to the database more frequently.

Let me illustrate what I mean: with acknowledgments to Goran, as it is the same point as he has made.  You are obviously bringing data over from both your JobTitles and Applicants tables at the start of the app.  It may therefore be (we don't know this for certain) that, when you've made a change to the Applicants datatable in your app, you already have IN YOUR APP sufficient information to make any necessary changes to the JobTitles datatable IN YOUR APP.  If that were to be the case then there would be no reason to use ExecuteNonQuery on a stored procedure to update the JobTitles table in your database just so's you could bring the revised JobTitles table back to the app again.  If you make the necessary changes in the datatable in your app, that will automatically be reflected in your JobTitlesDataGridView.  And, when the time comes, you can pass the changes back to the database with a Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles) call.

I stress that that is just what it says - an illustration of an alternative approach that you may like to consider.  But if that would be viable, and appeals, you may be able also to gather the other information that you want for your txtAvailable, txtTaken and txtTotal controls from data that is already held in your app.  It looks almost certain, for instance, that you could get rid of this additional call to the database in your form_load sub

        Dim cnString As String = "UID=dmegnin;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SYEP2007;Data Source=BETASERVE;Packet Size=4096;"
        Dim cn As SqlConnection = Nothing
        Dim cmd As SqlCommand = Nothing

            cn = New SqlConnection(cnString)
            cn.Open()
            Dim cmd1 As New SqlCommand("SELECT COUNT(*) FROM Applicants", cn)
            Dim count As Integer = Convert.ToInt32(cmd1.ExecuteScalar())
            txtTotalApplicants.Text = String.Format("{0} Applicants", count)
            cn.Close()

If I have understood correctly what that is doing, it is asking the database for information which your app already knows.  Having just filled the Applicants datatable with

       Me.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)

the number of applicants will be the number of rows in that datatable.  Won't it?  ;-)

Have a think about the general theme we've discussed above.  If you want to pursue it, and find difficulty doing so, come back.  But as Goran says, a more detailed explanation would be useful.  Even though the picture has become a bit clearer with the extra code you have now posted, it's very difficult for us to work back from snippets of code to what the full story is likely to be.

Roger  
Roger,
Thank you!
A single manager will be making Applicant to JobTitle assignments.  No multi-user access is needed.

Yes, I'm afraid my code is very "mess".  This is actually my very first coding project since taking a 5 day ASP.NET class and a job as a "developer".  So if you want to throw in any other pointers about how I should "clean up" my code, please feel free.

Those snips are pretty much my complete Form1.vb code.

The overall goal is to take the kids in the Applicants table and assign them to jobs in the JobTitles table.  I'm using JobTitles because each Worksite may have multiple Jobs, each having multiple individual positions available.

The table JobTitles contains all the Jobs and a number of available positions for each as:
tblJobTitles(PK_JobTitleID, JobName, intAvailablePositions) = (123456, Clerk, 5)  ...I hope this makes since.

The Applicants table has the kids and a foreign key field for the JobTitle ID to go in:
tblApplicants(PK_ApplicantID, FK_JobTitleID, FName, etc.) = (83838383, 123456, Bob, etc.)

Now JobTitle 123456 only has 4 of the original 5 positions available.

Form1 has two DataGridViews: ApplicantsDataGridView and JobTitlesDataGridView.

I chose the DGVs because they show multiple rows on the form so the manager can see half a dozen or so kids and jobs at once.  He is trying to assign jobs to kids based on location city and a preference they have indicated and is a field represented in the table.

If all the selecting and calculating can be done in the local copy of the data and saved back to the database with a button click, whenever, then that is fine.  I chose everything I've done so far based on zero experience and no examples to go on.  My strategy and methods are the guesses of a complete beginner.

If the application itself would be clearer, tell me what files from my Solution/Project to send and I will gladly do so.  I'm using Visual Studio 2005 and VB.  If need be I can try to figure out how to make a "CREATE TABLE" query to create my database structure.

I'll study the instructions in your post and see if I can eliminate some of the unnecessary code.  I've learned more from your post than I have from two days of Google searches.

Thank you very much!
-David
No, don't send any of your application's files - at the moment, anyway.  But can you please amplify a couple of points.

1)  how does the Worksite fit into the scheme of things?  There's obviously a Worksite table in the database, which includes keyMonitorID and TotalPositions fields.  It looks like there's also a WorksitesDataGridView on your form.  But that doesn't tie in with your most recent statement that

>>
Form1 has two DataGridViews: ApplicantsDataGridView and JobTitlesDataGridView.
<<

I also notice you refer to that the WorksitesDataGridView in a combobox's SelectedIndexChanged sub - so I'm wondering how the datagridview and the combobox relate to each other.  Are the specific jobs (keyJobTitleID) in the JobTitles table linked to the Worksites table?  And if so, how?

2)  the impression that I get is that, with the database structure you describe, you could have a number of different applicants referencing the same FK_JobTitleID.  So you say

>>
The Applicants table has the kids and a foreign key field for the JobTitle ID to go in:
tblApplicants(PK_ApplicantID, FK_JobTitleID, FName, etc.) = (83838383, 123456, Bob, etc.)

Now JobTitle 123456 only has 4 of the original 5 positions available.
<<

My impression is that if one of the remaining 4 positions was now given to another applicant we could also have

tblApplicants(PK_ApplicantID, FK_JobTitleID, FName, etc.) = (93939393, 123456, Joe, etc.)

That is, two applicant records with the same reference 123456 in FK_JobTitleID, with no way of knowing which of the five original positions Bob was in and which Joe was in.  I appreciate that there may be no real difference between any of the five original positions, so it might not matter in practice "which of the five original positions Bob was in and which Joe was in".  But whether we do or do not know that could have implications in database and application design terms.  So could you clarify that point, too, please.

Roger

PS.  It seems pretty clear we're in different time zones.  I'm in the UK, so will be off to bed soon.  But I'll look in again tomorrow.
Thanks for the reply, Roger!

1)  Each Worksite may have multiple JobTitles.  e.g. the Library may have "custodian" and "clerk".
...so tblWorksites(PK_WorksiteID, WorksiteName, City, FK_MonitorID...) = (456123, Library, Lauderhill,21,...)
and tblJobTitles(PK_JobTitleID,FK_WorksiteID, JobName, intAvailablePositions) = (123456, 456123, Clerk, 5)
Relationships:  tblWorksites -> one to many -> tblJobTitles -> one to many -> tblApplicants.
I'm using the naming convention "PK_SomePrimaryKeyID" and "FK_SomeForeignKeyID" here for clarity, although in my code I've used "keyTableNameID" for all keys, primary and foreign, since I know if the key has the same name as the table it's a primary key.

There is a "Monitors" table (I'm referring to all tables with the "tbl" prefix here for clarity, though I don't use it in my database. That's why you see "Monitors", "Worksites", "Applicants" in my code and "tblMonitors", "tblWorksites", etc. in my explanation.) which contains "Worksite Monitors" who will be assigned to worksites in the same manner that Applicants are assigned to JobTitles.  I'm ignoring them right now because when I learn to do Applicants to JobTitles I'll use the same mechanism to do Monitors to Worksites.

The extra DataGridView is on another "Tab" in my application which has different tabs for different functionality, e.g. Tab1 has Applicant information, Tab2 has the Applicant to JobTitle assignment tool, Tab3 has the Monitor to Worksite assignment tool, for example. ( I actually have several tabs with "trial and error", mostly "error" ;-)
So, I should have said, "On Form1, Tab2 has two DataGridViews: ApplicantsDataGridView and JobTitlesDataGridView."

>>
the WorksitesDataGridView in a combobox's SelectedIndexChanged
<<
That DGV and ComboBox are on another tab I was trying to assign the Monitors to Worksites.  Let's disregard that area for now.  Once I can assign Applicants to JobTitles I use the same mechanism to assign Monitors to Worksites. What I was doing with them was using the ComboBox's databinding to link the MonitorsDataGridView to the WorksitesDataGridView.  In ComboBox3's "Tasks" I have Use data bound items checked, then in the "Data Binding Mode" I have the following setting:
Data Source = MonitorsBindingSource
Display Member = "LastName"  <--- from the Monitors table
Value Member = "keyMonitorID"  <--- PK from Monitors table
Selected Value = "WorksitesBindingSource - keyMonitorID  <--- FK from Worksites Table
What that did for me was that when I have a Monitor selected in the MonitorsDataGridView who has been assigned to a Worksite, then the Worksite in the WorksitesDataGridView is also selected, so when I scroll up and down in the list of Monitors I can see what Worksites they are assigned to, if any.  The selections in the two DGVs stay "linked".  I don't know if that's the right way to do that, but it was the only way I could figure out to link the two DataGridViews on the keyMonitorIDs in each.  I'm trying to do the same DGV link thing with the ApplicantsDataGridVies and the JobTitlesDataGridView using the ComboBox "cbFindJobTitle".  I searched Google and this site for two days and could find no other way to "link" two DGVs.  

The JobTitles table and the Worksites table are linked in a, Worksites --> one to many --> JobTitles relationship (also mentioned above) by "keyWorksiteID", a primary key in Worksites and a foreign key in JobTitles.

2) You are correct.  The *number* of positions is the only value that matters.  If there are 5 positions available for JobTitleID 123456, then, if 123456 is put into the FK_JobTitleID of three different Applicant records I only need to know that 2 are still available. Currently done thus:
UPDATE    JobTitles
SET              PositionsAvailable =
                          (SELECT     JobTitles_1.txtWorksiteJobTitlePositionsNumber - COUNT(Applicants.keyApplicantID) AS Expr1
                            FROM          Applicants INNER JOIN
                                                   JobTitles AS JobTitles_1 ON JobTitles_1.keyJobTitleID = Applicants.keyJobTitleID
                            WHERE      (JobTitles.keyJobTitleID = JobTitles_1.keyJobTitleID)
                            GROUP BY JobTitles_1.txtWorksiteJobTitlePositionsNumber, JobTitles_1.keyJobTitleID)

In the database there is no distinction between any of the 5 available positions.   There is simply a field for "total number of positions" and it contains an integer. The fieldname in the database is  JobTitles.txtWorksiteJobTitlePositionsNumber.

Oh my...  Yes, I'm in the Zulu -5 (Eastern United States) time zone.  So I hope you had a good rest.  Thank you for your assistance. I both need and appreciate it.  Like I said, I'm just starting to learn and I'm sure I'm doing *everything* incorrectly.  Goran mentioned that my code is "quite messy" which I do not doubt, so I do not take offense.

I'm sure there is probably an elegant way to assign a value from one table into another to accomplish my Applicant to JobTitle assignment, but I don't have any experience to base go by.

Thanks again.
-David








SOLUTION
Avatar of Priest04
Priest04
Flag of Serbia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Goran,

It appears that I'm trying to do things the hard way when there's a much simpler approach.

It makes much more sense to just subtract 1 from the available positions when an applicant is assigned to it than to re-SUM Query the database.

I *have* read that calculated fields are a no-no in the database.

In your example "dgw.Item(2,dgw.CurrentRow.Index)....." is "dgw" literally "dgw" or just short for my DataGridView name?  (I'm new, don't hurt me ;-)

Thank you, Goran.  Between you and Roger, you have given me a good bit of information to work with.  I really appreciate it.

I won't drag this question out much more.  Any other examples like the ones above you care to provide will be goldenly appreciated.  I like the simple example you gave of the clerk and custodian because you showed the actual code syntax, which is one point I'm really struggling with.

-David
Yes, David, you are correct, dgw represents a reference to DataGridView. Since this control is very often used in applications, it would be wise to read more about it, and understand how it works.

When I started programming, my first programs were also done in harder way, so you are not alone. This is the reason why I have later rewritten them, when the knowledge / experience came.

So dont hurry, go one step at the time, first code is almost never a good code, so dont burden yourself with it. In time everything will come. :)

Goran
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Roger and Goran, thank you so much, both of you!

It will take me some time to go through the demo and previous posts and try to understand how it works.

What further information sources do you recommend?  

I've been watching the videos on "www.LearnVisualStudio.Net".  They are quite helpful, but most are pre-Dot Net 2.0.  It seems like most of the .Net 1.x information on ADO.Net is still relevant if you are doing data stuff in code rather than using the new controls in .Net 2.0.

Please advise me how I should proceed from here.  Should I go ahead and close out this thread?   I sure hope you guys are willing to help me with future questions.  I know I will have many.  I haven't gotten very much response to some of my questions, I'm afraid perhaps, because I'm trying to do things so "screwed up" that my questions don't even make sense ;-)

Today is Friday.  I hope you both have a great weekend.

Roger - Thank you so much for that "little demo"!  I know that was time consuming.

Goran - I really appreciate your help and words of encouragement as well!

I wish I could give you each 5,000+ points.

-David

Question...

On row 102 of the demo, "Dim oldJobRow As DataRow() = dtJobs.Select("ID = " & applicantsRow.Item("Job"))",  I get a "blue squiggly" that says, "Option Strict On prohibits operands of type Object for operator '&' ".

Similarly on row 104, "oldJobRow(0).Item("Number") += 1" gives, "Option Strict On prohibits operands of type Object for operator '+'. ".

Should I just turn Option Strict off?  Or, is there another way to get around that?


On row 142, "Dim available As Integer = jobsRow.Item("Number")" gave, "Option Strict On disallows implicit conversions from 'Object' to 'Integer'. "
I replaced the line with "Dim available As Integer = CInt(jobsRow.Item("Number"))" to explicitly convert the Row.Item to an integer and then it was okay.

... Okay, I found this... "... ("ID = " & Convert.ToString(applicantsRow.Item("Job")))".  "Convert.ToString" seemed to do the trick.  :-)

"oldJobRow(0).Item("Number") += 1" looks a little trickier.  I thought it would be the same, but I'm not sure what to convert to what on this one.  
I tried "cInt" in a couple of places with no luck.  

I've seen this message before, so I imagine it's good to know how to avoid this conflict.

Thanks.
-David

My approach was (and would be) Option Strict Off - which is the default.  I only use Option Strict On in exceptional circumstances, and for specific reasons.  Although according to the docs it "improves performance", I've never done a program in which - after running checks specifically to test this - any difference has been discernable.

But if you want to preserve Option Strict On for the demo program explicit casting will - as you've discovered - cope with most of the problems.  But the += and -= needs a bit more work because it is combining a number of operations: getting the value from the row.item, incrementing the value, putting the value back in the row.item

Try, for example

   Dim myVal As Integer = CInt(oldJobRow(0).Item("Number"))
   myVal += 1 'or myVal = myVal + 1
   oldJobRow(0).Item("Number") = myVal  

Roger
Thank you, Roger!

I was suspecting that something like that would be required, but didn't know how to  construct it.

Let me know when I've pestered you guy enough.  I don't want to "wear out my welcome".  I do sincerely appreciate your fantastic help!

-David
David

Although you've not "worn out your welcome", I think it might be a good idea if you closed out this question.  That doesn't mean to say that I won't still be prepared to answer, in this thread, supplementaries on what has been said so far: e.g. like the "the demo code is not working" issue just raised.  But we've already ranged over a pretty wide field besides that indicated by the question's title and I think - even if only so that subsequent searchers will be able to identify points that might be useful to them - we've now reached the stage where, if there are more issues, it would be better to ask specific questions about them.

I'm not always about but, when I am, I generally trawl through recently posted questions to see if there's anything I can help with.  I imagine Goran will do the same.

Roger
I love the way that each Job is removed from the list where there are no more positions available for it.  That's awesome!

When I assigned all 9 positions (3 positons at each of the 3 Jobs) to 9 Kids and the last of the JobNames was removed from the DataGridView, as the last one disappeared it threw an "IndexOutOf RangeException", "Index -1 does not have a value."
Here's the full detail:
System.IndexOutOfRangeException was unhandled by user code
  Message="Index -1 does not have a value."
  Source="System.Windows.Forms"
  StackTrace:
       at System.Windows.Forms.CurrencyManager.get_Item(Int32 index)
       at System.Windows.Forms.CurrencyManager.get_Current()
       at EE_Demo.Form1.updateTotals() in C:\0-VisualStudioProjects\SYEP07\EE_Demo_DGV\EE_Demo\Form1.vb:line 149
       at EE_Demo.Form1.cmJobs_PositionChanged(Object sender, EventArgs e) in C:\0-VisualStudioProjects\SYEP07\EE_Demo_DGV\EE_Demo\Form1.vb:line 143
       at System.EventHandler.Invoke(Object sender, EventArgs e)
       at System.Windows.Forms.CurrencyManager.OnPositionChanged(EventArgs e)

I'm not sure whether a "Try...Catch" somewhere or something like, "If cmJobs.Current >=0 ..." is the correct way to handle this.  It seems to be a common issue to handle because I found so much about that exception with Google I didn't know what was what.

I hope I'm not starting to be a pest.  I think this piece will have me in good shape for fixing my application.  Then I'll go ahead and close this thread out and give you the points.  If I have other issues I'll start another question.

Thank you again.

-David
My fault.  I didn't test that.  It's trying to run the updateTotals sub when there's nothing left on which to do so.  It's complicated by the fact that that sub is called from two places - one on the currency manager's position changed and the other in the button_click - both of which will fire if an existing row is removed from the Jobs grid.  So, two corrections

    Private Sub cmJobs_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmJobs.PositionChanged
        'the jobs currency manager was declared WithEvents ...
        '... to make it easy to update the jobs numbers ...
        '... displays when a different job was selected ...
        '... in the grid
        If cmJobs.Count > 0 Then
            updateTotals()
        End If
    End Sub

and, at the start of the updateTotals() sub itself

        If cmJobs.Count = 0 Then
            txtAvailable.Text = ""
            txtTaken.Text = ""
            txtTotal.Text = ""
            MsgBox("No more jobs")
            Exit Sub
        End If

Some sort of Try Catch might handle it, but it's something that should be programmed for specifically rather than relying on that.

Roger
I see.  Great.  Thank you once again!

That worked like a charm!
I will only add a fwe things: Option strict should be set to Off, since you are a beginner. In time, when you get into to .Net programming, turning it On will be a good thing, so you can understand how things work.

Another thing (maybe a primary thing) would be to learn how to debug. You are correct when you say that the error you have received can be found in many cases, but if you understand what this error means, and find a line of code that hhrew an error, the rest is often easy. When you click on the Debug menu, you will se somethings like Step Into, Step over, Breakpoints, etc. This things allow you to quickly find a row that throws an error, and using Immediate Window you can get values of you variables, and many other things. Knowing how to debug is very important in programming.

Goran
Roger,

Would you mind terribly adding some comments to a couple of the Subs?  Most are commented beautifully.  

I can see in general terms what these are doing, but some of the code is unfamiliar to me.  I've never seen "TypeOf" before.  I'll have to look in the help or Google to see what that is.

Here are the two that a couple of comments would really help me with:

   Private Function getTitleFromID(ByVal ID As Integer) As String
        Dim dr As DataRow() = dtJobs.Select("ID = " & ID)
        If Not dr Is Nothing Then
            Return CStr(dr(0)("JobName"))
        Else
            Return ""
        End If
    End Function

    Private Sub fillInTitle(ByVal dgvr As DataGridViewRow)
        If TypeOf dgvr.Cells(2).Value Is DBNull Then
            dgvr.Cells(3).Value = ""
        Else
            dgvr.Cells(3).Value = getTitleFromID(CInt(dgvr.Cells(2).Value))
        End If
    End Sub


Thank you again.

-David
   Private Function getTitleFromID(ByVal ID As Integer) As String
        'use datatable filter to return only rows with this ID
        Dim dr As DataRow() = dtJobs.Select("ID = " & ID)
        'check if there are any such rows
        If Not dr Is Nothing Then
            'if yes, there should only be one - in position 0
            'return the value in the JobNmae field from that
            Return dr(0)("JobName")
        Else
            'otherwise, return empty string
            Return ""
        End If
    End Function

    Private Sub fillInTitle(ByVal dgvr As DataGridViewRow)
        'check if there is a value in the thurd cell ...
        '... (index 2, which is the "Job" field ...
        '... in this row from the datagridview
        If TypeOf dgvr.Cells(2).Value Is DBNull Then
            'if not, then there is no Job Title
            dgvr.Cells(3).Value = ""
        Else
            'if there is, use it to get the Job Title
            dgvr.Cells(3).Value = getTitleFromID(CInt(dgvr.Cells(2).Value))
        End If
    End Sub

Roger
Thank you again, very much.
I wanted to ask which would be easier to do, incorporate your perfectly working "demo" into my app or add my connection string and such into the "demo".

Instead of continuing here I asked the question in a new thread here:
https://www.experts-exchange.com/questions/22513546/Have-good-demo-and-messy-actual-web-form-Which-is-easier-to-modify-into-working-app.html

-David
Roger,

In my new question (my second comment) I've added a new DataGridView by dragging the Applicants table from the DataSet so it would create a TableAdapter.

The new one had a DataSource of "ApplicantsBindingSource" where the Demo you provided had "(none)" for a DataSource.  In the new question, I'm asking if I should leave the BindingSource or remove it.  I don't know if it would cause problems or give me some functionality that I want.

Thanks.
-David