i want do the do while process

[hikinchi]

i have problem to connect vb and mysql...
1) i want to build program to compare data from text file and from mysql. I don't know how to use command from vb to through into mysql..

anyone can help me...

2) my data output always duplicate. I think my command do while have a problem...

this is my script...

Private Sub cmdBaca_Click()
On Error Resume Next
Dim filesys, text, readfile, contents, a, b, c, d, e, f, h, i, j, k, memberinfo, status, transactionid
Dim recordtype, conn, rspage1, sql, db2ConnectStrin g, DBConn, transactiondb, Record_Type, BBMC_Batch_Numb er, Hold_Day_count
Dim Security_code, t1, t2, t3, t4, Amount, Transaction_Dat e, Pay_In_Slip_No, Payment_Mode, name, lenaddress1, COUNTRY_CODE, Tahun, Inst_No
Dim tu As String, txtfile As String
Dim bilhead As Integer, slipno As String, trkhlulus As String
Dim m As Integer, l As Integer, ctr As Integer
Dim cnDB As New ADODB.Connectio n 'DAO.Database
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim rs3 As New ADODB.Recordset
Dim rs4 As New ADODB.Recordset
Dim qr As String
Dim tslip As String
Dim mystring As String
Dim sqlquery As String
Dim pay As String
Dim ada As Integer


txtfile = FreeFile


CommonDialog1.S howOpen
If CommonDialog1.F ileName <> "" Then
Open CommonDialog1.F ileName For Input As #txtfile

'i = 0

l = 0
ctr = 0
'z = txtfile
Do While Not EOF(txtfile) 'read line from input file
Line Input #txtfile, tu



'baca Detail
m = Mid(tu, 1, 1)
'm = 1
If m = 1 Then
slipno = Mid(tu, 31, 12)
trkhlulus = Mid(tu, 2, 6)
ctr = ctr + 1

MsgBox slipno

Else
GoTo seterusnya

''Call takJumpa

End If

Set FileSystemObjec t = CreateObject("S cripting.FileSy stemObject")


With cnDB
.ConnectionStri ng = "DRIVER={My SQL ODBC 3.51 Driver};" _
& "SERVER=192.168 .240.27;" _
& "DATABASE=TAXIN FO;" _
& "UID=fpx;" _
& "PWD=fpx;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384 '

'1 - Client Can't handle the real column width being returned.
'2 - Have MySQL return found rows value.
'8 - Allow Big Values: We want the server to send out the BLOB values to our VB client (large packets), so this option must be set.
'32 - Toggle Dynamic cursor support.
'16384 - Convert LongLong to Int: This just helps makes sure that large numeric results get properly interpreted.


.CommandTimeout = 20
.CursorLocation = adUseClient
.Open
End With

Set rs = cnDB.Execute("S elect * From berjaya Where Pay_In_Slip_No = '" & slipno & "'")
'rs.Open "Select * From berjaya Where Pay_In_Slip_No = '" & slipno & "'", adOpenStatic


Set rs1 = cnDB.Execute("S elect * From gagal Where Pay_In_Slip_No = '" & slipno & "'")



Pay_In_Slip_No = rs(0)
'ada = Pay_In_Slip_No
'rs.Fields ("Pay_In_Slip_N o")
'Pay_In_Slip_No = rs.Fields(0).Va lue
'Pay_In_Slip_No = rs.Fields("Pay_ In_Slip_No").Va lue

If Pay_In_Slip_No = slipno Then
sqlquery = "Select * From berjaya Where Pay_In_Slip_No = '" & slipno & "'"
'Set rs = cnDB.Execute("S elect * From berjaya Where Pay_In_Slip_No = '" & slipno & "'")
End If

'Set rs1 = cnDB.Execute("S elect * From gagal Where Pay_In_Slip_No = '" & slipno & "'")

Pay_In_Slip_No = rs1(0)

'Pay_In_Slip_No = rs1.Fields("Pay _In_Slip_No").V alue

If Pay_In_Slip_No = slipno Then
sqlquery1 = "Select * From gagal Where Pay_In_Slip_No = '" & slipno & "'"
'Set rs = cnDB.Execute("S elect * From berjaya Where Pay_In_Slip_No = '" & slipno & "'")
End If


