Solved

Error in Windows 98 when large number of records in dataset

Posted on 2004-05-01
22
249 Views
Last Modified: 2010-04-24
Hello,
I am facing a problem in my application. This occurs when I synchronize data from one sql server to another via internet.

The problem is only faced on Windows 98, ME and some versions of XP.
It works perfectly on Windows 2000 computers.

The Error occurs on the statement myDataadapter.Fill(myDataset,"myDatatable")
Just for debugging i tried reducing the number of rows to be imported and the same codes ran fine but when the number of rows increased to 2000, (with almost 50 columns) then it gives the error.

what i am doing is when the user clicks on Synchronize button, it synchronizes with the remote server and my code run's like this

btnSynch.click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSynch.Click

conn.Open()
trans = conn.BeginTransaction()
Try
   table1()      ' Synchronizes table 1
   table2()      ' Synchronizes table 2
   table3()      ' Synchronizes table 3

 Catch oExcept As Exception
   trans.Rollback()
   MsgBox(oExcept.Message)
 End Try

and in these individual functions i have written all the codes for synchronization.
but in win98 & ME it is not even catching the error that has occured and gives Unhandled Exception message.
Shouldn't it catch the exception occured in the functions too ???

The Exception That I recieve is
"This SqlTransaction has completed; it is no longer usable"
Details :
System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
   at System.Data.SqlClient.SqlTransaction.Rollback()
   at MyProject.frmsynch.btnSynch_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.PerformClick()
   at System.Windows.Forms.Form.ProcessDialogKey(Keys keyData)
   at System.Windows.Forms.Control.ProcessDialogKey(Keys keyData)
   at System.Windows.Forms.Control.ProcessDialogKey(Keys keyData)
   at System.Windows.Forms.Control.PreProcessMessage(Message& msg)
   at System.Windows.Forms.ThreadContext.System.Windows.Forms.UnsafeNativeMethods+IMsoComponent.FPreTranslateMessage(MSG& msg)

And when i wrote the Try catch method in the table1() function, it gave me the Error
"General Network Error. Please check your Netwok documentation."

Please Help.

Thanking You all
and awaiting a prompt reply
@likheri
0
Comment
Question by:alikheri
  • 10
  • 9
22 Comments
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 10968220
of course it failed out.

It is dying on trans.Rollback() in your catch { } which is not protected with a try block.

the first exception that you are getting is a general network error (it had a problem with the SQLServer most likely returning large numbers of records it lost connection).

since it lost the connection the transaction is considerred invalid ...

when you try to RollBack the transaction the transaction is in an invalid state so it cant rollback.


from MSDN ...

Public Sub RunSqlTransaction(myConnString As String)
    Dim myConnection As New SqlConnection(myConnString)
    myConnection.Open()
   
    Dim myCommand As SqlCommand = myConnection.CreateCommand
    Dim myTrans As SqlTransaction
   
    ' Start a local transaction
    myTrans = myConnection.BeginTransaction("SampleTransaction")
    ' Must assign both transaction object and connection
    ' to Command object for a pending local transaction
    myCommand.Connection = myConnection
    myCommand.Transaction = myTrans
   
    Try
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
      myCommand.ExecuteNonQuery()
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"
      myCommand.ExecuteNonQuery()
      myTrans.Commit()
      Console.WriteLine("Both records are written to database.")
    Catch e As Exception
      Try
        myTrans.Rollback("SampleTransaction")
      Catch ex As SqlException
        If Not myTrans.Connection Is Nothing Then
          Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
                            " was encountered while attempting to roll back the transaction.")
        End If
      End Try
   
      Console.WriteLine("An exception of type " & e.GetType().ToString() & _
                      "was encountered while inserting the data.")
      Console.WriteLine("Neither record was written to database.")
    Finally
      myConnection.Close()
    End Try
End Sub 'RunSqlTransaction


I dont understand why they dont check Transaction.Connection is nothing first but perhaps its some weird timing issue ...


0
 

Author Comment

by:alikheri
ID: 10971168
Thanks for the reply,
but how do I eliminate the error that occurs due to a large number of records ?

Thanks
@likheri
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 10972191
Its probably the connection dying it being its over the internet ...

Ever download a 4 mb file and have it die ?

Its operating over a TCP socket ... if it loses connection its in the weeds.

Also are you opening your SQL server to the internet ?!

I'd break the operation down into smaller chunks.

Greg
0
 

Author Comment

by:alikheri
ID: 10972606
Thanks for the reply,
It's not the problem of the connection, because the connection is working fine on all systems running windows 2000 , but on Windows 98 and Me it's giving this problem.

