UPLOADING DOWNLOADING BLOB to SQL SERVER

[jmeadows7]

I have a REQUISITION application that has been in production for several years. However, now the users are want the capability to attach quotes (pdfs), estimates (XLS), contracts (docs), etc to a requisition. I tried to pursuade them to utilize a file server reference solution to no avail. They don't want to risk someone changing a contract on the file server after it is approved.

Does anyone have code to uplaod/download a file to a BLOB in SQL Server.

Thanks,

 

[vbajock]

it's a lot easier just to put the path to the file in a data field. Put the documents in a read-only folder on the server so no one can change them, write a little screen that the users can use to upload any doc to the folder that captures the path to a table on the SQL server. Otherwise, you are going to have one heck of a slow and fat SQL server. The documents load slow on retrieval, which is my biggest complaint. But if you must blob, the trick is the ADO Stream object, it's all here:

http://support.microsoft.com/kb/258038

 

[jmeadows7]

Thanks for the reply - I've gotten back around to working on this. I had an emergency for a couple of weeks that I had to take care of. I'm now looking at the code from microsoft. I've pasted it into a command button in access. I created a table in my test database (MSDE 2005) -with the following columns:
Table name: ABLOB
blob_id int
blob image
filename varchar(50)

I'm just trying to upload a .gif file as a test - I put it directly under c: to make it easy to get to.

When I run the code - it give the following error:
Either BOF or EOF is True, or the current record has been deleted. Requested operation rquires a current record.

I want to load a NEW record to the database - not edit a current one. The table is currently empty.

Any help in resolving this is greatly appreciated!

>>>>>>CODE I'm Using to try to upload<<<<<
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;
Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"

Set rs = New ADODB.Recordset
rs.Open "Select * from ABLOB", cn, adOpenKeyset, adLockOptimistic

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile "c:\warning.gif"
rs.Fields("blob").Value = mstream.Read
rs.Update

rs.Close
cn.Close

 

[jmeadows7]

I went back and "seeded" my test table with a single record. The "update" seemed to work fine after that.

Is there a way to just "append" a new item - or do you have to seed each one and get the key - and then "update" it with the file?

thanks

 

[oharab]

I'm with vbajock, this isn't usually A Good Thing, but as long as your clients are aware of the limitiations:

You need to 'stream' the BLOB into a specific record. Your current code will only ever write into the first record, which isn't what you want.

To create a new record in ADO use the AddNew Method of the recordset.

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim filepath As String: filepath = "C:\Warning.gif"

Set cn = CurrentProject.Connection

Dim fieldList(1) As Variant 'create array to hold list of fields to insert values into
Dim valueList(1) As Variant 'create array to hold list of values to insert values into
'Remember arrays are zero based

fieldList(0) = "Name"
fieldList(1) = "CreatedDate"

valueList(0) = filepath
valueList(1) = Now()

'Create stream to hold data
Dim mStream As New ADODB.Stream
mStream.Type = adTypeBinary
mStream.Open
mStream.LoadFromFile (filepath)


'Open recordset with just the fields we need
rs.Open "SELECT Name,File,CreatedDate FROM ABLOB", cn, adOpenForwardOnly, adLockOptimistic
'add new record with our values,
rs.AddNew fieldList, valueList
'then write our stream
rs.Fields("File").Value = mStream.Read
'finally save
rs.Save


'tidy up
mStream.Close
rs.Close
cn.Close

Set mStream = Nothing
Set rs = Nothing
Set cn = Nothing

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[vbajock]

The code oharib posted is the way to go, but to make an effective application I would take it one step further and add the Common Dialog object in as well so you can build a "document upload" screen, if you want it to look professional - it would give the users the ability to piont-and-click the document from any disk location up into your database, as is explained here:

http://support.microsoft.com/kb/888695