Solved

How to refresh a DataGridView after database Update.

Posted on 2007-04-09
28
32,790 Views
Last Modified: 2013-11-26
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
0
Comment
Question by:megnin
  • 14
  • 10
  • 4
28 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 18879187
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
0
 
LVL 1

Author Comment

by:megnin
ID: 18882198
...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.
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18882954
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
0
 
LVL 1

Author Comment

by:megnin
ID: 18883193
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
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18883823
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
0
 
LVL 18

Expert Comment

by:Priest04
ID: 18885552
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
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18886137
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  
0
 
LVL 1

Author Comment

by:megnin
ID: 18893215
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
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18894117
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.
0
 
LVL 1

Author Comment

by:megnin
ID: 18900121
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








0
 
LVL 18

Assisted Solution

by:Priest04
Priest04 earned 200 total points
ID: 18900507
[Quote] 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. [/[Quote]

I will give you a simple example how this can be achieved. Lets say that you have only this info in JobTitles

JobTitleID      JobTitle    Available positions
1                  clerk                   2
2                  custodian            4

I will present only these fields for the sake of simplicity. Now, when you assign clerk's position to some applicant, you save the data for this application, and now you need to refresh Available positions field. This you have been doing by reloading all data for all JobTitles, but only thing you needed to do is set Available positions for clerk to 1. How is this done?

1) You save data for applicant that is assigned a job - this you do with next line

rowsAffected = Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)

2) you say you have data for JobTitles shown in datagridview. The assumption is that Datagridview's CurrentRow is pointing to the JobTitle that you have just assigned to applicant. If yes, all you need to do is this:

dgw.Item(2,dgw.CurrentRow.Index).Value=cint(dgw.Item(2,dgw.CurrentRow.Index).Value)-1

as you have noticed, I have placed Available positions field in column that is of Index 2. This will automatically update the datatable that this dataGridView is bound to. The alternative method is to update DataTable row, which will reflect datagridview and refresh the value in it, since it is binded to DataTable.

Since you have Available positions as a field in database, you can update it immediatelly, with simple query

UPDATE JobTitles SET AvailablePostions=AvailablePostions-1 WHERE JobTitleID=" & JobTitleID

or if this is a standalone application, you can update it later, iwth BacthUpdate (like you did with Applicant data)

You may have read (or will read) somewhere that calculated fields are not to be saved in database, since you need to do several updates when one value has changed. This is true to some point, and it depends on whether the calculations are to heavy to be done every time data needs to be pulled from database. Its up to developer to decide what makes his life easier, with two things on his mind - is it easier to

1) save calculated fields, and later deal with cascade updates
2) have a more complex query to load data, but no worries later to do a cascade update

Goran
0
 
LVL 1

Author Comment

by:megnin
ID: 18901420
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
0
 
LVL 18

Expert Comment

by:Priest04
ID: 18902175
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
0
 
LVL 34

Accepted Solution

by:
Sancler earned 300 total points
ID: 18902241
Here's a little demo.  Make a form with two datagridviews, four textboxes and one button - their names should be apparent from what follows.  Copy this code into the code window.

