Working with Attachment

[beautieee]

What would be the method working with attachment stored in SQL? Searching from internet and found nothing. Anyone having experience with attachment?

A button click will enable me to attached a file and stored in SQL and when I view the record, I can download the file.

Whats the data type of SQL and whats the method using in VB?

Thank you.

Regards,
Beautieee.

 

[Andrzejek]

Go to

http://www.Google.com

and search for "store file in SQL server" or just "store file in SQL" - you will find 1000's of ideas.


Have fun.

---- Andy

 

[beautieee]

Thank you Andy. You are so lovely.

I was using Yahoo and searched for Attach file.

 

[beautieee]

Below link found for file attachment and it worked for Access database for reference.

http://www.buygold.net/v04n03/v04n03.html

Was trying to convert connection type to SQL but couldnt store filename.

 

[beautieee]

Here is the simple code for storing Binary data into Access database and SQL DB and both worked fine.

Data Type for Access is OLE Object
Data Type for SQL must be Image

Regards,
Beautieee

Dim sConn As String
Dim oConn As New ADODB.Connection
Dim oRs As New ADODB.Recordset

Public Function SaveFileToDB(ByVal FileName As String, _
RS As Object, FieldName As String) As Boolean
'**************************************************************
'PURPOSE: SAVES DATA FROM BINARY FILE (e.g., .EXE, WORD DOCUMENT
'CONTROL TO RECORDSET RS IN FIELD NAME FIELDNAME

'FIELD TYPE MUST BE BINARY (OLE OBJECT IN ACCESS)

'REQUIRES: REFERENCE TO MICROSOFT ACTIVE DATA OBJECTS 2.0 or ABOVE

'SAMPLE USAGE
'Dim sConn As String
'Dim oConn As New ADODB.Connection
'Dim oRs As New ADODB.Recordset
'
'
'sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDb.MDB;Persist Security Info=False"
'
'oConn.Open sConn
'oRs.Open "SELECT * FROM MYTABLE", oConn, adOpenKeyset, _
adLockOptimistic
'oRs.AddNew

'SaveFileToDB "C:\MyDocuments\MyDoc.Doc", oRs, "MyFieldName"
'oRs.Update
'oRs.Close
'**************************************************************

Dim iFileNum As Integer
Dim lFileLength As Long

Dim abBytes() As Byte
Dim iCtr As Integer

On Error GoTo ErrorHandler
If Dir(FileName) = "" Then Exit Function
If Not TypeOf RS Is ADODB.Recordset Then Exit Function

'read file contents to byte array
iFileNum = FreeFile
Open FileName For Binary Access Read As #iFileNum
lFileLength = LOF(iFileNum)
ReDim abBytes(lFileLength)
Get #iFileNum, , abBytes()

'put byte array contents into db field
RS.Fields(FieldName).AppendChunk abBytes()
Close #iFileNum

SaveFileToDB = True
ErrorHandler:
End Function

Public Function LoadFileFromDB(FileName As String, _
RS As Object, FieldName As String) As Boolean
'************************************************
'PURPOSE: LOADS BINARY DATA IN RECORDSET RS,
'FIELD FieldName TO a File Named by the FileName parameter

'REQUIRES: REFERENCE TO MICROSOFT ACTIVE DATA OBJECTS 2.0 or ABOVE

'SAMPLE USAGE
'Dim sConn As String
'Dim oConn As New ADODB.Connection
'Dim oRs As New ADODB.Recordset
'
'
'sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDb.MDB;Persist Security Info=False"
'
'oConn.Open sConn
'oRs.Open "SELECT * FROM MyTable", oConn, adOpenKeyset,
' adLockOptimistic
'LoadFileFromDB "C:\MyDocuments\MyDoc.Doc", oRs, "MyFieldName"
'oRs.Close
'************************************************
Dim iFileNum As Integer
Dim lFileLength As Long
Dim abBytes() As Byte
Dim iCtr As Integer

On Error GoTo ErrorHandler
If Not TypeOf RS Is ADODB.Recordset Then Exit Function

iFileNum = FreeFile
Open FileName For Binary As #iFileNum
lFileLength = LenB(RS(FieldName))

abBytes = RS(FieldName).GetChunk(lFileLength)
Put #iFileNum, , abBytes()
Close #iFileNum
LoadFileFromDB = True

ErrorHandler:
End Function

Private Sub Command1_Click()
oRs.AddNew
SaveFileToDB "m:\Doc1.Doc", oRs, "MyFieldName"
oRs.Update
oRs.Close
End Sub

Private Sub Command2_Click()

oRs.Open "SELECT * FROM MyTable", oConn, adOpenKeyset, _
adLockOptimistic
LoadFileFromDB "m:\doc2.doc", oRs, "MyFieldName"
oRs.Close

End Sub

Private Sub Form_Load()
'sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=m:\MyDb.MDB;Persist Security Info=False"
sConn = "DSN=StFile;uid=abc;pwd=abcd" ' Access DB
'sConn = "DSN=Activity;uid=abc;pwd=abcde" 'SQL DB
oConn.Open sConn
oRs.Open "SELECT * from mytable", oConn, adOpenForwardOnly, adLockOptimistic, adCmdText
End Sub