?
Solved

Query on one form locks out records from another

Posted on 2008-06-23
12
Medium Priority
?
205 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:Ryan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Ryan
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 13

Author Comment

by:Ryan
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:Ryan
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
 
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:Ryan
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:Ryan
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 2000 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:Ryan
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

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

801 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