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

asked on

Have good "demo" and messy actual web form. Which is easier to modify into working app?

I have a working "demo" application designed around my actual form structure.  
My actual form "kind of" works, but is very messy and the "demo" works perfectly.

I'm completely new at this and wondering if it would be easier to use the demo as the base and move my connection strings and such into it, or use my original form as the base and move the working code into it?

It looks like it will be quite a job either way (and I'm already over my deadline) so I want to do it the easiest way to avoid taking any more time than necessary.

I'll list first the great working demo, then my form code so you can see what I need to do:

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 = " & Convert.ToString(applicantsRow.Item("Job")))
            If Not oldJobRow Is Nothing Then
                'oldJobRow(0).Item("Number") += 1  '<--- This only works if Option Strict is OFF
                Dim myVal As Integer = CInt(oldJobRow(0).Item("Number"))
                myVal += 1 'or myVal = myVal + 1
                oldJobRow(0).Item("Number") = myVal
            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  '<--- This only works if Option Strict is OFF
        Dim myVal2 As Integer = CInt(jobsRow.Item("Number"))
        myVal2 -= 1 'or myVal2 = myVal2 + 1
        jobsRow.Item("Number") = myVal2

        '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
        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("Number"))
        'get the job ID from that row
        Dim thisJob As Integer = CInt(jobsRow.Item("ID"))
        'use the job ID to find how many applicants ...
        '... already have that job
        Dim taken As Integer = CInt(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
        '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 CStr(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
End Class

'****************************************************************************
'My messy code.   There are a couple of tabs in the first half of the code I'm not using.  The relevant part is noted in the comments.  
'****************************************************************************
Imports System.Data.SqlTypes
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms

Public Class Form1
'There is a TAB CONTROL on Form1 with two tabs and a couple of DataGridViews I'm not using right now.
'I would have removed them but I didn't know if that would break somethink so I'll just
'note where the relevant tab code starts.  Look for '---> Relevant Tab Starts Here.  It's about the last half of the code.
    Private Sub Form1_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.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.JobTitles)
        Me.MonitorsTableAdapter.Fill(Me.SYEP2007DataSet.Monitors)
        Me.VJobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.vJobTitles)
        Me.VMonitorLoadTableAdapter.Fill(Me.SYEP2007DataSet.vMonitorLoad)
        Me.VPositionsByMonitorTableAdapter.Fill(Me.SYEP2007DataSet.vPositionsByMonitor)
        Me.VPositionsByWorksiteTableAdapter.Fill(Me.SYEP2007DataSet.vPositionsByWorksite)
        Me.WorksitesTableAdapter.Fill(Me.SYEP2007DataSet.Worksites)

        Dim cnString As String = "UID=menin;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
        Dim reader As SqlDataReader = Nothing
        Try
            ' open the connection
            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)

            '' create a command string and use it to instantiate a command
            'Dim cmdString As String = _
            '    "SELECT cityid, cityName FROM tblcities_old ORDER BY cityid"
            'cmd = New SqlCommand(cmdString, cn)

            '' execute the command
            'reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            '' populate the list box with the results
            'Do While reader.Read()
            '    ListBox1.Items.Add(reader("cityID").ToString() & " " & _
            '        reader("cityName").ToString())
            'Loop

            '' close the data reader
            'reader.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", _
                MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn IsNot Nothing Then
                cn.Close()
            End If
        End Try
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click, Button1.Click
        Me.Close()
    End Sub
    Private Sub TextBox1_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.Enter
        If TextBox1.Text = "Last Name" Then
            TextBox1.Text = ""
        End If
    End Sub
    Private Sub TextBox1_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.LostFocus
        If TextBox1.Text = "" Then
            TextBox1.Text = "Last Name"
        End If
    End Sub
    Private Sub WorksitesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WorksitesBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.WorksitesBindingSource.EndEdit()
        Dim rowsAffected As Integer = 0
        rowsAffected = Me.WorksitesTableAdapter.Update(Me.SYEP2007DataSet.Worksites)
        MessageBox.Show(rowsAffected.ToString + " Rows were affected")
    End Sub

    Private Sub btnUpdateData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateWorksite.Click
        Me.Validate()
        Me.WorksitesBindingSource.EndEdit()
        Dim rowsAffected As Integer = 0
        rowsAffected = Me.WorksitesTableAdapter.Update(Me.SYEP2007DataSet.Worksites)
        MessageBox.Show(rowsAffected.ToString + " Rows were affected")
    End Sub

    Private Sub btnUpdateMonitor_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateMonitor.Click
        Me.Validate()
        Me.MonitorsBindingSource.EndEdit()
        Dim rowsAffected As Integer = 0
        rowsAffected = Me.MonitorsTableAdapter.Update(Me.SYEP2007DataSet.Monitors)
        MessageBox.Show(rowsAffected.ToString + " Rows were affected")
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

    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 = "UID=menin;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SYEP2007;Data Source=BETASERVE;Packet Size=4096;"
            cmd1.Connection = con
            '        cmd1.CommandText = "SELECT SUM(TotalPositions) AS Positions FROM Worksites"
            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


