Do Until adoRecordset.EOF
..........
If Not (dwdm.EOF And dwdm.BOF) Then '单位代码是否已经存在
..............
else
If Not (dwmc.EOF And dwmc.BOF) Then '单位名称是否已经存在
..........
else
........
endif endif
Loop运行,不是 提示loop 缺少 do.就是 If Not (dwmc.EOF And dwmc.BOF) 取不出值
Do Until adoRecordset.EOF 里面不能写两个If Not ??多谢指点一二~~~
..........
If Not (dwdm.EOF And dwdm.BOF) Then '单位代码是否已经存在
..............
else
If Not (dwmc.EOF And dwmc.BOF) Then '单位名称是否已经存在
..........
else
........
endif endif
Loop运行,不是 提示loop 缺少 do.就是 If Not (dwmc.EOF And dwmc.BOF) 取不出值
Do Until adoRecordset.EOF 里面不能写两个If Not ??多谢指点一二~~~
endif在光标换行后会自动分开为“End If”,这个不必担心。TO:楼主
我看了下,你的这段代码结构没有什么问题。
建议看一下你的代码的上下文是否有什么问题!!
Text2.Text = ""
CB = 0
CG = 0
Dim adoConnection As New ADODB.Connection
Dim adoRecordset As New ADODB.Recordset
Dim objCat As Object
Dim tbl As Object
Dim sTableName As String
Dim adoRecordsetDM As New ADODB.Recordset
'OLE DB + ODBC Driver 方式:
'adoConnection.Open "Data Provider=MSDASQL.1;driver=Microsoft Excel Driver (*.xls);DBQ=e:\temp\book2.xls"
'Microsoft.Jet.OLEDB.4.0 方式,(建议)
adoConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & Text1.Text & ";Extended Properties='Excel 8.0;HDR=Yes'"
'取sheet的名称
Set objCat = CreateObject("ADOX.Catalog")
Set objCat.ActiveConnection = adoConnection
For Each tbl In objCat.Tables
sTableName = tbl.Name
Exit For
Next tbl
Set objCat = Nothing
'构造查询
adoRecordset.Open "select * from [" & sTableName & "]", adoConnection, adOpenKeyset, adLockOptimistic
'Debug.Print adoRecordset.RecordCount
Dim i As Integer
Do Until adoRecordset.EOF
conn_init
' For i = 0 To adoRecordset.Fields.Count - 1
'For i = 0 To adoRecordset.RecordCount - 1
Dim cmdDM As New ADODB.Command
Dim rsDM As New ADODB.Recordset
Set cmdDM.ActiveConnection = XZDconn
cmdDM.CommandText = sqlDM
Dim cmdJC As New ADODB.Command
Dim rsJC As New ADODB.Recordset
Set cmdJC.ActiveConnection = XZDconn
cmdJC.CommandText = SQLJC
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Set cmd.ActiveConnection = XZDconn
cmd.CommandText = Sql
'
'检查 该记录 单位代码 是否存在重复
SQLJC = "SELECT dwdm,dwmc from dwtx where dwdm='" & adoRecordset.Fields.Item(2).Value & "'and dqh='" & adoRecordset.Fields.Item(0).Value & "'"
rsJC.Open SQLJC, XZDconn
Set dwdm = XZDconn.Execute(SQLJC)
If Not (dwdm.EOF And dwdm.BOF) Then '如果存在重复记录 ,则 日志文件 记录,跳到第二条记录,继续导入
'result = MsgBox("该客户代码已存在!", , "系统提示")
Text2.Text = vbCrLf & "地区号:" & adoRecordset.Fields.Item(0).Value & "单位代码:" & adoRecordset.Fields.Item(2).Value & "单位名称:" & adoRecordset.Fields.Item(3).Value & " 该记录已经存在!" & vbCrLf & Text2.Text
CB = CB + 1
Status Bar1.Panels(3).Text = "单位代码已存在,失败导入:" & CB & "条"
Else
If Check1.Value = 0 Then '单位名称重复 --忽略
' 如果无重复记录,则直接导入记录.
Sql = "insert Into dwtx(dqh,dqmc,dwdm,dwmc,pydm_,addr,zp,tel,fax,frdb,nsrdjh,khh,zh)" + _
" values('" & adoRecordset.Fields.Item(0).Value & "','" & adoRecordset.Fields.Item(1).Value & "','" & adoRecordset.Fields.Item(2).Value & "','" & adoRecordset.Fields.Item(3).Value & "','" & adoRecordset.Fields.Item(4).Value & "','" & adoRecordset.Fields.Item(5).Value & "','" & adoRecordset.Fields.Item(6).Value & "','" & adoRecordset.Fields.Item(7).Value & "','" & adoRecordset.Fields.Item(8).Value & "','" & adoRecordset.Fields.Item(9).Value & "','" & adoRecordset.Fields.Item(10).Value & "','" & adoRecordset.Fields.Item(11).Value & "','" & adoRecordset.Fields.Item(12).Value & "')"
rs.Open Sql, XZDconn
Text2.Text = vbCrLf & "地区号:" & adoRecordset.Fields.Item(0).Value & "单位代码:" & adoRecordset.Fields.Item(2).Value & "单位名称:" & adoRecordset.Fields.Item(3).Value & " 该记录成功导入!" & vbCrLf & Text2.Text
CG = CG + 1
Else
sqlDM = "SELECT dwdm,dwmc from dwtx where dwdm='" & adoRecordset.Fields.Item(2).Value & "'and dwmc='" & adoRecordset.Fields.Item(3).Value & "'"
rsDM.Open sqlDM, XZDconn
Set dwmc = XZDconn.Execute(sqlDM)
'Set dwmc = XZDconn.Execute(SQLJC)
If Not (dwmc.EOF And dwmc.BOF) Then '如果存在重复记录 ,则 日志文件 记录,跳到第二条记录,继续导入
Text2.Text = vbCrLf & "地区号:" & adoRecordset.Fields.Item(0).Value & "单位代码:" & adoRecordset.Fields.Item(2).Value & "单位名称:" & adoRecordset.Fields.Item(3).Value & " 该记录已经存在!" & vbCrLf & Text2.Text
DM = DM + 1
Status Bar1.Panels(4).Text = "单位名称重复,失败导入:" & DM & "条"
Else
Sql = "insert Into dwtx(dqh,dqmc,dwdm,dwmc,pydm_,addr,zp,tel,fax,frdb,nsrdjh,khh,zh)" + _
" values('" & adoRecordset.Fields.Item(0).Value & "','" & adoRecordset.Fields.Item(1).Value & "','" & adoRecordset.Fields.Item(2).Value & "','" & adoRecordset.Fields.Item(3).Value & "','" & adoRecordset.Fields.Item(4).Value & "','" & adoRecordset.Fields.Item(5).Value & "','" & adoRecordset.Fields.Item(6).Value & "','" & adoRecordset.Fields.Item(7).Value & "','" & adoRecordset.Fields.Item(8).Value & "','" & adoRecordset.Fields.Item(9).Value & "','" & adoRecordset.Fields.Item(10).Value & "','" & adoRecordset.Fields.Item(11).Value & "','" & adoRecordset.Fields.Item(12).Value & "')"
rs.Open Sql, XZDconn
Text2.Text = vbCrLf & "地区号:" & adoRecordset.Fields.Item(0).Value & "单位代码:" & adoRecordset.Fields.Item(2).Value & "单位名称:" & adoRecordset.Fields.Item(3).Value & " 该记录成功导入!" & vbCrLf & Text2.Text
CG = CG + 1
End If
End If
End If
conn_close
adoRecordset.MoveNext
Status Bar1.Panels(2).Text = "成功导入:" & CG & "条"
Loop
result = MsgBox("当 前 数 据 导 入 完毕", , "数据导入")
adoRecordset.Close
End Sub
If Not (dwmc.EOF And dwmc.BOF) Then '单位名称是否已经存在 只要 Not dwmc.EOF 就能判断“单位名称是否已经存在 ”了吧!
单纯靠EOF或BOF的值来判断记录集是否为空是不可以的。
只有当EOF和BOF全部为真时,才能判定记录集为空。