Timeout Expired Error

[maximc]

While executing the following statement, I get "Timeout Expired" error. The statement I am executing is:
DATable.Fill(DsTable, "MyTable")

The table has around 400,000 records. If I attempt to run the program again, it works fine without any problems. Sometimes on my third attempt, the program will run fine. Can someone help me as to how I can fix this problem? Thanks.

 

[kulrom]

on your 3rd attempt how much time it takes to run fine? Is it fast enough as you are satisifed of that or it is still slow but works anyway (you can live with that) ???

 

[maximc]

First off, I would like the program to go through in the very first attempt without popping up any error messages. When the program runs during the third attempt, it does not mean I am satisfied...I was just giving information as to why it fails the first time but runs later. With regard to time of execution, I haven't really noticed because the program fetches records from an AS/400 server and it takes a very long time anyway even when it works fine.

 

[kulrom]

it takes a very long time anyway even when it works fine

Means it doesn't work fine althrough you've said that (If I attempt to run the program again, it works fine without any problems).
Ok, what are you doing with this data in meantime. Are you populating some controls maybe? Post the code please!

 

[maximc]

When the control comes to the following routine, the system has already fetched records from AS/400 and placed it in a table called "TicketSales". Then this routine follows...

Private Sub cmdProcessTickets_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdProcessTickets.Click
Dim TotTickets, i, FoundRow, TotMSRec, TotSSRec, TotQSRec As Integer
Dim mAgentCode, mSector As String
Dim mSectorFrom, mSectorTo As String
Dim mClass, mRegionId, mAllSectorRegionId As Char
Dim mAmount As Double
Dim mRow As Data.DataRow
'Get the RegionId that includes All Sectors
DsRegionK1.Clear()
DARegion.Fill(DsRegionK1, "RegionMaster")
If DsRegionK1.Tables(0).Rows.Count > 0 Then
mAllSectorRegionId = DsRegionK1.Tables(0).Rows(0).Item("RegionId")
Else
mAllSectorRegionId = "#"
End If
'Read Ticket Sales
'Write to Monthly Sales, SectorSales, and QuarterSales
Try
DsTicketSales1.Clear()
DATicketSales.SelectCommand.Parameters("@YearNo").Value = MyCurrYear
DATicketSales.SelectCommand.Parameters("@MonthNo").Value = MyCurrMonth
DATicketSales.Fill(DsTicketSales1, "TicketSales")
TotTickets = DsTicketSales1.Tables(0).Rows.Count
Catch ex As Exception
MsgBox("Error: " + ex.Message)
End Try
For i = 0 To TotTickets - 1
......... lot of code here.........
Next i

The program fails at the code
DATicketSales.Fill(DsTicketSales1, "TicketSales")

The error message is,
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

 

[maximc]

Means it doesn't work fine althrough you've said that (If I attempt to run the program again, it works fine without any problems).
...

Well, I forgot to mention that I had no problems while the Table "TicketSales" had less data say upto 300,000 records. Once the table started growing, the problem started showing up. What do I mean by this is that every month, I load say around 4000 records. After it accumulates to greater than 300,000 records (not precisely, rough estimate) then the said error crops up.