• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

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?
0
Ryan
Asked:
Ryan
  • 6
  • 6
1 Solution
 
CSLEEDSCommented:
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.

regards.
0
 
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()
        da.Fill(dt)
 
        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
            da.Fill(tblTicketsHighDataTable)

Open in new window

0
 
CSLEEDSCommented:
hi,

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


regards.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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?
0
 
CSLEEDSCommented:
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!
0
 
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?
0
 
CSLEEDSCommented:
then try to put the creation and disposal of ur connection within a try block, then in the finally try somting like this:

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

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

regards.
0
 
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.
0
 
CSLEEDSCommented:
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.
0
 
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.
0
 
CSLEEDSCommented:
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.
0
 
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now