'---> Relevant Tab Starts Here


    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 = "UID=menin;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SYEP2007;Data Source=BETASERVE;Packet Size=4096;"
        cmd.Connection = con
        cmd.CommandType = CommandType.StoredProcedure
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()

        JobTitlesBindingSource.ResetBindings(False)
        'JobTitlesDataGridView.                  'uh, none of these worked.
        'JobTitlesDataGridView.EndEdit()
        'JobTitlesDataGridView.Refresh()
        'JobTitlesTableAdapter.Fill()
        'fktablebindingsource.position = var
        'Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)

    End Sub

    Private Sub cbFindJobTitle_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbFindJobTitle.SelectedIndexChanged

    End Sub

    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
        'ApplicantsDataGridView.Rows(0).Selected = True
    End Sub
    Private Sub ApplicantsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ApplicantsBindingNavigatorSaveItem.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")
    End Sub

    Private Sub FillByToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillByToolStripButton.Click
        Try
            Me.JobTitlesTableAdapter.FillBy(Me.SYEP2007DataSet.JobTitles)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try

    End Sub
End Class



Suggestions greatly appreciated.

Avatar of David Megnin
David Megnin
Flag of United States of America image

ASKER

Roger,
I hope you pop over here and take a look...

I started with the "demo" code you provided.  
In order to get my live ApplicantsTableAdapter on the form, I deleted the DataGridView that I had added when building the demo, then dragged the Applicants table from the SYEP2007DataSet in my Data Sources window.

That gave me a new DataGridView with the same name as the original one, ApplicantsDataGridView.

The original DGV in the Demo had a DataSource of "(none)".  The new one had a DataSource of "ApplicantsBindingSource".

Should I remove the DataSource by selecting "(none)" or does that do something for me that I want to keep?

For now I have removed it and am continuing to add my "live" components to the Demo as best I can.  I'm using the VS2005 Help for information on DataAdapters and how to use them.

Thanks.
-David
Roger,

I'm having a hell of a time figuring out how to load my data into the Demo app you provided me.  My Applicants table has about 70 columns.  I use the exact same field name for primary and foreign keys, for example in tblJobTitles the primary key is keyJobTitleID and in tblApplicants the related foreign key is also keyJobTitleID, so I think that's making things a bit confusing to convert as well.

I think tomorrow I'm going to start trying to go the other way and move your code into my original app and see if that's any easier.
Avatar of Sancler
Sancler

David

In the demo code the dtApplicants is equivalent to your Me.SYEP2007DataSet.Applicants and the dtJobs is equivalent to your Me.SYEP2007DataSet.JobTitles.  

So the simplest way of merging the two sets of code would be to put these declarations at the start of YOUR form

    Private dtApplicants DataTable
    Private cmApplicants As CurrencyManager
    Private dtJobs As DataTable
    Private dvjobs As New DataView
    Private WithEvents cmJobs As CurrencyManager

Then, in your form load sub, leave everything as it is down to and including

        Me.WorksitesTableAdapter.Fill(Me.SYEP2007DataSet.Worksites)

and comment out everything in that sub after that.  Then add, in that sub

   dtApplicants = Me.SYEP2007DataSet.Applicants
   dtJobs = Me.SYEP2007DataSet.JobTitles

