首先在SQL SERVER中用于存储文件的字段类型为TEXT
其次在FORM中使用控件RICHTEXT存入时只要保存RICHTEXT.TEXT就可以了,如
strSql = "insert into 表名 (存储文件的字段) values ('" & RICHTEXT.TEXT & "')"
然后执行这个SQL语句就可以了读出时可以这样,rdoMy为rdoResultset,MyConnect为RDO.rdoConnection
strSql = "select 存储文件的字段 from 表名"
Set rdoMy = MyConnect.OpenResultset(strSql, rdOpenKeyset, rdConcurLock)
注意的是,打开数据集的最后一个参数(rdConcurLock)一定要有
其次在FORM中使用控件RICHTEXT存入时只要保存RICHTEXT.TEXT就可以了,如
strSql = "insert into 表名 (存储文件的字段) values ('" & RICHTEXT.TEXT & "')"
然后执行这个SQL语句就可以了读出时可以这样,rdoMy为rdoResultset,MyConnect为RDO.rdoConnection
strSql = "select 存储文件的字段 from 表名"
Set rdoMy = MyConnect.OpenResultset(strSql, rdOpenKeyset, rdConcurLock)
注意的是,打开数据集的最后一个参数(rdConcurLock)一定要有
AppendChunk 和 GetChunk 。
就可以完全搞定。
将一个文件存放到数据库的blob类型的字段用函数:AppendChunk(),它将文件分为n个32K连续存放入数据库中。
从数据库读出则采用getchunk(),也是一次32K读出组合成一个文件。
仔细看看VB的连机帮助,你会有收获的!
good luck!
'将图片读入数据库中
'-------------------------------------------------------------
Public Function ImgToDB(ByVal strImageName As String, _
ByVal strFieldName As String, _
ByRef strError As String) As Boolean
Dim intFileChannel As Integer
Dim lngFileLen As Long
Dim intSegment As Integer
Dim intOffset As Integer
Dim bytContent() As Byte
Dim i As Integer
Const lngSegmentSize As Long = 32768 If Dir(strImageName, vbNormal) = "" Then
strError = "指定的工程图片文件 <" + strImageName + "> 没有找到!请确认文件路径!"
ImgToDB = False
Exit Function
End If
intFileChannel = 1
On Error GoTo OpenError:
Open strImageName For Binary Access Read As intFileChannel
lngFileLen = LOF(intFileChannel)
If lngFileLen = 0 Then
Close intFileChannel
ImgToDB = True
Exit Function
End If
On Error GoTo ReadError: intSegment = lngFileLen \ lngSegmentSize
intOffset = lngFileLen Mod lngSegmentSize
rdoDBresult(strFieldName).AppendChunk Null
ReDim bytContent(intOffset)
Get intFileChannel, , bytContent()
rdoDBresult(strFieldName).AppendChunk bytContent()
ReDim bytContent(lngSegmentSize)
For i = 1 To intSegment
Get intFileChannel, , bytContent()
rdoDBresult(strFieldName).AppendChunk bytContent()
Next i
Close intFileChannel
ImgToDB = True
Exit FunctionOpenError:
strError = "文件打开失败!请稍后再试!"
ImgToDB = False
Exit FunctionReadError:
Close intFileChannel
strError = "向数据库中存放图片文件失败!"
ImgToDB = False
End Function'-------------------------------------------------------------
'将数据库中的图片记录读入文件
'-------------------------------------------------------------Public Function DbToImg(ByVal strFieldName As String, _
ByRef strFileName As String, _
ByRef strError As String) As Boolean Dim intFileChannel As Integer
Dim lngFileLen As Long
Dim intSegment As Integer
Dim intOffset As Integer
Dim bytContent() As Byte
Dim i As Integer
Const lngSegmentSize As Long = 32768
intFileChannel = 1
On Error GoTo OpenError:
Open strFileName For Binary Access Write As intFileChannel
On Error GoTo WriteError:
lngFileLen = rdoDBresult(strFieldName).ColumnSize
If lngFileLen <= 0 Then
strFileName = ""
Close intFileChannel
DbToImg = True
Exit Function
End If
intSegment = lngFileLen \ lngSegmentSize
intOffset = lngFileLen Mod lngSegmentSize
ReDim bytContent(intOffset)
bytContent() = rdoDBresult(strFieldName).GetChunk(intOffset)
Put intFileChannel, , bytContent()
For i = 1 To intSegment
ReDim bytContent(lngSegmentSize)
bytContent() = rdoDBresult(strFieldName).GetChunk(lngSegmentSize)
Put intFileChannel, , bytContent()
Next i
Close intFileChannel
strError = ""
DbToImg = True
Exit FunctionOpenError:
strError = "打开文件写入时发生错误!"
DbToImg = False
Exit FunctionWriteError:
Close intFileChannel
strError = "从数据库中读取图片时发生错误!"
DbToImg = FalseEnd Function
多谢你的代码,但请问该函数是否可以保存其他类型的文件,而不只是图片,而且请问rdoDBresult()代表什么意思,是ADO返回的结果集吗?
哦,不好意思,rdodbresult是一个全局变量,是一个结果集类型的变量。
这段程序是我很早写的了,采用的rdo而不是ado。
哦,不好意思,rdodbresult是一个全局变量,是一个结果集类型的变量。
这段程序是我很早写的了,采用的rdo而不是ado。
哦,不好意思,rdodbresult是一个全局变量,是一个结果集类型的变量。
这段程序是我很早写的了,采用的rdo而不是ado,所以rdodbresult是返回的结果集,我想ado也应该差不多吧!
哦,不好意思,rdodbresult是一个全局变量,是一个结果集类型的变量。
这段程序是我很早写的了,采用的rdo而不是ado,所以rdodbresult是返回的结果集,我想ado也应该差不多吧!
从数据库读出后已经保存在硬盘上了。
我将你给我的代码进行了自己的修改如下:
Public Function SaveToDB(File_Path As Variant, File_Name As Variant) As Boolean
'存储到数据库
On Error GoTo SaveToDBErr
Dim mConn As New ADODB.Connection
Dim mCommStr As String
Dim mRs As New ADODB.Recordset
Dim intFileChannel As Integer
Dim lngFileLen As Long
Dim intSegment As Integer
Dim intOffset As Integer
Dim bytContent() As Byte
Dim i As Integer
Const lngSegmentSize As Long = 32768
Dim strFile As String
mCommStr = "select * from CFile where f_name is NULL"
mConn.ConnectionString = mConnStr
mConn.CursorLocation = adUseClient
mConn.Open
mRs.Open mCommStr, mConn, 1, 3
mRs.AddNew
mRs("f_path") = Trim(File_Path)
mRs("f_name") = Trim(File_Name)
'读取文件进数据库
intFileChannel = 1
strFile = File_Path & File_Name
Open strFile For Binary Access Read As intFileChannel
lngFileLen = LOF(intFileChannel)
mRs("f_size") = lngFileLen
intSegment = lngFileLen \ lngSegmentSize
intOffset = lngFileLen Mod lngSegmentSize
mRs("f_content").AppendChunk Null
ReDim bytContent(intOffset)
Get intFileChannel, , bytContent()
mRs("f_content").AppendChunk bytContent()
ReDim bytContent(lngSegmentSize)
For i = 1 To intSegment
Get intFileChannel, , bytContent()
mRs("f_content").AppendChunk bytContent()
Next i
Close intFileChannel
'mRs("f_content").AppendChunk File_Content
mRs.Update
mRs.Close
Set mRs = Nothing
Set mConn = Nothing
STD = True Exit Function
SaveToDBErr:
Set mRs = Nothing
Set mConn = Nothing
SaveToDB = False
Call RaiseError(MyUnhandledError, "SaveToDB Sub")
End Function'从将数据库中读取文件
Public Function GetFromDB(File_id As Variant,File_path as Variant,File_name as Variant) As Boolean On Error GoTo GetFromDBErr
Dim mConn As New ADODB.Connection
Dim mCommStr As String
Dim mRs As New ADODB.Recordset
Dim intFileChannel As Integer
Dim lngFileLen As Long
Dim intSegment As Integer
Dim intOffset As Integer
Dim bytContent() As Byte
Dim i As Integer
Const lngSegmentSize As Long = 32768
Dim strFile As String
mCommStr = "select * from CFile where file_id=" & File_id
mConn.ConnectionString = mConnStr
mConn.CursorLocation = adUseClient
mConn.Open
mRs.Open mCommStr, mConn, adOpenForwardOnly, adLockReadOnly
If Not mRs.EOF Then
'从数据库中读取文件
intFileChannel = 1
strFile = File_path & File_name
Open strFile For Binary Access Write As intFileChannel
'***********注意这里是否有错
lngFileLen = mRs("f_content").ActualSize
intSegment = lngFileLen \ lngSegmentSize
intOffset = lngFileLen Mod lngSegmentSize
ReDim bytContent(intOffset)
bytContent() = mRs("f_content").GetChunk(intOffset)
Put intFileChannel, , bytContent()
For i = 1 To intSegment
ReDim bytContent(lngSegmentSize)
bytContent() = mRs("f_content").GetChunk(lngSegmentSize)
Put intFileChannel, , bytContent()
Next i
Close intFileChannel
GetFromDB= True
Else
GetFromDB= False
End If
mRs.Close
Set mRs = Nothing
Set mConn = Nothing
Exit FunctionGetFromDBErr:
Set mRs = Nothing
Set mConn = Nothing
GetFromDB= False
Call RaiseError(MyUnhandledError, "GetFromDBSub")
End Function 修改后的代码,在GetFromDB函数中,注有星号的地方不知是否有错,在程序运行到bytContent() = mRs("f_content").GetChunk(intOffset)就会跳到GetFromDBErr,是否能帮忙解决?
mRs("f_content")的数据结构为Text。
http://support.microsoft.com/support/kb/articles/Q194/9/75.asp
讲的非常详细。
另外注意错误时候关闭文件指针!再试试看!
Public Function SaveToDB(File_Path As Variant, File_Name As Variant) As Boolean
'存储到数据库
On Error GoTo SaveToDBErr
Dim mConn As New ADODB.Connection
Dim mCommStr As String
Dim mRs As New ADODB.Recordset
Dim intFileChannel As Integer
Dim lngFileLen As Long
Dim intSegment As Integer
Dim intOffset As Integer
Dim bytContent() As Byte
Dim i As Integer
Const lngSegmentSize As Long = 32768
Dim strFile As String
mCommStr = "select * from CFile where f_name is NULL"
mConn.ConnectionString = mConnStr
mConn.CursorLocation = adUseClient
mConn.Open
mRs.Open mCommStr, mConn, 1, 3
mRs.AddNew
mRs("f_path") = Trim(File_Path)
mRs("f_name") = Trim(File_Name)
'读取文件进数据库
intFileChannel = 1
strFile = File_Path & File_Name
^^^^^^^^^^^^^^^^^^^^^^(这里应该有容错的处理如路径为C:\以及路径为C:\windows处理是不同的) Open strFile For Binary Access Read As intFileChannel
lngFileLen = LOF(intFileChannel)
mRs("f_size") = lngFileLen
intSegment = lngFileLen \ lngSegmentSize
intOffset = lngFileLen Mod lngSegmentSize
mRs("f_content").AppendChunk Null <---该行删除
ReDim bytContent(intOffset)
Get intFileChannel, , bytContent()
mRs("f_content").AppendChunk bytContent()
ReDim bytContent(lngSegmentSize)
For i = 1 To intSegment
Get intFileChannel, , bytContent()
mRs("f_content").AppendChunk bytContent()
Next i
Close intFileChannel
'mRs("f_content").AppendChunk File_Content
mRs.Update
mRs.Close
Set mRs = Nothing
Set mConn = Nothing
STD = True Exit Function
SaveToDBErr:
Set mRs = Nothing
Set mConn = Nothing
SaveToDB = False
Call RaiseError(MyUnhandledError, "SaveToDB Sub")
End Function'从将数据库中读取文件
Public Function GetFromDB(File_id As Variant,File_path as Variant,File_name as Variant) As Boolean On Error GoTo GetFromDBErr
Dim mConn As New ADODB.Connection
Dim mCommStr As String
Dim mRs As New ADODB.Recordset
Dim intFileChannel As Integer
Dim lngFileLen As Long
Dim intSegment As Integer
Dim intOffset As Integer
Dim bytContent() As Byte
Dim i As Integer
Const lngSegmentSize As Long = 32768
Dim strFile As String
mCommStr = "select * from CFile where file_id=" & File_id
mConn.ConnectionString = mConnStr
mConn.CursorLocation = adUseClient
mConn.Open
mRs.Open mCommStr, mConn, adOpenForwardOnly, adLockReadOnly
If Not mRs.EOF Then
'从数据库中读取文件
intFileChannel = 1
strFile = File_path & File_name
Open strFile For Binary Access Write As intFileChannel
'***********注意这里是否有错(这里没有问题,是rdo和ADO的不同)
lngFileLen = mRs("f_content").ActualSize
intSegment = lngFileLen \ lngSegmentSize
intOffset = lngFileLen Mod lngSegmentSize
ReDim bytContent(intOffset)
bytContent() = mRs("f_content").GetChunk(intOffset)
Put intFileChannel, , bytContent()
For i = 1 To intSegment
ReDim bytContent(lngSegmentSize)
bytContent() = mRs("f_content").GetChunk(lngSegmentSize)
Put intFileChannel, , bytContent()
Next i
Close intFileChannel
GetFromDB= True
Else
GetFromDB= False
End If
mRs.Close
Set mRs = Nothing
Set mConn = Nothing
Exit FunctionGetFromDBErr:
Set mRs = Nothing
Set mConn = Nothing
GetFromDB= False
Call RaiseError(MyUnhandledError, "GetFromDBSub")
End Function
问题基本解决,很多谢你的指教,希望以后能继续帮忙。
祝世纪新年快乐,万事如意,最紧要是身体健康,一句讲晒:心想事成!(我来自广州)