Thanks
@likheri
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 10974895
it may be driver related on the systems however it is still something that is not necesarily within your control.

also connection failures happen ...

your code should handle it as above.

"The problem is only faced on Windows 98, ME and some versions of XP."

Is it possible that you have just not had a problem report in Win 2000 yet or that there is some other difference between the 98/me/xp users and your 2000 users ? i.e. business reasons why the 2000 users would not be issuing larger syncs or (in my opinion likely) that the 2000 users tend to be on higher speed/more reliable connections than the 98/ME people ?
0
 

Author Comment

by:alikheri
ID: 10975693
Hi Greg
The application that we are talking about is installed on around 15 different location within different environments.
So there isnt an issue of internet speed as such, coz a couple of machines getting this error are on broadband.
I tried the wrote the try inside the roll back, but it dint catch the exception and terminated with the unhandeled exception.
On windows 2000 it, rolls back without any problem. and without the internal try.
And when I had them install Windows 2000, it worked, even on a dialup connection!!!
I am having them synchronize completely the first time.
The database size increases from 1 mb to 23 mb with an increase rate of the database set to 10%.

Thanks
@likheri
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 10978771
"I tried the wrote the try inside the roll back, but it dint catch the exception and terminated with the unhandeled exception."

You are saying that even with a try catch wrapping the rollback() that you are getting an unhandled exception ?

What is the exception ?
0
 

Author Comment

by:alikheri
ID: 10983660
The Exception recieved is the same as before:
"This SqlTransaction has completed; it is no longer usable"
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 10983839
I have forced this behavior here and with a try .. catch around the rollback as in the example I do not get an unhandled exception. Could you place your code up ?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:alikheri
ID: 10996113
Hi Here is the Code.

  Private Sub btnSynch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSynch.Click
            conn.Open()
            trans = sconn.BeginTransaction()
            Try
                  table1()     ' Synchronizes table 1
              table2()     ' Synchronizes table 2
              table3()     ' Synchronizes table 3
            strans.Commit()
           
            Catch oExcept As Exception
                Try
                    trans.Rollback()
                    Me.ustatus.Text = "Synchronization Could not Complete."
                    MsgBox(oExcept.Message)
                Catch ex As SqlException
                    If Not trans.Connection Is Nothing Then
                        MsgBox("An exception of type " & ex.GetType().ToString() & "was encountered while attempting to roll back the transaction.")
                    End If
                End Try
            Finally
                conn.Close()
               
            End Try
    End Sub

Thanks
0
 

Author Comment

by:alikheri
ID: 11031073
Hello,
Lets leave the try catch thing, but the main problem in th end is , The error,
"General Network Error, Please check network Documentation."
This is the error which is coming when it fills the DataSet.
Please help Resolve this.

Thanking You
and Awating a prompt reply
@likheri
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11033286
occasionally TCP connections break. Especially when dealing with large sets of data. There is not much you can do about it. The problem may manifest itself more often on some machines perhaps due to drivers etc but this is not something you have control over .... if you do it 10 times does it sometimes succeed ?
0
 

Author Comment

by:alikheri
ID: 11038967
On Windows 98 and Me: Always fails
on Windows 2000: Never fails
on Windows XP: 50 - 50
:-)


0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11041124
but 98 and me succeed with a smaller number of rows ?

0
 

Author Comment

by:alikheri
ID: 11041866
Yes, I passed the query giving Top 100 and it worked fine. only when the size increases, it fails.
0
 

Author Comment

by:alikheri
ID: 11070440
Hello,
Any luck ? I am very much confused due to this.
Please help
Thanking You
@likheri
0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 11180930
downloading large amounts of data will occasionally cause the socket to break it may be more likely to happen in w98 or ME based on slightly different drivers / OS socket instability etc but you have to code to handle it.

btw: Bob I looked through my emails and never got one on 5/14 I have noticed this happens occasionally.
0
 

Author Comment

by:alikheri
ID: 11186235
Hi, Well am still facing the problem, but got something working, now the connections stays on for about 5 mins, and then gives the general network error message, and breaks,
Checked the Data Transfer, was above 3.5 mb.
The error was on the fill Dataset, but as before it gave the error in less than a minute, this time aroiund it is going up to around 5 mins, and then giving the error.

Awaiting reply

Thanking You
@likheri
0
 
LVL 37

Accepted Solution

by:
gregoryyoung earned 500 total points
ID: 11187639
Why dont you break up your transfers into smaller pieces ?
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

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

9 Experts available now in Live!

Get 1:1 Help Now