and then put all the code from the demo form load sub after that.  You will need to make sure that the control names used in the code are the same as you've actually used on your form.

Given that the demo was supposed to replace all the stuff from your code that you've now marked "Relevant Tab Starts Here", you should comment that out, too.

That will then - possibly ;-) - run.

As the demo was a "scaled down" version of the real app - really just intended to demonstrate an approach rather than being intended to be copied and pasted into a working app - it may not be quite what you want, even assuming it does run OK.  But try that first, and then we'll have to take it from there.

I realise you've got a deadline, but I won't be able to deal with any supplementaries until tomorrow.

Roger
David

You will also need to change the references to columns in the datatables to match your own data's column names.  For example, where I've put "ID" you will need to use your own ID's name, similar for "Number" etc.

Roger
Roger,

Thanks a lot.   I realize it was intended to demonstrate an approach rather than to "become" my working app.  I've learned a lot so far in the process of merging the two.  I did figure out that I needed to change the column names to match the actual column names in my data.
When I left off last night I was getting an "index out of range" exception, I think in the "fillInTitle" sub.  Where the demo had 3 columns my Applicants table has 99 columns (0 - 98) so I changed
"dgvr.Cells(3).Value = getTitleFromID(CInt(dgvr.Cells(2).Value))" to "dgvr.Cells(99).Value = getTitleFromID(CInt(dgvr.Cells(2).Value))"  to account for
"ApplicantsDataGridView.Columns.Add(TitleCol)" in the Form1_Load adding a new column to the end.
I assumed that columns were counted even if their "Visible" property was set to "False".

Did I mention that this is the very first Windows application I've ever attempted.

I really appreciate your help, whenever your schedule permits.   My boss understands that I'm a beginner at this, so he cuts me some slack.

Thanks again for the tips in the previous two comments.

David
In this line

   dgvr.Cells(3).Value = getTitleFromID(CInt(dgvr.Cells(2).Value))

the 3, on the left hand side of the equals sign, is intended to refer to the index of the unbound column that you've added to show the job title in a textual form.  You are right that, if columns are just hidden, they are still counted.  So if the last index for existing columns was 98, you are correct that the one you want would be 99.  I just wonder whether that was the, or the only, problem as I would not have expected from that the specific error message you report - "Index out of range".  That normally occurs when an index is too high, not when it is too low.

The 2 on the right hand side of the equals sign is intended to refer to the column (whether hidden or not) which contains the Job ID (presumably some numerical or alphanumeric key) which links to that job title.

99 columns is an awful lot to show in a grid.  How many are you hiding?  Rather than individually coding the hiding of 60 or more columns, there may be better ways of setting up the grid.

Roger
In the DataGridViews on the design form I've selected "Edit Columns" and removed all the columns except for the three similar to the ones in the demo.  In The ApplicantsDataGridView I now only have keyApplicantID, LastName, and keyJobTitleID.  In the JobTitleDataGridView I have keyJobTitleID, txtWorksiteJobtitle, and txtWorksiteJobTitlePositionsNumber which represent the same data as in the demo.

In my DataGridViews I still have the BindingSources as the DataSource.  I noticed that in the demo there is no DataSource done in the design form, only in code.  
Should I remove the DataSource from my DGVs?

