Storing and Retrieving document from database

[divakar.it]

Hi,

As part my developement I would like to keep all employees document stored in database table rather in folder.

I dont want to store the files in folder and keep the file location in table so that get the file from the location.

I tried my level best in google to search for a code but no luck. I got the code in SQL but while retrieving the document its damaged ie unable to open

Below is the SQL code

Looking for your urgent help.

Storing the document:

Create table dbo.ORStable (doclen bigint, doc varbinary(max))
Insert into dbo.ORStable
Select len(bulkcolumn), * from openrowset(bulk 'C:\Users\Divakar\Documents\General files\Travel settlement\8H453869.pdf', SINGLE_BLOB) as r

Retrieving the document:

Declare @SQLcommand nvarchar(4000)
set @SQLcommand = 'bcp RMG.dbo.ORStable out C:\Users\Divakar\Documents\8H453869.pdf -c -T -S '+ convert(varchar,@@ServerName)
exec xp_cmdshell @SQLcommand

[debasisdas]

Please read this and this.

these are sample codes only.

[divakar.it]

Is this applicable for pdf, doc, docx etc as well?

[debasisdas]

have you tried for those file formats ?

[divakar.it]

I dont understand the code. No where in the code mentioned to insert the image. Also dont know the difference between cmdSave_Click() and SavePicture() subroutines as to what it will do.

Can you please explain?

[debasisdas]

I dont understand the code. No where in the code mentioned to insert the image

SavePicture()---does that for you.

as i said this only a sample and is not fully customized to cater to your exact requirement.

first insert a record into database without he document and then modify the code in the link.

[divakar.it]

Thanks for the suggestion. The example is entirely different from my requirement. Actually the code is not a common for all type of file so as just to change the file type in the code and run it.

I found the solution. Below is the code snippet

Loading a file:

This is a SQL code and can be plug into your VB code on the button click event

--Create a table is one time event

Create table dbo.ProfileStorage 
(Rec_Id Int Identity,
[FileName] Varchar(1000), 
[FileData] varbinary(max))

--The below SQL code needs to be added into your VB

Insert into dbo.ProfileStorage
Select 'Travel_Checklist_Ver_2.pdf', * from openrowset(bulk 'C:\Users\Divakar\Documents\General files\Learnings\VB\RMG\V8.0\File\Travel_Checklist_Ver_2.pdf', SINGLE_BLOB) as r

Code for Retrieving data

Private Sub Command2_Click()
Dim binObj As ADODB.Stream
Dim MyConn As ADODB.Connection
Dim MyRecSet1 As New ADODB.Recordset
    Set MyConn = New ADODB.Connection
    MyConn.CursorLocation = adUseClient
    MyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=DIVAKAR-PC;Initial Catalog=RMG;User Id=RMG;Password=Thinksoft"
    MyConn.Open
    
Dim SavePath As String
Dim SQL As String
On Error GoTo ExportBlobs_Error
    
    
    SQL = "Select * from ProfileStorage"
    SavePath = "C:\Users\Divakar\Documents\General files\Learnings\VB\RMG\V8.0\File\Result\"
    Set MyRecSet1 = MyConn.Execute(SQL)
    Do While Not MyRecSet1.EOF
        Set binObj = CreateObject("ADODB.Stream")
        binObj.Type = adTypeBinary
        binObj.Open
        binObj.Write MyRecSet1!FileName
        binObj.SaveToFile SavePath & "\" & MyRecSet1!FileName, adSaveCreateOverWrite
        binObj.Close
        Set binObj = Nothing
        MyRecSet1.MoveNext
    Loop
    On Error GoTo 0
    Exit Sub
    
ExportBlobs_Error:
    
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportBlobs of Form frmTest"
End Sub

[usiegd]

• Inline Code Example Here

Sub-Heading Here