	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

Question

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

9.0

Inspect / Critique 600 line VB.Net DataGridView Windows Forms application code

Asked by megnin in Microsoft Visual Basic.Net, SQL Server 2005, Visual Studio

Tags: VB.Net, VB 2008, Visual Basic, DataGridView, Optomize, VB 2005, SQL, SQL Server, SQL Server 2005, Windows Form

Hi Experts,

I've got a Windows Forms application I've pieced together over the past year or so with assistance here and other places and I think it's more like a "Franken Application" in that to solve various problems I've added code here and there that may be redundant or counter productive to itself. My programming skills are very basic so I'm having trouble cleaning it up without breaking it.

I'm looking for some help in looking at this thing and fixing things like filling DataTables and DataSets multiple times unnecessarily, updating an entire table where only a single record should be updated and such.

I'm using Visual Studio 2008 / VB. The app is about 600 lines including lots of comments, many of which were authored by "TheLearnedOne" and others here who have helped me with a lot of it.

Part of what's prompting me to request help is the app updates take way longer than they should so it does not seem very responsive.

Thank you for suggestions or any help anyone could provide. I've included various screen captures, the entire VB code and zipped up stored procedures called from the code.
Thanks in advance.

Get your answer NOW

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:
370:
371:
372:
373:
374:
375:
376:
377:
378:
379:
380:
381:
382:
383:
384:
385:
386:
387:
388:
389:
390:
391:
392:
393:
394:
395:
396:
397:
398:
399:
400:
401:
402:
403:
404:
405:
406:
407:
408:
409:
410:
411:
412:
413:
414:
415:
416:
417:
418:
419:
420:
421:
422:
423:
424:
425:
426:
427:
428:
429:
430:
431:
432:
433:
434:
435:
436:
437:
438:
439:
440:
441:
442:
443:
444:
445:
446:
447:
448:
449:
450:
451:
452:
453:
454:
455:
456:
457:
458:
459:
460:
461:
462:
463:
464:
465:
466:
467:
468:
469:
470:
471:
472:
473:
474:
475:
476:
477:
478:
479:
480:
481:
482:
483:
484:
485:
486:
487:
488:
489:
490:
491:
492:
493:
494:
495:
496:
497:
498:
499:
500:
501:
502:
503:
504:
505:
506:
507:
508:
509:
510:
511:
512:
513:
514:
515:
516:
517:
518:
519:
520:
521:
522:
523:
524:
525:
526:
527:
528:
529:
530:
531:
532:
533:
534:
535:
536:
537:
538:
539:
540:
541:
542:
543:
544:
545:
546:
547:
548:
549:
550:
551:
552:
553:
554:
555:
556:
557:
558:
559:
560:
561:
562:
563:
564:
565:
566:
567:
568:
569:
570:
571:
572:
573:
574:
575:
576:
577:
578:
579:
580:
581:
582:
583:
584:
585:
586:
587:
588:
589:
590:
591:
592:
593:
594:
595:
596:
597:
598:
599:
600:
601:
602:
603:
604:
605:
606:
607:
608:
609:
610:
611:
612:
613:
614:
615:
616:
617:
618:
619:
620:
621:
622:
623:
624:
625:
626:
627:
628:
629:
630:
631:
632:

           
           
             Option Strict Off
Imports System
Imports System.Data
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports System.Windows.Forms
Imports System.Drawing
 
