Public Sub SavePic(ByVal pPicPath As String) Dim Rs As New ADODB.Recordset dim adoCn As New ADODB.Connection Dim bytPicData() As Byte dim strCn As String
Open pPicPath For Binary As #1 ReDim bytPicData(LOF(1) - 1) Get #1, , bytPicData() Close #1 strCn = "Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=databasename;Data Source=Server;Password=;" adoCn.Open strCn Rs.Open "SELECT * FROM dPicture", adoCn, adOpenKeyset, adLockOptimistic Rs("PicData").AppendChunk bytPicData() Rs.Update Set Rs = Nothing End Sub
'Use ADODB.Stream Method 'After ADO 2.6 'Import the Image to SQLServer Private Sub ImportBLOB(cn As ADODB.Connection)
Dim rs As New ADODB.Recordset Dim stm As ADODB.Stream
Set stm = New ADODB.Stream
' Skip any table not found errors On Error Resume Next cn.Execute "drop table BinaryObject"
On Error GoTo 0 'Create the BinaryObject table cn.Execute "create table BinaryObject " & _ "(blob_id int IDENTITY(1,1), " & _ "blob_filename varchar(256), " & _ "blob_object image)"
rs.Open "Select * from BinaryObject where 1=2", cn, adOpenKeyset, adLockOptimistic 'Read the binary files from disk stm.Type = adTypeBinary stm.Open stm.LoadFromFile App.Path & "\BLOBsample.jpg"
End Sub 'Display the image on image control Private Sub DisplayBLOB(cn As ADODB.Connection) Dim rs As New ADODB.Recordset
' Select the only image in the table rs.Open "Select * from BinaryObject where blob_id = 1", cn
' Set the DataSource to the recordset Set imgBinaryData.DataSource = rs 'Set the DataField to the BLOB field imgBinaryData.DataField = rs!blob_object.Name
'Release the recordset rs.Close Set rs = NothingEnd Sub
Dim Rs As New ADODB.Recordset
dim adoCn As New ADODB.Connection
Dim bytPicData() As Byte
dim strCn As String
Open pPicPath For Binary As #1
ReDim bytPicData(LOF(1) - 1)
Get #1, , bytPicData()
Close #1 strCn = "Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=databasename;Data Source=Server;Password=;"
adoCn.Open strCn Rs.Open "SELECT * FROM dPicture", adoCn, adOpenKeyset, adLockOptimistic
Rs("PicData").AppendChunk bytPicData()
Rs.Update Set Rs = Nothing
End Sub
'After ADO 2.6
'Import the Image to SQLServer
Private Sub ImportBLOB(cn As ADODB.Connection)
Dim rs As New ADODB.Recordset
Dim stm As ADODB.Stream
Set stm = New ADODB.Stream
' Skip any table not found errors
On Error Resume Next
cn.Execute "drop table BinaryObject"
On Error GoTo 0
'Create the BinaryObject table
cn.Execute "create table BinaryObject " & _
"(blob_id int IDENTITY(1,1), " & _
"blob_filename varchar(256), " & _
"blob_object image)"
rs.Open "Select * from BinaryObject where 1=2", cn, adOpenKeyset, adLockOptimistic
'Read the binary files from disk
stm.Type = adTypeBinary
stm.Open
stm.LoadFromFile App.Path & "\BLOBsample.jpg"
rs.AddNew
rs!blob_filename = App.Path & "\BLOBsample.jpg"
rs!blob_object = stm.Read
'Insert the binary object in the table
rs.Update
rs.Close
stm.Close
Set rs = Nothing
Set stm = Nothing
End Sub
'Display the image on image control
Private Sub DisplayBLOB(cn As ADODB.Connection) Dim rs As New ADODB.Recordset
' Select the only image in the table
rs.Open "Select * from BinaryObject where blob_id = 1", cn
' Set the DataSource to the recordset
Set imgBinaryData.DataSource = rs
'Set the DataField to the BLOB field
imgBinaryData.DataField = rs!blob_object.Name
'Release the recordset
rs.Close
Set rs = NothingEnd Sub