I'm still getting the IndexOutOfRangeException on the getTitleFromID Function:

    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("keyJobTitleID = " & 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 CStr(dr(0)("txtWorksiteJobTitle"))
        Else
            'otherwise, return empty string
            Return ""
        End If
    End Function

I'm thinking that perhaps I have misinterpreted one of your field names and put the wrong one of mine in it's place.  There are several spots where I've replaced "ID" with "keyJobTitleID" and another spot in the same function where "ID" seems to be a variable and I left it as "ID".

Much of the syntax is still pretty much Greek to me.  I haven't had the luxury of starting with simple projects, so I've pretty much bitten of a rather large chunk and am having a hard time with it.

If I may, here is my code as it is now, with several large sections commented out as instructed.   I've also commented out the sections from the other non-relevant tabs.  If you could spot were I've gone wrong I'd be forever grateful.  There are no "squigglies" and it Builds succesfully, but I get the "IndexOutOfRangeException " as I mentioned.


Imports System.Data.SqlTypes
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms

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 Form1_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.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.JobTitles)
        Me.MonitorsTableAdapter.Fill(Me.SYEP2007DataSet.Monitors)
        Me.VJobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.vJobTitles)
        Me.VMonitorLoadTableAdapter.Fill(Me.SYEP2007DataSet.vMonitorLoad)
        Me.VPositionsByMonitorTableAdapter.Fill(Me.SYEP2007DataSet.vPositionsByMonitor)
        Me.VPositionsByWorksiteTableAdapter.Fill(Me.SYEP2007DataSet.vPositionsByWorksite)
        Me.WorksitesTableAdapter.Fill(Me.SYEP2007DataSet.Worksites)

        '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
        'Dim reader As SqlDataReader = Nothing
        'Try
        '    ' open the connection
        '    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)

        '    '' create a command string and use it to instantiate a command
        '    'Dim cmdString As String = _
        '    '    "SELECT cityid, cityName FROM tblcities_old ORDER BY cityid"
        '    'cmd = New SqlCommand(cmdString, cn)

        '    '' execute the command
        '    'reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        '    '' populate the list box with the results
        '    'Do While reader.Read()
        '    '    ListBox1.Items.Add(reader("cityID").ToString() & " " & _
        '    '        reader("cityName").ToString())
        '    'Loop

        '    '' close the data reader
        '    'reader.Close()
        'Catch ex As Exception
        '    MessageBox.Show(ex.Message, "Error", _
        '        MessageBoxButtons.OK, MessageBoxIcon.Error)
        'Finally
        '    If cn IsNot Nothing Then
        '        cn.Close()
        '    End If
        '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
        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"
        '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, Button1.Click
    '    Me.Close()
    'End Sub
    'Private Sub TextBox1_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.Enter
    '    If TextBox1.Text = "Last Name" Then
    '        TextBox1.Text = ""
    '    End If
    'End Sub
    'Private Sub TextBox1_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.LostFocus
    '    If TextBox1.Text = "" Then
    '        TextBox1.Text = "Last Name"
    '    End If
    'End Sub
    'Private Sub WorksitesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WorksitesBindingNavigatorSaveItem.Click
    '    Me.Validate()
    '    Me.WorksitesBindingSource.EndEdit()
    '    Dim rowsAffected As Integer = 0
    '    rowsAffected = Me.WorksitesTableAdapter.Update(Me.SYEP2007DataSet.Worksites)
    '    MessageBox.Show(rowsAffected.ToString + " Rows were affected")
    'End Sub

    'Private Sub btnUpdateData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateWorksite.Click
    '    Me.Validate()
    '    Me.WorksitesBindingSource.EndEdit()
    '    Dim rowsAffected As Integer = 0
    '    rowsAffected = Me.WorksitesTableAdapter.Update(Me.SYEP2007DataSet.Worksites)
    '    MessageBox.Show(rowsAffected.ToString + " Rows were affected")
    'End Sub

    'Private Sub btnUpdateMonitor_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateMonitor.Click
    '    Me.Validate()
    '    Me.MonitorsBindingSource.EndEdit()
    '    Dim rowsAffected As Integer = 0
    '    rowsAffected = Me.MonitorsTableAdapter.Update(Me.SYEP2007DataSet.Monitors)
    '    MessageBox.Show(rowsAffected.ToString + " Rows were affected")
    'End Sub

    'Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
    '    Me.Close()
    'End Sub

    '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 = "UID=dmegnin;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SYEP2007;Data Source=BETASERVE;Packet Size=4096;"
    '        cmd1.Connection = con
    '        '        cmd1.CommandText = "SELECT SUM(TotalPositions) AS Positions FROM Worksites"
    '        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

    Private Sub btnUpdatePosition_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdatePosition.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("keyJobTitleID = " & Convert.ToString(applicantsRow.Item("Job")))
            If Not oldJobRow Is Nothing Then
                'oldJobRow(0).Item("Number") += 1  '<--- This only works if Option Strict is OFF
                Dim myVal As Integer = CInt(oldJobRow(0).Item("txtWorksiteJobTitlePositionsNumber"))
                myVal += 1 'or myVal = myVal + 1
                oldJobRow(0).Item("txtWorksiteJobTitlePositionsNumber") = myVal
            Else
                MsgBox("Something wrong")
            End If
        End If
        'put the new job number in the applicant's record
        applicantsRow.Item("Job") = jobsRow.Item("keyJobTitleID")
        'decrement the number of jobs available
        'jobsRow.Item("Number") -= 1  '<--- This only works if Option Strict is OFF
        Dim myVal2 As Integer = CInt(jobsRow.Item("txtWorksiteJobTitlePositionsNumber"))
        myVal2 -= 1 'or myVal2 = myVal2 + 1
        jobsRow.Item("txtWorksiteJobTitlePositionsNumber") = myVal2

        '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 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 = "UID=dmegnin;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SYEP2007;Data Source=BETASERVE;Packet Size=4096;"
    '    cmd.Connection = con
    '    cmd.CommandType = CommandType.StoredProcedure
    '    con.Open()
    '    cmd.ExecuteNonQuery()
    '    con.Close()

    '    JobTitlesBindingSource.ResetBindings(False)
    '    'JobTitlesDataGridView.
    '    'JobTitlesDataGridView.EndEdit()
    '    'JobTitlesDataGridView.Refresh()
    '    'JobTitlesTableAdapter.Fill()
    '    'fktablebindingsource.position = var
    '    'Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)

    'End Sub
    'Private Sub cbFindJobTitle_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbFindJobTitle.SelectedIndexChanged

    'End Sub

    '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
    '    'ApplicantsDataGridView.Rows(0).Selected = True
    'End Sub
    'Private Sub ApplicantsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ApplicantsBindingNavigatorSaveItem.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")
    '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 Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    '    Form2.Show()

    'End Sub

    'Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
    '    Form3.Show()

    'End Sub

    'Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
    '    Form4.Show()

    'End Sub

    'Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
    '    Form5.Show()

    '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
    End Sub

    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("keyJobTitleID = " & 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 CStr(dr(0)("txtWorksiteJobTitle"))
        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

End Class




No, you shouldn't need to remove the bindingsources as the datasources.  The resetting of the datasources in code should override anything that was done in the designer.  Although your code is not quite as I'd suggested, I don't think the differences should cause problems.  It is, however, often very difficult to know just from reading code what might be causing problems.

But I do think you need to change one thing in that Function.  Change

        If Not dr Is Nothing Then

to

        If dr.Length > 0 Then

That was an error on my part.  My testing didn't pick it up, because none of the moves I made actually resulted in the return of no records into the dr() array.  But that array would never be Nothing because it has been declared - albeit as an empty array - in the previous line.  My guess is that what is happening is that no rows are being returned, but it is getting past the Not ... Nothing condition test, and so throwing an error in relation to element (0) in that array when the array is still empty.  If that is correct, making the alteration I've suggested should avoid the error, but it will leave the question of why no rows are being returned.

As Goran remarked in the other thread, one thing you will need to learn is debugging.  Now is the time, I think ;-)  But let's get into the subject gently.

