?
Solved

data reader('invalid operation exception')

Posted on 2006-05-18
4
Medium Priority
?
485 Views
Last Modified: 2012-08-13
i am using Microsoft Visual Studio 2005 Beta 2 version. In my program i want to use a data reader DRR. i use the following code.
   Dim cmdSQL As New SqlCommand
        Try
            GetGConnection()  // function to get the connection
            cmdSQL.Connection = gGlobal
            cmdSQL.CommandText = strSQL
            DRR = cmdSQL.ExecuteReader()
//In the above DRR an 'invalid operation exception' occurs, when i try to execute the reader
            If DRR.Read Then
                GetSingleFieldLong = Val(DRR.Item(0) & "")
            Else
                GetSingleFieldLong = 0
            End If
            DRR.Close()
        Catch ex As Exception
            If DRR.IsClosed = False Then DRR.Close()
            Exit Function
        End Try

when i run this code database connection time out appears. pls solve my problem.
0
Comment
Question by:princyelias
  • 2
4 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 16716101
I don't know Microsoft Visual Studio 2005 Beta 2 version but in any earlier version you have - for a DataReader, unlike for a DataAdapter - explicitly to .Open the connection before calling .ExecuteReader().  Perhaps that is not the case in the version you are using, although the error message suggests to me that the problem is a connection one.

Roger
0
 
LVL 35

Expert Comment

by:YZlat
ID: 16717024
shouldn't it be:

gGlobal=GetGConnection()  // function to get the connection
cmdSQL.Connection = gGlobal


where do you set gGlobal object?
0
 

Author Comment

by:princyelias
ID: 16723340
gGlobal is declared as:
 Public gGlobal As New SqlConnection.

i will give the details of my datareader. I am using a datareader DRR.

I have a table 'sourcingcandidate' and I want to do updation in that table.

for that I use ' ValidCandidate' .

Dim ValidCandidate As Long
If the value of ValidCandidate=0, the updation will not take place.I use the following code

  Dim ValidCandidate As Long
  cmd.Connection = conGlobal
  cmd.CommandTimeout = 1
  Call GetConnection() /* function to get the connection
  Trans = conGlobal.BeginTransaction
  cmd.Transaction = Trans
  cmd.CommandType = Data.CommandType.Text
   ValidCandidate = 0
For i As Integer = 1 To cgdSourcingCandidate.Row
   sts = "select CandidateId from cmssourcingcandidate where CandidateId=" & Val(cgdSourcingCandidate.Item(i, 20))
       ValidCandidate = obj.GetSingleFieldLong(sts)
/* GetSingleFieldLong is a function used to read datareader
         If ValidCandidate <> 0 Then
        st = "update cmssourcingcandidate set SourcingDate=...."    
     cmd.CommandText = st
      cmd.ExecuteNonQuery()
         Next i
                Trans.Commit()
                CloseConnection()
End sub



 Public Function GetSingleFieldLong(ByVal strSQL) As Long
        Dim cmdSQL As New SqlCommand
        Dim lDr As SqlDataReader
        Try
            GetGConnection() /* function to get the connection
            cmdSQL.Connection = gGlobal
            cmdSQL.CommandText = strSQL
            cmdSQL.CommandTimeout = 2000
            lDr = cmdSQL.ExecuteReader()
            If Not lDr Is Nothing Then
                If lDr.HasRows Then
                    If lDr.Read Then
                        GetSingleFieldLong = Val(lDr.Item(0) & "")
                    Else
                        GetSingleFieldLong = 0
                    End If
                    If Not lDr Is Nothing Then
                        If lDr.IsClosed = False Then lDr.Close()
                    End If
                End If
            End If
        Catch ex As Exception
            If lDr.IsClosed = False Then lDr.Close()
        End Try
    End Function

when the value of i=1,it will work. next time after getting the connetion(connection has no problem) ,when it enters into
  lDr = cmdSQL.ExecuteReader()

it will stuck for some time. and database timeout exeption occurs.why the database time expired. Please help me. it is very urgent.


0
 
LVL 34

Accepted Solution

by:
Sancler earned 180 total points
ID: 16723962
I cannot test the code so these must be guesses.  

Looking at the differences between when GetSingleFieldLong is called the first time - that is, when i=1 - and when it is called the second time - that is, when i<>1 - there are (at least) two.

Connection.  As you do not show the code for the GetGConnection() sub I don't know quite how it works.  It seems (a) that it places the connection in the global variable gGlobal and (b) that it is in an .Open state.  I must assume that, before doing that, it .Closes any connection that is already open in gGlobal.  But, if it doesn't, I wonder if that might be part of the problem - some conflict between an existing and a new connection.  And I do wonder, anyway, why - given that this same connection will be needed on every call to GetSingleFieldLong, and it is in a global variable - the GetGConnection() sub is not called once before

        For i As Integer = 1 To cgdSourcingCandidate.Row

or even why the other connection - conGlobal - which I assume is the same and is also open - is not used in GetSingleFieldLong as well.

Transaction.  A Transaction is begun on database table cmssourcingcandidate before the first iteration through the block of code beginning with the line just quoted and not committed until all the iterations are completed.  The first time GetSingleFieldLong is called that Transaction has involved no changes to the table.  But by the time the second iteration occurs, the first iteration has passed through these lines

            If ValidCandidate <> 0 Then
                st = "update cmssourcingcandidate set SourcingDate=...."
                cmd.CommandText = st
                cmd.ExecuteNonQuery()

(from which, by the way, an End If is missing, but I assume that is just a typo in your posting).  So the table - cmssourcingcandidate - to which the datareader is directed after any call but the first is in a changed, but uncommitted, state.  I have seldom worked with Transactions on SQL - I mostly use OleDb - but I wonder if that could be causing (or at least contributing to) the problem?

Roger
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

862 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