Public Class Form1

    Private dtApplicants As New DataTable("Applicants")
    Private cmApplicants As CurrencyManager
    Private dtJobs As New DataTable("Jobs")
    Private dvjobs As New DataView
    Private WithEvents cmJobs As CurrencyManager

    Private Sub filltables()
        'this sub is just to make dummy data
        Dim dca0 As New DataColumn("ID", GetType(Integer))
        dtApplicants.Columns.Add(dca0)
        Dim dca1 As New DataColumn("Name", GetType(String))
        dtApplicants.Columns.Add(dca1)
        Dim dca2 As New DataColumn("Job", GetType(Integer))
        dtApplicants.Columns.Add(dca2)
        For i As Integer = 1 To 10
            Dim dr As DataRow = dtApplicants.NewRow
            dr(0) = i
            dr(1) = "Kid " & i.ToString
            dtApplicants.Rows.Add(dr)
        Next
        Dim dcj0 As New DataColumn("ID", GetType(Integer))
        dtJobs.Columns.Add(dcj0)
        Dim dcj1 As New DataColumn("JobName", GetType(String))
        dtJobs.Columns.Add(dcj1)
        Dim dcj2 As New DataColumn("Number", GetType(Integer))
        dtJobs.Columns.Add(dcj2)
        For i As Integer = 1 To 3
            Dim dr As DataRow = dtJobs.NewRow
            dr(0) = i
            dr(1) = "Job " & i.ToString
            dr(2) = 3
            dtJobs.Rows.Add(dr)
        Next

    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        filltables() 'equivalent to filling tables with ...
        '... a dataadapter or a tableadapter

        'get and display the count of applicant records
        Dim count As Integer = dtApplicants.Rows.Count
        txtTotalApplicants.Text = String.Format("{0} Applicants", count)

        'bind the applicants table to the datagridview
        ApplicantsDataGridView.DataSource = dtApplicants
        'set up the currencymanager - convenient for referring ...
        '... to the currently selected row in the applicants grid
        cmApplicants = CType(BindingContext(dtApplicants), CurrencyManager)

        'set up a dataview to filter the jobs table so it ...
        '... only shows those where some jobs remain available
        dvjobs.Table = dtJobs
        dvjobs.RowFilter = "Number > 0"
        'bind the jobs table to the datagridview
        JobTitlesDataGridView.DataSource = dvjobs
        'set up the currency manager
        cmJobs = CType(BindingContext(dvjobs), CurrencyManager)

        'customise the grids - with a strongly typed dataset ...
        '... this could be done in the designer
        ApplicantsDataGridView.ReadOnly = True
        ApplicantsDataGridView.AllowUserToAddRows = False
        'hide the applicant ID
        ApplicantsDataGridView.Columns(0).Visible = False
        'hide the job ID ...
        ApplicantsDataGridView.Columns(2).Visible = False
        '... but add an unbound column to show ...
        '... the job title matching that ID
        Dim TitleCol As New DataGridViewTextBoxColumn
        TitleCol.HeaderText = "Job title"
        ApplicantsDataGridView.Columns.Add(TitleCol)

        JobTitlesDataGridView.ReadOnly = True
        JobTitlesDataGridView.AllowUserToAddRows = False
        JobTitlesDataGridView.Columns(0).Visible = False

        'run the sub to fill the jobs numbers displays
        updateTotals()

        'get the job titles for the applicants
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
        Next

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'use the currency manager to get the currently ...
        '... selected job in the jobs grid
        Dim jobsRow As DataRowView = CType(cmJobs.Current, DataRowView)
        'similar for the applicants
        Dim applicantsRow As DataRowView = CType(cmApplicants.Current, DataRowView)
        'check if this applicant already has a job
        If Not TypeOf (applicantsRow.Item("Job")) Is DBNull Then
            'need to return existing job to those available
            'get from table the row for the old job
            Dim oldJobRow As DataRow() = dtJobs.Select("ID = " & applicantsRow.Item("Job"))
            If Not oldJobRow Is Nothing Then
                oldJobRow(0).Item("Number") += 1
            Else
                MsgBox("Something wrong")
            End If
        End If
        'put the new job number in the applicant's record
        applicantsRow.Item("Job") = jobsRow.Item("ID")
        'decrement the number of jobs available
        jobsRow.Item("Number") -= 1
        'commit the edits - this is necessary as ...
        '... they have been done by via the currency ...
        '... manager's Current record, rather than ...
        '... (as above) in the datatable itself
        cmJobs.EndCurrentEdit()
        cmApplicants.EndCurrentEdit()

        'run the sub to update the jobs numbers displays
        updateTotals()

        'get the job title for this applicant
        Dim dgvr As DataGridViewRow = ApplicantsDataGridView.CurrentRow
        fillInTitle(dgvr)

    End Sub

    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
        updateTotals()
    End Sub

    Private Sub updateTotals()
        'use the currency manager to get the currently ...
        '... selected job in the jobs grid
        Dim jobsRow As DataRowView = CType(cmJobs.Current, DataRowView)
        'that says how many of that job are available
        Dim available As Integer = jobsRow.Item("Number")
        'get the job ID from that row
        Dim thisJob As Integer = jobsRow.Item("ID")
        'use the job ID to find how many applicants ...
        '... already have that job
        Dim taken As Integer = dtApplicants.Compute("Count(ID)", "Job = " & thisJob)
        'the total is the sum of those two
        Dim total As Integer = available + taken
        'display the results
        txtAvailable.Text = available.ToString
        txtTaken.Text = taken.ToString
        txtTotal.Text = total.ToString
    End Sub

    Private Function getTitleFromID(ByVal ID As Integer) As String
        Dim dr As DataRow() = dtJobs.Select("ID = " & ID)
        If Not dr Is Nothing Then
            Return 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

End Class

It illustrates the techniques I think you could use for a "disconnected" approach.  Your syntax would need to be a bit different, as it looks as though you are using a strongly typed dataset, but it shouldn't be too difficult for you to find the equivalent objects in your set-up.  And, in any event, it is supposed to provide a working model on which you can build rather than being a cut and paste offering for your actual project.

I should stress that these are not the ONLY ways - or even the best, perhaps - of meeting your needs.  But I've chosen methods which are easy to demonstrate and, hopefully, to understand.

Roger

PS, I see that Goran has just posted some words of encouragement.  I second them.  It's a mill we've all been through.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 1

Author Comment

by:megnin
ID: 18905011
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

0
 
LVL 1

Author Comment

by:megnin
ID: 18905884
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

0
 
LVL 34

Expert Comment

by:Sancler
ID: 18906059
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
0
 
LVL 1

Author Comment

by:megnin
ID: 18906280
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
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18906853
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
0
 
LVL 1

Author Comment

by:megnin
ID: 18906957
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
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18907255
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
0
 
LVL 1

Author Comment

by:megnin
ID: 18907823
I see.  Great.  Thank you once again!

That worked like a charm!
0
 
LVL 18

Expert Comment

by:Priest04
ID: 18907945
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
0
 
LVL 1

Author Comment

by:megnin
ID: 18908288
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
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18908754
   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
0
 
LVL 1

Author Comment

by:megnin
ID: 18917220
Thank you again, very much.
0
 
LVL 1

Author Comment

by:megnin
ID: 18917885
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:
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_22513546.html

-David
0
 
LVL 1

Author Comment

by:megnin
ID: 18919063
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
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 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

15 Experts available now in Live!

Get 1:1 Help Now