I am trying to write a ASP page to enable staff to import a file of payment recipts into QB. The code below is only part completed , but i have already encountered a horrific speed/ time problem.
Basicly in the code below, I'm reading a text file and grabbing the invoice number then looking up the TxnID and CustomerRefListID form the InvoiceLine when the InvoiceLine.RefNumber matches the invoice number in the import file
from a btn click event on the asp page i run the following in vb
Try
' open connection to QuickBooks
cnQODBC = New System.Data.Odbc.OdbcConnection("DSN=QuickBooks Data")
cnQODBC.Open()
' Read 13 lines out the file
Try
For LineCounter = 1 To 13
InputFileLine = Sr.ReadLine
Next
Do
' First Record
InputFileLine = Sr.ReadLine
daQODBC = New System.Data.Odbc.OdbcDataAdapter("SELECT TxnID, CustomerRefListID From InvoiceLine where RefNumber = '" & Trim(Mid(InputFileLine, 43, 6)) & "'", cnQODBC)
dsQODBC = New System.Data.DataSet
daQODBC.Fill(dsQODBC, "TempTable")
strmsg = dsQODBC.Tables(0).Rows(1).Item(0).ToString
' Finished with dataset so dispose
daQODBC.Dispose()
dsQODBC.Dispose()
Loop Until InStr(1, InputFileLine, "Transaction Count") > 0
Catch ex As Exception
MsgBox("ex " & ex.Message)
End Try
Catch exConnect As Exception
' write error with opening
MsgBox("exConnect " & exConnect.Message)
Finally
' Clean up
Sr.Close()
Sr.Dispose()
cnQODBC.Close()
cnQODBC.Dispose()
End Try
I was expecting this to whip through fairly fast but so far it has taken over an hour. Every thing is running on a local dev machine. Optimizer is set on, Optimize data is set to 'The start of every new connection( with load updated data). As i stated before this percedure isnt complete as once the record has been located I will then do an update. I must be doing something wrong for this to be taking so long, has anyone any suggestions for the novice programmer to try and speed this up
thanks in advance
| 
