Dim strSql As String Dim objRec As ADODB.Recordset Dim objField As ADODB.Field
strSql = "SELECT * FROM xxxt.jbxx WHERE fileid = '" & strFileId & "'" If g_AdoDataSource.CreateWritableRs(strSql, objRec) Then If Not (objRec.BOF And objRec.EOF) Then objRec.MoveFirst objRec.Delete adAffectCurrent objRec.Update End If End If
strSql = "SELECT * FROM xxxt.jbxx"
If g_AdoDataSource.CreateWritableRs(strSql, objRec) Then objRec.AddNew Set objField = objRec("filename") objRec("fileid").Value = strFileId BinFileToFld objField, strFileName objRec.Update WriteBlob = True End If
PROC_EXIT: If Not objRec Is Nothing Then Set objRec = Nothing If Not objField Is Nothing Then Set objField = Nothing Exit Function End Function Public Function ReadBlob() As String
On Error GoTo PROC_ERROR
'* 如果没有设置通用对话框成员,退出 If Me.m_ctrCommonDialog Is Nothing Then GoTo PROC_EXIT
'* 如果当前是写操作,退出 If m_blnWrite = True Then GoTo PROC_EXIT
Dim strFileId As String '* 采用文件名称作为标识,包括文件后缀 Dim strFileName As String '* 被写入的文件所在的全路径
Dim strSql As String Dim objRec As ADODB.Recordset Dim objField As ADODB.Field
'* 先查看数据库中是否已经有了该文件,如果有,将其删除 strSql = "SELECT * FROM xxxt.jbxx WHERE fileid = '" & strFileId & "'"
If g_AdoDataSource.CreateReadonlyRs(strSql, objRec, False) Then If Not (objRec.BOF And objRec.EOF) Then Set objField = objRec("filename") BinFldToFile objField, strFileName ReadBlob = strFileName Else MsgBox "该文件不存在", vbCritical ReadBlob = "" End If End If
PROC_EXIT: If Not objRec Is Nothing Then Set objRec = Nothing If Not objField Is Nothing Then Set objField = Nothing Exit Function End Function Private Function BinFileToFld(objField As ADODB.Field, strFileName As String) As Boolean '* Purpose : 把文件转换为二进制大对象,放入数据库中
Dim bBuffer() As Byte '* 比Byte类型灵活一点 Dim lngFileSize As Long Dim intFileNum As Integer
If strFileName <> "" Then '* 注意:如果文件已经被打开了,这里的FileLen函数求得的是文件在打开前的大小 '* 也就是说,如果文件作了修改,则求得的大小可能与文件当前大小不符, '* 因此在使用这一函数时要确保文件没有被打开,LOF函数可以得到打开文件的大小 lngFileSize = VBA.FileLen(strFileName) ReDim bBuffer(lngFileSize - 1) If objField.Type = adLongVarBinary Then intFileNum = FreeFile() Open strFileName For Binary Access Read As #intFileNum Get intFileNum, , bBuffer objField.AppendChunk bBuffer Close intFileNum End If Else objField.Value = vbNull End If BinFileToFld = True Exit Function End Function Private Function BinFldToFile(objField As ADODB.Field, strFileName As String) As Boolean '* Purpose : 从数据库中都出二进制对象,转存为文件
On Error GoTo PROC_ERROR
Dim bBuffer() As Byte Dim lngFileSize As Long Dim intFileNum As Long
If strFileName <> "" Then '* 如果文件已经存在就将其删除 If Len(Dir(strFileName)) > 0 Then Kill strFileName End If
If objField.Type = adLongVarBinary Then lngFileSize = objField.ActualSize ReDim bBuffer(lngFileSize - 1) bBuffer = objField.GetChunk(lngFileSize)
intFileNum = FreeFile() Open strFileName For Binary Access Write As #intFileNum
Put #intFileNum, , bBuffer Close intFileNum End If End If BinFldToFile = True Exit Function End Function Private Sub Class_Terminate() Set Me.m_ctrCommonDialog = Nothing End Subg_AdoDatasource是连接数据库类的实例。
我写了一段程序插入图片,可是运行的时候在 updata 时出错,错误代码-2147487259 请大家帮我分析原因,多谢多谢~~strSQL = "select media from t_dzjcxx where dzjcbh='" & strPIC & "' " If Not p_MsSql.UpdateImgFile(strSQL, strCurName) Then GoTo ErrProgressPublic Function UpdateImgFile(ByVal strSQL As String, ByVal imgFile As String) As Boolean Dim Dynaset As New ADODB.Recordset Dim ret As Integer Dim ImgFileHD As Integer Dim ImgSize As Long Dim bImgSrc() As Byte ret = True ' 空系统错误信息 p_ErrCode = 0 p_ErrMessage = vbNullString On Error Resume Next 'MSSqlDynaset对象生成 Set Dynaset = Nothing Set Dynaset = New ADODB.Recordset Dynaset.CursorType = adOpenKeyset Dynaset.LockType = adLockOptimistic Dynaset.Open strSQL, p_MSSqlDatabase
If Err.Number <> 0 Then p_ErrCode = Err.Number p_ErrMessage = Err.Description ret = False Err.Clear End If If ret = True Then ImgFileHD = FreeFile Open imgFile For Binary Access Read As ImgFileHD ImgSize = LOF(ImgFileHD) ReDim bImgSrc(ImgSize) Get ImgFileHD, , bImgSrc() Dynaset.Fields(0).Attributes = adFldLong Dynaset.Fields(0).AppendChunk bImgSrc() Dynaset.Update <------------------这里出错 Dynaset.Close Close ImgFileHD End If
多谢各位的指导~~
急啊~~
通常都是使用流(stream)的形式讀寫的。
' CREATE TABLE xxxt.jbxx (fileid VARCHAR2(200) primary key,filename BLOB);Public m_ctrCommonDialog As CommonDialog
Public m_blnWrite As Boolean '* TRUE表示写,FALSE表示读Public Function WriteBlob() As Boolean
'* 暂时只支持一次写一个文件
If Me.m_ctrCommonDialog Is Nothing Then GoTo PROC_EXIT
'* 如果当前是读操作,退出
If m_blnWrite = False Then GoTo PROC_EXIT
Dim strFileId As String
Dim strFileName As String
strFileId = Me.m_ctrCommonDialog.FileTitle
strFileName = Me.m_ctrCommonDialog.FileName
Dim strSql As String
Dim objRec As ADODB.Recordset
Dim objField As ADODB.Field
strSql = "SELECT * FROM xxxt.jbxx WHERE fileid = '" & strFileId & "'"
If g_AdoDataSource.CreateWritableRs(strSql, objRec) Then
If Not (objRec.BOF And objRec.EOF) Then
objRec.MoveFirst
objRec.Delete adAffectCurrent
objRec.Update
End If
End If
strSql = "SELECT * FROM xxxt.jbxx"
If g_AdoDataSource.CreateWritableRs(strSql, objRec) Then
objRec.AddNew
Set objField = objRec("filename")
objRec("fileid").Value = strFileId
BinFileToFld objField, strFileName
objRec.Update
WriteBlob = True
End If
PROC_EXIT:
If Not objRec Is Nothing Then Set objRec = Nothing
If Not objField Is Nothing Then Set objField = Nothing
Exit Function
End Function
Public Function ReadBlob() As String
On Error GoTo PROC_ERROR
'* 如果没有设置通用对话框成员,退出
If Me.m_ctrCommonDialog Is Nothing Then GoTo PROC_EXIT
'* 如果当前是写操作,退出
If m_blnWrite = True Then GoTo PROC_EXIT
Dim strFileId As String '* 采用文件名称作为标识,包括文件后缀
Dim strFileName As String '* 被写入的文件所在的全路径
strFileId = Me.m_ctrCommonDialog.FileTitle
strFileName = Me.m_ctrCommonDialog.FileName
Dim strSql As String
Dim objRec As ADODB.Recordset
Dim objField As ADODB.Field
'* 先查看数据库中是否已经有了该文件,如果有,将其删除
strSql = "SELECT * FROM xxxt.jbxx WHERE fileid = '" & strFileId & "'"
If g_AdoDataSource.CreateReadonlyRs(strSql, objRec, False) Then
If Not (objRec.BOF And objRec.EOF) Then
Set objField = objRec("filename")
BinFldToFile objField, strFileName
ReadBlob = strFileName
Else
MsgBox "该文件不存在", vbCritical
ReadBlob = ""
End If
End If
PROC_EXIT:
If Not objRec Is Nothing Then Set objRec = Nothing
If Not objField Is Nothing Then Set objField = Nothing
Exit Function
End Function
Private Function BinFileToFld(objField As ADODB.Field, strFileName As String) As Boolean
'* Purpose : 把文件转换为二进制大对象,放入数据库中
Dim bBuffer() As Byte '* 比Byte类型灵活一点
Dim lngFileSize As Long
Dim intFileNum As Integer
If strFileName <> "" Then
'* 注意:如果文件已经被打开了,这里的FileLen函数求得的是文件在打开前的大小
'* 也就是说,如果文件作了修改,则求得的大小可能与文件当前大小不符,
'* 因此在使用这一函数时要确保文件没有被打开,LOF函数可以得到打开文件的大小
lngFileSize = VBA.FileLen(strFileName)
ReDim bBuffer(lngFileSize - 1)
If objField.Type = adLongVarBinary Then
intFileNum = FreeFile()
Open strFileName For Binary Access Read As #intFileNum
Get intFileNum, , bBuffer
objField.AppendChunk bBuffer
Close intFileNum
End If
Else
objField.Value = vbNull
End If
BinFileToFld = True
Exit Function
End Function
Private Function BinFldToFile(objField As ADODB.Field, strFileName As String) As Boolean
'* Purpose : 从数据库中都出二进制对象,转存为文件
On Error GoTo PROC_ERROR
Dim bBuffer() As Byte
Dim lngFileSize As Long
Dim intFileNum As Long
If strFileName <> "" Then
'* 如果文件已经存在就将其删除
If Len(Dir(strFileName)) > 0 Then
Kill strFileName
End If
If objField.Type = adLongVarBinary Then
lngFileSize = objField.ActualSize
ReDim bBuffer(lngFileSize - 1)
bBuffer = objField.GetChunk(lngFileSize)
intFileNum = FreeFile()
Open strFileName For Binary Access Write As #intFileNum
Put #intFileNum, , bBuffer
Close intFileNum
End If
End If
BinFldToFile = True
Exit Function
End Function
Private Sub Class_Terminate()
Set Me.m_ctrCommonDialog = Nothing
End Subg_AdoDatasource是连接数据库类的实例。
请大家帮我分析原因,多谢多谢~~strSQL = "select media from t_dzjcxx where dzjcbh='" & strPIC & "' "
If Not p_MsSql.UpdateImgFile(strSQL, strCurName) Then GoTo ErrProgressPublic Function UpdateImgFile(ByVal strSQL As String, ByVal imgFile As String) As Boolean
Dim Dynaset As New ADODB.Recordset
Dim ret As Integer
Dim ImgFileHD As Integer
Dim ImgSize As Long
Dim bImgSrc() As Byte
ret = True ' 空系统错误信息
p_ErrCode = 0
p_ErrMessage = vbNullString On Error Resume Next
'MSSqlDynaset对象生成
Set Dynaset = Nothing
Set Dynaset = New ADODB.Recordset
Dynaset.CursorType = adOpenKeyset
Dynaset.LockType = adLockOptimistic
Dynaset.Open strSQL, p_MSSqlDatabase
If Err.Number <> 0 Then
p_ErrCode = Err.Number
p_ErrMessage = Err.Description
ret = False
Err.Clear
End If If ret = True Then
ImgFileHD = FreeFile
Open imgFile For Binary Access Read As ImgFileHD
ImgSize = LOF(ImgFileHD)
ReDim bImgSrc(ImgSize)
Get ImgFileHD, , bImgSrc()
Dynaset.Fields(0).Attributes = adFldLong
Dynaset.Fields(0).AppendChunk bImgSrc()
Dynaset.Update <------------------这里出错
Dynaset.Close
Close ImgFileHD
End If
'返回值设定
UpdateImgFile = retEnd Function