Ptream.Type = adTypeBinary Ptream.Open If Len(FilesName) <> 0 Then 'filesname 是打開的圖片的路 Ptream.LoadFromFile FilesName Rs.Fields("picture").Value = Ptream.Read Else Rs.Fields("picture").Value = "" End If Rs.Update Ptream.Close
Option Explicit'工程->引用->Microsoft ActiveX Data Object 2.6 Library (后面为版本号) Dim cn As New ADODB.Connection, rs As New ADODB.Recordset'保存 Private Sub Command1_Click() Dim stmCon As New Stream
If rs.State = adStateOpen Then rs.Close cn.CursorLocation = adUseClient cn.Execute "DELETE FROM TABLENAME" rs.Open "SELECT * FROM TABLENAME", cn, adOpenDynamic, adLockOptimistic rs.AddNew rs!ID = "12" rs!Name = "bb" rs.Fields("PHOTO") = stmCon.Read rs.Update
cn.CursorLocation = adUseServer If rs.State = adStateOpen Then rs.Close rs.Open "SELECT * FROM TABLENAME WHERE ID = '12'", cn, adOpenDynamic, adLockOptimistic rs.Fields("PHOTO") = stmCon.Read rs.Update
stmCon.Close Set stmCon = Nothing End Sub'打开 Private Sub Command2_Click() Dim stmCon As New Stream
If rs.State = adStateOpen Then rs.Close rs.Open "SELECT * FROM TABLENAME", cn, adOpenForwardOnly, adLockReadOnly If Not rs.EOF Then stmCon.Type = adTypeBinary stmCon.Open stmCon.Write (rs.Fields("PHOTO").Value) stmCon.SaveToFile "C:\aa.bmp", adSaveCreateOverWrite End If Image1.Picture = LoadPicture("C:\aa.bmp") End SubPrivate Sub Command3_Click() rs.Open "SELECT * FROM TEST", cn, 3, 3 End SubPrivate Sub Form_Load() 'On Error GoTo Errhandle cn.ConnectionString = "Driver={SQL Server};SERVER=DataServer;DATABASE=zxzx;UID=information;PWD=information*&#" 'cn.ConnectionString = "DBQ=D:\My Documents\db2.mdb;DefaultDir=" & _ App.Path & ";Driver={Microsoft Access Driver (*.mdb)};" & _ "DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes;" & _ "MaxBufferSize=512;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;" & _ "Threads=3;UID=ADMIN;UserCommitSync=Yes;PWD=;" 'cn.ConnectionString = "Driver={MySQL};Option=262144;port=1024;stmt=;Server=192.168.2.126;DataBase=mysql;User=root;Password="
cn.Open
Exit Sub Errhandle: MsgBox Err.Description, vbExclamation End SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) On Error Resume Next rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub
使用Stream对象,可以实现对数据库的图像存取。 数据库中存放图像的字段是二进制类型(Access为OLE类型)。 比如,如果用“CommonDialog”控件来选择你硬盘上的图像文件; 用“Picture”控件来显示图像,那么下面的代码供参考: (已连接数据库,打开了相应的记录集rs) Dim StmPic As ADODB.Stream Dim StrPicTemp As String ...... '保存你所选择的图像 Set StmPic = New ADODB.Stream StmPic.Type = adTypeBinary '指定流是二进制类型 StmPic.Open '将数据获取到Stream对象中 StmPic.LoadFromFile (CommonDialog1.FileName) '将选择的图像加载到打开的StmPic中 rs.AddNew rs.Fields(1).Value = StmPic.Read '从StmPic对象中读取数据 rs.Update StmPic.Close ...... '读取显示数据库中的图像 Set StmPic = New ADODB.Stream StrPicTemp = "c:\temp.tmp" '临时文件,用来保存读出的图片 With StmPic .Type = adTypeBinary .Open .Write rs.Fields(1) '写入数据库中的数据至Stream中 .SaveToFile StrPicTemp, adSaveCreateOverWrite '将Stream中数据写入临时文件中 .Close End With Picture1.Picture = LoadPicture(StrPicTemp) '用Picture控件显示图像 ......
Microsoft也提供了一个实例(Employee)将微软的员工照片信息从数据库中写入和读出,可参照.
Ptream.Type = adTypeBinary
Ptream.Open
If Len(FilesName) <> 0 Then 'filesname 是打開的圖片的路
Ptream.LoadFromFile FilesName
Rs.Fields("picture").Value = Ptream.Read
Else
Rs.Fields("picture").Value = ""
End If
Rs.Update
Ptream.Close
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset'保存
Private Sub Command1_Click()
Dim stmCon As New Stream
stmCon.Type = adTypeBinary
stmCon.Open
stmCon.LoadFromFile "C:\bb.bmp"
If rs.State = adStateOpen Then rs.Close
cn.CursorLocation = adUseClient
cn.Execute "DELETE FROM TABLENAME"
rs.Open "SELECT * FROM TABLENAME", cn, adOpenDynamic, adLockOptimistic rs.AddNew
rs!ID = "12"
rs!Name = "bb"
rs.Fields("PHOTO") = stmCon.Read
rs.Update
cn.CursorLocation = adUseServer
If rs.State = adStateOpen Then rs.Close
rs.Open "SELECT * FROM TABLENAME WHERE ID = '12'", cn, adOpenDynamic, adLockOptimistic
rs.Fields("PHOTO") = stmCon.Read
rs.Update
stmCon.Close
Set stmCon = Nothing
End Sub'打开
Private Sub Command2_Click()
Dim stmCon As New Stream
If rs.State = adStateOpen Then rs.Close
rs.Open "SELECT * FROM TABLENAME", cn, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
stmCon.Type = adTypeBinary
stmCon.Open
stmCon.Write (rs.Fields("PHOTO").Value)
stmCon.SaveToFile "C:\aa.bmp", adSaveCreateOverWrite
End If Image1.Picture = LoadPicture("C:\aa.bmp")
End SubPrivate Sub Command3_Click()
rs.Open "SELECT * FROM TEST", cn, 3, 3
End SubPrivate Sub Form_Load()
'On Error GoTo Errhandle
cn.ConnectionString = "Driver={SQL Server};SERVER=DataServer;DATABASE=zxzx;UID=information;PWD=information*&#"
'cn.ConnectionString = "DBQ=D:\My Documents\db2.mdb;DefaultDir=" & _
App.Path & ";Driver={Microsoft Access Driver (*.mdb)};" & _
"DriverId=25;FIL=MS Access;ImplicitCommitSync=Yes;" & _
"MaxBufferSize=512;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;" & _
"Threads=3;UID=ADMIN;UserCommitSync=Yes;PWD=;"
'cn.ConnectionString = "Driver={MySQL};Option=262144;port=1024;stmt=;Server=192.168.2.126;DataBase=mysql;User=root;Password="
cn.Open
Exit Sub
Errhandle:
MsgBox Err.Description, vbExclamation
End SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
数据库中存放图像的字段是二进制类型(Access为OLE类型)。
比如,如果用“CommonDialog”控件来选择你硬盘上的图像文件;
用“Picture”控件来显示图像,那么下面的代码供参考:
(已连接数据库,打开了相应的记录集rs)
Dim StmPic As ADODB.Stream
Dim StrPicTemp As String
......
'保存你所选择的图像
Set StmPic = New ADODB.Stream
StmPic.Type = adTypeBinary '指定流是二进制类型
StmPic.Open '将数据获取到Stream对象中
StmPic.LoadFromFile (CommonDialog1.FileName) '将选择的图像加载到打开的StmPic中
rs.AddNew
rs.Fields(1).Value = StmPic.Read '从StmPic对象中读取数据
rs.Update
StmPic.Close
......
'读取显示数据库中的图像
Set StmPic = New ADODB.Stream
StrPicTemp = "c:\temp.tmp" '临时文件,用来保存读出的图片
With StmPic
.Type = adTypeBinary
.Open
.Write rs.Fields(1) '写入数据库中的数据至Stream中
.SaveToFile StrPicTemp, adSaveCreateOverWrite '将Stream中数据写入临时文件中
.Close
End With
Picture1.Picture = LoadPicture(StrPicTemp) '用Picture控件显示图像
......