If rs("Pay_In_Slip _No") = Null Then
MsgBox "tiada data"
End If


Set rs = cnDB.Execute(sq lquery)
Set rs1 = cnDB.Execute(sq lquery1)



'Do While Not rs.EOF

'Pay_In_Slip_No = rs(0)

Set rs2 = cnDB.Execute("S elect * From detailrecord01 Where Pay_In_Slip_No = '" & slipno & "'")



Pay_In_Slip_No = rs2(6)

If Not rs2.EOF Then
'Do While Not rs2.EOF
'MsgBox "Rekod ADA"
Record_Type = rs2(0)
BBMC_Batch_Numb er = rs2(1)
Hold_Day_count = rs2(2)
Security_code = rs2(3)
Amount = rs2(4)
Transaction_Dat e = rs2(5)
t1 = Mid(Transaction _Date, 1, 4)
t2 = Mid(Transaction _Date, 5, 2)
t3 = Mid(Transaction _Date, 7, 2)
t4 = t3 & t2 & t1

Pay_In_Slip_No = rs2(6)
Payment_Mode = rs2(7)
Payment_Code = rs2(8)
Bank_Code = rs2(9)
It_Group = rs2(10)
Reference_No = rs2(11)
Check_Digit = rs2(12)
Wife_CD = rs2(13)
name = rs2(14)
ic = rs2(15)
Old_Ic_Number = rs2(16)
Address_Line1 = rs2(17)
Address_Line2 = rs2(18)
Address_Line3 = rs2(19)
Post_Code = rs2(20)
Passport_Number = rs2(21)
COUNTRY_CODE = rs2(22)
Tahun = rs2(23)
Inst_No = rs2(24)
assmno = rs2(25)
scheno = rs2(26)

rekod_data = rekod_data + 1


dayy = Mid(Transaction _Date, 7, 2)
bulann = Mid(Transaction _Date, 5, 2)

tahunn = Mid(Transaction _Date, 1, 4)




tarikhh = dayy & "/" & bulann & "/" & tahunn

lentahun = Len(Tahun)
jumlentahun = 4 - lentahun
If lentahun = 0 Then
jumlentahun = 4
End If

lenIt_Group = Len(It_Group)
jumlenIt_Group = 2 - lenIt_Group
If lenIt_Group = 0 Then
jumlenIt_Group = 2
End If

lenReference_No = Len(Reference_N o)
jumlenReference _No = 8 - lenReference_No
If lenReference_No = 0 Then
jumlenReference _No = 8
End If


lenWife_CD = Len(Wife_CD)
jumlenWife_CD = 1 - lenWife_CD
If lenWife_CD = 0 Then
jumlenWife_CD = 1
End If

lenname = Len(name)
jumlenname = 60 - lenname
If lenname = 0 Then
jumlenname = 60
End If

lenic = Len(ic)
jumlenic = 12 - lenic
If lenic = 0 Then
jumlenic = 12
End If
' Old_Ic_Number

lenOld_Ic_Numbe r = Len(Old_Ic_Numb er)
jumlenlenOld_Ic _Number = 8 - lenOld_Ic_Numbe r
If lenOld_Ic_Numbe r = 0 Then
jumlenlenOld_Ic _Number = 8
End If

lenAddress_Line 1 = Len(Address_Lin e1)
jumlenAddress_L ine1 = 40 - lenAddress_Line 1
If lenAddress_Line 1 = 0 Then
jumlenAddress_L ine1 = 40
End If

lenAddress_Line 2 = Len(Address_Lin e2)
jumlenAddress_L ine2 = 40 - lenAddress_Line 2
If lenAddress_Line 2 = 0 Then
jumlenAddress_L ine2 = 40
End If

lenAddress_Line 3 = Len(Address_Lin e3)
jumlenAddress_L ine3 = 40 - lenAddress_Line 3
If lenAddress_Line 3 = 0 Then
jumlenAddress_L ine3 = 40
End If

lenPost_Code = Len(Post_Code)
jumlenPost_Code = 10 - lenPost_Code
If lenPost_Code = 0 Then
jumlenPost_Code = 10
End If

