I have a DataGridView that updates a database. Roger helped me with it and it works great.
When I close the app and open it back up the DataGridView rows are not populated again from the database.
All the information is in the database and in the DataSet, but is not being "filled" back into the DataGridView.
This code is in the Form1_Load event. It looks like this should be doing the fill. I don't understand why this doesn't fill the DataGridView rows on form load:
'get the job titles for the applicants
For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
fillInTitle(dgvr)
Next
Here's the fillInTitle sub:
Private Sub fillInTitle(ByVal dgvr As DataGridViewRow)
'check if there is a value in the third cell ...
'... (index 2, which is the "Job" field ...
'... in this row from the datagridview
If TypeOf dgvr.Cells(2).Value Is DBNull Then
'if not, then there is no Job Title
dgvr.Cells(4).Value = ""
Else
'if there is, use it to get the Job Title
dgvr.Cells(4).Value = getTitleFromID(CInt(dgvr.Cells(2).Value))
End If
End Sub
Thanks.
David
Below is the whole Form1.vb code for reference:
------------------------------------------------------------------------------------------------------------
Imports System.Data.SqlTypes
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
'Look up some of the instructions on "refreshing" a DataGridView from the database...
Public Class Form1
Private dtApplicants As New DataTable("Applicants")
Private cmApplicants As CurrencyManager
Private dtJobs As New DataTable("Jobs")
Private dvjobs As New DataView
Private WithEvents cmJobs As CurrencyManager
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'SYEP2007DataSet.JobTitles' table. You can move, or remove it, as needed.
Me.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)
Me.JobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.JobTitles)
dtApplicants = Me.SYEP2007DataSet.Applicants
dtJobs = Me.SYEP2007DataSet.JobTitles
'filltables() 'equivalent to filling tables with ...
'... a dataadapter or a tableadapter
'get and display the count of applicant records
Dim count As Integer = dtApplicants.Rows.Count
txtTotalApplicants.Text = String.Format("{0} Applicants", count)
'bind the applicants table to the datagridview
ApplicantsDataGridView.DataSource = dtApplicants
'set up the currencymanager - convenient for referring ...
'... to the currently selected row in the applicants grid
cmApplicants = CType(BindingContext(dtApplicants), CurrencyManager)
'set up a dataview to filter the jobs table so it ...
'... only shows those where some jobs remain available
dvjobs.Table = dtJobs
dvjobs.RowFilter = "txtWorksiteJobTitlePositionsNumber > 0"
'bind the jobs table to the datagridview
JobTitlesDataGridView.DataSource = dvjobs
'set up the currency manager
cmJobs = CType(BindingContext(dvjobs), CurrencyManager)
'customise the grids - with a strongly typed dataset ...
'... this could be done in the designer
ApplicantsDataGridView.ReadOnly = True
ApplicantsDataGridView.AllowUserToAddRows = False
'hide the applicant ID
ApplicantsDataGridView.Columns(0).Visible = False
'hide the job ID ...
ApplicantsDataGridView.Columns(2).Visible = False
'hide the ProgramID column in Applicants DGV
ApplicantsDataGridView.Columns(3).Visible = False
'... but add an unbound column to show ...
'... the job title matching that ID
Dim TitleCol As New DataGridViewTextBoxColumn
TitleCol.HeaderText = "Job Title"
ApplicantsDataGridView.Columns.Add(TitleCol)
Dim NumberofAdults As New DataGridViewTextBoxColumn
NumberofAdults.HeaderText = "Adults"
ApplicantsDataGridView.Columns.Add(NumberofAdults)
JobTitlesDataGridView.ReadOnly = True
JobTitlesDataGridView.AllowUserToAddRows = False
JobTitlesDataGridView.Columns(0).Visible = False
ApplicantsDataGridView.Columns(1).Width = 100
'run the sub to fill the jobs numbers displays
updateTotals()
'get the job titles for the applicants
For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
fillInTitle(dgvr)
Next
TabControl1.SelectedIndex = 1
End Sub
Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
'make sure any outstanding edits are committed to the datatables
cmApplicants.EndCurrentEdit()
cmJobs.EndCurrentEdit()
'then save datatable changes to database
Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles)
End Sub
Private Sub btnUpdatePosition_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdatePosition.Click
Dim jobRow As DataRow()
'use the currency manager to get the currently ...
'... selected job in the jobs grid
Dim jobsRow As DataRowView = CType(cmJobs.Current, DataRowView)
'and save the job number
Dim newJobNumber As Integer = CInt(jobsRow("keyJobTitleID"))
'similar for the applicants
Dim applicantsRow As DataRowView = CType(cmApplicants.Current, DataRowView)
'check if this applicant already has a job
If Not TypeOf (applicantsRow.Item("keyJobTitleID")) Is DBNull Then
'if so, seek the relevant row from the datatable
jobRow = dtJobs.Select("keyJobTitleID = " & Convert.ToString(applicantsRow.Item("keyJobTitleID")))
'if there is one
If jobRow.Length > 0 Then
'it will be the first in the row array: update it
Dim myVal As Integer = CInt(jobRow(0).Item("txtWorksiteJobTitlePositionsNumber"))
myVal += 1 'or myVal = myVal + 1
jobRow(0).Item("txtWorksiteJobTitlePositionsNumber") = myVal
Else
'if there isn't one ...
MsgBox("Something wrong")
End If
End If
'put the new job number in the applicant's record
applicantsRow.Item("keyJobTitleID") = newJobNumber
'decrement the number of jobs available
'seek the relevant row from the datatable
jobRow = dtJobs.Select("keyJobTitleID = " & newJobNumber)
'if there is one
If jobRow.Length > 0 Then
'it will be the first in the row array: update it
Dim myVal As Integer = CInt(jobRow(0).Item("txtWorksiteJobTitlePositionsNumber"))
myVal -= 1 'or myVal = myVal - 1
jobRow(0).Item("txtWorksiteJobTitlePositionsNumber") = myVal
Else
'if there isn't one ...
MsgBox("Something wrong")
End If
''commit the edits - this is necessary where ...
''... they have been done by via the currency ...
''... manager's Current record, rather than ...
''... (as above) in the datatable itself
cmApplicants.EndCurrentEdit()
cmJobs.EndCurrentEdit()
'run the sub to update the jobs numbers displays
updateTotals()
'get the job title for this applicant
Dim dgvr As DataGridViewRow = ApplicantsDataGridView.CurrentRow
fillInTitle(dgvr)
End Sub
Private Sub btnCancelPosition_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelPosition.Click
Me.Close()
End Sub
Private Sub cmJobs_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmJobs.PositionChanged
'the jobs currency manager was declared WithEvents ...
'... to make it easy to update the jobs numbers ...
'... displays when a different job was selected ...
'... in the grid
If cmJobs.Count > 0 Then
updateTotals()
End If
End Sub
Private Sub updateTotals()
If cmJobs.Count = 0 Then
txtAvailable.Text = ""
txtTaken.Text = ""
txtTotal.Text = ""
MsgBox("No more jobs")
Exit Sub
End If
'use the currency manager to get the currently ...
'... selected job in the jobs grid
Dim jobsRow As DataRowView = CType(cmJobs.Current, DataRowView)
'that says how many of that job are available
Dim available As Integer = CInt(jobsRow.Item("txtWorksiteJobTitlePositionsNumber"))
'get the job ID from that row
Dim thisJob As Integer = CInt(jobsRow.Item("keyJobTitleID"))
'use the job ID to find how many applicants ...
'... already have that job
Dim taken As Integer = CInt(dtApplicants.Compute("Count(keyJobTitleID)", "keyJobTitleID = " & thisJob))
'the total is the sum of those two
Dim total As Integer = available + taken
'display the results
txtAvailable.Text = available.ToString
txtTaken.Text = taken.ToString
txtTotal.Text = total.ToString
End Sub
Private Function getTitleFromID(ByVal ID As Integer) As String
'Temporary line for debugging purposes
'If that is reporting 0, then we know the problem is with the datatable.
'If it is reporting more than that, then the problem is with either the value
'that is being passed to the function,
'or with the syntax or references in the select statement.
' 'Debug.WriteLine(dtJobs.Rows.Count)
'use datatable filter to return only rows with this ID
Dim dr As DataRow() = dtJobs.Select("keyJobTitleID = " & ID)
'check if there are any such rowsI
If dr.Length > 0 Then
'if yes, there should only be one - in position 0
'return the value in the JobNmae field from that
Return CStr(dr(0)("txtWorksiteJobTitle"))
Else
'otherwise, return empty string
Return ""
End If
End Function
Private Sub fillInTitle(ByVal dgvr As DataGridViewRow)
'check if there is a value in the third 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(4).Value = "" 'I added the ProgramID field and changed Cells(3) to Cells(4) (added on line 55)
Else
'if there is, use it to get the Job Title
dgvr.Cells(4).Value = getTitleFromID(CInt(dgvr.Cells(2).Value)) 'I added the ProgramID field and changed Cells(3) to Cells(4)
End If
End Sub
Private Sub ApplicantsDataGridView_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles ApplicantsDataGridView.DataError
Debug.WriteLine(e.ColumnIndex)
Debug.WriteLine(e.RowIndex)
Debug.WriteLine(ApplicantsDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value)
Debug.WriteLine(ApplicantsDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).EditedFormattedValue)
e.ThrowException = True
End Sub
End Class
by: dtoddPosted on 2007-04-19 at 14:13:18ID: 18942442
Hi,
Does the datagrid have a bind() method?
Which version of dot net framework are you using?
What do you have to do in your form when it is running to get the data to appear?
Regards
David