Hello guys..

I'm new here and hopefully someone will help me to solve this problem, i create one program which read data from MYSQL database and write it into text file, the problem is that it works fine for certain record, but for some record, it will write the same record repeatedly(loop forever). For u'r info i have hard coded the name and the length of each field after i failed to code how to get the size of each field and size of remaining field size after it was fill up by data. The remarks@comment sign is my previous code. I really hope that someone can help me to correct this code, or if someone has other solution on how to write data form database into text file, please let me know..

'---------CODE START---------------------
Public Function StartExport() As Boolean
On Error Resume Next

Dim tRs As ADODB.Recordset
Dim tRecCtr, tFldCount, tRecordCount, tCtr, tProgress As Integer
Dim tStr, jenny, strContents, Filename As String
Dim tHashTotal As Integer
Dim temp

If icbalib.CheckSecurity(Me) = False Then
StartExport = False
Exit Function
End If
Screen.MousePointer = 11

Set tRs = New ADODB.Recordset
tRs.CursorLocation = adUseClient
tRs.CursorType = adOpenStatic
tRs.LockType = adLockPessimistic
tRs.Source = "SELECT * FROM TRANS WHERE UPDHOST<>'Y'"
tRs.ActiveConnection = mylib.ADO_Connection
tRs.Open

If tRs.RecordCount = 0 Then
MsgBox "No Record found", vbCritical, App.Title
Screen.MousePointer = 0
tRs.Close
StartExport = False
Exit Function
End If

tStr = ""
Filename = Trim(Me.txtFilename.Text)
tRecCtr = tRs.RecordCount
tHashTotal = 0
Me.ProgressBar1.Value = 0
Me.ProgressBar1.Max = tRecCtr
tProgress = tRecCtr / 100
If tProgress < 1 Then
tProgress = 1
End If

tFldCount = tRs.Fields.Count
tRecordCount = 0

MsgBox tFldCount & " " & tRecCtr

Open Filename For Output As #1
tRs.MoveFirst

'For tCtr = 0 To tFldCount - 1
' tStr = tStr & Trim(tRs.Fields(tCtr).Name) & String(tRs.Fields(tCtr).DefinedSize, " ") & Chr(9)
'Next tCtr
'
'Print #1, tStr
tStr = ""
'While tRs.EOF = False

For tRecordCount = 0 To tRecCtr - 1

For tCtr = 0 To tFldCount - 1

'Characters
Select Case tRs.Fields(tCtr).Type
'VARCHAR, CHAR
Case adVarChar, adChar
'MsgBox "VARCHAR" & tRs.Fields(tCtr) DefinedSize & " " & tRs.Fields(tCtr).ActualSize, vbOKOnly


tStr = tStr & Trim(tRs.Fields(tCtr).Value) & String(tRs.Fields(tCtr).DefinedSize - tRs.Fields(tCtr).ActualSize, " ") '& Chr(9)

'INTEGERS
'cannot use DefineDSize and ActualSize for integer, boolean datatype
'if someone alter TRANS table, this area of code must me updated as well.

Case adInteger, adUnsignedInt

'MsgBox "INTEGER" & tRs.Fields(tCtr).DefinedSize & " " & tRs.Fields(tCtr).ActualSize, vbOKOnly
'tStr = tStr & Trim(Str(tRs.Fields(tCtr).Value)) & String(tRs.Fields(tCtr).DefinedSize - tRs.Fields(tCtr).ActualSize, " ") '& Chr(9)
Select Case tRs.Fields(tCtr).Name

'SIZE EQUAL TO 6
Case "SEQNO", "ACNO", "ACNO2", "SEQCNT", "REFSEQNO"
tStr = tStr & Right(Space(6) & Trim(Str(tRs.Fields(tCtr).Value)), 6)


'SIZE EQUAL TO 2
Case "CHKDGT", "FLTDAY", "CHKDGT2"
tStr = tStr & Right(Space(2) & Trim(Str(tRs.Fields(tCtr).Value)), 2)