lenPassport_Num ber = Len(Passport_Nu mber)
jumlenPassport_ Number = 12 - lenPassport_Num ber
If lenPassport_Num ber = 0 Then
jumlenPassport_ Number = 12
End If

leninst_No = Len(Inst_No)
jumleninst_No = 3 - leninst_No
If leninst_No = 0 Then
jumleninst_No = 3 + "0"
End If

lenassmno = Len(assmno)
jumlenassmno = 3 - lenassmno
If lenassmno = 0 Then
jumlenassmno = 3 + "0"
End If

lenscheno = Len(scheno)
jumlenscheno = 3 - lenscheno
If lenscheno = 0 Then
jumlenscheno = 3 + "0"
End If

lenAmount = Len(Amount)
jumlenAmount = 14 - lenAmount
' MsgBox Transaction_Dat e
ddDay = Mid(Transaction _Date, 7, 2)
ddMonth = Mid(Transaction _Date, 5, 2)
ddYear = Mid(Transaction _Date, 1, 4)
' MsgBox "year" & year
tarikh = ddDay + "/" + ddMonth + "/" + ddYear
'MsgBox tarikh
TotalAmount = TotalAmount + Amount
'MsgBox TotalAmount
End If
panjang = Len(Amount)
exeLocation = InStr(LCase(Amo unt), ".")

If exeLocation = 0 Then
' Error, not found. Complain/do something
prgpath2 = Amount
panjangprgpath1 = Len(prgpath2)
jumpanjangprgpa th2 = 12 - panjangprgpath1
prgpathh1 = "00"
Else
prgPath = Left(theString, exeLocation) ' to get the . part
jumpanjang = panjang - exeLocation
prgpath1 = Right(Amount, jumpanjang)
panjangprgpath1 = Len(prgpath1)

If panjangprgpath1 = 1 Then
prgpathh1 = prgpath1 + "0"
Else
prgpathh1 = prgpath1
End If



prgpath2 = Left(Amount, exeLocation - 1)
panjangprgpath2 = Len(prgpath2)
jumpanjangprgpa th2 = 12 - panjangprgpath2
End If

If jumpanjangprgpa th2 = 1 Then
jumspaces = "0"
End If

If jumpanjangprgpa th2 = 2 Then
jumspaces = "00"
End If

If jumpanjangprgpa th2 = 3 Then
jumspaces = "000"
End If

If jumpanjangprgpa th2 = 4 Then
jumspaces = "0000"
End If

If jumpanjangprgpa th2 = 5 Then
jumspaces = "00000"
End If

If jumpanjangprgpa th2 = 6 Then
jumspaces = "000000"
End If

If jumpanjangprgpa th2 = 7 Then
jumspaces = "0000000"
End If

If jumpanjangprgpa th2 = 8 Then
jumspaces = "00000000"
End If

If jumpanjangprgpa th2 = 9 Then
jumspaces = "000000000"
End If

If jumpanjangprgpa th2 = 10 Then
jumspaces = "0000000000 "
End If

If jumpanjangprgpa th2 = 11 Then
jumspaces = "0000000000 0"
End If

If jumpanjangprgpa th2 = 12 Then
jumspaces = "0000000000 00"
End If



WriteDetail = Record_Type & t4 & "01" & Hold_Day_count & Security_code & jumspaces & prgpath2 & prgpathh1 & tarikh & Pay_In_Slip_No & Payment_Mode & Payment_Code & _
Bank_Code & It_Group & Space(jumlenIt_ Group) & Reference_No & Space(jumlenRef erence_No) & Check_Digit & Wife_CD & Space(jumlenWif e_CD) & name & Space(jumlennam e) & ic & Space(jumlenic) & Old_Ic_Number & Space(jumlenlen Old_Ic_Number) & Address_Line1 & Space(jumlenAdd ress_Line1) & Address_Line2 & Space(jumlenAdd ress_Line2) & Address_Line3 & Space(jumlenAdd ress_Line3) & _
Post_Code & Space(jumlenPos t_Code) & Passport_Number & Space(jumlenPas sport_Number) & COUNTRY_CODE & Tahun & Inst_No & assmno & scheno
Data_RCVDT(reko d_data, 1) = WriteDetail
rs.MoveNext
m = MoveNext


