We help IT Professionals succeed at work.

Getting "Cannot update identity column" error when I'm not trying to update an identity column

2,286 Views
Last Modified: 2013-11-26
In a VB Windows app I'm getting a "Cannot update identity column 'keyJobTitleID'" and I'm not trying to update the identity column 'keyJobTitleID'.
The error is falling on the line immediately preceding End Sub here:
    Private Sub updateTotals()
        If cmJobs.Count = 0 Then
            txtAvailable.Text = ""
            txtTaken.Text = ""
            txtTotal.Text = ""
            MsgBox("No more jobs")
            Exit Sub
        End If
        'use the currency manager to get the currently ...
        '... selected job in the jobs grid
        Dim jobsRow As DataRowView = CType(cmJobs.Current, DataRowView)
        'that says how many of that job are available
        Dim available As Integer = CInt(jobsRow.Item("txtWorksiteJobTitlePositionsNumber"))
        'get the job ID from that row
        Dim thisJob As Integer = CInt(jobsRow.Item("keyJobTitleID"))
        'use the job ID to find how many applicants ...
        '... already have that job
        Dim taken As Integer = CInt(dtApplicants.Compute("Count(keyJobTitleID)", "keyJobTitleID = " & thisJob))
        'the total is the sum of those two
        Dim total As Integer = available + taken
        'display the results
        txtAvailable.Text = available.ToString
        txtTaken.Text = taken.ToString
        txtTotal.Text = total.ToString

        'update TextBoxes for count of SYEP, STEPS and Fort Lauderdale Kids - IN Form_Load, UpdateTotals and btnRefresh_Click
        txtSYEP.Text = Me.ApplicantsTableAdapter.CountSYEPActivity().ToString
        txtSTEPS.Text = Me.ApplicantsTableAdapter.CountSTEPSActivity().ToString
        txtFTLauderdale.Text = Me.ApplicantsTableAdapter.CountFtLauderdaleActivity().ToString
        txtConsideredCount.Text = Me.ApplicantsTableAdapter.CountConsidered().ToString

        'make sure any outstanding edits are committed to the datatables
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()
        'then save datatable changes to database        
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles)   '<-- Error: Cannot update Identity column 'keyJobTitleID'
    End Sub

The above sub is called when I make a change in the DataGridView by this 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

The JobTitlesTableAdapter has this SQL CommandText:
SELECT     COUNT(*) AS Expr1
FROM         JobTitles INNER JOIN
                      Worksites ON JobTitles.keyWorksiteID = Worksites.keyWorksiteID

The ApplicantsTableAdapter has this stored procedure:
ALTER PROCEDURE [dbo].[spUpdate_Applicants_WorksiteName_From_JobTitles] AS
UPDATE Applicants  
SET keyWorksiteID = J.keyWorksiteID,
      keyEmployerID = J.keyEmployerID,
      WorksiteName = J.WorksiteName,
      EmployerName = J.EmployerName
FROM Applicants  A
INNER JOIN JobTitles J
ON A.keyJobTitleID = J.keyJobTitleID

and some select and count statements.

In the Applicants table the Identity column is "keyApplicantID", in the JobTitles table the Identity column is "keyJobTitleID".

I'm not trying to change the keyJobTitleID field in JobTitles, I'm basically copying the value into the Forein Key field with the same name, "keyJobTitleID" in the Applicants table, then updating some totals in the JobTitles table.  The purpose is to copy into or "assign" JobTitle IDs to the Applicants table FK, "keyJobTitleID".

It was working during the time I was generating my own PKs with the columns set to Identity=No.  I started getting this error when I changed the PK, "keyJobTitleID" etc. to Identity=Yes.

The error appears on line 248 when I make a JobTitle assignment, see "Private Sub cmJobs_PositionChanged".

Any help is greatly apprecited.  I'm learning and don't know what I'm doing. :-}


Imports System
Imports System.Data
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports System.Windows.Forms
Imports System.Drawing
 