We need to narrow down, with your setup and your data, why no rows are being returned.  First, we need to check that there are rows in the datatable - dtJobs - itself.  So, temporarily, put either this line

        Debug.WriteLine(dtJobs.Rows.Count)

or this line

        MsgBox(dtJobs.Rows.Count)

as the first line in the getTitleFromID function.  I would use the former, but that means I have to be able to see the Immediate Window as the program is running.  If the app is maximized, that might be hidden, so you might prefer the latter.

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

       "keyJobTitleID = " & ID

Can you try that, and let me know what happens?

Oh, and if the report is that there are rows in the datatable, can you please confirm that the keyJobTitleID is indeed an Integer?

Roger
I made the change above to the getTitleFromID function and added the Debug.WriteLine(dtJobs.Rows.Count) line to the beginning of the function.
I got this error before the changes and continued to get it after the changes, so I have a problem somewhere else.

"DataGridView Default Error Dialog
[red X]
The following exception occurred in the DataGridView:
System.Exception:  is not a valid value for Int32.  ---> System.IndexOutOf RangeException: Index was outside the bounds of the array.
at System.ComponentModel.BaseNumberconverter.convertFrom(ITypeDescriptorContext context, CultureInfo culture, Object value)
---End of inner exception stack trace ---
at System.ComponentModel.Typeconverter.ConvertFrom(Object value)
at System.Windoes.Forms.DataGridView.DataGridViewDataConnection.PushValue(Int32 boundColumnIndex, Int32 columnIndex, Int32 rowIndex, Object value)
To replace this default dialog please handle the DataError event."