' 'jayacnt = jayacnt + 1
' WriteDetail = Record_Type & t4 & "01" & Hold_Day_count & Security_code & jumspaces & prgpath2 & prgpathh1 & tarikh & Pay_In_Slip_No & Payment_Mode & Payment_Code & _
' Bank_Code & It_Group & Space(jumlenIt_ Group) & Reference_No & Space(jumlenRef erence_No) & Check_Digit & Wife_CD & Space(jumlenWif e_CD) & name & Space(jumlennam e) & ic & Space(jumlenic) & Old_Ic_Number & Space(jumlenlen Old_Ic_Number) & Address_Line1 & Space(jumlenAdd ress_Line1) & Address_Line2 & Space(jumlenAdd ress_Line2) & Address_Line3 & Space(jumlenAdd ress_Line3) & _
' Post_Code & Space(jumlenPos t_Code) & Passport_Number & Space(jumlenPas sport_Number) & COUNTRY_CODE & Tahun & Inst_No & assmno & scheno
' Data_RCVDT(reko d_data, 1) = WriteDetail
' 'Data_RCVDTJ(ja yacnt, 1) = writejaya(jayac nt)
' 'Writetext.writ eline
' 'End If
' 'End If



'Loop



'Loop
panjang = Len(TotalAmount )
exeLocation = InStr(LCase(Tot alAmount), ".")

If exeLocation = 0 Then
' Error, not found. Complain/do something
prgpath4 = TotalAmount
panjangprgpath2 = Len(prgpath4)
jumpanjangprgpa th2 = 12 - panjangprgpath2
prgpathh3 = "00"
Else
prgPath = Left(theString, exeLocation) ' to get the . part
jumpanjang = panjang - exeLocation
prgpath3 = Right(TotalAmou nt, jumpanjang)
panjangprgpath3 = Len(prgpath3)

If panjangprgpath3 = 1 Then
prgpathh3 = prgpath3 + "0"
Else
prgpathh3 = prgpath3
End If

prgpath4 = Left(TotalAmoun t, exeLocation - 1)
panjangprgpath2 = Len(prgpath4)
jumpanjangprgpa th2 = 12 - panjangprgpath2
End If

If jumpanjangprgpa th2 = 1 Then
jumspaces = "0"
End If

If jumpanjangprgpa th2 = 2 Then
jumspaces = "00"
End If

If jumpanjangprgpa th2 = 3 Then
jumspaces = "000"
End If

If jumpanjangprgpa th2 = 4 Then
jumspaces = "0000"
End If

If jumpanjangprgpa th2 = 5 Then
jumspaces = "00000"
End If

If jumpanjangprgpa th2 = 6 Then
jumspaces = "000000"
End If

If jumpanjangprgpa th2 = 7 Then
jumspaces = "0000000"
End If

If jumpanjangprgpa th2 = 8 Then
jumspaces = "00000000"
End If

If jumpanjangprgpa th2 = 9 Then
jumspaces = "000000000"
End If

If jumpanjangprgpa th2 = 10 Then
jumspaces = "0000000000 "
End If

If jumpanjangprgpa th2 = 11 Then
jumspaces = "0000000000 0"
End If

If jumpanjangprgpa th2 = 12 Then
jumspaces = "0000000000 00"
End If

Set Createtxt = FileSystemObjec t.CreateTextFil e(App.Path & "\Data\" & t1 + t2 + t3 & ".txt")
Createtxt.Close
Set Writetext = FileSystemObjec t.OpenTextFile( App.Path & "\Data\" & t1 + t2 + t3 & ".txt", 2)

'Header
WriteHeader = "H" & t4 & "01" & "0000200000 " & jumspaces & prgpath4 & prgpathh3
Writetext.write line WriteHeader



'Details
For X = 0 To rekod_data
Writetext.write line Data_RCVDT(X, 1)
Next

'MsgBox "rekod telah selesai dibaca"

'End Sub

seterusnya:
Call takJumpa

Loop
End If



End Sub

'Sub CreateOutput()
Sub takJumpa()
mystring = mystring & "EFL"
Print #1, mystring

'MsgBox "Rekod Telah Selesai Dibaca"
rs.Close
End Sub


Private Sub End_Click()
Unload Me
End Sub