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
5 Years
Discussion Span
Last Post by usiegd

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?


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.


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"
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.Write MyRecSet1!FileName
        binObj.SaveToFile SavePath & "\" & MyRecSet1!FileName, adSaveCreateOverWrite
        Set binObj = Nothing
    On Error GoTo 0
    Exit Sub
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportBlobs of Form frmTest"
End Sub
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.