Child List for Field 'BatchSummary' Cannot be created Error

Hi All,

im having a very strange problem and im not sure how to solve it. I have created an image scanning system for a client. I have a gridview which displays the scanned batches which included the batch ID, No. of Documents, No. Recognised and the No. Verified.

When a new batch is run the scanner can process 100+ documents per minute (its quite fast). For each document scanned i create a record in a 'TempImages' Table in SQL Server database to record the batch Number and Image Number as well as other information, I then Update the 'batchSummary' Table with the counts of the Temp Images Table which does a live count as the documents are scanned. I then reload the gridview to show the counts incrementing as the documents are scanned.

The problem im having is very occasionally my client recieved the error "Child List for Field 'BatchSummary' Cannot be created"
This then crashed the application.

I use threading during scanning as i also process and verify each image as it is scanned in.
It looks to me like at a certain point the application is reading from the batch summary on one thread but on another it is updating the record and so the read fails to return results as possibly the table or record has been locked.

Is there any way i can check to see if the record/table has been locked for editing. If it has been locked then i want to be able to skip loading the gridview for that instance.

I have included the code for the GridView Creation and the Insert Statement and Update Statement.

Thanks in advance
gleepyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gleepyAuthor Commented:
Sorry forgot to add the code.

It fails only if i add LoadGridView() to the UpdateBatchDocumentCount() function. This then gives a live view of the documents being processed which is ideal. if this code is removed then it will only show the counts once the scan batch has been completed.

It only causes this error very intermittently and i have not been able to replicate it on my development PC but have witnessed it occuring on my clients machine. Would PC Specification have anything to do with it. ie i have a pentium Dual Core processor 2.66Ghz and my client is using Pentium Dual Core 2.0Ghz??

============ Load grid view Code ================
Private Sub LoadGridView()
        thisds = New DataSet
        thisds.Clear()
        Dim Todaygridcommand As New SqlCommand
        Dim TodaygridAdapter As New SqlDataAdapter
        Try
            Todaygridcommand.Connection = gthisconn
            Todaygridcommand.CommandText = "SELECT BatchSummary.ID, BatchSummary.BatchNo, BatchSummary.Project, BatchSummary.BatchDate, BatchSummary.DMSDate as [Import Date], BatchSummary.Filepath, (Select Count(*) from TempImages Where BatchID = BatchSummary.ID) as [Documents], CASE (Select Count(*) from TempImages Where BatchID = BatchSummary.ID and Recognised = 1) WHEN 0 THEN '0%' ELSE CAST(CAST((Select Count(*) from TempImages Where BatchID = BatchSummary.ID and Recognised = 1) AS FLOAT)/(Select Count(*) from TempImages Where BatchID = BatchSummary.ID)*100.0 AS VARCHAR) + '% (' + CAST((Select Count(*) from TempImages Where BatchID = BatchSummary.ID and Recognised = 1) AS VARCHAR) + ')' END AS [Recognised (%)], CASE (Select Count(*) from TempImages Where BatchID = BatchSummary.ID and Verified = 1) WHEN 0 THEN '0%' ELSE CAST(CAST((Select Count(*) from TempImages Where BatchID = BatchSummary.ID and Verified = 1) AS FLOAT)/(Select Count(*) from TempImages Where BatchID = BatchSummary.ID)*100.0 AS VARCHAR) + '% (' + CAST((Select Count(*) from TempImages Where BatchID = BatchSummary.ID and Verified = 1) AS VARCHAR) + ')' END AS [Verified (%)], Recognised, Verified, BatchSummary.Completed, BatchSummary.Locked, BatchSummary.LockedBy as [Locked By] FROM BatchSummary where BatchDate = '" & ConvertDate(Date.Now.Date) & "' ORDER BY BatchSummary.Completed, BatchSummary.ID Desc"
            openconnection()
            TodaygridAdapter.SelectCommand = Todaygridcommand
            TodaygridAdapter.Fill(thisds, "BatchSummary")
        Catch ex As Exception
            TodaygridAdapter.Dispose()
            MessageBox.Show("Error loading Todays batch Grid, Exeption Report - " & ex.ToString)
        Finally
            TodaygridAdapter.Dispose()
            Todaygridcommand.Dispose()
        End Try

        dgvBatches.Columns.Clear()

        dgvBatches.DataSource = thisds

        dgvBatches.DataMember = "BatchSummary"
        'Set Column Widths
        dgvBatches.Columns.Item(0).Visible = False
        dgvBatches.Columns.Item(1).Width = 200%
        dgvBatches.Columns.Item(2).Visible = False
        dgvBatches.Columns.Item(3).Width = 100%
        dgvBatches.Columns.Item(4).Width = 100%
        dgvBatches.Columns.Item(5).Visible = False
        dgvBatches.Columns.Item(6).Width = 90%
        dgvBatches.Columns.Item(7).Width = 110%
        dgvBatches.Columns.Item(8).Width = 110%
        dgvBatches.Columns.Item(9).Visible = False
        dgvBatches.Columns.Item(10).Visible = False
        dgvBatches.Columns.Item(11).Visible = False
        dgvBatches.Columns.Item(12).Visible = False
        dgvBatches.Columns.Item(13).Width = 150%
        Dim checkBoxCol As DataGridViewCheckBoxColumn = New DataGridViewCheckBoxColumn()
        checkBoxCol.TrueValue = 1
        checkBoxCol.FalseValue = 0
        checkBoxCol.HeaderText = "Completed"
        checkBoxCol.DataPropertyName = "Completed"
        dgvBatches.Columns.Insert(12, checkBoxCol)
        dgvBatches.Columns.Item(12).Width = 70%
        Dim ImageCol As DataGridViewImageColumn = New DataGridViewImageColumn
        ImageCol.Image = Image.FromFile(gstrProjectsFolder & "\Images\Opensml.png")
        ImageCol.HeaderText = "Locked"
        dgvBatches.Columns.Insert(13, ImageCol)
        dgvBatches.Columns.Item(13).Width = 70%
End Sub

========= Update Batch Document Count ================

   Private Sub UpdateBatchDocumentCount()
        Dim thiscommand As New SqlCommand
        Try
            thiscommand.Connection = gthisconn
            thiscommand.CommandText = "Update BatchSummary Set Documents = (Select Count(*) from TempImages Where BatchID = " & DBBatchAutoID & ") where ID = " & DBBatchAutoID
            thiscommand.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show("Error Updating Batch Document Count")
        Finally
            thiscommand.Dispose()
        End Try
        loadgridView() <--- If i remove this it runs fine and never causes an Error
    End Sub

Open in new window

0
käµfm³d 👽Commented:
Have you considered moving loadgridView() inside of the Try?
0
gleepyAuthor Commented:
Thanks for your help kaufmed

We finally resolved this by removing the dynamic count function.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.