Function GetCLob(ByVal fld As ADODB.Field) As String Dim aBytes() As Byte
If fld.ActualSize = 0 Then Exit Function aBytes = fld.GetChunk(fld.ActualSize)
GetCLob = StrConv(aBytes, vbUnicode) End Function
网上找到这么一段取CLOB的,试验后发现在赋值:dy5("gc") = rs.Fields("gc")中,rs.Fields("gc")提示“在对应的名称或序列的集合中未找到项目。”具体代码如下:Set rn = New ADODB.Connection '本段专门用来处理CLOB字段的 With rn .ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=" & kl & "; User ID=" & yhm & "; Data Source=" & sjk & ";Locale Identifier=2052" .Open End With Dim rs As New ADODB.Recordset Set db2 = OpenDatabase(App.Path & "\zxk.mdb") i1 = 0 Set dy = db2.OpenRecordset("select * from s_table4 where tmc='销售'") If Not dy.BOF Or Not dy.EOF Then tname1 = dy.Fields("tname1").ValueSet db5 = OpenDatabase(App.Path & "\gc.mdb") Set dy5 = db5.OpenRecordset("select * from a01") If Not (dy5.BOF And dy5.EOF) Then k = 0 dy5.MoveLast dy5.MoveFirst data_num = dy5.RecordCountDo Until dy5.EOF k = k + 1 SSPanel1.FloodPercent = k / data_num * 100 dy5.Edit rs.ActiveConnection = rn rs.LockType = adLockOptimistic rs.CursorLocation = adUseClient rs.Source = "select gc from B01 where jh='X1' and to_char(rq,'yyyy-mm-dd')='2008-01-01'" rs.Open If Not (rs.BOF And rs.EOF) Then dy5("gc") = rs.Fields("gc")'''''''''''''''''''''出错处!!!“在对应的名称或序列的集合中未找到项目。” End If Set rs = Nothing dy5.Update dy5.MoveNext Loop End If dy5.Close End If dy.Closern.Close Set rn = Nothing
LOB 字段不能直接赋值,要用 GetChunk 读、用 AppendChunk 写。
Dim aBytes() As Byte aBytes = rs.Fields("gc").GetChunk(fld.ActualSize) dy5("gc").AppendChunk(aBytes)
Dim aBytes() As Byte
If fld.ActualSize = 0 Then Exit Function
aBytes = fld.GetChunk(fld.ActualSize)
GetCLob = StrConv(aBytes, vbUnicode)
End Function
With rn
.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=" & kl & "; User ID=" & yhm & "; Data Source=" & sjk & ";Locale Identifier=2052"
.Open
End With
Dim rs As New ADODB.Recordset
Set db2 = OpenDatabase(App.Path & "\zxk.mdb")
i1 = 0
Set dy = db2.OpenRecordset("select * from s_table4 where tmc='销售'")
If Not dy.BOF Or Not dy.EOF Then
tname1 = dy.Fields("tname1").ValueSet db5 = OpenDatabase(App.Path & "\gc.mdb")
Set dy5 = db5.OpenRecordset("select * from a01")
If Not (dy5.BOF And dy5.EOF) Then
k = 0
dy5.MoveLast
dy5.MoveFirst
data_num = dy5.RecordCountDo Until dy5.EOF
k = k + 1
SSPanel1.FloodPercent = k / data_num * 100
dy5.Edit
rs.ActiveConnection = rn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Source = "select gc from B01 where jh='X1' and to_char(rq,'yyyy-mm-dd')='2008-01-01'"
rs.Open
If Not (rs.BOF And rs.EOF) Then
dy5("gc") = rs.Fields("gc")'''''''''''''''''''''出错处!!!“在对应的名称或序列的集合中未找到项目。”
End If
Set rs = Nothing dy5.Update
dy5.MoveNext
Loop
End If
dy5.Close
End If
dy.Closern.Close
Set rn = Nothing
aBytes = rs.Fields("gc").GetChunk(fld.ActualSize)
dy5("gc").AppendChunk(aBytes)