Query on one form locks out records from another

I have an initial form that has a datagridview which should be readonly. Double clicking an item opens a new form which displays more data and allows editing.

However, the query to get some of the data fails to return any records. But if I remove the code that populated the DGV on the first form the records on the 2nd will query correctly.

Is there anyway to set datatables/datasets to readonly, or at least locktype to optimistic like adodb has?
LVL 13
RyanProject Engineer, ElectricalAsked:
Who is Participating?
CSLEEDSConnect With a Mentor Commented:
what i suggest is create ur own class that contains of multiple function to work with ur db. if it is a select query then what u need is return a datatable, or if u r perform update or insert, then return the integer which represent the affected rows. n remember to include the try block as i shown previously so that all the adapter or reader u r using are disposed peoperly.

good luck.
can you show the code here? it possibly cause by the data reader or connection that u have opened previously and still remains open when u come to the next execution. try close or dispose all the connection, datareader, etc right after use.

RyanProject Engineer, ElectricalAuthor Commented:
appVars is a global module I made which contains a connection that is opened on application start and closed on application end.

I use it in all my database calls.

The view (qryUnfinishedPOs) used a bunch of tables. One of those tables I attempt to read from on the next form (this first form never closes, the 2nd form is modal)

I know the 2nd forms code is not ideal, since I'm only getting 1 result, I could use an executeQuery, but it doesn't work either way. The variable iPO is set on form open. I know its working.

This 2nd set of code works if the DGV from the first is not used.
'--==Offending code on first form.==--'
'Populate List'
        Dim strSQL As String = "SELECT * FROM dbo.qryUnfinishedPOs"
        Dim comm As SqlCommand = New SqlCommand(strSQL, appVars.getCnn)
        Dim da As SqlDataAdapter = New SqlDataAdapter(comm)
        Dim dt As DataTable = New DataTable()
        With dgvSelect
            .DataSource = dt
            'format list
            .Columns("!").Width = 30
            .Columns("PO").Width = 60
            .Columns("Qty").Width = 35
            .Columns("Sets").Width = 35
            .Columns("Size").Width = 100
            .Columns("MatNo").Width = 80
            .Columns("Batch").Width = 40
            .Columns("Wt").Width = 40
            .Columns("formby").Width = 70
        End With
'--==Failing code on modal form==--'
cmd = New SqlClient.SqlCommand("SELECT MAX(BlockID) AS maxID from tblCodesOfPO where (PO =" & _
                                            iPO & ")", appVars.getCnn)
            da = New SqlClient.SqlDataAdapter(cmd)
            Dim tblTicketsHighDataTable As New DataTable

Open in new window

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.


try dispose the command and data adapter within your populate list block before u continue to the code in modal form.

RyanProject Engineer, ElectricalAuthor Commented:
I think that is the problem. I think its locking up when I debug and exit (my connection dispose is on application.close event- which I don't think fires when debug exits).

So disposing the dataadapter and command will remedy that hung connection?
disposing the data adapter and command possibly will address the problem, if problem still persist then u may need to open a connection to the db locally whenever u need to access the db instead of using a single connection through out your application.

good luck!
RyanProject Engineer, ElectricalAuthor Commented:
disposing did seem to fix the problem, but if the program crashes between creation and disposal, it locks out the records.

This will be a very serious problem if it occurs on the real server, as restarting is really not an option during operational hours.

Is there a way to dispose everything, even if an error occurs?
then try to put the creation and disposal of ur connection within a try block, then in the finally try somting like this:

'open ur conn
catch ex as exception
'catch any exception that occurs, or u may create ur own class for handling the exception
if conn.open then conn.close
end try

this help to ensure the creation or disposal of conn, good luck!

RyanProject Engineer, ElectricalAuthor Commented:
This connection is going to be used over and over, so I had planned to put it in a module and keep it open. Is the problem with the connection being open or with the dataadapter? Given that it only locks out records of a specific query I'm thinking its either the Datadaapter or datatable.
the problem comes from the datareader or dataadapter that u r using which currently associated with the specific connection therefore you have to make sure that all these component that u are using are disposed properly in the later stage so that your application can execute multiple query within the same block. another consideration is the connection time out where some query required a open and available connection, therefore u should expect a block in your module to handle this.
RyanProject Engineer, ElectricalAuthor Commented:
I'm finding documentation on these subtleties a little limited. Until I learn more I'm just going to create a function that takes a SQL string and returns a datatable. Everything is opened, used and disposed immediately.

I'll update with a update queries in a similar fashion.
RyanProject Engineer, ElectricalAuthor Commented:
This answer likely would work. My real problem was it wasn't the connection at all. I had data missing from my tables that didn't import for other reasons.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.