TXT文件和sql server中数据比较

[leonnet]

是一个银行转帐报盘程序，打开一个TXT文件根据条件生成相应格式的TXT，现在想加入先和数据库中某个字段比较，如果数据库中有这个卡号就不生成到TXT中。
每天的TXT文件内容都不一样，如文件内容为：
GCCD 006CBC62 3818529980130070789 520005992008090900000000000147400
GCCD 006CBC62 6227003813030099382 520005822008090900000000000063000
GCCD 006CBC62 5240945023823011 520005402008090900000000000552000
GCCD 006CBC62 6227003811550123095 520004072008090900000000000062100
....
sql server信息：
服务器：gcshuju
库：gcagy
表：cbcacc 字段：code(卡),name（姓名）,date（时间）
ID 和 密码都是 SA

原程序如下：Public Sub DLO2BANK(StrSrcfile As String)
Dim blexist As Boolean
strline = ""
Dim stragt As String
Dim strBank As String
Dim strAccount As String
Dim strPolNum As String
Dim strduedate As String
Dim strAmount As String
Dim strcltname As String
Dim strfileexist(36) As String
Dim Caseamount As Currency
Dim strOutFile As String
Dim fileindex As Integer
Dim strInsured As String
Dim strInsured1 As String

Dim i As Integer
i = 0
Dim stragtrec As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Dim cobj As gc.UtilClass
Set cobj = New gc.UtilClass
Dim SQLKey() As String
SQLKey = cobj.GetPW("Conn400")
Dim constring As String
Dim constringSql As String
Dim stricbc As String
Dim strboc As String
Dim StrCBCAccountNo As String
Dim cnsql As ADODB.Connection
Dim rsba1 As ADODB.Recordset
Dim rsba2 As ADODB.Recordset
Dim rsba3 As ADODB.Recordset
Dim rsba4 As ADODB.Recordset
Dim strcampaign As String
Dim strotlbank As String
constring = "DSN=as400;UID=" & SQLKey(0) & ";PWD=" & SQLKey(1)
Dim SqlServerKey() As String
SqlServerKey = cobj.GetPW("ConnSQL")
constringSql = "DSN=GCIT;UID=" & SqlServerKey(0) & ";pwd=" & SqlServerKey(1) & ";database=gcit"
Set cobj = Nothing
cn.ConnectionString = constring
cn.Open
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn

Set rsba3 = New ADODB.Recordset
rsba3.ActiveConnection = constring
.....
blexist = False
Dim blexist_ccb_account As Boolean
Dim blexist_ccb_card As Boolean
blexist_ccb_account = False
blexist_ccb_card = False
Open StrSrcfile For Input As #1
Do Until EOF(1)
Line Input #1, strline
strBank = MidX(strline, 11, 10)
If MidX(strBank, 4, 3) = "CBC" Then
StrCBCAccountNo = MidX(strline, 31, 19)
If MidX(StrCBCAccountNo, 1, 4) = "4367"
Open "S2CCB_LOGIN" & CStr(Year(Date) * 10000 + Month(Date) * 100 + Day(Date)) & ".TXT" For Append As #4
strAccount = MidX(strline, 31, 19)
strPolNum = MidX(strline, 51, 8)
strduedate = MidX(strline, 59, 8)
strAmount = Format(MidX(strline, 67, 17) / 100, "####0.00")
rs.Source = "select bankaccdsc from clbl where bankacckey = '" & strAccount & "'"
rs.Open
Set cnsql = New ADODB.Connection
cnsql.Open constringSql
Print #4, Trim(strAccount) & "|" & Trim(rs.Fields("bankaccdsc")) & "|"
...

万分感谢

[leonnet]

我加了下边的一段代码，但没有做任何比较，TXT里有多少数据就直接输出了，不能判断是否存在，请高人指点

Dim conn11 As New Connection
conn11.Open "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=gcscagy;Data Source=10.0.0.1"
Dim rs11 As New Recordset
rs11.Open = "select code from cbcacc where code = '" & strAccount & "'"

[jhone99]

假设用变量strC比较字段dataC
Dim conn11 As New Connection
conn11.Open "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=gcscagy;Data Source=10.0.0.1"
Dim rs11 As New Recordset
rs11.Open = "select code from cbcacc where code = '" & strAccount & "' and datac ='" & strc & "' "

if rs11.recodcount>0 then
'不写入txt
else
'写入txt
end if

[zhufenghappy]

看来楼主用的是oracle了。

[leonnet]

求高人指点

[Tiger_Zhao]

RecordSet 根本与 Connection 没有关联，怎么取数?

Dim conn11 As New Connection 
conn11.Open "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=gcscagy;Data Source=10.0.0.1" 
Dim rs11 As New Recordset
'RecordCount 的正确性依赖与游标类型，而用 Count(*) 可以始终正确
rs11.Open "select Count(*) from cbcacc where code = '" & strAccount & "' and datac ='" & strc & "' ", conn11

if rs11(0).Value >0 then 
    '不写入txt 
else 
    '写入txt 
end if