Solved

Query on one form locks out records from another

Posted on 2008-06-23
12
199 Views
Last Modified: 2013-11-26
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
Comment
Question by:MrBullwinkle
  • 6
  • 6
12 Comments
 
LVL 4

Expert Comment

by:CSLEEDS
ID: 21852763
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
 
LVL 13

Author Comment

by:MrBullwinkle
ID: 21858009
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
 
LVL 4

Expert Comment

by:CSLEEDS
ID: 21861885
hi,

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


regards.
0
 
LVL 13

Author Comment

by:MrBullwinkle
ID: 21862422
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
 
LVL 4

Expert Comment

by:CSLEEDS
ID: 21862454
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
 
LVL 13

Author Comment

by:MrBullwinkle
ID: 21878281
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 4

Expert Comment

by:CSLEEDS
ID: 21880198
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
 
LVL 13

Author Comment

by:MrBullwinkle
ID: 21880253
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
 
LVL 4

Expert Comment

by:CSLEEDS
ID: 21880362
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
 
LVL 13

Author Comment

by:MrBullwinkle
ID: 21880396
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
 
LVL 4

Accepted Solution

by:
CSLEEDS earned 500 total points
ID: 21880423
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
 
LVL 13

Author Comment

by:MrBullwinkle
ID: 21995792
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now