Public Class YouthJob
    Private dtApplicants As DataTable
    Private cmApplicants As CurrencyManager
    Private dtJobs As DataTable
    Private dvjobs As New DataView
    Private WithEvents cmJobs As CurrencyManager
 
    Private Sub ApplicantsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ApplicantsBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.ApplicantsBindingSource.EndEdit()
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
    End Sub
 
    Private Sub YouthJob_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'SYEP2007DataSet.JobTitles' table. You can move, or remove it, as needed.
        'Me.JobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.JobTitles)
        'Me.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)
        Try
            Me.JobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.JobTitles)
        Catch ex As Exception
            MessageBox.Show(ex.StackTrace)
        End Try
        Try
            Me.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)
        Catch ex As Exception
            MessageBox.Show(ex.StackTrace)
        End Try
 
        dtApplicants = Me.SYEP2007DataSet.Applicants
        dtJobs = Me.SYEP2007DataSet.JobTitles
 
        '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 'So I can filter the count I added a Stored Procedure and us it instead _
        '                                                 of dtApplicants.Rows.Count.
        Dim count As Integer = CType(Me.ApplicantsTableAdapter.spCount_Applicants2008Total, Integer)
        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 = "txtWorksiteJobTitlePositionsNumber > 0"         '<<<<<Moved to end of YouthJob_Load
        '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 = False
        ApplicantsDataGridView.AllowUserToAddRows = False   'Was True.  I don't know why.
        'hide the applicant ID
        'ApplicantsDataGridView.Columns(0).Visible = True    'This is what whas keeping the columns from being hidden
        'hide the job ID ...
        'ApplicantsDataGridView.Columns(1).Visible = True    'This is what whas keeping the columns from being hidden
        'hide the ProgramID column in Applicants DGV
        'ApplicantsDataGridView.Columns(3).Visible = True    'This is what whas keeping the columns from being hidden
        '... but add an unbound column to show ...
        '... the job title matching that ID
        Dim TitleCol As New DataGridViewTextBoxColumn
        'TitleCol.HeaderText = "Job Title"
 
        'ApplicantsDataGridView.Columns(0).Width = 50  'AppID
        'ApplicantsDataGridView.Columns(1).Width = 50  'JobTitleID
        'ApplicantsDataGridView.Columns(2).Width = 100  'LastName
        'ApplicantsDataGridView.Columns(3).Width = 100  'FirstName
        'ApplicantsDataGridView.Columns(4).Width = 150  'Home City
        'ApplicantsDataGridView.Columns(5).Width = 60  'Zip
        'ApplicantsDataGridView.Columns(6).Width = 150  'City Preference
        'ApplicantsDataGridView.Columns(7).Width = 60  'Hours
        'ApplicantsDataGridView.Columns(8).Width = 50  'M-F
        ApplicantsDataGridView.Columns.Add("TitleCol", "Job Title Assigned")
        ApplicantsDataGridView.Columns("TitleCol").DisplayIndex = 7             'This places the JobTitle column on the GridView. It needs to do it this way to update
        'TitleCol.SortMode = DataGridViewColumnSortMode.Automatic        ' <-- This did not make the column sortable.  graye on EE said that it should work, though
        'ApplicantsDataGridView.Sort(ApplicantsDataGridView.Columns("TitleCol"), System.ComponentModel.ListSortDirection.Ascending) ' <---- Got an error with this
 
        '        For example:  <-- An example from graye on Experts Exchange of how he created a ComboBoxcolumn and made it sortable
        '            ' replace the Priority column with a combobox
        '            Dim Priority As New DataGridViewComboBoxColumn()
        '            Priority.DataSource = taPriority.GetActive
        '            Priority.DataPropertyName = "Priority"
        '            Priority.DisplayMember = "PriorityCode"
        '            Priority.Name = "Priority"
        '            Priority.HeaderText = "Pri"
        '            Priority.SortMode = DataGridViewColumnSortMode.Automatic
        '            dgvAssign.Columns.RemoveAt(13)
        '            dgvAssign.Columns.Insert(13, Priority)
        'This works for me....
 
        'ApplicantsDataGridView.Columns("TitleCol").Width = 250  'Job Title
        'ApplicantsDataGridView.Columns(10).Width = 200  'Work Preference
        'ApplicantsDataGridView.Columns(11).Width = 95  'Referred by
        'ApplicantsDataGridView.Columns(12).Width = 50  'Offender
 
        'Dim NumberofAdults As New DataGridViewTextBoxColumn  'Adding column this way did not show any data
        'NumberofAdults.HeaderText = "Adults"                 'Adding it in the designer shows the data
        'ApplicantsDataGridView.Columns.Add(NumberofAdults)   'The above Titlecol column goes on the end
 
        JobTitlesDataGridView.ReadOnly = True
        JobTitlesDataGridView.AllowUserToAddRows = False
        'JobTitlesDataGridView.Columns(0).Visible = False  'Hide JobTitleID
 
 
        'run the sub to fill the jobs numbers displays  ' ------------------Check to see if this needs to e here or just in the Update button-----
        updateTotals()
 
        'get the job titles for the applicants
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next
 
        dvjobs.RowFilter = "txtWorksiteJobTitlePositionsNumber > 0"   '<<<<<Moved to end of YouthJob_Load from above.
 
        'update TextBoxes for count of SYEP, STEPS and Fort Lauderdale Kids - IN Form_Load, UpdateTotals and btnRefresh_Click
        txtSYEP.Text = Me.ApplicantsTableAdapter.CountSYEPActivity().ToString  'The TableAdapter did have: "WHERE (ddlActivityType like '%SYEP%') AND (keyApplicantID > 200900000)" now it's: Status = '1'
        txtSTEPS.Text = Me.ApplicantsTableAdapter.CountWIAActivity().ToString 'No STEPS program for SYEP 2008
        txtFTLauderdale.Text = Me.ApplicantsTableAdapter.CountFtLauderdaleActivity().ToString
        txtConsideredCount.Text = Me.ApplicantsTableAdapter.CountConsidered().ToString
 
        'This just pops the current deployment version number into that label in the top right corner e.g. v1.0.0.51
        If System.Deployment.Application _
        .ApplicationDeployment.IsNetworkDeployed Then
            lblVersion.Text = "v" & System.Deployment.Application _
            .ApplicationDeployment.CurrentDeployment.CurrentVersion.ToString
        Else
            lblVersion.Text = "v" & My.Application.Info.Version.ToString
        End If
 
    End Sub
    Private Sub YouthJob_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        'make sure any outstanding edits are committed to the datatables
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()
        'then save datatable changes to database        
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles)
 
    End Sub
 
    ''This function is to get the currently selected row of JobTitlesDataGridView in order to return to the selected row after an assignment update.
    'Public Function getDGindex(ByVal ValueToLook As Integer) As Integer
    '    Dim dr As DataGridViewRow
    '    For Each dr In JobTitlesDataGridView.Rows 'DataGridView1.Rows
    '        If dr.Cells("JobTitleID").Value = ValueToLook Then
    '            Return dr.Index
    '        End If
    '    Next
    'End Function
 
    Function getJobTitleIndex(ByVal ID As Integer) As Integer
        For Each row As DataGridViewRow In Me.JobTitlesDataGridView.Rows
            If row.Cells(19).Value = ID Then
                Return row.Index
            End If
        Next
    End Function
    Function getApplicantIndex(ByVal ID As Integer) As Integer
        For Each row As DataGridViewRow In Me.ApplicantsDataGridView.Rows
            If row.Cells(5).Value = ID Then
                Return row.Index
            End If
        Next
    End Function
 
    Private Sub btnUpdatePosition_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdatePosition.Click
        ' ' -=== Assign Worksite to Youth Button ===-
        ' 'Call the function to get the currently selected row of JobTitlesDataGridView
        'Dim myPreviousValue As Long = Me.JobTitlesDataGridView.SelectedRows.Item(0).Cells(19).Value
        ' Stores the current row number
        ' Check if there's any selected row
        'If Not Me.JobTitlesDataGridView.SelectedRows Is Nothing Then 'CurrentRow Is Nothing Then
        If Me.JobTitlesDataGridView.SelectedRows.Count > 0 Then
            'If Not Me.ApplicantsDataGridView.SelectedRows Is Nothing Then 'CurrentRow Is Nothing Then
            If Me.ApplicantsDataGridView.SelectedRows.Count > 0 Then
 
                Dim SelectedID As Integer = Me.JobTitlesDataGridView.CurrentRow.Cells(19).Value
                Dim SelectedID2 As Integer = Me.ApplicantsDataGridView.CurrentRow.Cells(5).Value
 
                Me.Cursor = Cursors.WaitCursor
                Call UpdateAll()
                Call RefreshAll()
                Me.Cursor = Cursors.Default
 
                ' Set the old position
                Dim DGVindex As Integer = getJobTitleIndex(SelectedID)
                Me.JobTitlesDataGridView.ClearSelection()
                Me.JobTitlesDataGridView.Rows(DGVindex).Selected = True
                Me.JobTitlesDataGridView.FirstDisplayedScrollingRowIndex = DGVindex
 
                Dim DGVindex2 As Integer = getApplicantIndex(SelectedID2)
                Me.ApplicantsDataGridView.ClearSelection()
                Me.ApplicantsDataGridView.Rows(DGVindex2).Selected = True
                Me.ApplicantsDataGridView.FirstDisplayedScrollingRowIndex = DGVindex2
 
                Me.JobTitlesDataGridView.ClearSelection()
                Me.ApplicantsDataGridView.ClearSelection()
 
            Else
                Exit Sub
            End If
        End If
        ''Restore the previously selected row after an update.
        'Dim myRowIndex As Integer = getDGindex(myPreviousValue)
        'Me.JobTitlesDataGridView.Rows(myRowIndex).Selected = True
        'Me.JobTitlesDataGridView.FirstDisplayedScrollingRowIndex = myRowIndex
 
        ''Maybe put here UpdateTotals() then RefreshAll()  'Note for 20090312
        ''UpdateTotals() is already called in UpdateAll()
 
 
    End Sub
    Private Sub UpdateAll()
        Dim jobRow As DataRow()
        'use the currency manager to get the currently ...
        '... selected job in the jobs grid
        Dim jobsRow As DataRowView = CType(cmJobs.Current, DataRowView)
        'and save the job number
        Dim newJobNumber As Integer = CInt(jobsRow("keyJobTitleID"))
        '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("keyJobTitleID")) Is DBNull Then
            'if so, seek the relevant row from the datatable
            jobRow = dtJobs.Select("keyJobTitleID = " & Convert.ToString(applicantsRow.Item("keyJobTitleID")))
            'if there is one
            If jobRow.Length > 0 Then
                'it will be the first in the row array: update it
                Dim myVal As Integer = CInt(jobRow(0).Item("txtWorksiteJobTitlePositionsNumber"))
                myVal += 1 'or myVal = myVal + 1
                jobRow(0).Item("txtWorksiteJobTitlePositionsNumber") = myVal
            Else
                'if there isn't one ...
                MsgBox("Something wrong")
            End If
        End If
        'put the new job number in the applicant's record
        applicantsRow.Item("keyJobTitleID") = newJobNumber
        'decrement the number of jobs available
        'seek the relevant row from the datatable
        jobRow = dtJobs.Select("keyJobTitleID = " & newJobNumber)
        'if there is one
        If jobRow.Length > 0 Then
            'it will be the first in the row array: update it
            Dim myVal As Integer = CInt(jobRow(0).Item("txtWorksiteJobTitlePositionsNumber"))
            myVal -= 1 'or myVal = myVal - 1
            jobRow(0).Item("txtWorksiteJobTitlePositionsNumber") = myVal
        Else
            'if there isn't one ...
            MsgBox("Something wrong")
        End If
 
        ''commit the edits - this is necessary where ...
        ''... they have been done by via the currency ...
        ''... manager's Current record, rather than ...
        ''... (as above) in the datatable itself
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()
 
        'run the sub to update the jobs numbers displays
        Call updateTotals()
 
        'get the job title for this applicant
        Dim dgvr As DataGridViewRow = ApplicantsDataGridView.CurrentRow
        fillInTitle(dgvr)
 
        'make sure any outstanding edits are committed to the datatables
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()
        'then save datatable changes to database        
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles)
 
    End Sub
 
    Private Sub btnCancelPosition_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelPosition.Click
        Me.Close()
    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
        If cmJobs.Count > 0 Then
            updateTotals()
        End If
    End Sub
 
    Private Sub updateTotals()
        If cmJobs.Count = 0 Then
            txtAvailable.Text = ""
            txtTaken.Text = ""
            txtTotal.Text = ""
            MsgBox("No more jobs")
            Exit Sub
        End If
        '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 = CInt(jobsRow.Item("txtWorksiteJobTitlePositionsNumber"))
        'get the job ID from that row
        Dim thisJob As Integer = CInt(jobsRow.Item("keyJobTitleID"))
        'use the job ID to find how many applicants ...
        '... already have that job
        Dim taken As Integer = CInt(dtApplicants.Compute("Count(keyJobTitleID)", "keyJobTitleID = " & 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
 
        ''update TextBoxes with Available, Taken and Total Job Positions
        ''ERROR:  There is already an open DataReader associated with this Command which must be closed first.
        txtPAvailable.Text = Me.JobTitlesTableAdapter.spSUM_JobTitles_PositionsAvailable().ToString
        txtPTaken.Text = Me.JobTitlesTableAdapter.spSUM_JobTitles_PositionsTaken().ToString
        txtPTotal.Text = Me.JobTitlesTableAdapter.spSUM_JobTitles_PositionsTotal().ToString
 
        'update TextBoxes for count of SYEP, STEPS and Fort Lauderdale Kids - IN Form_Load, UpdateTotals and btnRefresh_Click
        txtSYEP.Text = Me.ApplicantsTableAdapter.CountSYEPActivity().ToString
        txtSTEPS.Text = Me.ApplicantsTableAdapter.CountWIAActivity().ToString   ' No STEPS Program for SYEP 2008
        txtFTLauderdale.Text = Me.ApplicantsTableAdapter.CountFtLauderdaleActivity().ToString
        txtConsideredCount.Text = Me.ApplicantsTableAdapter.CountConsidered().ToString
 
        'make sure any outstanding edits are committed to the datatables
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()
        'then save datatable changes to database        
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles)
    End Sub
 
    Private Function getTitleFromID(ByVal ID As Integer) As String
        'Temporary line for debugging purposes
        'If that is reporting 0, then we know the problem is with the datatable.  
        'If it is reporting more than that, then the problem is with either the value 
        'that is being passed to the function, 
        'or with the syntax or references in the select statement.
        'Debug.WriteLine(dtJobs.Rows.Count)
 
        'use datatable filter to return only rows with this ID
        Dim dr As DataRow() = dtJobs.Select("keyJobTitleID = " & ID)
        'check if there are any such rowsI
        If dr.Length > 0 Then
            'if yes, there should only be one - in position 0
            'return the value in the JobName field from that
            '          Return CStr(dr(0)("txtWorksiteJobTitle"))
            Dim s As String = CStr(dr(0)("txtWorksiteJobTitle"))
            ' MsgBox("| s = " & s & "|")  'Here for troubleshooting
            Return s
        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 third cell ...
        '... (index 2, which is the "Job" field ...
        '... in this row from the datagridview
        If TypeOf dgvr.Cells("KeyJobTitleIDDataGridViewTextBoxColumn").Value Is DBNull Then
            'if not, then there is no Job Title
            dgvr.Cells("TitleCol").Value = ""
        Else
            'if there is, use it to get the Job Title
            'Debug.WriteLine("Cell 2 = " & CInt(dgvr.Cells(2).Value))  'I didn't see anything unusual in the debug window when this ran.
            'MsgBox("Cell 2 = " & CInt(dgvr.Cells(2).Value))  '  Here for troubleshooting.
            dgvr.Cells("TitleCol").Value = getTitleFromID(CInt(dgvr.Cells("KeyJobTitleIDDataGridViewTextBoxColumn").Value))
            'Debug.WriteLine("Cell 4 = '" & CInt(dgvr.Cells(4).Value) & "'")
            'MsgBox("Cell 4 = '" & CStr(dgvr.Cells(4).Value) & "'")   'Here for troubleshooting.
            'MsgBox("Cell 4 = '" & CStr(dgvr.Cells("TitleCol").Value) & "'")
        End If
 
 
    End Sub
 
    Private Sub ApplicantsDataGridView_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles ApplicantsDataGridView.DataError
        Debug.WriteLine(e.ColumnIndex)
        Debug.WriteLine(e.RowIndex)
        Debug.WriteLine(ApplicantsDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value)
        Debug.WriteLine(ApplicantsDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).EditedFormattedValue)
        e.ThrowException = True
    End Sub
 
    Private Sub btnRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefresh.Click
        RefreshAll()
    End Sub
    Private Sub RefreshAll()
        ''get the job titles for the applicants
        ''I copied this from the bottom of YouthJob_Load in an attempt to make the Applicants DGV update the Job Titles after a column sort.
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next
 
        'update TextBoxes for count of SYEP, STEPS and Fort Lauderdale Kids - IN Form_Load, UpdateTotals and btnRefresh_Click
        txtSYEP.Text = Me.ApplicantsTableAdapter.CountSYEPActivity().ToString
        txtSTEPS.Text = Me.ApplicantsTableAdapter.CountWIAActivity().ToString   'No STEPS Program for SYEP 2008
        txtFTLauderdale.Text = Me.ApplicantsTableAdapter.CountFtLauderdaleActivity().ToString
        txtConsideredCount.Text = Me.ApplicantsTableAdapter.CountConsidered().ToString
 
        'make sure any outstanding edits are committed to the datatables
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()
        'then save datatable changes to database        
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles)
 
        'Update Applicants(keyWorksiteID, keyEmployerID, WorksiteName, EmployerName) from JobTitles(keyWorksiteID, keyEmployerID, WorksiteName, EmployerName) in the database tables
        Try
            Me.ApplicantsTableAdapter.spUpdate_Applicants_WorksiteName_From_JobTitles()
            Me.ApplicantsTableAdapter.spUpdate_Applicants_CityName_From_tblCities(Me.SYEP2007DataSet.Applicants)
            Me.ApplicantsTableAdapter.spUpdate_Applicants_ZipCode_From_tblZips(Me.SYEP2007DataSet.Applicants)
            'Me.ApplicantsTableAdapter.spUpdate_Applicants_WorkersCompCode(Me.SYEP2007DataSet.Applicants)  '###### Check on this occasionally - It should be fine.
            Me.ApplicantsTableAdapter.spUpdate_Applicants_WorksiteCityPreference(Me.SYEP2007DataSet.Applicants)
            Me.JobTitlesTableAdapter.FillBy1(Me.SYEP2007DataSet.JobTitles) ' runs spUpdate_JobTitles_WorksiteName Re-loads JobTitles. Need to refresh JobTitles GridView
            Me.JobTitlesTableAdapter.spUpdate_JobTitles_EmployerName(Me.SYEP2007DataSet.JobTitles)  'runs spUpdate_JobTitles_EmployerName to put Employer Names into the GridView
            Me.JobTitlesTableAdapter.spUpdate_JobTitles_WorksiteCity(Me.SYEP2007DataSet.JobTitles) '****** Copy this Try/Catch to the Load Event
            Me.JobTitlesTableAdapter.spUpdate_JobTitles_WorksiteZip(Me.SYEP2007DataSet.JobTitles)
            Me.JobTitlesTableAdapter.spUpdate_JobTitles_PositionsAvailable(Me.SYEP2007DataSet.JobTitles)
            Me.JobTitlesTableAdapter.spUpdate_JobTitles_PositionsTaken(Me.SYEP2007DataSet.JobTitles)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
 
        '*****Start  Copied from the Load Event *****  Not sure if all lines are necessary
        Try
            Me.JobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.JobTitles)
        Catch ex As Exception
            MessageBox.Show(ex.StackTrace)
        End Try
        'Try
        '    Me.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)
        'Catch ex As Exception
        '    MessageBox.Show(ex.StackTrace)
        'End Try
 
        'dtApplicants = Me.SYEP2007DataSet.Applicants
        'dtJobs = Me.SYEP2007DataSet.JobTitles
        '*****End  Copied from the Load Event *****  Not sure if all lines are necessary
 
        ''#####  Start  Update Positions Available, Positions Taken and Total in DataGridView  Note !!!  PositionsAvailable and txtPositions are labeled backwards !!!
        'Try
        'Me.JobTitlesTableAdapter.spUpdate_JobTitles_PositionsAvailable(Me.SYEP2007DataSet.JobTitles)
        'Me.JobTitlesTableAdapter.spUpdate_JobTitles_PositionsTaken(Me.SYEP2007DataSet.JobTitles)
        'Catch ex As Exception
        '    System.Windows.Forms.MessageBox.Show(ex.Message)
        'End Try
        ''#####  End    Update Positions Available, Positions Taken and Total in DataGridView  Note !!!  PositionsAvailable and txtPositions are labeled backwards !!!
        '*****Start  Copied from the btn2008 Event to repopulate the Applicants GridView after a btnRefresh - Must be last *****  
        Try
            Me.ApplicantsTableAdapter.FillBy3(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        '*****End  Copied from the btn2008 Event to repopulate the Applicants GridView after a btnRefresh *****  
 
        ''get the job titles for the applicants '## Copied from Private Sub ApplicantsDataGridView_ColumnHeaderMouseClick right below so that when the 
        ''Refresh button is clicked it will update the JobTitles column because the Refresh button was clearing the column and I had to then click
        '' the ColumnHeader of the JobTitles column to get the job titles back.  This should do it automatically.
        ''I copied this from the bottom of YouthJob_Load in an attempt to make the Applicants DGV update the Job Titles after a column sort.
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next
 
    End Sub
    Private Sub ApplicantsDataGridView_ColumnHeaderMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles ApplicantsDataGridView.ColumnHeaderMouseClick
        ''get the job titles for the applicants
        ''I copied this from the bottom of YouthJob_Load in an attempt to make the Applicants DGV update the Job Titles after a column sort.
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next
    End Sub
    Private Sub FillByToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillByToolStripButton.Click
        Try
            Me.ApplicantsTableAdapter.FillBy(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub
    Private Sub btnConsideredFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConsideredFilter.Click
        Try
            Me.ApplicantsTableAdapter.FillBy(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next
        btnSYEPFilter.BackColor = Color.LightGray
        btnConsideredFilter.BackColor = Color.Violet
        btnFtLauderdaleFilter.BackColor = Color.LightGray
        btn2008.BackColor = Color.LightGray
 
    End Sub
 
    Private Sub FillBy1ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillBy1ToolStripButton.Click
        Try
            Me.ApplicantsTableAdapter.FillBy1(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub
 
    Private Sub btnSYEPFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSYEPFilter.Click
        'For 2009 there are no STEPS so I made this filter on Status = '1' instead.  The button says "Status 1".
        Try
            Me.ApplicantsTableAdapter.FillBy1(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
 
        ''get the job titles for the applicants
        ''I copied this from the bottom of YouthJob_Load in an attempt to make the Applicants DGV update the Job Titles after a column sort.
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next
        btnSYEPFilter.BackColor = Color.Violet
        btnConsideredFilter.BackColor = Color.LightGray
        btnFtLauderdaleFilter.BackColor = Color.LightGray
        btn2008.BackColor = Color.LightGray
 
    End Sub
    Private Sub btnFtLauderdaleFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFtLauderdaleFilter.Click
        Try
            Me.ApplicantsTableAdapter.spFilter_FtLauderdaleKids(Me.SYEP2007DataSet.Applicants) '.Fill Applicants Table Adapter with Fort Lauderdale Kids only
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        btnSYEPFilter.BackColor = Color.LightGray
        btnConsideredFilter.BackColor = Color.LightGray
        btnFtLauderdaleFilter.BackColor = Color.Violet
        btn2008.BackColor = Color.LightGray
 
    End Sub
 
    Private Sub FillBy2ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillBy2ToolStripButton.Click
        Try
            Me.ApplicantsTableAdapter.FillBy2(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub
 
    Private Sub txtAllFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtAllFilter.Click
        'All 07-09 Button
        Try
            Me.ApplicantsTableAdapter.FillBy2(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        ' Put Total of all Applicants from all years into Text Box.  Sp counts all applicants.
        Dim count As Integer = CType(Me.ApplicantsTableAdapter.spCount_AllApplicants, Integer)
        txtTotalApplicants.Text = String.Format("{0} Applicants", count)
 
    End Sub
    Private Sub test()
        'Just testing how to convert a string to Proper Case.  Names like McIntire are problematic.
        Dim myTest As String
        myTest = StrConv("test", VbStrConv.ProperCase)
    End Sub
 
    Private Sub btn2008_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn2008.Click
        '2009 Button
        Try
            Me.ApplicantsTableAdapter.FillBy3(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
 
        ''get the job titles for the applicants '## Copied from Private Sub ApplicantsDataGridView_ColumnHeaderMouseClick right below so that when the 
        ''Refresh button is clicked it will update the JobTitles column because the Refresh button was clearing the column and I had to then click
        '' the ColumnHeader of the JobTitles column to get the job titles back.  This should do it automatically.
        ''I copied this from the bottom of YouthJob_Load in an attempt to make the Applicants DGV update the Job Titles after a column sort.
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next
        ' Put total of 2009 Applicants into the text box. The sp counts 2009 Applicants
        Dim count As Integer = CType(Me.ApplicantsTableAdapter.spCount_Applicants2008Total, Integer)
        txtTotalApplicants.Text = String.Format("{0} Applicants", count)
        btnSYEPFilter.BackColor = Color.LightGray
        btnConsideredFilter.BackColor = Color.LightGray
        btnFtLauderdaleFilter.BackColor = Color.LightGray
        btn2008.BackColor = Color.Violet
 
    End Sub
    Private Sub btnWIA_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnWIA.Click
        'Filter Applicants Grid on WIA in txtActivityType field
        'spFilter_WIA_Applicants
        Try
            Me.ApplicantsTableAdapter.spFilter_WIA_Applicants(Me.SYEP2007DataSet.Applicants) '.Fill Applicants Table Adapter with WIA 19-24 yo Kids only
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
        btnSYEPFilter.BackColor = Color.LightGray
        btnConsideredFilter.BackColor = Color.LightGray
        btnFtLauderdaleFilter.BackColor = Color.LightGray
        btn2008.BackColor = Color.LightGray
        btnWIA.BackColor = Color.Violet
    End Sub
 
End Class

           
         

Attachments:

YouthJob.vb.png (43 KB) (File Type Details)

Image of Windows form in Visual Studio 2008 Design View

YouthJob.vb.running.png (94 KB) (File Type Details)

Image of Windows form running

YouthJob.DataSet.png (119 KB) (File Type Details)

DataSet.xds in Design view

StoredProcedures.zip (3 KB) (File Type Details)

Seven Stored Procedures used by in the app and by the DataGridViews

20091111-EE-VQP-89 - Hierarchy / EE_QW_3_20080625

Hall of Fame

1. CodeCruiser1,798,137
2. jpaulino788,962
3. Idle_Mind778,416
4. TheLearn...414,325
5. Dhaest307,558
6. emoreau279,424
7. abel277,221
8. VBRocks277,167
9. webtubbs239,394
10. Fernando...194,509
11. kaufmed165,773
12. JackOfPH128,910
13. guru_sami117,814
14. Chaosian116,233
15. jaime_oliv...113,308
16. Africans110,800
17. shahprabal108,964
18. angelIII107,064
19. mlmcc97,645
20. oobayly96,584
21. PaulHews96,152
22. mwvisa189,386
23. Juan_Bar...88,460
24. jinal79,568
25. nmarun76,505

1. TheLearn...4,803,403
2. Idle_Mind4,615,460
3. Fernando...2,397,762
4. Sancler2,356,988
5. jpaulino2,349,769
6. CodeCruiser2,135,385
7. emoreau1,473,723
8. VBRocks1,370,339
9. RonaldBie...1,159,695
10. Dhaest1,058,504
11. webtubbs1,016,669
12. angelIII843,243
13. iboutchkine822,529
14. Chaosian814,067
15. PaulHews749,950
16. jaime_oliv...746,433
17. graye730,743
18. planocz707,043
19. gregoryyo...611,810
20. arif_eqbal596,164
21. vbturbo511,530
22. mlmcc503,210
23. ZeonFlash492,695
24. AlexFM473,611
25. BriCrowe435,867

Inspect / Critique 600 line VB.Net DataGridView Windows Forms application code

Asked by megnin in Microsoft Visual Basic.Net, SQL Server 2005, Visual Studio

Tags: VB.Net, VB 2008, Visual Basic, DataGridView, Optomize, VB 2005, SQL, SQL Server, SQL Server 2005, Windows Form

About this solution