'SIZE EQUAL TO 3
Case "TENOR"
tStr = tStr & Right(Space(3) & Trim(Str(tRs.Fields(tCtr).Value)), 3)

'SIZE EQUAL TO 4
Case "YEAR"
tStr = tStr & Right(Space(4) & Trim(Str(tRs.Fields(tCtr).Value)), 4)

Case Else
MsgBox "System didn't recognize this field!! " & tRs.Fields(tCtr).Name & ".. Please contact administrator", vbCritical, App.Title
'tStr = tStr & Right(Space(tRs.Fields(tCtr).DefinedSize) & Trim(tRs.Fields(tCtr).Value), tRs.Fields(tCtr).Properties.Count)

End Select

'FLOAT
Case adSingle, adDouble
'If tRs.Fields(tCtr).Name = "TRNAMT" Then
'tStr = tStr & Trim(Str(tRs.Fields(tCtr).Value)) & String(16 - Len(Trim(Str(tRs.Fields(tCtr).Value))), " ") '& Chr(9)

'tHashTotal = tHashTotal + ComputeHash(tRs.Fields(tCtr).Value)
'Else
'tStr = tStr & Trim(Str(tRs.Fields(tCtr).Value)) & String(tRs.Fields(tCtr).DefinedSize - tRs.Fields(tCtr).ActualSize, " ") '& Chr(9)

'End If

Select Case tRs.Fields(tCtr).Name

'SIZE EQUAL TO (16,2) 19
Case "TRNAMT"
tStr = tStr & Right(Space(19) & Trim(Str(tRs.Fields(tCtr).Value)), 19)

'SIZE EQUAL TO (12,2) 15
Case "COMAMT", "CLRCOM"
tStr = tStr & Right(Space(15) & Trim(Str(tRs.Fields(tCtr).Value)), 15)

'SIZE EQUAL TO (10,2) 13
Case "CHARGES"
tStr = tStr & Right(Space(13) & Trim(Str(tRs.Fields(tCtr).Value)), 13)

Case Else
MsgBox "System didn't recognize this field!! " & tRs.Fields(tCtr).Name & " Please contact administrator", vbCritical, App.Title
End Select

'DATE
Case adDBDate, adDBTimeStamp

Select Case tRs.Fields(tCtr).Name

'SIZE EQUAL TO DATE 10
Case "UPDDT", "TRNDT"

temp = Format(Trim(Str(tRs.Fields(tCtr).Value)), "dd/mm/yyyy")

'tStr = tStr & Right(Space(10) & "0" & Trim(Str(tRs.Fields(tCtr).Value)), 10)
tStr = tStr & Right(Space(10) & temp, 10)

'SIZE EQUAL TO TIMESTAMP 19
Case "COMAMT", "TRNTIME"

temp = Format(Trim(Str(tRs.Fields(tCtr).Value)), "dd/mm/yyyy hh:mm:ss")
tStr = tStr & Right(Space(19) & temp, 19)

Case Else
MsgBox "System didn't recognize this field!! " & tRs.Fields(tCtr).Name & " Please contact administrator", vbCritical, App.Title
End Select

Case Else

MsgBox "Invalid type", vbCritical, App.Title
End Select

Next tCtr

Print #1, tStr
tRs!UPDHOST = "Y"
tRs!UPDDT = icbalib.cpTRNDT
tRs!UPDID = icbalib.cpTLRID
tRs.Update

If Me.ProgressBar1.Value < Me.ProgressBar1.Max Then
Me.ProgressBar1.Value = Me.ProgressBar1.Value + tProgress
End If

tStr = ""
If tRs.EOF = False Then
tRs.MoveNext
End If

Next tRecordCount
'Wend

Print #1, Trim(Str(tHashTotal))
Close #1
Screen.MousePointer = 0
MsgBox Trim(Str(tRecCtr)) & " records exported", vbInformation, App.Title

If Err Then
MsgBox Error, 48, App.Title
StartExport = False
Exit Function
End If
StartExport = True
End Function