David Megnin
asked on
How to refresh a DataGridView after database Update.
How do you refresh a DataGridView with the current contents of the database after you do an UPDATE?
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 = "workingConnectionString"
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedur e
con.Open()
cmd.ExecuteNonQuery()
con.Close()
This button Updates the database, but the changes are not reflected in the DataGridView, JobTitlesDataGridView, until I exit the application and run it again.
I've searched the web for nearly a week now and none of the solutions work. Most give me an error right from the beginning. I'm using VS2005, VB.NET, SQL Server 2000.
The DataSource for the DataGridView is JobTitlesBindingSource
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 = "workingConnectionString"
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedur
con.Open()
cmd.ExecuteNonQuery()
con.Close()
This button Updates the database, but the changes are not reflected in the DataGridView, JobTitlesDataGridView, until I exit the application and run it again.
I've searched the web for nearly a week now and none of the solutions work. Most give me an error right from the beginning. I'm using VS2005, VB.NET, SQL Server 2000.
The DataSource for the DataGridView is JobTitlesBindingSource
ASKER
...and how would I do that? ...I'm just learning.
Maybe I'm approaching the problem wrong to begin with.
What I'm trying to do is to allow a manager to assign Applicants to JobTitles.
I have a table for JobTitles and another table for the Applicants. Each JobTitle can have more than one Applicant. There is PK_JobTitleID in tblJobTitles and FK_JobTitleID in tblApplicants.
It should be a simple matter, but I started out with the two DataGridViews and have not gotten anywhere.
Maybe I'm approaching the problem wrong to begin with.
What I'm trying to do is to allow a manager to assign Applicants to JobTitles.
I have a table for JobTitles and another table for the Applicants. Each JobTitle can have more than one Applicant. There is PK_JobTitleID in tblJobTitles and FK_JobTitleID in tblApplicants.
It should be a simple matter, but I started out with the two DataGridViews and have not gotten anywhere.
Given that your JobTitlesDataGridView is updated when you "exit the application and run it again", there must be some code which runs on your application's start up and properly gets the data to fill the datagridview. My guess (based just on the terminology in the bits of code you show) is that in your form_load sub there will be something like
Me.JobTitlesTableAdapter.F ill(...
with the name of, or a reference to, a datatable where I've put ...
If you copy that code (NB "copy" - that is, leave the original where it is but put a duplicate) at the end of your btnUpdatePosition_Click sub, it might solve the problem.
But I stress that that's a guess. It's very difficult to know for certain what's going on. For example, I've no real idea - I've only made an assumption - what your stored procedure is doing.
Roger
Me.JobTitlesTableAdapter.F
with the name of, or a reference to, a datatable where I've put ...
If you copy that code (NB "copy" - that is, leave the original where it is but put a duplicate) at the end of your btnUpdatePosition_Click sub, it might solve the problem.
But I stress that that's a guess. It's very difficult to know for certain what's going on. For example, I've no real idea - I've only made an assumption - what your stored procedure is doing.
Roger
ASKER
Thank you, Roger.
Yes. Here is my form_load sub:
Me.ApplicantsTableAdapter. Fill(Me.SY EP2007Data Set.Applic ants)
Me.JobTitlesTableAdapter.F ill(Me.SYE P2007DataS et.JobTitl 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
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)
cn.Close()
Here is my StoredProcedure:
UPDATE JobTitles
SET PositionsAvailable =
(SELECT JobTitles_1.txtWorksiteJob TitlePosit ionsNumber - COUNT(Applicants.keyApplic antID) AS Expr1
FROM Applicants INNER JOIN
JobTitles AS JobTitles_1 ON JobTitles_1.keyJobTitleID = Applicants.keyJobTitleID
WHERE (JobTitles.keyJobTitleID = JobTitles_1.keyJobTitleID)
GROUP BY JobTitles_1.txtWorksiteJob TitlePosit ionsNumber , JobTitles_1.keyJobTitleID)
My "Update Data" Button:
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 = "...my connection string..."
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedur e
con.Open()
cmd.ExecuteNonQuery()
con.Close()
'JobTitlesBindingSource.Re setBinding s(False) 'Doesn't update the DGV
...and here's trying to keep track of how many possitions are still available:
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
End Sub
...and here's trying to keep the selected row on the JobTitles DGV matching the selected row of the Applicants DGV so you can tell who's already assigned to where:
Private Sub ComboBox3_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 = "... my connection string..."
cmd1.Connection = con
cmd1.CommandText = "SELECT SUM(TotalPositions) FROM Worksites WHERE keyMonitorID='" & SelectedMonitor & "'"
con.Open()
total = CInt(cmd1.ExecuteScalar)
con.Close()
txtPositions.Text = total.ToString
End If
End Sub
End Sub
If you can help me with any of this I would really appreciate it. I don't really know what I'm doing. I've tried to piece things together but from what I'm finding this may not be the best way to do what I'm trying to do.
Thanks again.
David
Yes. Here is my form_load sub:
Me.ApplicantsTableAdapter.
Me.JobTitlesTableAdapter.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
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)
cn.Close()
Here is my StoredProcedure:
UPDATE JobTitles
SET PositionsAvailable =
(SELECT JobTitles_1.txtWorksiteJob
FROM Applicants INNER JOIN
JobTitles AS JobTitles_1 ON JobTitles_1.keyJobTitleID = Applicants.keyJobTitleID
WHERE (JobTitles.keyJobTitleID = JobTitles_1.keyJobTitleID)
GROUP BY JobTitles_1.txtWorksiteJob
My "Update Data" Button:
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 = "...my connection string..."
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedur
con.Open()
cmd.ExecuteNonQuery()
con.Close()
'JobTitlesBindingSource.Re
...and here's trying to keep track of how many possitions are still available:
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
End Sub
...and here's trying to keep the selected row on the JobTitles DGV matching the selected row of the Applicants DGV so you can tell who's already assigned to where:
Private Sub ComboBox3_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 = "... my connection string..."
cmd1.Connection = con
cmd1.CommandText = "SELECT SUM(TotalPositions) FROM Worksites WHERE keyMonitorID='" & SelectedMonitor & "'"
con.Open()
total = CInt(cmd1.ExecuteScalar)
con.Close()
txtPositions.Text = total.ToString
End If
End Sub
End Sub
If you can help me with any of this I would really appreciate it. I don't really know what I'm doing. I've tried to piece things together but from what I'm finding this may not be the best way to do what I'm trying to do.
Thanks again.
David
David
It'll be a few hours before I can look at all that. Someone else might pick it up in the meantime. If not, I'll come back to it. So hang in there.
Roger
It'll be a few hours before I can look at all that. Someone else might pick it up in the meantime. If not, I'll come back to it. So hang in there.
Roger
You can do it either
1) move a code that loads data in Form_Load event to a procedure, and call that procedure from Form_load event and also in Update event to reload data after the update
2) If I understood you correctly (which may not be true, since the code is quite messy), when you update applicants table, you need to refresh a position field in JobTitles table. I still dont understand how this application works, and what is expected from it, and what is a database structure, but you could perhaps udate the position field everytime a applicant is added/removed, which will bring an up-to-date JobTitles datatable. When updating you can call Update on both dataadapters, and no need for dbo.spUpdate_JobTitles_Pos itionsAvai lable sp, cine the postion is maintained regularely
If you didnt understand what I said here, a better explanation and more data will be needed from you, so we can help you.
Goran
1) move a code that loads data in Form_Load event to a procedure, and call that procedure from Form_load event and also in Update event to reload data after the update
2) If I understood you correctly (which may not be true, since the code is quite messy), when you update applicants table, you need to refresh a position field in JobTitles table. I still dont understand how this application works, and what is expected from it, and what is a database structure, but you could perhaps udate the position field everytime a applicant is added/removed, which will bring an up-to-date JobTitles datatable. When updating you can call Update on both dataadapters, and no need for dbo.spUpdate_JobTitles_Pos
If you didnt understand what I said here, a better explanation and more data will be needed from you, so we can help you.
Goran
David
Is this app operating with the database on a single-user or a multi-user basis? That is, is it essential that the app keeps going back to the database to refresh data - both ways - so that multi-users can be kept informed of changes that other users are making: or would it be possible, because only one instance of the app would be using the database at any one time, to bring everything necessary over from the database to the app at the start, make all changes in the app, and then send all changes back to the database at the end?
There are, in the code you've posted, pointers both ways. But the fact that your btnUpdatePosition_Click sub envisages the possibility of a NUMBER of Applicant records being updated at one time makes me feel that a disconnected - do-it-all-in-the-app-then- save - approach might be possible. If so, it would probably be neater and easier to code. Indeed, doing it that way may be better even in a multi-user setting: just doing updates to the database more frequently.
Let me illustrate what I mean: with acknowledgments to Goran, as it is the same point as he has made. You are obviously bringing data over from both your JobTitles and Applicants tables at the start of the app. It may therefore be (we don't know this for certain) that, when you've made a change to the Applicants datatable in your app, you already have IN YOUR APP sufficient information to make any necessary changes to the JobTitles datatable IN YOUR APP. If that were to be the case then there would be no reason to use ExecuteNonQuery on a stored procedure to update the JobTitles table in your database just so's you could bring the revised JobTitles table back to the app again. If you make the necessary changes in the datatable in your app, that will automatically be reflected in your JobTitlesDataGridView. And, when the time comes, you can pass the changes back to the database with a Me.JobTitlesTableAdapter.U pdate(Me.S YEP2007Dat aSet.JobTi tles) call.
I stress that that is just what it says - an illustration of an alternative approach that you may like to consider. But if that would be viable, and appeals, you may be able also to gather the other information that you want for your txtAvailable, txtTaken and txtTotal controls from data that is already held in your app. It looks almost certain, for instance, that you could get rid of this additional call to the database in your form_load sub
Dim cnString As String = "UID=dmegnin;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SYEP2007;Data Source=BETASERVE;Packet Size=4096;"
Dim cn As SqlConnection = Nothing
Dim cmd As SqlCommand = Nothing
cn = New SqlConnection(cnString)
cn.Open()
Dim cmd1 As New SqlCommand("SELECT COUNT(*) FROM Applicants", cn)
Dim count As Integer = Convert.ToInt32(cmd1.Execu teScalar() )
txtTotalApplicants.Text = String.Format("{0} Applicants", count)
cn.Close()
If I have understood correctly what that is doing, it is asking the database for information which your app already knows. Having just filled the Applicants datatable with
Me.ApplicantsTableAdapter. Fill(Me.SY EP2007Data Set.Applic ants)
the number of applicants will be the number of rows in that datatable. Won't it? ;-)
Have a think about the general theme we've discussed above. If you want to pursue it, and find difficulty doing so, come back. But as Goran says, a more detailed explanation would be useful. Even though the picture has become a bit clearer with the extra code you have now posted, it's very difficult for us to work back from snippets of code to what the full story is likely to be.
Roger
Is this app operating with the database on a single-user or a multi-user basis? That is, is it essential that the app keeps going back to the database to refresh data - both ways - so that multi-users can be kept informed of changes that other users are making: or would it be possible, because only one instance of the app would be using the database at any one time, to bring everything necessary over from the database to the app at the start, make all changes in the app, and then send all changes back to the database at the end?
There are, in the code you've posted, pointers both ways. But the fact that your btnUpdatePosition_Click sub envisages the possibility of a NUMBER of Applicant records being updated at one time makes me feel that a disconnected - do-it-all-in-the-app-then-
Let me illustrate what I mean: with acknowledgments to Goran, as it is the same point as he has made. You are obviously bringing data over from both your JobTitles and Applicants tables at the start of the app. It may therefore be (we don't know this for certain) that, when you've made a change to the Applicants datatable in your app, you already have IN YOUR APP sufficient information to make any necessary changes to the JobTitles datatable IN YOUR APP. If that were to be the case then there would be no reason to use ExecuteNonQuery on a stored procedure to update the JobTitles table in your database just so's you could bring the revised JobTitles table back to the app again. If you make the necessary changes in the datatable in your app, that will automatically be reflected in your JobTitlesDataGridView. And, when the time comes, you can pass the changes back to the database with a Me.JobTitlesTableAdapter.U
I stress that that is just what it says - an illustration of an alternative approach that you may like to consider. But if that would be viable, and appeals, you may be able also to gather the other information that you want for your txtAvailable, txtTaken and txtTotal controls from data that is already held in your app. It looks almost certain, for instance, that you could get rid of this additional call to the database in your form_load sub
Dim cnString As String = "UID=dmegnin;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SYEP2007;Data Source=BETASERVE;Packet Size=4096;"
Dim cn As SqlConnection = Nothing
Dim cmd As SqlCommand = Nothing
cn = New SqlConnection(cnString)
cn.Open()
Dim cmd1 As New SqlCommand("SELECT COUNT(*) FROM Applicants", cn)
Dim count As Integer = Convert.ToInt32(cmd1.Execu
txtTotalApplicants.Text = String.Format("{0} Applicants", count)
cn.Close()
If I have understood correctly what that is doing, it is asking the database for information which your app already knows. Having just filled the Applicants datatable with
Me.ApplicantsTableAdapter.
the number of applicants will be the number of rows in that datatable. Won't it? ;-)
Have a think about the general theme we've discussed above. If you want to pursue it, and find difficulty doing so, come back. But as Goran says, a more detailed explanation would be useful. Even though the picture has become a bit clearer with the extra code you have now posted, it's very difficult for us to work back from snippets of code to what the full story is likely to be.
Roger
ASKER
Roger,
Thank you!
A single manager will be making Applicant to JobTitle assignments. No multi-user access is needed.
Yes, I'm afraid my code is very "mess". This is actually my very first coding project since taking a 5 day ASP.NET class and a job as a "developer". So if you want to throw in any other pointers about how I should "clean up" my code, please feel free.
Those snips are pretty much my complete Form1.vb code.
The overall goal is to take the kids in the Applicants table and assign them to jobs in the JobTitles table. I'm using JobTitles because each Worksite may have multiple Jobs, each having multiple individual positions available.
The table JobTitles contains all the Jobs and a number of available positions for each as:
tblJobTitles(PK_JobTitleID , JobName, intAvailablePositions) = (123456, Clerk, 5) ...I hope this makes since.
The Applicants table has the kids and a foreign key field for the JobTitle ID to go in:
tblApplicants(PK_Applicant ID, FK_JobTitleID, FName, etc.) = (83838383, 123456, Bob, etc.)
Now JobTitle 123456 only has 4 of the original 5 positions available.
Form1 has two DataGridViews: ApplicantsDataGridView and JobTitlesDataGridView.
I chose the DGVs because they show multiple rows on the form so the manager can see half a dozen or so kids and jobs at once. He is trying to assign jobs to kids based on location city and a preference they have indicated and is a field represented in the table.
If all the selecting and calculating can be done in the local copy of the data and saved back to the database with a button click, whenever, then that is fine. I chose everything I've done so far based on zero experience and no examples to go on. My strategy and methods are the guesses of a complete beginner.
If the application itself would be clearer, tell me what files from my Solution/Project to send and I will gladly do so. I'm using Visual Studio 2005 and VB. If need be I can try to figure out how to make a "CREATE TABLE" query to create my database structure.
I'll study the instructions in your post and see if I can eliminate some of the unnecessary code. I've learned more from your post than I have from two days of Google searches.
Thank you very much!
-David
Thank you!
A single manager will be making Applicant to JobTitle assignments. No multi-user access is needed.
Yes, I'm afraid my code is very "mess". This is actually my very first coding project since taking a 5 day ASP.NET class and a job as a "developer". So if you want to throw in any other pointers about how I should "clean up" my code, please feel free.
Those snips are pretty much my complete Form1.vb code.
The overall goal is to take the kids in the Applicants table and assign them to jobs in the JobTitles table. I'm using JobTitles because each Worksite may have multiple Jobs, each having multiple individual positions available.
The table JobTitles contains all the Jobs and a number of available positions for each as:
tblJobTitles(PK_JobTitleID
The Applicants table has the kids and a foreign key field for the JobTitle ID to go in:
tblApplicants(PK_Applicant
Now JobTitle 123456 only has 4 of the original 5 positions available.
Form1 has two DataGridViews: ApplicantsDataGridView and JobTitlesDataGridView.
I chose the DGVs because they show multiple rows on the form so the manager can see half a dozen or so kids and jobs at once. He is trying to assign jobs to kids based on location city and a preference they have indicated and is a field represented in the table.
If all the selecting and calculating can be done in the local copy of the data and saved back to the database with a button click, whenever, then that is fine. I chose everything I've done so far based on zero experience and no examples to go on. My strategy and methods are the guesses of a complete beginner.
If the application itself would be clearer, tell me what files from my Solution/Project to send and I will gladly do so. I'm using Visual Studio 2005 and VB. If need be I can try to figure out how to make a "CREATE TABLE" query to create my database structure.
I'll study the instructions in your post and see if I can eliminate some of the unnecessary code. I've learned more from your post than I have from two days of Google searches.
Thank you very much!
-David
No, don't send any of your application's files - at the moment, anyway. But can you please amplify a couple of points.
1) how does the Worksite fit into the scheme of things? There's obviously a Worksite table in the database, which includes keyMonitorID and TotalPositions fields. It looks like there's also a WorksitesDataGridView on your form. But that doesn't tie in with your most recent statement that
>>
Form1 has two DataGridViews: ApplicantsDataGridView and JobTitlesDataGridView.
<<
I also notice you refer to that the WorksitesDataGridView in a combobox's SelectedIndexChanged sub - so I'm wondering how the datagridview and the combobox relate to each other. Are the specific jobs (keyJobTitleID) in the JobTitles table linked to the Worksites table? And if so, how?
2) the impression that I get is that, with the database structure you describe, you could have a number of different applicants referencing the same FK_JobTitleID. So you say
>>
The Applicants table has the kids and a foreign key field for the JobTitle ID to go in:
tblApplicants(PK_Applicant ID, FK_JobTitleID, FName, etc.) = (83838383, 123456, Bob, etc.)
Now JobTitle 123456 only has 4 of the original 5 positions available.
<<
My impression is that if one of the remaining 4 positions was now given to another applicant we could also have
tblApplicants(PK_Applicant ID, FK_JobTitleID, FName, etc.) = (93939393, 123456, Joe, etc.)
That is, two applicant records with the same reference 123456 in FK_JobTitleID, with no way of knowing which of the five original positions Bob was in and which Joe was in. I appreciate that there may be no real difference between any of the five original positions, so it might not matter in practice "which of the five original positions Bob was in and which Joe was in". But whether we do or do not know that could have implications in database and application design terms. So could you clarify that point, too, please.
Roger
PS. It seems pretty clear we're in different time zones. I'm in the UK, so will be off to bed soon. But I'll look in again tomorrow.
1) how does the Worksite fit into the scheme of things? There's obviously a Worksite table in the database, which includes keyMonitorID and TotalPositions fields. It looks like there's also a WorksitesDataGridView on your form. But that doesn't tie in with your most recent statement that
>>
Form1 has two DataGridViews: ApplicantsDataGridView and JobTitlesDataGridView.
<<
I also notice you refer to that the WorksitesDataGridView in a combobox's SelectedIndexChanged sub - so I'm wondering how the datagridview and the combobox relate to each other. Are the specific jobs (keyJobTitleID) in the JobTitles table linked to the Worksites table? And if so, how?
2) the impression that I get is that, with the database structure you describe, you could have a number of different applicants referencing the same FK_JobTitleID. So you say
>>
The Applicants table has the kids and a foreign key field for the JobTitle ID to go in:
tblApplicants(PK_Applicant
Now JobTitle 123456 only has 4 of the original 5 positions available.
<<
My impression is that if one of the remaining 4 positions was now given to another applicant we could also have
tblApplicants(PK_Applicant
That is, two applicant records with the same reference 123456 in FK_JobTitleID, with no way of knowing which of the five original positions Bob was in and which Joe was in. I appreciate that there may be no real difference between any of the five original positions, so it might not matter in practice "which of the five original positions Bob was in and which Joe was in". But whether we do or do not know that could have implications in database and application design terms. So could you clarify that point, too, please.
Roger
PS. It seems pretty clear we're in different time zones. I'm in the UK, so will be off to bed soon. But I'll look in again tomorrow.
ASKER
Thanks for the reply, Roger!
1) Each Worksite may have multiple JobTitles. e.g. the Library may have "custodian" and "clerk".
...so tblWorksites(PK_WorksiteID , WorksiteName, City, FK_MonitorID...) = (456123, Library, Lauderhill,21,...)
and tblJobTitles(PK_JobTitleID ,FK_Worksi teID, JobName, intAvailablePositions) = (123456, 456123, Clerk, 5)
Relationships: tblWorksites -> one to many -> tblJobTitles -> one to many -> tblApplicants.
I'm using the naming convention "PK_SomePrimaryKeyID" and "FK_SomeForeignKeyID" here for clarity, although in my code I've used "keyTableNameID" for all keys, primary and foreign, since I know if the key has the same name as the table it's a primary key.
There is a "Monitors" table (I'm referring to all tables with the "tbl" prefix here for clarity, though I don't use it in my database. That's why you see "Monitors", "Worksites", "Applicants" in my code and "tblMonitors", "tblWorksites", etc. in my explanation.) which contains "Worksite Monitors" who will be assigned to worksites in the same manner that Applicants are assigned to JobTitles. I'm ignoring them right now because when I learn to do Applicants to JobTitles I'll use the same mechanism to do Monitors to Worksites.
The extra DataGridView is on another "Tab" in my application which has different tabs for different functionality, e.g. Tab1 has Applicant information, Tab2 has the Applicant to JobTitle assignment tool, Tab3 has the Monitor to Worksite assignment tool, for example. ( I actually have several tabs with "trial and error", mostly "error" ;-)
So, I should have said, "On Form1, Tab2 has two DataGridViews: ApplicantsDataGridView and JobTitlesDataGridView."
>>
the WorksitesDataGridView in a combobox's SelectedIndexChanged
<<
That DGV and ComboBox are on another tab I was trying to assign the Monitors to Worksites. Let's disregard that area for now. Once I can assign Applicants to JobTitles I use the same mechanism to assign Monitors to Worksites. What I was doing with them was using the ComboBox's databinding to link the MonitorsDataGridView to the WorksitesDataGridView. In ComboBox3's "Tasks" I have Use data bound items checked, then in the "Data Binding Mode" I have the following setting:
Data Source = MonitorsBindingSource
Display Member = "LastName" <--- from the Monitors table
Value Member = "keyMonitorID" <--- PK from Monitors table
Selected Value = "WorksitesBindingSource - keyMonitorID <--- FK from Worksites Table
What that did for me was that when I have a Monitor selected in the MonitorsDataGridView who has been assigned to a Worksite, then the Worksite in the WorksitesDataGridView is also selected, so when I scroll up and down in the list of Monitors I can see what Worksites they are assigned to, if any. The selections in the two DGVs stay "linked". I don't know if that's the right way to do that, but it was the only way I could figure out to link the two DataGridViews on the keyMonitorIDs in each. I'm trying to do the same DGV link thing with the ApplicantsDataGridVies and the JobTitlesDataGridView using the ComboBox "cbFindJobTitle". I searched Google and this site for two days and could find no other way to "link" two DGVs.
The JobTitles table and the Worksites table are linked in a, Worksites --> one to many --> JobTitles relationship (also mentioned above) by "keyWorksiteID", a primary key in Worksites and a foreign key in JobTitles.
2) You are correct. The *number* of positions is the only value that matters. If there are 5 positions available for JobTitleID 123456, then, if 123456 is put into the FK_JobTitleID of three different Applicant records I only need to know that 2 are still available. Currently done thus:
UPDATE JobTitles
SET PositionsAvailable =
(SELECT JobTitles_1.txtWorksiteJob TitlePosit ionsNumber - COUNT(Applicants.keyApplic antID) AS Expr1
FROM Applicants INNER JOIN
JobTitles AS JobTitles_1 ON JobTitles_1.keyJobTitleID = Applicants.keyJobTitleID
WHERE (JobTitles.keyJobTitleID = JobTitles_1.keyJobTitleID)
GROUP BY JobTitles_1.txtWorksiteJob TitlePosit ionsNumber , JobTitles_1.keyJobTitleID)
In the database there is no distinction between any of the 5 available positions. There is simply a field for "total number of positions" and it contains an integer. The fieldname in the database is JobTitles.txtWorksiteJobTi tlePositio nsNumber.
Oh my... Yes, I'm in the Zulu -5 (Eastern United States) time zone. So I hope you had a good rest. Thank you for your assistance. I both need and appreciate it. Like I said, I'm just starting to learn and I'm sure I'm doing *everything* incorrectly. Goran mentioned that my code is "quite messy" which I do not doubt, so I do not take offense.
I'm sure there is probably an elegant way to assign a value from one table into another to accomplish my Applicant to JobTitle assignment, but I don't have any experience to base go by.
Thanks again.
-David
1) Each Worksite may have multiple JobTitles. e.g. the Library may have "custodian" and "clerk".
...so tblWorksites(PK_WorksiteID
and tblJobTitles(PK_JobTitleID
Relationships: tblWorksites -> one to many -> tblJobTitles -> one to many -> tblApplicants.
I'm using the naming convention "PK_SomePrimaryKeyID" and "FK_SomeForeignKeyID" here for clarity, although in my code I've used "keyTableNameID" for all keys, primary and foreign, since I know if the key has the same name as the table it's a primary key.
There is a "Monitors" table (I'm referring to all tables with the "tbl" prefix here for clarity, though I don't use it in my database. That's why you see "Monitors", "Worksites", "Applicants" in my code and "tblMonitors", "tblWorksites", etc. in my explanation.) which contains "Worksite Monitors" who will be assigned to worksites in the same manner that Applicants are assigned to JobTitles. I'm ignoring them right now because when I learn to do Applicants to JobTitles I'll use the same mechanism to do Monitors to Worksites.
The extra DataGridView is on another "Tab" in my application which has different tabs for different functionality, e.g. Tab1 has Applicant information, Tab2 has the Applicant to JobTitle assignment tool, Tab3 has the Monitor to Worksite assignment tool, for example. ( I actually have several tabs with "trial and error", mostly "error" ;-)
So, I should have said, "On Form1, Tab2 has two DataGridViews: ApplicantsDataGridView and JobTitlesDataGridView."
>>
the WorksitesDataGridView in a combobox's SelectedIndexChanged
<<
That DGV and ComboBox are on another tab I was trying to assign the Monitors to Worksites. Let's disregard that area for now. Once I can assign Applicants to JobTitles I use the same mechanism to assign Monitors to Worksites. What I was doing with them was using the ComboBox's databinding to link the MonitorsDataGridView to the WorksitesDataGridView. In ComboBox3's "Tasks" I have Use data bound items checked, then in the "Data Binding Mode" I have the following setting:
Data Source = MonitorsBindingSource
Display Member = "LastName" <--- from the Monitors table
Value Member = "keyMonitorID" <--- PK from Monitors table
Selected Value = "WorksitesBindingSource - keyMonitorID <--- FK from Worksites Table
What that did for me was that when I have a Monitor selected in the MonitorsDataGridView who has been assigned to a Worksite, then the Worksite in the WorksitesDataGridView is also selected, so when I scroll up and down in the list of Monitors I can see what Worksites they are assigned to, if any. The selections in the two DGVs stay "linked". I don't know if that's the right way to do that, but it was the only way I could figure out to link the two DataGridViews on the keyMonitorIDs in each. I'm trying to do the same DGV link thing with the ApplicantsDataGridVies and the JobTitlesDataGridView using the ComboBox "cbFindJobTitle". I searched Google and this site for two days and could find no other way to "link" two DGVs.
The JobTitles table and the Worksites table are linked in a, Worksites --> one to many --> JobTitles relationship (also mentioned above) by "keyWorksiteID", a primary key in Worksites and a foreign key in JobTitles.
2) You are correct. The *number* of positions is the only value that matters. If there are 5 positions available for JobTitleID 123456, then, if 123456 is put into the FK_JobTitleID of three different Applicant records I only need to know that 2 are still available. Currently done thus:
UPDATE JobTitles
SET PositionsAvailable =
(SELECT JobTitles_1.txtWorksiteJob
FROM Applicants INNER JOIN
JobTitles AS JobTitles_1 ON JobTitles_1.keyJobTitleID = Applicants.keyJobTitleID
WHERE (JobTitles.keyJobTitleID = JobTitles_1.keyJobTitleID)
GROUP BY JobTitles_1.txtWorksiteJob
In the database there is no distinction between any of the 5 available positions. There is simply a field for "total number of positions" and it contains an integer. The fieldname in the database is JobTitles.txtWorksiteJobTi
Oh my... Yes, I'm in the Zulu -5 (Eastern United States) time zone. So I hope you had a good rest. Thank you for your assistance. I both need and appreciate it. Like I said, I'm just starting to learn and I'm sure I'm doing *everything* incorrectly. Goran mentioned that my code is "quite messy" which I do not doubt, so I do not take offense.
I'm sure there is probably an elegant way to assign a value from one table into another to accomplish my Applicant to JobTitle assignment, but I don't have any experience to base go by.
Thanks again.
-David
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Goran,
It appears that I'm trying to do things the hard way when there's a much simpler approach.
It makes much more sense to just subtract 1 from the available positions when an applicant is assigned to it than to re-SUM Query the database.
I *have* read that calculated fields are a no-no in the database.
In your example "dgw.Item(2,dgw.CurrentRow .Index)... .." is "dgw" literally "dgw" or just short for my DataGridView name? (I'm new, don't hurt me ;-)
Thank you, Goran. Between you and Roger, you have given me a good bit of information to work with. I really appreciate it.
I won't drag this question out much more. Any other examples like the ones above you care to provide will be goldenly appreciated. I like the simple example you gave of the clerk and custodian because you showed the actual code syntax, which is one point I'm really struggling with.
-David
It appears that I'm trying to do things the hard way when there's a much simpler approach.
It makes much more sense to just subtract 1 from the available positions when an applicant is assigned to it than to re-SUM Query the database.
I *have* read that calculated fields are a no-no in the database.
In your example "dgw.Item(2,dgw.CurrentRow
Thank you, Goran. Between you and Roger, you have given me a good bit of information to work with. I really appreciate it.
I won't drag this question out much more. Any other examples like the ones above you care to provide will be goldenly appreciated. I like the simple example you gave of the clerk and custodian because you showed the actual code syntax, which is one point I'm really struggling with.
-David
Yes, David, you are correct, dgw represents a reference to DataGridView. Since this control is very often used in applications, it would be wise to read more about it, and understand how it works.
When I started programming, my first programs were also done in harder way, so you are not alone. This is the reason why I have later rewritten them, when the knowledge / experience came.
So dont hurry, go one step at the time, first code is almost never a good code, so dont burden yourself with it. In time everything will come. :)
Goran
When I started programming, my first programs were also done in harder way, so you are not alone. This is the reason why I have later rewritten them, when the knowledge / experience came.
So dont hurry, go one step at the time, first code is almost never a good code, so dont burden yourself with it. In time everything will come. :)
Goran
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 and Goran, thank you so much, both of you!
It will take me some time to go through the demo and previous posts and try to understand how it works.
What further information sources do you recommend?
I've been watching the videos on "www.LearnVisualStudio.Net". They are quite helpful, but most are pre-Dot Net 2.0. It seems like most of the .Net 1.x information on ADO.Net is still relevant if you are doing data stuff in code rather than using the new controls in .Net 2.0.
Please advise me how I should proceed from here. Should I go ahead and close out this thread? I sure hope you guys are willing to help me with future questions. I know I will have many. I haven't gotten very much response to some of my questions, I'm afraid perhaps, because I'm trying to do things so "screwed up" that my questions don't even make sense ;-)
Today is Friday. I hope you both have a great weekend.
Roger - Thank you so much for that "little demo"! I know that was time consuming.
Goran - I really appreciate your help and words of encouragement as well!
I wish I could give you each 5,000+ points.
-David
It will take me some time to go through the demo and previous posts and try to understand how it works.
What further information sources do you recommend?
I've been watching the videos on "www.LearnVisualStudio.Net". They are quite helpful, but most are pre-Dot Net 2.0. It seems like most of the .Net 1.x information on ADO.Net is still relevant if you are doing data stuff in code rather than using the new controls in .Net 2.0.
Please advise me how I should proceed from here. Should I go ahead and close out this thread? I sure hope you guys are willing to help me with future questions. I know I will have many. I haven't gotten very much response to some of my questions, I'm afraid perhaps, because I'm trying to do things so "screwed up" that my questions don't even make sense ;-)
Today is Friday. I hope you both have a great weekend.
Roger - Thank you so much for that "little demo"! I know that was time consuming.
Goran - I really appreciate your help and words of encouragement as well!
I wish I could give you each 5,000+ points.
-David
ASKER
Question...
On row 102 of the demo, "Dim oldJobRow As DataRow() = dtJobs.Select("ID = " & applicantsRow.Item("Job")) ", I get a "blue squiggly" that says, "Option Strict On prohibits operands of type Object for operator '&' ".
Similarly on row 104, "oldJobRow(0).Item("Number ") += 1" gives, "Option Strict On prohibits operands of type Object for operator '+'. ".
Should I just turn Option Strict off? Or, is there another way to get around that?
On row 142, "Dim available As Integer = jobsRow.Item("Number")" gave, "Option Strict On disallows implicit conversions from 'Object' to 'Integer'. "
I replaced the line with "Dim available As Integer = CInt(jobsRow.Item("Number" ))" to explicitly convert the Row.Item to an integer and then it was okay.
... Okay, I found this... "... ("ID = " & Convert.ToString(applicant sRow.Item( "Job")))". "Convert.ToString" seemed to do the trick. :-)
"oldJobRow(0).Item("Number ") += 1" looks a little trickier. I thought it would be the same, but I'm not sure what to convert to what on this one.
I tried "cInt" in a couple of places with no luck.
I've seen this message before, so I imagine it's good to know how to avoid this conflict.
Thanks.
-David
On row 102 of the demo, "Dim oldJobRow As DataRow() = dtJobs.Select("ID = " & applicantsRow.Item("Job"))
Similarly on row 104, "oldJobRow(0).Item("Number
Should I just turn Option Strict off? Or, is there another way to get around that?
On row 142, "Dim available As Integer = jobsRow.Item("Number")" gave, "Option Strict On disallows implicit conversions from 'Object' to 'Integer'. "
I replaced the line with "Dim available As Integer = CInt(jobsRow.Item("Number"
... Okay, I found this... "... ("ID = " & Convert.ToString(applicant
"oldJobRow(0).Item("Number
I tried "cInt" in a couple of places with no luck.
I've seen this message before, so I imagine it's good to know how to avoid this conflict.
Thanks.
-David
My approach was (and would be) Option Strict Off - which is the default. I only use Option Strict On in exceptional circumstances, and for specific reasons. Although according to the docs it "improves performance", I've never done a program in which - after running checks specifically to test this - any difference has been discernable.
But if you want to preserve Option Strict On for the demo program explicit casting will - as you've discovered - cope with most of the problems. But the += and -= needs a bit more work because it is combining a number of operations: getting the value from the row.item, incrementing the value, putting the value back in the row.item
Try, for example
Dim myVal As Integer = CInt(oldJobRow(0).Item("Nu mber"))
myVal += 1 'or myVal = myVal + 1
oldJobRow(0).Item("Number" ) = myVal
Roger
But if you want to preserve Option Strict On for the demo program explicit casting will - as you've discovered - cope with most of the problems. But the += and -= needs a bit more work because it is combining a number of operations: getting the value from the row.item, incrementing the value, putting the value back in the row.item
Try, for example
Dim myVal As Integer = CInt(oldJobRow(0).Item("Nu
myVal += 1 'or myVal = myVal + 1
oldJobRow(0).Item("Number"
Roger
ASKER
Thank you, Roger!
I was suspecting that something like that would be required, but didn't know how to construct it.
Let me know when I've pestered you guy enough. I don't want to "wear out my welcome". I do sincerely appreciate your fantastic help!
-David
I was suspecting that something like that would be required, but didn't know how to construct it.
Let me know when I've pestered you guy enough. I don't want to "wear out my welcome". I do sincerely appreciate your fantastic help!
-David
David
Although you've not "worn out your welcome", I think it might be a good idea if you closed out this question. That doesn't mean to say that I won't still be prepared to answer, in this thread, supplementaries on what has been said so far: e.g. like the "the demo code is not working" issue just raised. But we've already ranged over a pretty wide field besides that indicated by the question's title and I think - even if only so that subsequent searchers will be able to identify points that might be useful to them - we've now reached the stage where, if there are more issues, it would be better to ask specific questions about them.
I'm not always about but, when I am, I generally trawl through recently posted questions to see if there's anything I can help with. I imagine Goran will do the same.
Roger
Although you've not "worn out your welcome", I think it might be a good idea if you closed out this question. That doesn't mean to say that I won't still be prepared to answer, in this thread, supplementaries on what has been said so far: e.g. like the "the demo code is not working" issue just raised. But we've already ranged over a pretty wide field besides that indicated by the question's title and I think - even if only so that subsequent searchers will be able to identify points that might be useful to them - we've now reached the stage where, if there are more issues, it would be better to ask specific questions about them.
I'm not always about but, when I am, I generally trawl through recently posted questions to see if there's anything I can help with. I imagine Goran will do the same.
Roger
ASKER
I love the way that each Job is removed from the list where there are no more positions available for it. That's awesome!
When I assigned all 9 positions (3 positons at each of the 3 Jobs) to 9 Kids and the last of the JobNames was removed from the DataGridView, as the last one disappeared it threw an "IndexOutOf RangeException", "Index -1 does not have a value."
Here's the full detail:
System.IndexOutOfRangeExce ption was unhandled by user code
Message="Index -1 does not have a value."
Source="System.Windows.For ms"
StackTrace:
at System.Windows.Forms.Curre ncyManager .get_Item( Int32 index)
at System.Windows.Forms.Curre ncyManager .get_Curre nt()
at EE_Demo.Form1.updateTotals () in C:\0-VisualStudioProjects\ SYEP07\EE_ Demo_DGV\E E_Demo\For m1.vb:line 149
at EE_Demo.Form1.cmJobs_Posit ionChanged (Object sender, EventArgs e) in C:\0-VisualStudioProjects\ SYEP07\EE_ Demo_DGV\E E_Demo\For m1.vb:line 143
at System.EventHandler.Invoke (Object sender, EventArgs e)
at System.Windows.Forms.Curre ncyManager .OnPositio nChanged(E ventArgs e)
I'm not sure whether a "Try...Catch" somewhere or something like, "If cmJobs.Current >=0 ..." is the correct way to handle this. It seems to be a common issue to handle because I found so much about that exception with Google I didn't know what was what.
I hope I'm not starting to be a pest. I think this piece will have me in good shape for fixing my application. Then I'll go ahead and close this thread out and give you the points. If I have other issues I'll start another question.
Thank you again.
-David
When I assigned all 9 positions (3 positons at each of the 3 Jobs) to 9 Kids and the last of the JobNames was removed from the DataGridView, as the last one disappeared it threw an "IndexOutOf RangeException", "Index -1 does not have a value."
Here's the full detail:
System.IndexOutOfRangeExce
Message="Index -1 does not have a value."
Source="System.Windows.For
StackTrace:
at System.Windows.Forms.Curre
at System.Windows.Forms.Curre
at EE_Demo.Form1.updateTotals
at EE_Demo.Form1.cmJobs_Posit
at System.EventHandler.Invoke
at System.Windows.Forms.Curre
I'm not sure whether a "Try...Catch" somewhere or something like, "If cmJobs.Current >=0 ..." is the correct way to handle this. It seems to be a common issue to handle because I found so much about that exception with Google I didn't know what was what.
I hope I'm not starting to be a pest. I think this piece will have me in good shape for fixing my application. Then I'll go ahead and close this thread out and give you the points. If I have other issues I'll start another question.
Thank you again.
-David
My fault. I didn't test that. It's trying to run the updateTotals sub when there's nothing left on which to do so. It's complicated by the fact that that sub is called from two places - one on the currency manager's position changed and the other in the button_click - both of which will fire if an existing row is removed from the Jobs grid. So, two corrections
Private Sub cmJobs_PositionChanged(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
and, at the start of the updateTotals() sub itself
If cmJobs.Count = 0 Then
txtAvailable.Text = ""
txtTaken.Text = ""
txtTotal.Text = ""
MsgBox("No more jobs")
Exit Sub
End If
Some sort of Try Catch might handle it, but it's something that should be programmed for specifically rather than relying on that.
Roger
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
and, at the start of the updateTotals() sub itself
If cmJobs.Count = 0 Then
txtAvailable.Text = ""
txtTaken.Text = ""
txtTotal.Text = ""
MsgBox("No more jobs")
Exit Sub
End If
Some sort of Try Catch might handle it, but it's something that should be programmed for specifically rather than relying on that.
Roger
ASKER
I see. Great. Thank you once again!
That worked like a charm!
That worked like a charm!
I will only add a fwe things: Option strict should be set to Off, since you are a beginner. In time, when you get into to .Net programming, turning it On will be a good thing, so you can understand how things work.
Another thing (maybe a primary thing) would be to learn how to debug. You are correct when you say that the error you have received can be found in many cases, but if you understand what this error means, and find a line of code that hhrew an error, the rest is often easy. When you click on the Debug menu, you will se somethings like Step Into, Step over, Breakpoints, etc. This things allow you to quickly find a row that throws an error, and using Immediate Window you can get values of you variables, and many other things. Knowing how to debug is very important in programming.
Goran
Another thing (maybe a primary thing) would be to learn how to debug. You are correct when you say that the error you have received can be found in many cases, but if you understand what this error means, and find a line of code that hhrew an error, the rest is often easy. When you click on the Debug menu, you will se somethings like Step Into, Step over, Breakpoints, etc. This things allow you to quickly find a row that throws an error, and using Immediate Window you can get values of you variables, and many other things. Knowing how to debug is very important in programming.
Goran
ASKER
Roger,
Would you mind terribly adding some comments to a couple of the Subs? Most are commented beautifully.
I can see in general terms what these are doing, but some of the code is unfamiliar to me. I've never seen "TypeOf" before. I'll have to look in the help or Google to see what that is.
Here are the two that a couple of comments would really help me with:
Private Function getTitleFromID(ByVal ID As Integer) As String
Dim dr As DataRow() = dtJobs.Select("ID = " & ID)
If Not dr Is Nothing Then
Return CStr(dr(0)("JobName"))
Else
Return ""
End If
End Function
Private Sub fillInTitle(ByVal dgvr As DataGridViewRow)
If TypeOf dgvr.Cells(2).Value Is DBNull Then
dgvr.Cells(3).Value = ""
Else
dgvr.Cells(3).Value = getTitleFromID(CInt(dgvr.C ells(2).Va lue))
End If
End Sub
Thank you again.
-David
Would you mind terribly adding some comments to a couple of the Subs? Most are commented beautifully.
I can see in general terms what these are doing, but some of the code is unfamiliar to me. I've never seen "TypeOf" before. I'll have to look in the help or Google to see what that is.
Here are the two that a couple of comments would really help me with:
Private Function getTitleFromID(ByVal ID As Integer) As String
Dim dr As DataRow() = dtJobs.Select("ID = " & ID)
If Not dr Is Nothing Then
Return CStr(dr(0)("JobName"))
Else
Return ""
End If
End Function
Private Sub fillInTitle(ByVal dgvr As DataGridViewRow)
If TypeOf dgvr.Cells(2).Value Is DBNull Then
dgvr.Cells(3).Value = ""
Else
dgvr.Cells(3).Value = getTitleFromID(CInt(dgvr.C
End If
End Sub
Thank you again.
-David
Private Function getTitleFromID(ByVal ID As Integer) As String
'use datatable filter to return only rows with this ID
Dim dr As DataRow() = dtJobs.Select("ID = " & ID)
'check if there are any such rows
If Not dr Is Nothing Then
'if yes, there should only be one - in position 0
'return the value in the JobNmae field from that
Return dr(0)("JobName")
Else
'otherwise, return empty string
Return ""
End If
End Function
Private Sub fillInTitle(ByVal dgvr As DataGridViewRow)
'check if there is a value in the thurd cell ...
'... (index 2, which is the "Job" field ...
'... in this row from the datagridview
If TypeOf dgvr.Cells(2).Value Is DBNull Then
'if not, then there is no Job Title
dgvr.Cells(3).Value = ""
Else
'if there is, use it to get the Job Title
dgvr.Cells(3).Value = getTitleFromID(CInt(dgvr.C ells(2).Va lue))
End If
End Sub
Roger
'use datatable filter to return only rows with this ID
Dim dr As DataRow() = dtJobs.Select("ID = " & ID)
'check if there are any such rows
If Not dr Is Nothing Then
'if yes, there should only be one - in position 0
'return the value in the JobNmae field from that
Return dr(0)("JobName")
Else
'otherwise, return empty string
Return ""
End If
End Function
Private Sub fillInTitle(ByVal dgvr As DataGridViewRow)
'check if there is a value in the thurd cell ...
'... (index 2, which is the "Job" field ...
'... in this row from the datagridview
If TypeOf dgvr.Cells(2).Value Is DBNull Then
'if not, then there is no Job Title
dgvr.Cells(3).Value = ""
Else
'if there is, use it to get the Job Title
dgvr.Cells(3).Value = getTitleFromID(CInt(dgvr.C
End If
End Sub
Roger
ASKER
Thank you again, very much.
ASKER
I wanted to ask which would be easier to do, incorporate your perfectly working "demo" into my app or add my connection string and such into the "demo".
Instead of continuing here I asked the question in a new thread here:
https://www.experts-exchange.com/questions/22513546/Have-good-demo-and-messy-actual-web-form-Which-is-easier-to-modify-into-working-app.html
-David
Instead of continuing here I asked the question in a new thread here:
https://www.experts-exchange.com/questions/22513546/Have-good-demo-and-messy-actual-web-form-Which-is-easier-to-modify-into-working-app.html
-David
ASKER
Roger,
In my new question (my second comment) I've added a new DataGridView by dragging the Applicants table from the DataSet so it would create a TableAdapter.
The new one had a DataSource of "ApplicantsBindingSource" where the Demo you provided had "(none)" for a DataSource. In the new question, I'm asking if I should leave the BindingSource or remove it. I don't know if it would cause problems or give me some functionality that I want.
Thanks.
-David
In my new question (my second comment) I've added a new DataGridView by dragging the Applicants table from the DataSet so it would create a TableAdapter.
The new one had a DataSource of "ApplicantsBindingSource" where the Demo you provided had "(none)" for a DataSource. In the new question, I'm asking if I should leave the BindingSource or remove it. I don't know if it would cause problems or give me some functionality that I want.
Thanks.
-David
Roger