I verified that all my keyXxxxID fields are of type int in the database.
In the DataGridViews the fields are all text boxes, but are listed as data type integer if you select "customize" in the Data Sources window for the individual field under the DataSet.

I'll keep trying to track down that problem.

Thank you again so much for your help.  I can't tell you how much I appreciate it.  I would be completely lost without your assistance.

David
David

Sorry, there was another error of a similar sort elsewhere in my code that I've just spotted.  In the btnUpdatePosition_Click sub replace this

            If Not oldJobRow Is Nothing Then

by this

            If oldJobRow.Length > 0 Then

That is not, in fact, the actual cause of the current error, but it could certainly lead to an error.

What the error message you are currently reporting usually means is that a value of one datatype in the DataGridView is trying to slot itself into a bound datatable in a column of a different datatype.  To try track the problem down, add this code

    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

It will still throw the error, but the debug output should tell you in which row and column of the data it is occurring, and the old value (which should be legitimate) and the new value (which is likely to be wrong) in that datacell.  That will give us a lead as to what is trying to put a wrong value there.

Roger

I made the change above and added the sub and ran it.  The app started and then I made an "assignment" and pressed the "update data" button1_click.

It gave me a "row not found" or something on row 182, so I changed:
"If Not TypeOf (applicantsRow.Item("Job")) Is DBNull Then" to
"If Not TypeOf (applicantsRow.Item("keyJobTitleID")) Is DBNull Then" to match the column name in my database.

I ran it again and it got past that line and gave me the same error on line 197 so I did the same there, changing:
"applicantsRow.Item("Job") = jobsRow.Item("keyJobTitleID")" to
"applicantsRow.Item("keyJobTitleID") = jobsRow.Item("keyJobTitleID")"

On restoring a Job previously removed, by reassigning that person a different job I found another instance of "Job" on line 83 and changed it as well.

This time it ran, added up the totals correctly and put the job title where they belong and everything.

I made some "assignments" and everything worked perfectly.  The Jobs were removed from the list as the number of available reached zero, just like they are suppose to.  :-))

The only thing it didn't do was to actually update the database with the new information.
I closed the app and opened it and it was back as it was before any assignments.

I made several assignments and then went back to some I had made and changed them to a new Job.  In most cases the old Job came back as it should and the new assignment was made correctly.

In a few cases I assigned a kid a job that had only one position left, so that one was removed. When I tried to re-assign the same kid a different Job, nothing happend.  Then I assigned a different kid a different job and came back and was able to assign the first kid a different Job and the old one came back.

In another case I selected a Job and the one above it was assigned to the kid.  In that case, the Job was several down in the list and had, say, 3 available and the one above it had, maybe 2 available.  I selected the one with 3, but the one with 2 went to the kid.

Neither of those happens consistently.  In most cases it all works correctly.
Playing with it some more... I can make it work correctly in the first case, by making an assignment to  a different kid, then going back to the one I want to change his assignment.  Then it will make the change and give back the Job if it had been removed.

The one where it assigns the wrong Job; the one above the selected one; seems to happen when the old assignment was a single Job so the it has to be "given back" to the list.  Maybe when it is put back on the list it pushes the selected one down prior to the assignment making the index off by one, so the wrong Job is assigned.  Did I make any sense there?

Wahoo.  I think we are very close to this being done.   I really appreciate all of your help.

David
>>
The only thing it didn't do was to actually update the database with the new information.
I closed the app and opened it and it was back as it was before any assignments.
<<

That's because there is, nowhere that I can see anyway, any code to do that.  All you should need is something on the lines

        '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)

You coud either add a button marked, say, "Save" and put the code in the click event for that.  Or you could put it in your form's FormClosing event sub.

