以前写的: Dim rs As New ADODB.Recordset Dim StrCnn As String Dim StrSql As String Dim RsStream As New ADODB.Stream Dim StrMyId As StringPrivate Sub CmmSave_Click() If txtFilePath.Text = "" Then Exit Sub StrSql = "Delete from myimge where myid = '" & StrMyId & "'" adoSysConn.Execute StrSql StrSql = "Select MyId ,image from Myimge" If rs.State = adStateOpen Then rs.Close rs.Open StrSql, adoSysConn, adOpenKeyset, adLockOptimistic RsStream.Type = adTypeBinary RsStream.Open RsStream.LoadFromFile Trim(txtFilePath.Text) With rs .AddNew .Fields("MyId") = StrMyId .Fields("image") = RsStream.Read .Update End With Set rs = Nothing Set RsStream = Nothing Call CboShow End SubPrivate Sub CmmShow_Click() Dim PathTmp As String PathTmp = App.Path & "\Temp.tmp" StrSql = "Select MyId,image from Myimge where MyId = '" & Trim(cboId.Text) & "' " If rs.State = adStateOpen Then rs.Close rs.Open StrSql, adoSysConn, adOpenStatic, adLockReadOnly RsStream.Type = adTypeBinary RsStream.Open RsStream.Write rs!Image RsStream.SaveToFile PathTmp, adSaveCreateOverWrite RsStream.Close Image1.Picture = LoadPicture(PathTmp) Set rs = Nothing Set RsStream = Nothing
End SubPrivate Sub CboShow() StrSql = "select MyId from myimge" If rs.State = adStateOpen Then rs.Close rs.Open StrSql, adoSysConn, adOpenKeyset, adLockOptimistic 'If rs.RecordCount < 1 Then Exit Sub rs.MoveFirst cboId.Clear Do While Not rs.EOF cboId.AddItem rs!MyId rs.MoveNext Loop cboId.ListIndex = 0
End SubPrivate Sub CmmUpdate_Click() If txtFilePath.Text = "" Then Exit Sub StrSql = "delete from Myimge where MyId = '" & Trim(cboId.Text) & "'" adoSysConn.Execute StrSql StrSql = "Select MyId ,image from Myimge" If rs.State = adStateOpen Then rs.Close rs.Open StrSql, adoSysConn, adOpenKeyset, adLockOptimistic RsStream.Type = adTypeBinary RsStream.Open RsStream.LoadFromFile Trim(txtFilePath.Text) With rs .AddNew .Fields("MyId") = Trim(cboId.Text) .Fields("image") = RsStream.Read .Update End With Set rs = Nothing Set RsStream = Nothing Call CboShow End Sub
注:写图片文件到数据库 Col为栏位名,ImgFile为要写到数据库的图片文件名,BockSize为每次写多少字节,缺省为每次写8K字节到数据库 Public Sub WriteDB(Col As ADODB.Field, ImgFile As String, Optional BlockSize As Long=8192) Dim byteData() As Byte, FileLength As Long, NumBlocks As Integer Dim LeftOver As Long, SourceFileNum As Integer, i As Integer
SourceFileNum = FreeFile Open ImgFile For Binary As SourceFileNum FileLength = LOF(SourceFileNum) If FileLength > 50 Then NumBlocks = FileLength \ BlockSize LeftOver = FileLength Mod BlockSize
ReDim byteData(LeftOver) Get SourceFileNum, , byteData() Col.AppendChunk byteData() ReDim byteData(BlockSize) For i = 1 To NumBlocks Get SourceFileNum, , byteData() Col.AppendChunk byteData() Next End If Close SourceFileNum End Sub
ImgFile为从数据库读出数据写到磁盘的文件名,BlockSize为每次向文件写多少个字节,缺省为8K字节,当ReadDB=True,得到图片文件後,可以用LoadPicter(图片文件名)显示图片到PictureBox或Image框中. Public Function ReadDB(Col As ADODB.Field, ImgFile As String,Optional BlockSize As Long=8192) As Boolean Dim byteData() As Byte, NumBlocks As Integer Dim LeftOver As Long, DestFileNum As Integer, i As Integer Dim ColSize As Long
On Error GoTo ErrRead ReadDB = False
'If Dir(ImgFile) <> "" Then Kill ImgFile
DestFileNum = FreeFile Open ImgFile For Binary As #DestFileNum
ReDim byteData(LeftOver) byteData() = Col.GetChunk(LeftOver) Put DestFileNum, , byteData() ReDim byteData(BlockSize) For i = 1 To NumBlocks byteData() = Col.GetChunk(BlockSize) Put #DestFileNum, , byteData() Next If LOF(DestFileNum) > 200 Then ReadDB = True Close #DestFileNum Exit Function
ErrRead: MsgBox "READ PICTURE ERR:" & Err.Number ReadDB = False Exit Function End Function//如果ReadDB=False则写文件失败。
光读取就简单了 Dim Cn As New ADODB.Connection Dim Recotmp As New ADODB.Recordset Dim Mstream As ADODB.Stream '先把连接做好Recotmp.Open "Select * from Photo where empid=" & Empid & " ", Cn, adOpenKeyset, adLockOptimistic If Not Recotmp.EOF Then Set Mstream = New ADODB.Stream Mstream.Type = adTypeBinary Mstream.Open Mstream.Write Recotmp.Fields("Empphoto").Value Mstream.SaveToFile "c:\TempPhoto.bmp", adSaveCreateOverWrite Image1.Picture = LoadPicture("c:\TempPhoto.bmp") Image1.Refresh End If Recotmp .close Set Recotmp = Nothing
Dim rs As New ADODB.Recordset
Dim StrCnn As String
Dim StrSql As String
Dim RsStream As New ADODB.Stream
Dim StrMyId As StringPrivate Sub CmmSave_Click()
If txtFilePath.Text = "" Then Exit Sub
StrSql = "Delete from myimge where myid = '" & StrMyId & "'"
adoSysConn.Execute StrSql
StrSql = "Select MyId ,image from Myimge"
If rs.State = adStateOpen Then rs.Close
rs.Open StrSql, adoSysConn, adOpenKeyset, adLockOptimistic
RsStream.Type = adTypeBinary
RsStream.Open
RsStream.LoadFromFile Trim(txtFilePath.Text)
With rs
.AddNew
.Fields("MyId") = StrMyId
.Fields("image") = RsStream.Read
.Update
End With
Set rs = Nothing
Set RsStream = Nothing
Call CboShow
End SubPrivate Sub CmmShow_Click()
Dim PathTmp As String
PathTmp = App.Path & "\Temp.tmp"
StrSql = "Select MyId,image from Myimge where MyId = '" & Trim(cboId.Text) & "' "
If rs.State = adStateOpen Then rs.Close
rs.Open StrSql, adoSysConn, adOpenStatic, adLockReadOnly
RsStream.Type = adTypeBinary
RsStream.Open
RsStream.Write rs!Image
RsStream.SaveToFile PathTmp, adSaveCreateOverWrite
RsStream.Close
Image1.Picture = LoadPicture(PathTmp)
Set rs = Nothing
Set RsStream = Nothing
End SubPrivate Sub CboShow()
StrSql = "select MyId from myimge"
If rs.State = adStateOpen Then rs.Close
rs.Open StrSql, adoSysConn, adOpenKeyset, adLockOptimistic
'If rs.RecordCount < 1 Then Exit Sub
rs.MoveFirst
cboId.Clear
Do While Not rs.EOF
cboId.AddItem rs!MyId
rs.MoveNext
Loop
cboId.ListIndex = 0
End SubPrivate Sub CmmUpdate_Click()
If txtFilePath.Text = "" Then Exit Sub
StrSql = "delete from Myimge where MyId = '" & Trim(cboId.Text) & "'"
adoSysConn.Execute StrSql
StrSql = "Select MyId ,image from Myimge"
If rs.State = adStateOpen Then rs.Close
rs.Open StrSql, adoSysConn, adOpenKeyset, adLockOptimistic
RsStream.Type = adTypeBinary
RsStream.Open
RsStream.LoadFromFile Trim(txtFilePath.Text)
With rs
.AddNew
.Fields("MyId") = Trim(cboId.Text)
.Fields("image") = RsStream.Read
.Update
End With
Set rs = Nothing
Set RsStream = Nothing
Call CboShow
End Sub
Col为栏位名,ImgFile为要写到数据库的图片文件名,BockSize为每次写多少字节,缺省为每次写8K字节到数据库
Public Sub WriteDB(Col As ADODB.Field, ImgFile As String, Optional BlockSize As Long=8192)
Dim byteData() As Byte, FileLength As Long, NumBlocks As Integer
Dim LeftOver As Long, SourceFileNum As Integer, i As Integer
SourceFileNum = FreeFile
Open ImgFile For Binary As SourceFileNum
FileLength = LOF(SourceFileNum)
If FileLength > 50 Then
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
ReDim byteData(LeftOver)
Get SourceFileNum, , byteData()
Col.AppendChunk byteData()
ReDim byteData(BlockSize)
For i = 1 To NumBlocks
Get SourceFileNum, , byteData()
Col.AppendChunk byteData()
Next
End If
Close SourceFileNum
End Sub
ImgFile为从数据库读出数据写到磁盘的文件名,BlockSize为每次向文件写多少个字节,缺省为8K字节,当ReadDB=True,得到图片文件後,可以用LoadPicter(图片文件名)显示图片到PictureBox或Image框中.
Public Function ReadDB(Col As ADODB.Field, ImgFile As String,Optional BlockSize As Long=8192) As Boolean
Dim byteData() As Byte, NumBlocks As Integer
Dim LeftOver As Long, DestFileNum As Integer, i As Integer
Dim ColSize As Long
On Error GoTo ErrRead
ReadDB = False
'If Dir(ImgFile) <> "" Then Kill ImgFile
DestFileNum = FreeFile
Open ImgFile For Binary As #DestFileNum
ColSize = Col.ActualSize
NumBlocks = ColSize \ BlockSize
LeftOver = ColSize Mod BlockSize
ReDim byteData(LeftOver)
byteData() = Col.GetChunk(LeftOver)
Put DestFileNum, , byteData()
ReDim byteData(BlockSize)
For i = 1 To NumBlocks
byteData() = Col.GetChunk(BlockSize)
Put #DestFileNum, , byteData()
Next
If LOF(DestFileNum) > 200 Then ReadDB = True
Close #DestFileNum
Exit Function
ErrRead:
MsgBox "READ PICTURE ERR:" & Err.Number
ReadDB = False
Exit Function
End Function//如果ReadDB=False则写文件失败。
Dim Cn As New ADODB.Connection
Dim Recotmp As New ADODB.Recordset
Dim Mstream As ADODB.Stream
'先把连接做好Recotmp.Open "Select * from Photo where empid=" & Empid & " ", Cn, adOpenKeyset, adLockOptimistic
If Not Recotmp.EOF Then
Set Mstream = New ADODB.Stream
Mstream.Type = adTypeBinary
Mstream.Open
Mstream.Write Recotmp.Fields("Empphoto").Value
Mstream.SaveToFile "c:\TempPhoto.bmp", adSaveCreateOverWrite
Image1.Picture = LoadPicture("c:\TempPhoto.bmp")
Image1.Refresh
End If
Recotmp .close
Set Recotmp = Nothing