Long Updating

Updating from a text file

Page 1 of 1

2 Replies - 1774 Views - Last Post: 30 January 2005 - 08:09 AM Rate Topic: -----

#1 NuLLiFy  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 39
  • Joined: 25-March 03

Long Updating

Posted 21 August 2003 - 06:46 PM

I am doing a project. I must read from a text file and update it into the database. The text file is large. The average contain about 30,000 lines. The size is about 3Mb. Once I read the contents of the text file, I add it into the listbox to display it to the user. Then when the user clicks the Add button, the adding into the database process starts.
The problem is, it takes about 20-30 mins to finish the whole process. It will be even worse if there are lots of records in the database. I know my code is not efficient. I need your help to save time in the adding process. These are the codes for adding process:

'Break up each line, check them to see if they are unique and then save them into the database.
If lstBox.ListCount > 0 Then
For intCount = 0 To lstBox.ListCount - 1
Record_Serial_Number = Mid(lstBox.List(intCount), 1, 5) 'this is the unique number.
Shore_Station_Identification = Mid(lstBox.List(intCount), 6, 3)
Start_Date = Mid(lstBox.List(intCount), 9, 6)
Call_Direction_Code = Mid(lstBox.List(intCount), 33, 1)
Satellite_Link_Holding_Time = Mid(lstBox.List(intCount), 87, 5)
Chargeable_Duration = Mid(lstBox.List(intCount), 92, 5)
Assigned_Type_Of_Service = Mid(lstBox.List(intCount), 107, 2)

If rst.RecordCount > 0 Then
rst.MoveFirst
rst.Find "Record_Serial_Number=" & Record_Serial_Number 'To see if the new serial no. exists in the database.
intAbsolutePosition = rst.AbsolutePosition 'If the serial no. is found in the database, then intAbsolutePosition will be positive.
End If

If intAbsolutePosition < 0 Then 'if the serial number is unique.
With rst
.AddNew
.Fields("Record_Serial_Number") = Record_Serial_Number
.Fields("Shore_Station_Identification") = Shore_Station_Identification
.Fields("Start_Date") = Start_Date
.Fields("Call_Direction_Code") = Call_Direction_Code
.Fields("Satellite_Link_Holding_Time") = Satellite_Link_Holding_Time
.Fields("Chargeable_Duration") = Chargeable_Duration
.Fields("Assigned_Type_Of_Service") = Assigned_Type_Of_Service
.Update
End With

Else
If lstDuplicateIds.ListCount = 0 Then
lstDuplicateIds.AddItem "The following records were not added because they already exist."
End If

lstDuplicateIds.AddItem Record_Serial_Number

End If
next
End If

-----------------------------------------------------------------------------------------
'As for my database connection, I use the recordset. It's Cursorlocation is adUseClient,
CursorType is adOpenDynamic and LockType is adLockOptimistic.

May I know what the problem is?

Is This A Good Question/Topic? 0
  • +

Replies To: Long Updating

#2 born2c0de  Icon User is offline

  • printf("I'm a %XR",195936478);
  • member icon

Reputation: 180
  • View blog
  • Posts: 4,667
  • Joined: 26-November 04

Re: Long Updating

Posted 30 January 2005 - 04:44 AM

it's really irritating to read the code as the code in each line is extended onto 3 or 4 Lines....Try uploading the form that does all the transfer from the Text File to the Database....Dont send the TEXT File or the whole project...I just want to read the code and see if yur code can be improved.
Was This Post Helpful? 0
  • +
  • -

#3 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Long Updating

Posted 30 January 2005 - 08:09 AM

If you copy and paste the code into your IDE of choice (or file editor), the line breaks should disapear. He may not be able to upload the text file, I haven't seen NuLLiFy around for a while (see post date).
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1