Error in Windows 98 when large number of records in dataset

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
alikheriAsked:
Who is Participating?
 
gregoryyoungConnect With a Mentor Commented:
Why dont you break up your transfers into smaller pieces ?
0
 
gregoryyoungCommented:
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
 
alikheriAuthor Commented:
Thanks for the reply,
but how do I eliminate the error that occurs due to a large number of records ?

Thanks
@likheri
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
gregoryyoungCommented:
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
 
alikheriAuthor Commented:
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
 
gregoryyoungCommented:
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
 
alikheriAuthor Commented:
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
 
gregoryyoungCommented:
"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
 
alikheriAuthor Commented:
The Exception recieved is the same as before:
"This SqlTransaction has completed; it is no longer usable"
0
 
gregoryyoungCommented:
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
 
alikheriAuthor Commented:
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
 
alikheriAuthor Commented:
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
 
gregoryyoungCommented:
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
 
alikheriAuthor Commented:
On Windows 98 and Me: Always fails
on Windows 2000: Never fails
on Windows XP: 50 - 50
:-)


0
 
gregoryyoungCommented:
but 98 and me succeed with a smaller number of rows ?

0
 
alikheriAuthor Commented:
Yes, I passed the query giving Top 100 and it worked fine. only when the size increases, it fails.
0
 
alikheriAuthor Commented:
Hello,
Any luck ? I am very much confused due to this.
Please help
Thanking You
@likheri
0
 
gregoryyoungCommented:
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
 
alikheriAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.