哥们,你狠!如何使用 ADO Stream 对象访问和修改 SQL Server BLOB 数据 http://support.microsoft.com/default.aspx?scid=kb;zh-cn;258038
Private Sub UpdateDate_Employees()
Dim bytX() As Byte Dim intFileNumber As Integer Dim lngBlocks As Long Dim lngCounter As Long Dim lngFileLength As Long Dim lngLeftOver As Long
' 异动开始。 mobjConn.BeginTrans
' 登录资录至空白记录。 mobjRst!LastName = txtField(0) mobjRst!FirstName = txtField(1) If txtField(2) <> "" Then mobjRst!Title = txtField(2) Else mobjRst!Title = "" If txtField(3) <> "" Then mobjRst!ReportsTo = txtField(3) Else mobjRst!ReportsTo = Null If txtField(4) <> "" Then mobjRst!HireDate = txtField(4) Else mobjRst!HireDate = Null If txtField(5) <> "" Then mobjRst!Extension = txtField(5) Else mobjRst!Extension = "" If txtField(6) <> "" Then mobjRst!Address = txtField(6) Else mobjRst!Address = "" If txtField(7) <> "" Then mobjRst!City = txtField(7) Else mobjRst!City = "" If txtField(8) <> "" Then mobjRst!Region = txtField(8) Else mobjRst!Region = "" If txtField(9) <> "" Then mobjRst!PostalCode = txtField(9) Else mobjRst!PostalCode = "" If txtField(10) <> "" Then mobjRst!Country = txtField(10) Else mobjRst!Country = "" If txtField(11) <> "" Then mobjRst!HomePhone = txtField(11) Else mobjRst!HomePhone = "" If txtField(12) <> "" Then mobjRst!Notes = txtField(12) Else mobjRst!Notes = "" If txtField(13) <> "" Then mobjRst!TitleOfCourtesy = txtField(13) Else mobjRst!TitleOfCourtesy = Null If txtField(14) <> "" Then mobjRst!BirthDate = txtField(14) Else mobjRst!BirthDate = Null If txtPhoto <> "" Then mobjRst!PhotoPath = txtPhoto Else mobjRst!PhotoPath = ""
'------------------------------------------------ ' G0 大型二进制数据如图形之处理 '------------------------------------------------ If mblnPhotoExist <> False Then ' 取得档案编号。 intFileNumber = FreeFile
' 以「二进制」模式开启档案,并限制只能对该档作输入的动作。 Open mstrFileName_View For Binary Access Read As #intFileNumber
' 传回一个 Long 型态的值,它是以字节为单位,用来代表由 Open 陈述式所开启的档案之大小 lngFileLength = LOF(intFileNumber)
If lngFileLength > 0 Then lngBlocks = lngFileLength / BLOCK_SIZE lngLeftOver = lngFileLength Mod BLOCK_SIZE
ReDim bytX(BLOCK_SIZE) For lngCounter = 1 To lngBlocks
' 将此记录读入变量中 Get #intFileNumber, , bytX()
' 将数据附加至一个具有大量文字或二进制数据的 Field 或 Parameter 对象。 mobjRst!photo.AppendChunk bytX() Next lngCounter
If lngLeftOver > 0 Then ReDim bytX(lngLeftOver) Get #intFileNumber, , bytX() mobjRst!photo.AppendChunk bytX() End If End If Else mobjRst!photo = Null End If
给一个vb的例子给你 Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim stm As ADODB.StreamPrivate Sub SavePictureToDB(cn As ADODB.Connection) '将BMP图片存入数据库 On Error GoTo EH Set stm = New ADODB.Stream rs.Open "select ImagePath,ImageValue from tbl_Image", cn, adOpenKeyset, adLockOptimistic CommonDialog1.ShowOpen Text1.Text = CommonDialog1.FileName
With stm .Type = adTypeBinary .Open .LoadFromFile CommonDialog1.FileName End With With rs .AddNew .Fields("ImagePath") = Text1.Text .Fields("ImageValue") = stm.Read .Update End With rs.Close Set rs = Nothing Exit Sub EH: MsgBox Err.Description, vbInformation, "Error" End Sub Private Sub LoadPictureFromDB(cn As ADODB.Connection) '载数据库中读出BMP图片 On Error GoTo EH Dim strTemp As String Set stm = New ADODB.Stream strTemp = "c:\temp.tmp" '临时文件,用来保存读出的图片 rs.Open "select ImagePath,ImageValue from tbl_image", cn, , , adCmdText With stm .Type = adTypeBinary .Open .Write rs("ImageValue") .SaveToFile strTemp, adSaveCreateOverWrite .Close End With Image1.Picture = LoadPicture(strTemp) Set stm = Nothing rs.Close Set rs = Nothing Exit Sub EH: MsgBox Err.Description, vbInformation, "Error" End Sub 好象一大把代码啊,怎么自己不找找呢??
'============================ '以下过程用于处理图片 '============================ '图片显示过程 Private Sub ShowPic() If Not IsNull(adoPrimaryRS.Fields("Pic")) Then Set StmPic = New ADODB.Stream StrPicTemp = App.Path + "\lawyer.pic" '临时文件,用来保存读出的图片 With StmPic .Type = adTypeBinary .Open '打开 .Write adoPrimaryRS.Fields("Pic") '写入数据库中的二进制文件 .SaveToFile StrPicTemp, adSaveCreateOverWrite .Close End With Pic.Picture = LoadPicture(StrPicTemp) '载入临时文件中的图像 Else Pic.Picture = LoadPicture() End If
End SubPrivate Sub SavePic() Set StmPic = New ADODB.Stream StmPic.Type = adTypeBinary StmPic.Open If (Pic.Picture = 0) Then StmPic.LoadFromFile (App.Path + "\Zero.gif") adoPrimaryRS.Fields("Pic").Value = StmPic.Read adoPrimaryRS!ParentKey = sCurrentOrgKey adoPrimaryRS.UpdateBatch adAffectAll StmPic.Close Exit Sub End If If (CommonDialog1.FileName <> "") And BolPic Then StmPic.LoadFromFile (CommonDialog1.FileName) adoPrimaryRS.Fields("Pic").Value = StmPic.Read End If adoPrimaryRS.UpdateBatch adAffectAll StmPic.CloseEnd Sub Private Sub cmdDelPic_Click() Pic.Picture = LoadPicture() CommonDialog1.FileName = "" BolPic = FalseEnd SubPrivate Sub cmdLoadPic_Click() Dim FileName As String
On Error GoTo LoadErr CommonDialog1.CancelError = True CommonDialog1.Filter = "图像文件(*.jpg;*.gif)|*.jpg;*.gif" '筛选图像文件 CommonDialog1.FilterIndex = 2 CommonDialog1.ShowOpen If CommonDialog1.FileName = "" Then '按下取消按钮恢复原来显示状态 Exit Sub End If FileName = CommonDialog1.FileName If FileLen(CommonDialog1.FileName) > 10240 Then MsgBox "图片太大!请转换图片格式,修改图片大小!" Exit Sub End If Pic.Picture = LoadPicture(FileName) '预览图片 BolPic = True Exit Sub
http://support.microsoft.com/default.aspx?scid=kb;zh-cn;258038
Dim bytX() As Byte
Dim intFileNumber As Integer
Dim lngBlocks As Long
Dim lngCounter As Long
Dim lngFileLength As Long
Dim lngLeftOver As Long
' 异动开始。
mobjConn.BeginTrans
' 登录资录至空白记录。
mobjRst!LastName = txtField(0)
mobjRst!FirstName = txtField(1)
If txtField(2) <> "" Then mobjRst!Title = txtField(2) Else mobjRst!Title = ""
If txtField(3) <> "" Then mobjRst!ReportsTo = txtField(3) Else mobjRst!ReportsTo = Null
If txtField(4) <> "" Then mobjRst!HireDate = txtField(4) Else mobjRst!HireDate = Null
If txtField(5) <> "" Then mobjRst!Extension = txtField(5) Else mobjRst!Extension = ""
If txtField(6) <> "" Then mobjRst!Address = txtField(6) Else mobjRst!Address = ""
If txtField(7) <> "" Then mobjRst!City = txtField(7) Else mobjRst!City = ""
If txtField(8) <> "" Then mobjRst!Region = txtField(8) Else mobjRst!Region = ""
If txtField(9) <> "" Then mobjRst!PostalCode = txtField(9) Else mobjRst!PostalCode = ""
If txtField(10) <> "" Then mobjRst!Country = txtField(10) Else mobjRst!Country = ""
If txtField(11) <> "" Then mobjRst!HomePhone = txtField(11) Else mobjRst!HomePhone = ""
If txtField(12) <> "" Then mobjRst!Notes = txtField(12) Else mobjRst!Notes = ""
If txtField(13) <> "" Then mobjRst!TitleOfCourtesy = txtField(13) Else mobjRst!TitleOfCourtesy = Null
If txtField(14) <> "" Then mobjRst!BirthDate = txtField(14) Else mobjRst!BirthDate = Null
If txtPhoto <> "" Then mobjRst!PhotoPath = txtPhoto Else mobjRst!PhotoPath = ""
'------------------------------------------------
' G0 大型二进制数据如图形之处理
'------------------------------------------------
If mblnPhotoExist <> False Then
' 取得档案编号。
intFileNumber = FreeFile
' 以「二进制」模式开启档案,并限制只能对该档作输入的动作。
Open mstrFileName_View For Binary Access Read As #intFileNumber
' 传回一个 Long 型态的值,它是以字节为单位,用来代表由 Open 陈述式所开启的档案之大小
lngFileLength = LOF(intFileNumber)
If lngFileLength > 0 Then
lngBlocks = lngFileLength / BLOCK_SIZE
lngLeftOver = lngFileLength Mod BLOCK_SIZE
ReDim bytX(BLOCK_SIZE)
For lngCounter = 1 To lngBlocks
' 将此记录读入变量中
Get #intFileNumber, , bytX()
' 将数据附加至一个具有大量文字或二进制数据的 Field 或 Parameter 对象。
mobjRst!photo.AppendChunk bytX()
Next lngCounter
If lngLeftOver > 0 Then
ReDim bytX(lngLeftOver)
Get #intFileNumber, , bytX()
mobjRst!photo.AppendChunk bytX()
End If
End If
Else
mobjRst!photo = Null
End If
' 将该记录写入数据库。
mobjRst.Update
mobjRst.Requery
' 异动结束。
mobjConn.CommitTrans
End Sub
参考这个程序,不过这是繁体的,不过你可以自己转化成简体
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim stm As ADODB.StreamPrivate Sub SavePictureToDB(cn As ADODB.Connection)
'将BMP图片存入数据库
On Error GoTo EH
Set stm = New ADODB.Stream
rs.Open "select ImagePath,ImageValue from tbl_Image", cn, adOpenKeyset, adLockOptimistic
CommonDialog1.ShowOpen
Text1.Text = CommonDialog1.FileName
With stm
.Type = adTypeBinary
.Open
.LoadFromFile CommonDialog1.FileName
End With
With rs
.AddNew
.Fields("ImagePath") = Text1.Text
.Fields("ImageValue") = stm.Read
.Update
End With
rs.Close
Set rs = Nothing
Exit Sub
EH: MsgBox Err.Description, vbInformation, "Error"
End Sub
Private Sub LoadPictureFromDB(cn As ADODB.Connection)
'载数据库中读出BMP图片
On Error GoTo EH
Dim strTemp As String
Set stm = New ADODB.Stream
strTemp = "c:\temp.tmp" '临时文件,用来保存读出的图片
rs.Open "select ImagePath,ImageValue from tbl_image", cn, , , adCmdText
With stm
.Type = adTypeBinary
.Open
.Write rs("ImageValue")
.SaveToFile strTemp, adSaveCreateOverWrite
.Close
End With
Image1.Picture = LoadPicture(strTemp)
Set stm = Nothing
rs.Close
Set rs = Nothing
Exit Sub
EH: MsgBox Err.Description, vbInformation, "Error"
End Sub
好象一大把代码啊,怎么自己不找找呢??
'以下过程用于处理图片
'============================
'图片显示过程
Private Sub ShowPic() If Not IsNull(adoPrimaryRS.Fields("Pic")) Then
Set StmPic = New ADODB.Stream
StrPicTemp = App.Path + "\lawyer.pic" '临时文件,用来保存读出的图片
With StmPic
.Type = adTypeBinary
.Open '打开
.Write adoPrimaryRS.Fields("Pic") '写入数据库中的二进制文件
.SaveToFile StrPicTemp, adSaveCreateOverWrite
.Close
End With
Pic.Picture = LoadPicture(StrPicTemp) '载入临时文件中的图像
Else
Pic.Picture = LoadPicture()
End If
End SubPrivate Sub SavePic() Set StmPic = New ADODB.Stream
StmPic.Type = adTypeBinary
StmPic.Open
If (Pic.Picture = 0) Then
StmPic.LoadFromFile (App.Path + "\Zero.gif")
adoPrimaryRS.Fields("Pic").Value = StmPic.Read
adoPrimaryRS!ParentKey = sCurrentOrgKey
adoPrimaryRS.UpdateBatch adAffectAll
StmPic.Close
Exit Sub
End If
If (CommonDialog1.FileName <> "") And BolPic Then
StmPic.LoadFromFile (CommonDialog1.FileName)
adoPrimaryRS.Fields("Pic").Value = StmPic.Read
End If adoPrimaryRS.UpdateBatch adAffectAll
StmPic.CloseEnd Sub
Private Sub cmdDelPic_Click() Pic.Picture = LoadPicture()
CommonDialog1.FileName = ""
BolPic = FalseEnd SubPrivate Sub cmdLoadPic_Click() Dim FileName As String
On Error GoTo LoadErr
CommonDialog1.CancelError = True
CommonDialog1.Filter = "图像文件(*.jpg;*.gif)|*.jpg;*.gif" '筛选图像文件
CommonDialog1.FilterIndex = 2
CommonDialog1.ShowOpen
If CommonDialog1.FileName = "" Then '按下取消按钮恢复原来显示状态
Exit Sub
End If
FileName = CommonDialog1.FileName
If FileLen(CommonDialog1.FileName) > 10240 Then
MsgBox "图片太大!请转换图片格式,修改图片大小!"
Exit Sub
End If
Pic.Picture = LoadPicture(FileName) '预览图片
BolPic = True
Exit Sub
LoadErr:
End Sub