Public Class YouthJob
    Private dtApplicants As DataTable
    Private cmApplicants As CurrencyManager
    Private dtJobs As DataTable
    Private dvjobs As New DataView
    Private WithEvents cmJobs As CurrencyManager
 
    Private Sub ApplicantsBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ApplicantsBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.ApplicantsBindingSource.EndEdit()
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
    End Sub
 
    Private Sub YouthJob_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'SYEP2007DataSet.JobTitles' table. You can move, or remove it, as needed.
        'Me.JobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.JobTitles)
        'Me.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)
        Try
            Me.JobTitlesTableAdapter.Fill(Me.SYEP2007DataSet.JobTitles)
        Catch ex As Exception
            MessageBox.Show(ex.StackTrace)
        End Try
        Try
            Me.ApplicantsTableAdapter.Fill(Me.SYEP2007DataSet.Applicants)
        Catch ex As Exception
            MessageBox.Show(ex.StackTrace)
        End Try
 
 
 
        dtApplicants = Me.SYEP2007DataSet.Applicants
        dtJobs = Me.SYEP2007DataSet.JobTitles
 
        'filltables() 'equivalent to filling tables with ...
        '... a dataadapter or a tableadapter
 
        'get and display the count of applicant records
        Dim count As Integer = dtApplicants.Rows.Count
        txtTotalApplicants.Text = String.Format("{0} Applicants", count)
 
        'TabControl1.SelectedIndex = 1    'Commented out.  There's no TabControl on this form.  Just on the original Form1
 
        'bind the applicants table to the datagridview
        ApplicantsDataGridView.DataSource = dtApplicants
        'set up the currencymanager - convenient for referring ...
        '... to the currently selected row in the applicants grid
        cmApplicants = CType(BindingContext(dtApplicants), CurrencyManager)
 
        'set up a dataview to filter the jobs table so it ...
        '... only shows those where some jobs remain available
        dvjobs.Table = dtJobs
        'dvjobs.RowFilter = "txtWorksiteJobTitlePositionsNumber > 0"         '<<<<<Moved to end of YouthJob_Load
        'bind the jobs table to the datagridview
        JobTitlesDataGridView.DataSource = dvjobs
        'set up the currency manager
        cmJobs = CType(BindingContext(dvjobs), CurrencyManager)
 
        'customise the grids - with a strongly typed dataset ...
        '... this could be done in the designer
        ApplicantsDataGridView.ReadOnly = False
        ApplicantsDataGridView.AllowUserToAddRows = False   'Was True.  I don't know why.
        'hide the applicant ID
        ApplicantsDataGridView.Columns(0).Visible = True
        'hide the job ID ...
        ApplicantsDataGridView.Columns(1).Visible = True
        'hide the ProgramID column in Applicants DGV
        ApplicantsDataGridView.Columns(3).Visible = True
        '... but add an unbound column to show ...
        '... the job title matching that ID
        Dim TitleCol As New DataGridViewTextBoxColumn
        'TitleCol.HeaderText = "Job Title"
 
        'ApplicantsDataGridView.Columns(0).Width = 50  'AppID
        'ApplicantsDataGridView.Columns(1).Width = 50  'JobTitleID
        'ApplicantsDataGridView.Columns(2).Width = 100  'LastName
        'ApplicantsDataGridView.Columns(3).Width = 100  'FirstName
        'ApplicantsDataGridView.Columns(4).Width = 150  'Home City
        'ApplicantsDataGridView.Columns(5).Width = 60  'Zip
        'ApplicantsDataGridView.Columns(6).Width = 150  'City Preference
        'ApplicantsDataGridView.Columns(7).Width = 60  'Hours
        'ApplicantsDataGridView.Columns(8).Width = 50  'M-F
        ApplicantsDataGridView.Columns.Add("TitleCol", "Job Title")
        ApplicantsDataGridView.Columns("TitleCol").DisplayIndex = 9
        'ApplicantsDataGridView.Columns("TitleCol").Width = 250  'Job Title
        'ApplicantsDataGridView.Columns(10).Width = 200  'Work Preference
        'ApplicantsDataGridView.Columns(11).Width = 95  'Referred by
        'ApplicantsDataGridView.Columns(12).Width = 50  'Offender
 
        'Dim NumberofAdults As New DataGridViewTextBoxColumn  'Adding column this way did not show any data
        'NumberofAdults.HeaderText = "Adults"                 'Adding it in the designer shows the data
        'ApplicantsDataGridView.Columns.Add(NumberofAdults)   'The above Titlecol column goes on the end
 
        JobTitlesDataGridView.ReadOnly = True
        JobTitlesDataGridView.AllowUserToAddRows = False
        'JobTitlesDataGridView.Columns(0).Visible = False  'Hide JobTitleID
 
 
        'run the sub to fill the jobs numbers displays
        updateTotals()
 
        'get the job titles for the applicants
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
 
        Next
        dvjobs.RowFilter = "txtWorksiteJobTitlePositionsNumber > 0"   '<<<<<Moved to end of YouthJob_Load from above.
 
        'update TextBoxes for count of SYEP, STEPS and Fort Lauderdale Kids - IN Form_Load, UpdateTotals and btnRefresh_Click
        txtSYEP.Text = Me.ApplicantsTableAdapter.CountSYEPActivity().ToString
        txtSTEPS.Text = Me.ApplicantsTableAdapter.CountSTEPSActivity().ToString
        txtFTLauderdale.Text = Me.ApplicantsTableAdapter.CountFtLauderdaleActivity().ToString
        txtConsideredCount.Text = Me.ApplicantsTableAdapter.CountConsidered().ToString
 
    End Sub
    Private Sub YouthJob_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        'make sure any outstanding edits are committed to the datatables
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()
        'then save datatable changes to database        
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles)
 
    End Sub
    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)
 
        'make sure any outstanding edits are committed to the datatables
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()
        'then save datatable changes to database        
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles)
 
 
    End Sub
 
    Private Sub btnCancelPosition_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelPosition.Click
        Me.Close()
    End Sub
 
    Private Sub cmJobs_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmJobs.PositionChanged
        'the jobs currency manager was declared WithEvents ...
        '... to make it easy to update the jobs numbers ...
        '... displays when a different job was selected ...
        '... in the grid
        If cmJobs.Count > 0 Then
            updateTotals()
        End If
    End Sub
 
    Private Sub updateTotals()
        If cmJobs.Count = 0 Then
            txtAvailable.Text = ""
            txtTaken.Text = ""
            txtTotal.Text = ""
            MsgBox("No more jobs")
            Exit Sub
        End If
        'use the currency manager to get the currently ...
        '... selected job in the jobs grid
        Dim jobsRow As DataRowView = CType(cmJobs.Current, DataRowView)
        'that says how many of that job are available
        Dim available As Integer = CInt(jobsRow.Item("txtWorksiteJobTitlePositionsNumber"))
        'get the job ID from that row
        Dim thisJob As Integer = CInt(jobsRow.Item("keyJobTitleID"))
        'use the job ID to find how many applicants ...
        '... already have that job
        Dim taken As Integer = CInt(dtApplicants.Compute("Count(keyJobTitleID)", "keyJobTitleID = " & thisJob))
        'the total is the sum of those two
        Dim total As Integer = available + taken
        'display the results
        txtAvailable.Text = available.ToString
        txtTaken.Text = taken.ToString
        txtTotal.Text = total.ToString
 
        'update TextBoxes for count of SYEP, STEPS and Fort Lauderdale Kids - IN Form_Load, UpdateTotals and btnRefresh_Click
        txtSYEP.Text = Me.ApplicantsTableAdapter.CountSYEPActivity().ToString
        txtSTEPS.Text = Me.ApplicantsTableAdapter.CountSTEPSActivity().ToString
        txtFTLauderdale.Text = Me.ApplicantsTableAdapter.CountFtLauderdaleActivity().ToString
        txtConsideredCount.Text = Me.ApplicantsTableAdapter.CountConsidered().ToString
 
        'make sure any outstanding edits are committed to the datatables
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()
        'then save datatable changes to database        
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles)
    End Sub
 
    Private Function getTitleFromID(ByVal ID As Integer) As String
        'Temporary line for debugging purposes
        'If that is reporting 0, then we know the problem is with the datatable.  
        'If it is reporting more than that, then the problem is with either the value 
        'that is being passed to the function, 
        'or with the syntax or references in the select statement.
        'Debug.WriteLine(dtJobs.Rows.Count)
 
        'use datatable filter to return only rows with this ID
        Dim dr As DataRow() = dtJobs.Select("keyJobTitleID = " & ID)
        'check if there are any such rowsI
        If dr.Length > 0 Then
            'if yes, there should only be one - in position 0
            'return the value in the JobName field from that
            '          Return CStr(dr(0)("txtWorksiteJobTitle"))
            Dim s As String = CStr(dr(0)("txtWorksiteJobTitle"))
            ' MsgBox("| s = " & s & "|")  'Here for troubleshooting
            Return s
        Else
            'otherwise, return empty string
            Return ""
        End If
    End Function
 
    Private Sub fillInTitle(ByVal dgvr As DataGridViewRow)
        'check if there is a value in the third cell ...
        '... (index 2, which is the "Job" field ...
        '... in this row from the datagridview
        If TypeOf dgvr.Cells("keyJobTitleID").Value Is DBNull Then
            'if not, then there is no Job Title
            dgvr.Cells("TitleCol").Value = ""
        Else
            'if there is, use it to get the Job Title
            'Debug.WriteLine("Cell 2 = " & CInt(dgvr.Cells(2).Value))  'I didn't see anything unusual in the debug window when this ran.
            'MsgBox("Cell 2 = " & CInt(dgvr.Cells(2).Value))  '  Here for troubleshooting.
            dgvr.Cells("TitleCol").Value = getTitleFromID(CInt(dgvr.Cells("keyJobTitleID").Value))
            'Debug.WriteLine("Cell 4 = '" & CInt(dgvr.Cells(4).Value) & "'")
            'MsgBox("Cell 4 = '" & CStr(dgvr.Cells(4).Value) & "'")   'Here for troubleshooting.
            'MsgBox("Cell 4 = '" & CStr(dgvr.Cells("TitleCol").Value) & "'")
        End If
 
 
    End Sub
 
    Private Sub ApplicantsDataGridView_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles ApplicantsDataGridView.DataError
        Debug.WriteLine(e.ColumnIndex)
        Debug.WriteLine(e.RowIndex)
        Debug.WriteLine(ApplicantsDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).Value)
        Debug.WriteLine(ApplicantsDataGridView.Rows(e.RowIndex).Cells(e.ColumnIndex).EditedFormattedValue)
        e.ThrowException = True
    End Sub
 
    Private Sub btnRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefresh.Click
        ''get the job titles for the applicants
        ''I copied this from the bottom of YouthJob_Load in an attempt to make the Applicants DGV update the Job Titles after a column sort.
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next
 
        'update TextBoxes for count of SYEP, STEPS and Fort Lauderdale Kids - IN Form_Load, UpdateTotals and btnRefresh_Click
        txtSYEP.Text = Me.ApplicantsTableAdapter.CountSYEPActivity().ToString
        txtSTEPS.Text = Me.ApplicantsTableAdapter.CountSTEPSActivity().ToString
        txtFTLauderdale.Text = Me.ApplicantsTableAdapter.CountFtLauderdaleActivity().ToString
        txtConsideredCount.Text = Me.ApplicantsTableAdapter.CountConsidered().ToString
 
        'make sure any outstanding edits are committed to the datatables
        cmApplicants.EndCurrentEdit()
        cmJobs.EndCurrentEdit()
        'then save datatable changes to database        
        Me.ApplicantsTableAdapter.Update(Me.SYEP2007DataSet.Applicants)
        Me.JobTitlesTableAdapter.Update(Me.SYEP2007DataSet.JobTitles)
 
        'Update Applicants(keyWorksiteID, keyEmployerID, WorksiteName, EmployerName) from JobTitles(keyWorksiteID, keyEmployerID, WorksiteName, EmployerName) in the database tables
        Try
            Me.ApplicantsTableAdapter.spUpdate_Applicants_WorksiteName_From_JobTitles()
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
 
    End Sub
    Private Sub ApplicantsDataGridView_ColumnHeaderMouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles ApplicantsDataGridView.ColumnHeaderMouseClick
        ''get the job titles for the applicants
        ''I copied this from the bottom of YouthJob_Load in an attempt to make the Applicants DGV update the Job Titles after a column sort.
        For Each dgvr As DataGridViewRow In ApplicantsDataGridView.Rows
            fillInTitle(dgvr)
            'cmApplicants.EndCurrentEdit() '<<< NEW LINE HERE 'This had no apparent affect.  It didn't cause the Job Title column to fill in on form load
            'ApplicantsDataGridView.EndEdit()  'This didn't do it either.
        Next
 
    End Sub
    Private Sub FillByToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillByToolStripButton.Click
        Try
            Me.ApplicantsTableAdapter.FillBy(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub
    Private Sub btnConsideredFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConsideredFilter.Click
        Try
            Me.ApplicantsTableAdapter.FillBy(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub
 
    Private Sub FillBy1ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillBy1ToolStripButton.Click
        Try
            Me.ApplicantsTableAdapter.FillBy1(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub
 
    Private Sub btnSYEPFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSYEPFilter.Click
        Try
            Me.ApplicantsTableAdapter.FillBy1(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub
 
    Private Sub FillBy2ToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillBy2ToolStripButton.Click
        Try
            Me.ApplicantsTableAdapter.FillBy2(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
 
    End Sub
 
    Private Sub txtAllFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtAllFilter.Click
        Try
            Me.ApplicantsTableAdapter.FillBy2(Me.SYEP2007DataSet.Applicants)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class

Open in new window

Comment
Watch Question

CERTIFIED EXPERT

Commented:
JobTitlesTableAdapter has more than one sql statement - the one you provided is for SELECT, but there must be others for update and delete. When you issue JobTitlesTableAdapter.Update, it's the UPDATE statement that gets executed. Please find it.

Another possibility is that the table has a trigger that tries to update identity column.

Author

Commented:
No triggers.  I ran sp_triggerhelp on the table and nothing turned up.
I did try to find an UPDATE statement or sp with an update on JobTitlesTableAdapter.  I'll look again.
CERTIFIED EXPERT

Commented:
look at the properties of the adapter JobTitlesTableAdapter, and look for the property "update".

Author

Commented:
There are a couple of places to look.  In the YouthJob.vb[Design] with JobTitlesTableAdapter at the bottom of the screen, the properties don't have very much; (Name), GenerateMember, Modifiers and ClearBeforeFill:True

If I click the tiny triangle on JobTitlesTableAdapter and select "Edit Queries in DataSet Designer" I see all the tables and their TableAdapters.  Under JobTitlesTableAdapter is only "Fill,GetData()" which is a SELECT statement, "FillBy()", another SELECT statement and my "PositionsByWorksite ()" which is a SELECT COUNT statement.

I looked through all my stored procedures and none try to update keyJobTitleID.

I can't think of any place else to look.
CERTIFIED EXPERT

Commented:
Look in the Solution Explorer window. Find the Dataset.xsd.  Open it. You will see your table JobTitles. In the lower part, there will be JobTitlesTableAdapter header. Click on that header and look at the properties - there will be SelectCommand and UpdateCommand. Look at UpdateCommand.

From my own experience, I recommend that you develop some subset of the objects that you know and can effectively use in Visual Studio. The assortment of all available objects is overwhelmingly large, and trying to even figure out what is for what, is very big effort. New objects are not necessarily best, and if in order to issue a simple select you have to create 10 interrelated objects, all these datasets, and adapters, and datatables, and in new release of Visual Studio the assortment always doubles, always with the claim that new objects are much better than their predecessors, introduced only 2 years ago, it hardly makes sense to me to try to follow all that.

Author

Commented:
Ah ha!  I was looking all around it but never looked at the properties of the TA header.

That UPDATE includes a SET [keyJobTitlesID] statement.

That worked when I was creating the keyJobTitleID "manually" and adding the value to the Primary Key field myself.  But, I had to change the PK field to an Intity field and that "broke" it.

If I just remove the keyJobTitleID field from the SET statement, will that work?

I agree with your statement about developing a subset of objects to learn to use well instead of trying to figure out everything.  What I have been doing to this point is when I get stuck on something I don't know how to do, which is most everything righ not ;-), I ask for assistance and use whatever seems to work.  
I would indeed prefer to use the simplest method to get the job done.  Most everything I need to do involves simply populating a database from an input Web Form and pulling out bits of data for like a confirmation page or a "Here's what you entered" page.
This application is for "assigning" a worksite to a student, so I'm trying to allow a user of the app to select a student from a datagridview then select a worksite from another datagridview and click a button to put the PK, keyJobTitleID, from the JobTitles table into the FK, keyJobTitleID, of the Applicants table.  I actually need to do this in a Web Application instead of this, Windows app, but I'm not sure if I can do that in the time I have.  I need to get a working solution by January 7th.

I'm sorry for going on and on.  I'm just starting to learn and sometimes it's a bit overwhelming.


UPDATE [JobTitles] SET [keyJobTitleID] = @keyJobTitleID, [keyWorksiteID] = @keyWorksiteID, [keyEmployerID] = @keyEmployerID, [txtWorksiteJobTitle] = @txtWorksiteJobTitle, [txtWorksiteJobTitlePositionsNumber] = @txtWorksiteJobTitlePositionsNumber, [ddlDressCode] = @ddlDressCode, [ddlIndoorOutdoor] = @ddlIndoorOutdoor, [ddlWorkHours] = @ddlWorkHours, [cbWeekdays] = @cbWeekdays, [cbSaturdays] = @cbSaturdays, [cbSundays] = @cbSundays, [ddlBackgroundCheck] = @ddlBackgroundCheck, [txtJobDescriptionPrim1] = @txtJobDescriptionPrim1, [txtJobDescriptionPrim2] = @txtJobDescriptionPrim2, [txtJobDescriptionPrim3] = @txtJobDescriptionPrim3, [txtJobDescriptionPrim4] = @txtJobDescriptionPrim4, [txtJobDescriptionPrim5] = @txtJobDescriptionPrim5, [txtJobDescriptionSec1] = @txtJobDescriptionSec1, [txtJobDescriptionSec2] = @txtJobDescriptionSec2, [txtJobDescriptionSec3] = @txtJobDescriptionSec3, [txtJobDescriptionSec4] = @txtJobDescriptionSec4, [txtJobDescriptionSec5] = @txtJobDescriptionSec5, [ApplicationDatePosition] = @ApplicationDatePosition, [WorksiteCity] = @WorksiteCity, [PositionsAvailable] = @PositionsAvailable, [PositionsTaken] = @PositionsTaken, [EmployerName] = @EmployerName, [WorksiteName] = @WorksiteName, [WorksiteZip] = @WorksiteZip WHERE (([keyJobTitleID] = @Original_keyJobTitleID) AND ((@IsNull_keyWorksiteID = 1 AND [keyWorksiteID] IS NULL) OR ([keyWorksiteID] = @Original_keyWorksiteID)) AND ((@IsNull_keyEmployerID = 1 AND [keyEmployerID] IS NULL) OR ([keyEmployerID] = @Original_keyEmployerID)) AND ((@IsNull_txtWorksiteJobTitle = 1 AND [txtWorksiteJobTitle] IS NULL) OR ([txtWorksiteJobTitle] = @Original_txtWorksiteJobTitle)) AND ((@IsNull_txtWorksiteJobTitlePositionsNumber = 1 AND [txtWorksiteJobTitlePositionsNumber] IS NULL) OR ([txtWorksiteJobTitlePositionsNumber] = @Original_txtWorksiteJobTitlePositionsNumber)) AND ((@IsNull_ddlDressCode = 1 AND [ddlDressCode] IS NULL) OR ([ddlDressCode] = @Original_ddlDressCode)) AND ((@IsNull_ddlIndoorOutdoor = 1 AND [ddlIndoorOutdoor] IS NULL) OR ([ddlIndoorOutdoor] = @Original_ddlIndoorOutdoor)) AND ((@IsNull_ddlWorkHours = 1 AND [ddlWorkHours] IS NULL) OR ([ddlWorkHours] = @Original_ddlWorkHours)) AND ((@IsNull_cbWeekdays = 1 AND [cbWeekdays] IS NULL) OR ([cbWeekdays] = @Original_cbWeekdays)) AND ((@IsNull_cbSaturdays = 1 AND [cbSaturdays] IS NULL) OR ([cbSaturdays] = @Original_cbSaturdays)) AND ((@IsNull_cbSundays = 1 AND [cbSundays] IS NULL) OR ([cbSundays] = @Original_cbSundays)) AND ((@IsNull_ddlBackgroundCheck = 1 AND [ddlBackgroundCheck] IS NULL) OR ([ddlBackgroundCheck] = @Original_ddlBackgroundCheck)) AND ((@IsNull_txtJobDescriptionPrim1 = 1 AND [txtJobDescriptionPrim1] IS NULL) OR ([txtJobDescriptionPrim1] = @Original_txtJobDescriptionPrim1)) AND ((@IsNull_txtJobDescriptionPrim2 = 1 AND [txtJobDescriptionPrim2] IS NULL) OR ([txtJobDescriptionPrim2] = @Original_txtJobDescriptionPrim2)) AND ((@IsNull_txtJobDescriptionPrim3 = 1 AND [txtJobDescriptionPrim3] IS NULL) OR ([txtJobDescriptionPrim3] = @Original_txtJobDescriptionPrim3)) AND ((@IsNull_txtJobDescriptionPrim4 = 1 AND [txtJobDescriptionPrim4] IS NULL) OR ([txtJobDescriptionPrim4] = @Original_txtJobDescriptionPrim4)) AND ((@IsNull_txtJobDescriptionPrim5 = 1 AND [txtJobDescriptionPrim5] IS NULL) OR ([txtJobDescriptionPrim5] = @Original_txtJobDescriptionPrim5)) AND ((@IsNull_txtJobDescriptionSec1 = 1 AND [txtJobDescriptionSec1] IS NULL) OR ([txtJobDescriptionSec1] = @Original_txtJobDescriptionSec1)) AND ((@IsNull_txtJobDescriptionSec2 = 1 AND [txtJobDescriptionSec2] IS NULL) OR ([txtJobDescriptionSec2] = @Original_txtJobDescriptionSec2)) AND ((@IsNull_txtJobDescriptionSec3 = 1 AND [txtJobDescriptionSec3] IS NULL) OR ([txtJobDescriptionSec3] = @Original_txtJobDescriptionSec3)) AND ((@IsNull_txtJobDescriptionSec4 = 1 AND [txtJobDescriptionSec4] IS NULL) OR ([txtJobDescriptionSec4] = @Original_txtJobDescriptionSec4)) AND ((@IsNull_txtJobDescriptionSec5 = 1 AND [txtJobDescriptionSec5] IS NULL) OR ([txtJobDescriptionSec5] = @Original_txtJobDescriptionSec5)) AND ((@IsNull_ApplicationDatePosition = 1 AND [ApplicationDatePosition] IS NULL) OR ([ApplicationDatePosition] = @Original_ApplicationDatePosition)) AND ((@IsNull_WorksiteCity = 1 AND [WorksiteCity] IS NULL) OR ([WorksiteCity] = @Original_WorksiteCity)) AND ((@IsNull_PositionsAvailable = 1 AND [PositionsAvailable] IS NULL) OR ([PositionsAvailable] = @Original_PositionsAvailable)) AND ((@IsNull_PositionsTaken = 1 AND [PositionsTaken] IS NULL) OR ([PositionsTaken] = @Original_PositionsTaken)) AND ((@IsNull_EmployerName = 1 AND [EmployerName] IS NULL) OR ([EmployerName] = @Original_EmployerName)) AND ((@IsNull_WorksiteName = 1 AND [WorksiteName] IS NULL) OR ([WorksiteName] = @Original_WorksiteName)) AND ((@IsNull_WorksiteZip = 1 AND [WorksiteZip] IS NULL) OR ([WorksiteZip] = @Original_WorksiteZip)));
SELECT keyJobTitleID, keyWorksiteID, keyEmployerID, txtWorksiteJobTitle, txtWorksiteJobTitlePositionsNumber, ddlDressCode, ddlIndoorOutdoor, ddlWorkHours, cbWeekdays, cbSaturdays, cbSundays, ddlBackgroundCheck, txtJobDescriptionPrim1, txtJobDescriptionPrim2, txtJobDescriptionPrim3, txtJobDescriptionPrim4, txtJobDescriptionPrim5, txtJobDescriptionSec1, txtJobDescriptionSec2, txtJobDescriptionSec3, txtJobDescriptionSec4, txtJobDescriptionSec5, ApplicationDatePosition, WorksiteCity, PositionsAvailable, PositionsTaken, EmployerName, WorksiteName, WorksiteZip FROM JobTitles WHERE (keyJobTitleID = @keyJobTitleID)

Open in new window

CERTIFIED EXPERT

Commented:
> If I just remove the keyJobTitleID field from the SET statement, will that work?


Probably. It's hard for me to tell without having the full structure of your table and without full understanding of your application. One pitfall: if you closely look at the update statement,  you will see variables like @IsNull_txtJobDescriptionPrim3, which are not clear where coming from, so this update statement is kinda a blackbox.

You will know when you try it.

Another note: once you move to the web application, it's possible that you will find totally different set of objects available to you, so the experience of this applciation may or may not be of any use. I suggest that you try in web application.

Illustrarting the general thoughts about vs.net: without all these sophisticated .net objects, the task would be pretty simple:

sub cmdOK_click
 dim cn as object
 set cn=createobject("adodb.connection")
 cn.open "<sql connection string>"
 cn.execute "insert into applicants(FK, keyJobTitleID) select " & me.field1.value & ","  & me.field2.value
 cn.close
end sub

Author

Commented:
Are you saying that this is the basic code that would accomplish assigning a "JobTitle" to an "Applicant" in my Web Application?

sub cmdOK_click
 dim cn as object
 set cn=createobject("adodb.connection")
 cn.open "<sql connection string>"
 cn.execute "insert into applicants(FK, keyJobTitleID) select " & me.field1.value & ","  & me.field2.value
 cn.close
end sub

That looks way easier than the way I'm doing it now.
Then, I'm also doing things like looking at the total available positions in a JobTitle and only if there are >0 available allowing the assignment and subtracting 1 from the number available, with Total Positions and Currently Available Positions being two fields in the database.  I had a great deal of help with the original Windows Application.  ;-)

I had to work on an "emergence project" today, so I haven't made any progress since yesterday.  Please check back on me a little later.  
If removing "keyJobTitleID" from that SET statement gets rid of that error, I'll close this question out as "solved" and post another question if... err, when I get stuck again.  ;-)
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
> "configure" in your table


...in the dataset - right-click the table, choose "configure" from the context menu.

Author

Commented:
Oh, thank you for telling me about "configure" on the dataset.  I didn't even know I could do that.  I was thinking about just editing the file manually in the properties.  I'm glad I didn't go ahead and do that.  My luck it would have totally messed it up.  I'm at home letting the swelling in my brain go down a bit, with the aid of a little red wine.  I hope you are enjoying the holiday as well.  ;-)  I'll have to tackle this again in a day or two.

Author

Commented:
vadimrapp1, re-"configured" the tableadapter as you suggested.  It did indeed remove the PK from the SET statement.  After rebuilding the app it worked again.  Thank you very much!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.