David Megnin
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(d ca0)
Dim dca1 As New DataColumn("Name", GetType(String))
dtApplicants.Columns.Add(d ca1)
Dim dca2 As New DataColumn("Job", GetType(Integer))
dtApplicants.Columns.Add(d ca2)
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.Dat aSource = dtApplicants
'set up the currencymanager - convenient for referring ...
'... to the currently selected row in the applicants grid
cmApplicants = CType(BindingContext(dtApp licants), 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.Data Source = dvjobs
'set up the currency manager
cmJobs = CType(BindingContext(dvjob s), CurrencyManager)
'customise the grids - with a strongly typed dataset ...
'... this could be done in the designer
ApplicantsDataGridView.Rea dOnly = True
ApplicantsDataGridView.All owUserToAd dRows = False
'hide the applicant ID
ApplicantsDataGridView.Col umns(0).Vi sible = False
'hide the job ID ...
ApplicantsDataGridView.Col umns(2).Vi sible = False
'... but add an unbound column to show ...
'... the job title matching that ID
Dim TitleCol As New DataGridViewTextBoxColumn
TitleCol.HeaderText = "Job title"
ApplicantsDataGridView.Col umns.Add(T itleCol)
JobTitlesDataGridView.Read Only = True
JobTitlesDataGridView.Allo wUserToAdd Rows = False
JobTitlesDataGridView.Colu mns(0).Vis ible = 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.Row s
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(applicant sRow.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("Nu mber"))
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.EndCurrentEdi t()
'run the sub to update the jobs numbers displays
updateTotals()
'get the job title for this applicant
Dim dgvr As DataGridViewRow = ApplicantsDataGridView.Cur rentRow
fillInTitle(dgvr)
End Sub
Private Sub cmJobs_PositionChanged(ByV al 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.C ells(2).Va lue))
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.SY EP2007Data Set.Applic ants)
Me.JobTitlesTableAdapter.F ill(Me.SYE P2007DataS et.JobTitl es)
Me.MonitorsTableAdapter.Fi ll(Me.SYEP 2007DataSe t.Monitors )
Me.VJobTitlesTableAdapter. Fill(Me.SY EP2007Data Set.vJobTi tles)
Me.VMonitorLoadTableAdapte r.Fill(Me. SYEP2007Da taSet.vMon itorLoad)
Me.VPositionsByMonitorTabl eAdapter.F ill(Me.SYE P2007DataS et.vPositi onsByMonit or)
Me.VPositionsByWorksiteTab leAdapter. Fill(Me.SY EP2007Data Set.vPosit ionsByWork site)
Me.WorksitesTableAdapter.F ill(Me.SYE P2007DataS et.Worksit es)
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.Execu teScalar() )
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(CommandB ehavior.Cl oseConnect ion)
'' populate the list box with the results
'Do While reader.Read()
' ListBox1.Items.Add(reader( "cityID"). ToString() & " " & _
' reader("cityName").ToStrin g())
'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 WorksitesBindingNavigatorS aveItem_Cl ick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WorksitesBindingNavigatorS aveItem.Cl ick
Me.Validate()
Me.WorksitesBindingSource. EndEdit()
Dim rowsAffected As Integer = 0
rowsAffected = Me.WorksitesTableAdapter.U pdate(Me.S YEP2007Dat aSet.Works ites)
MessageBox.Show(rowsAffect ed.ToStrin g + " 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.U pdate(Me.S YEP2007Dat aSet.Works ites)
MessageBox.Show(rowsAffect ed.ToStrin g + " Rows were affected")
End Sub
Private Sub btnUpdateMonitor_Click(ByV al sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateMonitor.Click
Me.Validate()
Me.MonitorsBindingSource.E ndEdit()
Dim rowsAffected As Integer = 0
rowsAffected = Me.MonitorsTableAdapter.Up date(Me.SY EP2007Data Set.Monito rs)
MessageBox.Show(rowsAffect ed.ToStrin g + " 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_SelectedIndexCha nged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox3.SelectedIndexCha nged
Dim con As New SqlConnection
Dim cmd1 As New SqlCommand
Dim total As Integer
Dim SelectedMonitor As String
If WorksitesDataGridView.Sele ctedRows.C ount > 0 Then
SelectedMonitor = WorksitesDataGridView.Sele ctedRows(0 ).Cells(0) .Value.ToS tring
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(By Val 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. SYEP2007Da taSet.Appl icants)
MessageBox.Show(rowsAffect ed.ToStrin g + " Rows were affected")
Dim con As New SqlConnection
Dim cmd As SqlCommand = New SqlCommand("dbo.spUpdate_J obTitles_P ositionsAv ailable", 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.StoredProcedur e
con.Open()
cmd.ExecuteNonQuery()
con.Close()
JobTitlesBindingSource.Res etBindings (False)
'JobTitlesDataGridView. 'uh, none of these worked.
'JobTitlesDataGridView.End Edit()
'JobTitlesDataGridView.Ref resh()
'JobTitlesTableAdapter.Fil l()
'fktablebindingsource.posi tion = var
'Me.ApplicantsTableAdapter .Update(Me .SYEP2007D ataSet.App licants)
End Sub
Private Sub cbFindJobTitle_SelectedInd exChanged( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbFindJobTitle.SelectedInd exChanged
End Sub
Private Sub JobTitlesDataGridView_Sele ctionChang ed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataG ridViewCel lEventArgs ) Handles JobTitlesDataGridView.Cell Enter
'txtAvailable.Text = CStr(cbFindJobTitle.Select edValue)
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.Sele ctedRows.C ount > 0 Then
varJobTitleID = JobTitlesDataGridView.Sele ctedRows(0 ).Cells(5) .Value.ToS tring
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.txtWorksiteJobTi tlePositio nsNumber - COUNT(Applicants.keyApplic antID) AS availablePositons FROM JobTitles INNER JOIN Applicants ON JobTitles.keyJobTitleID = Applicants.keyJobTitleID WHERE (JobTitles.keyJobTitleID = '" & varJobTitleID & "') GROUP BY JobTitles.txtWorksiteJobTi tlePositio nsNumber"
cmd3.Connection = con ' Taken
cmd3.CommandText = "SELECT COUNT(Applicants.keyApplic antID) 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.txtWorksiteJ obTitlePos itionsNumb er) 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.Ro ws(0).Sele cted = True
End Sub
Private Sub ApplicantsBindingNavigator SaveItem_C lick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ApplicantsBindingNavigator SaveItem.C lick
Me.Validate()
Me.ApplicantsBindingSource .EndEdit()
Dim rowsAffected As Integer = 0
rowsAffected = Me.ApplicantsTableAdapter. Update(Me. SYEP2007Da taSet.Appl icants)
MessageBox.Show(rowsAffect ed.ToStrin g + " Rows were affected")
End Sub
Private Sub FillByToolStripButton_Clic k(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillByToolStripButton.Clic k
Try
Me.JobTitlesTableAdapter.F illBy(Me.S YEP2007Dat aSet.JobTi tles)
Catch ex As System.Exception
System.Windows.Forms.Messa geBox.Show (ex.Messag e)
End Try
End Sub
End Class
Suggestions greatly appreciated.
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(d
Dim dca1 As New DataColumn("Name", GetType(String))
dtApplicants.Columns.Add(d
Dim dca2 As New DataColumn("Job", GetType(Integer))
dtApplicants.Columns.Add(d
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.Dat
'set up the currencymanager - convenient for referring ...
'... to the currently selected row in the applicants grid
cmApplicants = CType(BindingContext(dtApp
'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.Data
'set up the currency manager
cmJobs = CType(BindingContext(dvjob
'customise the grids - with a strongly typed dataset ...
'... this could be done in the designer
ApplicantsDataGridView.Rea
ApplicantsDataGridView.All
'hide the applicant ID
ApplicantsDataGridView.Col
'hide the job ID ...
ApplicantsDataGridView.Col
'... but add an unbound column to show ...
'... the job title matching that ID
Dim TitleCol As New DataGridViewTextBoxColumn
TitleCol.HeaderText = "Job title"
ApplicantsDataGridView.Col
JobTitlesDataGridView.Read
JobTitlesDataGridView.Allo
JobTitlesDataGridView.Colu
'run the sub to fill the jobs numbers displays
updateTotals()
'get the job titles for the applicants
For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Row
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
'check if this applicant already has a job
If Not TypeOf (applicantsRow.Item("Job")
'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(applicant
If Not oldJobRow Is Nothing Then
'oldJobRow(0).Item("Number
Dim myVal As Integer = CInt(oldJobRow(0).Item("Nu
myVal += 1 'or myVal = myVal + 1
oldJobRow(0).Item("Number"
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.EndCurrentEdi
'run the sub to update the jobs numbers displays
updateTotals()
'get the job title for this applicant
Dim dgvr As DataGridViewRow = ApplicantsDataGridView.Cur
fillInTitle(dgvr)
End Sub
Private Sub cmJobs_PositionChanged(ByV
'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(
'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.C
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
Me.ApplicantsTableAdapter.
Me.JobTitlesTableAdapter.F
Me.MonitorsTableAdapter.Fi
Me.VJobTitlesTableAdapter.
Me.VMonitorLoadTableAdapte
Me.VPositionsByMonitorTabl
Me.VPositionsByWorksiteTab
Me.WorksitesTableAdapter.F
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.Execu
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(CommandB
'' populate the list box with the results
'Do While reader.Read()
' ListBox1.Items.Add(reader(
' reader("cityName").ToStrin
'Loop
'' close the data reader
'reader.Close()
Catch ex As Exception
MessageBox.Show(ex.Message
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 WorksitesBindingNavigatorS
Me.Validate()
Me.WorksitesBindingSource.
Dim rowsAffected As Integer = 0
rowsAffected = Me.WorksitesTableAdapter.U
MessageBox.Show(rowsAffect
End Sub
Private Sub btnUpdateData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateWorksite.Click
Me.Validate()
Me.WorksitesBindingSource.
Dim rowsAffected As Integer = 0
rowsAffected = Me.WorksitesTableAdapter.U
MessageBox.Show(rowsAffect
End Sub
Private Sub btnUpdateMonitor_Click(ByV
Me.Validate()
Me.MonitorsBindingSource.E
Dim rowsAffected As Integer = 0
rowsAffected = Me.MonitorsTableAdapter.Up
MessageBox.Show(rowsAffect
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_SelectedIndexCha
Dim con As New SqlConnection
Dim cmd1 As New SqlCommand
Dim total As Integer
Dim SelectedMonitor As String
If WorksitesDataGridView.Sele
SelectedMonitor = WorksitesDataGridView.Sele
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(By
Me.Validate()
Me.ApplicantsBindingSource
Dim rowsAffected As Integer = 0
rowsAffected = Me.ApplicantsTableAdapter.
MessageBox.Show(rowsAffect
Dim con As New SqlConnection
Dim cmd As SqlCommand = New SqlCommand("dbo.spUpdate_J
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.StoredProcedur
con.Open()
cmd.ExecuteNonQuery()
con.Close()
JobTitlesBindingSource.Res
'JobTitlesDataGridView. 'uh, none of these worked.
'JobTitlesDataGridView.End
'JobTitlesDataGridView.Ref
'JobTitlesTableAdapter.Fil
'fktablebindingsource.posi
'Me.ApplicantsTableAdapter
End Sub
Private Sub cbFindJobTitle_SelectedInd
End Sub
Private Sub JobTitlesDataGridView_Sele
'txtAvailable.Text = CStr(cbFindJobTitle.Select
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.Sele
varJobTitleID = JobTitlesDataGridView.Sele
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.txtWorksiteJobTi
cmd3.Connection = con ' Taken
cmd3.CommandText = "SELECT COUNT(Applicants.keyApplic
cmd4.Connection = con ' Total
cmd4.CommandText = "SELECT SUM(JobTitles.txtWorksiteJ
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.Ro
End Sub
Private Sub ApplicantsBindingNavigator
Me.Validate()
Me.ApplicantsBindingSource
Dim rowsAffected As Integer = 0
rowsAffected = Me.ApplicantsTableAdapter.
MessageBox.Show(rowsAffect
End Sub
Private Sub FillByToolStripButton_Clic
Try
Me.JobTitlesTableAdapter.F
Catch ex As System.Exception
System.Windows.Forms.Messa
End Try
End Sub
End Class
Suggestions greatly appreciated.
ASKER
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.
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.
David
In the demo code the dtApplicants is equivalent to your Me.SYEP2007DataSet.Applica nts and the dtJobs is equivalent to your Me.SYEP2007DataSet.JobTitl es.
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.F ill(Me.SYE P2007DataS et.Worksit es)
and comment out everything in that sub after that. Then add, in that sub
dtApplicants = Me.SYEP2007DataSet.Applica nts
dtJobs = Me.SYEP2007DataSet.JobTitl es
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
In the demo code the dtApplicants is equivalent to your Me.SYEP2007DataSet.Applica
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.F
and comment out everything in that sub after that. Then add, in that sub
dtApplicants = Me.SYEP2007DataSet.Applica
dtJobs = Me.SYEP2007DataSet.JobTitl
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
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
ASKER
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.C ells(2).Va lue))" to "dgvr.Cells(99).Value = getTitleFromID(CInt(dgvr.C ells(2).Va lue))" to account for
"ApplicantsDataGridView.Co lumns.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
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.C
"ApplicantsDataGridView.Co
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.C ells(2).Va lue))
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
dgvr.Cells(3).Value = getTitleFromID(CInt(dgvr.C
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
ASKER
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 txtWorksiteJobTitlePositio nsNumber 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("keyJobTitle 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)("txtWorksiteJob Title"))
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.SY EP2007Data Set.Applic ants)
Me.JobTitlesTableAdapter.F ill(Me.SYE P2007DataS et.JobTitl es)
Me.MonitorsTableAdapter.Fi ll(Me.SYEP 2007DataSe t.Monitors )
Me.VJobTitlesTableAdapter. Fill(Me.SY EP2007Data Set.vJobTi tles)
Me.VMonitorLoadTableAdapte r.Fill(Me. SYEP2007Da taSet.vMon itorLoad)
Me.VPositionsByMonitorTabl eAdapter.F ill(Me.SYE P2007DataS et.vPositi onsByMonit or)
Me.VPositionsByWorksiteTab leAdapter. Fill(Me.SY EP2007Data Set.vPosit ionsByWork site)
Me.WorksitesTableAdapter.F ill(Me.SYE P2007DataS et.Worksit es)
'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.Execu teScalar() )
' 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(CommandB ehavior.Cl oseConnect ion)
' '' populate the list box with the results
' 'Do While reader.Read()
' ' ListBox1.Items.Add(reader( "cityID"). ToString() & " " & _
' ' reader("cityName").ToStrin g())
' '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.Applica nts
dtJobs = Me.SYEP2007DataSet.JobTitl es
'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.Dat aSource = dtApplicants
'set up the currencymanager - convenient for referring ...
'... to the currently selected row in the applicants grid
cmApplicants = CType(BindingContext(dtApp licants), 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 = "txtWorksiteJobTitlePositi onsNumber > 0"
'bind the jobs table to the datagridview
JobTitlesDataGridView.Data Source = dvjobs
'set up the currency manager
cmJobs = CType(BindingContext(dvjob s), CurrencyManager)
'customise the grids - with a strongly typed dataset ...
'... this could be done in the designer
ApplicantsDataGridView.Rea dOnly = True
ApplicantsDataGridView.All owUserToAd dRows = False
'hide the applicant ID
ApplicantsDataGridView.Col umns(0).Vi sible = False
'hide the job ID ...
ApplicantsDataGridView.Col umns(2).Vi sible = False
'... but add an unbound column to show ...
'... the job title matching that ID
Dim TitleCol As New DataGridViewTextBoxColumn
TitleCol.HeaderText = "Job title"
ApplicantsDataGridView.Col umns.Add(T itleCol)
JobTitlesDataGridView.Read Only = True
JobTitlesDataGridView.Allo wUserToAdd Rows = False
JobTitlesDataGridView.Colu mns(0).Vis ible = 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.Row s
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 WorksitesBindingNavigatorS aveItem_Cl ick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles WorksitesBindingNavigatorS aveItem.Cl ick
' Me.Validate()
' Me.WorksitesBindingSource. EndEdit()
' Dim rowsAffected As Integer = 0
' rowsAffected = Me.WorksitesTableAdapter.U pdate(Me.S YEP2007Dat aSet.Works ites)
' MessageBox.Show(rowsAffect ed.ToStrin g + " 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.U pdate(Me.S YEP2007Dat aSet.Works ites)
' MessageBox.Show(rowsAffect ed.ToStrin g + " Rows were affected")
'End Sub
'Private Sub btnUpdateMonitor_Click(ByV al sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateMonitor.Click
' Me.Validate()
' Me.MonitorsBindingSource.E ndEdit()
' Dim rowsAffected As Integer = 0
' rowsAffected = Me.MonitorsTableAdapter.Up date(Me.SY EP2007Data Set.Monito rs)
' MessageBox.Show(rowsAffect ed.ToStrin g + " 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_SelectedIndexCha nged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox3.SelectedIndexCha nged
' Dim con As New SqlConnection
' Dim cmd1 As New SqlCommand
' Dim total As Integer
' Dim SelectedMonitor As String
' If WorksitesDataGridView.Sele ctedRows.C ount > 0 Then
' SelectedMonitor = WorksitesDataGridView.Sele ctedRows(0 ).Cells(0) .Value.ToS tring
' 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(By Val 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("keyJobTitle ID = " & Convert.ToString(applicant sRow.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("tx tWorksiteJ obTitlePos itionsNumb er"))
myVal += 1 'or myVal = myVal + 1
oldJobRow(0).Item("txtWork siteJobTit lePosition sNumber") = myVal
Else
MsgBox("Something wrong")
End If
End If
'put the new job number in the applicant's record
applicantsRow.Item("Job") = jobsRow.Item("keyJobTitleI D")
'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("txtWork siteJobTit lePosition sNumber"))
myVal2 -= 1 'or myVal2 = myVal2 + 1
jobsRow.Item("txtWorksiteJ obTitlePos itionsNumb er") = 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.EndCurrentEdi t()
'run the sub to update the jobs numbers displays
updateTotals()
'get the job title for this applicant
Dim dgvr As DataGridViewRow = ApplicantsDataGridView.Cur rentRow
fillInTitle(dgvr)
End Sub
'Private Sub btnUpdatePosition_Click(By Val 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. SYEP2007Da taSet.Appl icants)
' MessageBox.Show(rowsAffect ed.ToStrin g + " Rows were affected")
' Dim con As New SqlConnection
' Dim cmd As SqlCommand = New SqlCommand("dbo.spUpdate_J obTitles_P ositionsAv ailable", 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.StoredProcedur e
' con.Open()
' cmd.ExecuteNonQuery()
' con.Close()
' JobTitlesBindingSource.Res etBindings (False)
' 'JobTitlesDataGridView.
' 'JobTitlesDataGridView.End Edit()
' 'JobTitlesDataGridView.Ref resh()
' 'JobTitlesTableAdapter.Fil l()
' 'fktablebindingsource.posi tion = var
' 'Me.ApplicantsTableAdapter .Update(Me .SYEP2007D ataSet.App licants)
'End Sub
'Private Sub cbFindJobTitle_SelectedInd exChanged( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbFindJobTitle.SelectedInd exChanged
'End Sub
'Private Sub JobTitlesDataGridView_Sele ctionChang ed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataG ridViewCel lEventArgs ) Handles JobTitlesDataGridView.Cell Enter
' 'txtAvailable.Text = CStr(cbFindJobTitle.Select edValue)
' 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.Sele ctedRows.C ount > 0 Then
' varJobTitleID = JobTitlesDataGridView.Sele ctedRows(0 ).Cells(5) .Value.ToS tring
' 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.txtWorksiteJobTi tlePositio nsNumber - COUNT(Applicants.keyApplic antID) AS availablePositons FROM JobTitles INNER JOIN Applicants ON JobTitles.keyJobTitleID = Applicants.keyJobTitleID WHERE (JobTitles.keyJobTitleID = '" & varJobTitleID & "') GROUP BY JobTitles.txtWorksiteJobTi tlePositio nsNumber"
' cmd3.Connection = con ' Taken
' cmd3.CommandText = "SELECT COUNT(Applicants.keyApplic antID) 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.txtWorksiteJ obTitlePos itionsNumb er) 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.Ro ws(0).Sele cted = True
'End Sub
'Private Sub ApplicantsBindingNavigator SaveItem_C lick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ApplicantsBindingNavigator SaveItem.C lick
' Me.Validate()
' Me.ApplicantsBindingSource .EndEdit()
' Dim rowsAffected As Integer = 0
' rowsAffected = Me.ApplicantsTableAdapter. Update(Me. SYEP2007Da taSet.Appl icants)
' MessageBox.Show(rowsAffect ed.ToStrin g + " Rows were affected")
'End Sub
Private Sub btnCancelPosition_Click(By Val 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(ByV al 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("txtWork siteJobTit lePosition sNumber"))
'get the job ID from that row
Dim thisJob As Integer = CInt(jobsRow.Item("keyJobT itleID"))
'use the job ID to find how many applicants ...
'... already have that job
Dim taken As Integer = CInt(dtApplicants.Compute( "Count(key JobTitleID )", "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("keyJobTitle 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)("txtWorksiteJob Title"))
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.C ells(2).Va lue))
End If
End Sub
End Class
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("keyJobTitle
'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)("txtWorksiteJob
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
Me.ApplicantsTableAdapter.
Me.JobTitlesTableAdapter.F
Me.MonitorsTableAdapter.Fi
Me.VJobTitlesTableAdapter.
Me.VMonitorLoadTableAdapte
Me.VPositionsByMonitorTabl
Me.VPositionsByWorksiteTab
Me.WorksitesTableAdapter.F
'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.Execu
' 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(CommandB
' '' populate the list box with the results
' 'Do While reader.Read()
' ' ListBox1.Items.Add(reader(
' ' reader("cityName").ToStrin
' 'Loop
' '' close the data reader
' 'reader.Close()
'Catch ex As Exception
' MessageBox.Show(ex.Message
' MessageBoxButtons.OK, MessageBoxIcon.Error)
'Finally
' If cn IsNot Nothing Then
' cn.Close()
' End If
'End Try
dtApplicants = Me.SYEP2007DataSet.Applica
dtJobs = Me.SYEP2007DataSet.JobTitl
'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.Dat
'set up the currencymanager - convenient for referring ...
'... to the currently selected row in the applicants grid
cmApplicants = CType(BindingContext(dtApp
'set up a dataview to filter the jobs table so it ...
'... only shows those where some jobs remain available
dvjobs.Table = dtJobs
dvjobs.RowFilter = "txtWorksiteJobTitlePositi
'bind the jobs table to the datagridview
JobTitlesDataGridView.Data
'set up the currency manager
cmJobs = CType(BindingContext(dvjob
'customise the grids - with a strongly typed dataset ...
'... this could be done in the designer
ApplicantsDataGridView.Rea
ApplicantsDataGridView.All
'hide the applicant ID
ApplicantsDataGridView.Col
'hide the job ID ...
ApplicantsDataGridView.Col
'... but add an unbound column to show ...
'... the job title matching that ID
Dim TitleCol As New DataGridViewTextBoxColumn
TitleCol.HeaderText = "Job title"
ApplicantsDataGridView.Col
JobTitlesDataGridView.Read
JobTitlesDataGridView.Allo
JobTitlesDataGridView.Colu
'run the sub to fill the jobs numbers displays
updateTotals()
'get the job titles for the applicants
For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Row
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 WorksitesBindingNavigatorS
' Me.Validate()
' Me.WorksitesBindingSource.
' Dim rowsAffected As Integer = 0
' rowsAffected = Me.WorksitesTableAdapter.U
' MessageBox.Show(rowsAffect
'End Sub
'Private Sub btnUpdateData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateWorksite.Click
' Me.Validate()
' Me.WorksitesBindingSource.
' Dim rowsAffected As Integer = 0
' rowsAffected = Me.WorksitesTableAdapter.U
' MessageBox.Show(rowsAffect
'End Sub
'Private Sub btnUpdateMonitor_Click(ByV
' Me.Validate()
' Me.MonitorsBindingSource.E
' Dim rowsAffected As Integer = 0
' rowsAffected = Me.MonitorsTableAdapter.Up
' MessageBox.Show(rowsAffect
'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_SelectedIndexCha
' Dim con As New SqlConnection
' Dim cmd1 As New SqlCommand
' Dim total As Integer
' Dim SelectedMonitor As String
' If WorksitesDataGridView.Sele
' SelectedMonitor = WorksitesDataGridView.Sele
' 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(By
'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
'check if this applicant already has a job
If Not TypeOf (applicantsRow.Item("Job")
'need to return existing job to those available
'get from table the row for the old job
Dim oldJobRow As DataRow() = dtJobs.Select("keyJobTitle
If Not oldJobRow Is Nothing Then
'oldJobRow(0).Item("Number
Dim myVal As Integer = CInt(oldJobRow(0).Item("tx
myVal += 1 'or myVal = myVal + 1
oldJobRow(0).Item("txtWork
Else
MsgBox("Something wrong")
End If
End If
'put the new job number in the applicant's record
applicantsRow.Item("Job") = jobsRow.Item("keyJobTitleI
'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("txtWork
myVal2 -= 1 'or myVal2 = myVal2 + 1
jobsRow.Item("txtWorksiteJ
'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.EndCurrentEdi
'run the sub to update the jobs numbers displays
updateTotals()
'get the job title for this applicant
Dim dgvr As DataGridViewRow = ApplicantsDataGridView.Cur
fillInTitle(dgvr)
End Sub
'Private Sub btnUpdatePosition_Click(By
' Me.Validate()
' Me.ApplicantsBindingSource
' Dim rowsAffected As Integer = 0
' rowsAffected = Me.ApplicantsTableAdapter.
' MessageBox.Show(rowsAffect
' Dim con As New SqlConnection
' Dim cmd As SqlCommand = New SqlCommand("dbo.spUpdate_J
' 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.StoredProcedur
' con.Open()
' cmd.ExecuteNonQuery()
' con.Close()
' JobTitlesBindingSource.Res
' 'JobTitlesDataGridView.
' 'JobTitlesDataGridView.End
' 'JobTitlesDataGridView.Ref
' 'JobTitlesTableAdapter.Fil
' 'fktablebindingsource.posi
' 'Me.ApplicantsTableAdapter
'End Sub
'Private Sub cbFindJobTitle_SelectedInd
'End Sub
'Private Sub JobTitlesDataGridView_Sele
' 'txtAvailable.Text = CStr(cbFindJobTitle.Select
' 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.Sele
' varJobTitleID = JobTitlesDataGridView.Sele
' 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.txtWorksiteJobTi
' cmd3.Connection = con ' Taken
' cmd3.CommandText = "SELECT COUNT(Applicants.keyApplic
' cmd4.Connection = con ' Total
' cmd4.CommandText = "SELECT SUM(JobTitles.txtWorksiteJ
' 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.Ro
'End Sub
'Private Sub ApplicantsBindingNavigator
' Me.Validate()
' Me.ApplicantsBindingSource
' Dim rowsAffected As Integer = 0
' rowsAffected = Me.ApplicantsTableAdapter.
' MessageBox.Show(rowsAffect
'End Sub
Private Sub btnCancelPosition_Click(By
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(ByV
'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("txtWork
'get the job ID from that row
Dim thisJob As Integer = CInt(jobsRow.Item("keyJobT
'use the job ID to find how many applicants ...
'... already have that job
Dim taken As Integer = CInt(dtApplicants.Compute(
'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("keyJobTitle
'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)("txtWorksiteJob
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.C
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.Row s.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
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.Row
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
ASKER
I made the change above to the getTitleFromID function and added the Debug.WriteLine(dtJobs.Row s.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.Base Numberconv erter.conv ertFrom(IT ypeDescrip torContext context, CultureInfo culture, Object value)
---End of inner exception stack trace ---
at System.ComponentModel.Type converter. ConvertFro m(Object value)
at System.Windoes.Forms.DataG ridView.Da taGridView DataConnec tion.PushV alue(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
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.Base
---End of inner exception stack trace ---
at System.ComponentModel.Type
at System.Windoes.Forms.DataG
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_Dat aError(ByV al sender As Object, ByVal e As System.Windows.Forms.DataG ridViewDat aErrorEven tArgs) Handles ApplicantsDataGridView.Dat aError
Debug.WriteLine(e.ColumnIn dex)
Debug.WriteLine(e.RowIndex )
Debug.WriteLine(Applicants DataGridVi ew.Rows(e. RowIndex). Cells(e.Co lumnIndex) .Value)
Debug.WriteLine(Applicants DataGridVi ew.Rows(e. RowIndex). Cells(e.Co lumnIndex) .EditedFor mattedValu e)
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
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_Dat
Debug.WriteLine(e.ColumnIn
Debug.WriteLine(e.RowIndex
Debug.WriteLine(Applicants
Debug.WriteLine(Applicants
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
ASKER
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")
"If Not TypeOf (applicantsRow.Item("keyJo
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")
"applicantsRow.Item("keyJo
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.EndCurrentEdi t
cmJobs.EndCurrentEdit
'then save datatable changes to database
Me.ApplicantsTableAdapter. Update(Me. SYEP2007Da taSet.Appl icants)
Me.JobTitlesTableAdapter.U pdate(Me.S YEP2007Dat aSet.JobTi tles)
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
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.EndCurrentEdi
cmJobs.EndCurrentEdit
'then save datatable changes to database
Me.ApplicantsTableAdapter.
Me.JobTitlesTableAdapter.U
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
ASKER
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 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
ASKER
'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.SY EP2007Data Set.Applic ants)
Me.JobTitlesTableAdapter.F ill(Me.SYE P2007DataS et.JobTitl es)
dtApplicants = Me.SYEP2007DataSet.Applica nts
dtJobs = Me.SYEP2007DataSet.JobTitl es
'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.Dat aSource = dtApplicants
'set up the currencymanager - convenient for referring ...
'... to the currently selected row in the applicants grid
cmApplicants = CType(BindingContext(dtApp licants), 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 = "txtWorksiteJobTitlePositi onsNumber > 0"
'bind the jobs table to the datagridview
JobTitlesDataGridView.Data Source = dvjobs
'set up the currency manager
cmJobs = CType(BindingContext(dvjob s), CurrencyManager)
'customise the grids - with a strongly typed dataset ...
'... this could be done in the designer
ApplicantsDataGridView.Rea dOnly = True
ApplicantsDataGridView.All owUserToAd dRows = False
'hide the applicant ID
ApplicantsDataGridView.Col umns(0).Vi sible = False
'hide the job ID ...
ApplicantsDataGridView.Col umns(2).Vi sible = False
'... but add an unbound column to show ...
'... the job title matching that ID
Dim TitleCol As New DataGridViewTextBoxColumn
TitleCol.HeaderText = "Job Title"
ApplicantsDataGridView.Col umns.Add(T itleCol)
JobTitlesDataGridView.Read Only = True
JobTitlesDataGridView.Allo wUserToAdd Rows = False
JobTitlesDataGridView.Colu mns(0).Vis ible = 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.Row s
fillInTitle(dgvr)
Next
End Sub
Private Sub btnUpdatePosition_Click(By Val 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("keyJo bTitleID") ) 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("keyJobTitle ID = " & Convert.ToString(applicant sRow.Item( "keyJobTit leID")))
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("tx tWorksiteJ obTitlePos itionsNumb er"))
myVal += 1 'or myVal = myVal + 1
oldJobRow(0).Item("txtWork siteJobTit lePosition sNumber") = myVal
Else
MsgBox("Something wrong")
End If
End If
'put the new job number in the applicant's record
'applicantsRow.Item("Job") = jobsRow.Item("keyJobTitleI D") 'changed Job to keyJobTitleID
applicantsRow.Item("keyJob TitleID") = jobsRow.Item("keyJobTitleI D")
'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("txtWork siteJobTit lePosition sNumber"))
myVal2 -= 1 'or myVal2 = myVal2 + 1
jobsRow.Item("txtWorksiteJ obTitlePos itionsNumb er") = 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.EndCurrentEdi t()
'run the sub to update the jobs numbers displays
updateTotals()
'get the job title for this applicant
Dim dgvr As DataGridViewRow = ApplicantsDataGridView.Cur rentRow
fillInTitle(dgvr)
End Sub
Private Sub btnCancelPosition_Click(By Val sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelPosition.Click
Me.Close()
End Sub
Private Sub cmJobs_PositionChanged(ByV al 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("txtWork siteJobTit lePosition sNumber"))
'get the job ID from that row
Dim thisJob As Integer = CInt(jobsRow.Item("keyJobT itleID"))
'use the job ID to find how many applicants ...
'... already have that job
Dim taken As Integer = CInt(dtApplicants.Compute( "Count(key JobTitleID )", "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.Ro ws.Count)
'use datatable filter to return only rows with this ID
Dim dr As DataRow() = dtJobs.Select("keyJobTitle ID = " & 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)("txtWorksiteJob Title"))
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.C ells(2).Va lue))
End If
End Sub
Private Sub ApplicantsDataGridView_Dat aError(ByV al sender As Object, ByVal e As System.Windows.Forms.DataG ridViewDat aErrorEven tArgs) Handles ApplicantsDataGridView.Dat aError
Debug.WriteLine(e.ColumnIn dex)
Debug.WriteLine(e.RowIndex )
Debug.WriteLine(Applicants DataGridVi ew.Rows(e. RowIndex). Cells(e.Co lumnIndex) .Value)
Debug.WriteLine(Applicants DataGridVi ew.Rows(e. RowIndex). Cells(e.Co lumnIndex) .EditedFor mattedValu e)
e.ThrowException = True
End Sub
End Class
'David
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
Me.ApplicantsTableAdapter.
Me.JobTitlesTableAdapter.F
dtApplicants = Me.SYEP2007DataSet.Applica
dtJobs = Me.SYEP2007DataSet.JobTitl
'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.Dat
'set up the currencymanager - convenient for referring ...
'... to the currently selected row in the applicants grid
cmApplicants = CType(BindingContext(dtApp
'set up a dataview to filter the jobs table so it ...
'... only shows those where some jobs remain available
dvjobs.Table = dtJobs
dvjobs.RowFilter = "txtWorksiteJobTitlePositi
'bind the jobs table to the datagridview
JobTitlesDataGridView.Data
'set up the currency manager
cmJobs = CType(BindingContext(dvjob
'customise the grids - with a strongly typed dataset ...
'... this could be done in the designer
ApplicantsDataGridView.Rea
ApplicantsDataGridView.All
'hide the applicant ID
ApplicantsDataGridView.Col
'hide the job ID ...
ApplicantsDataGridView.Col
'... but add an unbound column to show ...
'... the job title matching that ID
Dim TitleCol As New DataGridViewTextBoxColumn
TitleCol.HeaderText = "Job Title"
ApplicantsDataGridView.Col
JobTitlesDataGridView.Read
JobTitlesDataGridView.Allo
JobTitlesDataGridView.Colu
'run the sub to fill the jobs numbers displays
updateTotals()
'get the job titles for the applicants
For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Row
fillInTitle(dgvr)
Next
End Sub
Private Sub btnUpdatePosition_Click(By
'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
'check if this applicant already has a job
'If Not TypeOf (applicantsRow.Item("Job")
If Not TypeOf (applicantsRow.Item("keyJo
'need to return existing job to those available
'get from table the row for the old job
Dim oldJobRow As DataRow() = dtJobs.Select("keyJobTitle
If oldJobRow.Length > 0 Then
'oldJobRow(0).Item("Number
Dim myVal As Integer = CInt(oldJobRow(0).Item("tx
myVal += 1 'or myVal = myVal + 1
oldJobRow(0).Item("txtWork
Else
MsgBox("Something wrong")
End If
End If
'put the new job number in the applicant's record
'applicantsRow.Item("Job")
applicantsRow.Item("keyJob
'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("txtWork
myVal2 -= 1 'or myVal2 = myVal2 + 1
jobsRow.Item("txtWorksiteJ
'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.EndCurrentEdi
'run the sub to update the jobs numbers displays
updateTotals()
'get the job title for this applicant
Dim dgvr As DataGridViewRow = ApplicantsDataGridView.Cur
fillInTitle(dgvr)
End Sub
Private Sub btnCancelPosition_Click(By
Me.Close()
End Sub
Private Sub cmJobs_PositionChanged(ByV
'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("txtWork
'get the job ID from that row
Dim thisJob As Integer = CInt(jobsRow.Item("keyJobT
'use the job ID to find how many applicants ...
'... already have that job
Dim taken As Integer = CInt(dtApplicants.Compute(
'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.Ro
'use datatable filter to return only rows with this ID
Dim dr As DataRow() = dtJobs.Select("keyJobTitle
'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)("txtWorksiteJob
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.C
End If
End Sub
Private Sub ApplicantsDataGridView_Dat
Debug.WriteLine(e.ColumnIn
Debug.WriteLine(e.RowIndex
Debug.WriteLine(Applicants
Debug.WriteLine(Applicants
e.ThrowException = True
End Sub
End Class
'David
ASKER
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
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
ASKER
It seems like the Me.ApplicantsTableAdapter. Fill(Me.SY EP2007Data Set.Applic ants)
in the Form1_Load should be doing that shouldn't it?
David
in the Form1_Load should be doing that shouldn't it?
David
ASKER
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
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
ASKER
Preview Data shows the Jobs in the SYEP2007DataSet.
ApplicantsTableAdapter also shows the Jobs
ApplicantsTableAdapter also shows the Jobs
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
ASKER
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
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
ASKER
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