On the other point, I see what you mean.  Something else I hadn't tested.  I've got an idea as to what might be causing it but won't have time to check it out for an hour or so.  I'll be back ASAP

Roger
Thank you very much Roger!

I showed it to my boss and he loves it.

Would it cause any problems if I put the "Save" code in the "Update" button, so it commits the changes as they are made?

How can I remove a Job from a kid once it's assigned?  For example a kid drops out of the program, so he will not be re-assigned another Job, but his Job needs to be returned to the list.

Thanks again.  It's looking really good.  I can see now that I would have never been able to get it to work.  

David
'I thought I'd go ahead and past the code here as it currently is so if you need to look at the same thing I'm running instead of something that may have already been changed.  I did remove the blocks of code that were commented out...

Imports System.Data.SqlTypes
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms

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 Form1_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.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.JobTitles)

        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
        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"
        '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 btnUpdatePosition_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdatePosition.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                   'changed the name from Job to keyJobTitleID
        If Not TypeOf (applicantsRow.Item("keyJobTitleID")) 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("keyJobTitleID = " & Convert.ToString(applicantsRow.Item("keyJobTitleID")))
            If oldJobRow.Length > 0 Then
                'oldJobRow(0).Item("Number") += 1  '<--- This only works if Option Strict is OFF
                Dim myVal As Integer = CInt(oldJobRow(0).Item("txtWorksiteJobTitlePositionsNumber"))
                myVal += 1 'or myVal = myVal + 1
                oldJobRow(0).Item("txtWorksiteJobTitlePositionsNumber") = myVal
            Else
                MsgBox("Something wrong")
            End If
        End If
        'put the new job number in the applicant's record
        'applicantsRow.Item("Job") = jobsRow.Item("keyJobTitleID")                  'changed Job to keyJobTitleID
        applicantsRow.Item("keyJobTitleID") = jobsRow.Item("keyJobTitleID")
        'decrement the number of jobs available
        'jobsRow.Item("Number") -= 1  '<--- This only works if Option Strict is OFF
        Dim myVal2 As Integer = CInt(jobsRow.Item("txtWorksiteJobTitlePositionsNumber"))
        myVal2 -= 1 'or myVal2 = myVal2 + 1
        jobsRow.Item("txtWorksiteJobTitlePositionsNumber") = myVal2

        '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 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
    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 rows
        If dr.Length > 0 Then
            'if yes, there should only be one - in position 0
            'return the value in the JobNmae field from that
            Return CStr(dr(0)("txtWorksiteJobTitle"))
        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
    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
End Class


'David
I committed a few records, then closed the app and checked the database to be sure the Jobs were in the correct fields.  Everything was saved correctly.

I opened the app and all the assignments were blank again.   I'm not sure at what point the dataset and datatables get "filled" from the database.  The Jobs are going to the database.  Just not coming back to the app after it's closed and opened again.

David
It seems like the Me.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)
in the Form1_Load should be doing that shouldn't it?

David
I just notice that the numbers are correct according to what's in the database.
If a Job had a total of 2 positions and I assigned 1 of them to a kid, close the app and opened it up again.  In the DataGridView, the Job Title field is blank, but the Positions Available, Taken and Total correctly show 1, 1 and 2.

David
Preview Data shows the Jobs in the SYEP2007DataSet.
ApplicantsTableAdapter also shows the Jobs
ASKER CERTIFIED SOLUTION
Avatar of Sancler
Sancler

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,

I just got back from Jury Duty.  This project actually got me excused because we are providing jobs for disabled kids this summer.

Anyway, yes, I agree about closing this thread.  I really, really appreciate all your help so far.  I'll try to figure out how to return a job to the pool if an applicant drops out of the program.  

I do hope you will help me again, as I'm sure to have more questions as I go along.  You have helped me tremendously.  I really appreciate it.

David
David

I'll certainly be happy to help you again.  As I say, when I'm about, I do keep my eye on who's asking what.

Thanks for the points and the kind comments.

Roger
Roger,

I did move the EndCurrentEdit and Update code back to the Form1_Closing event.   I couldn't think of any reason not to.

Thanks again.

Daivd