Joe Feldhake
asked on
Binary Data from SQL database to actual FIle?
I'm developing a system to house all kinds of files in a SQL database. I need to be able to send these files via email, so I'm trying to create a temp file to attach to the email that is generated. I'm able to create an empty file, open it for writing but I'm getting error when i try to put the file. My Question is do I need to loop through the data byte by byte or can I just dump the whole thing. Either way I'd appreciate the code for the PUT statement or the loop?
This is the code that's generating the error
intFileNumber = FreeFile
Open str For Binary Access Write Lock Read Write As #intFileNumber
'Write binary data to the file
Put #intFileNumber, , objRecordSet("binary_data" )
'Close the file
Close #intFileNumber
Thanx in advance
Joe
This is the code that's generating the error
intFileNumber = FreeFile
Open str For Binary Access Write Lock Read Write As #intFileNumber
'Write binary data to the file
Put #intFileNumber, , objRecordSet("binary_data"
'Close the file
Close #intFileNumber
Thanx in advance
Joe
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oops my bad...
sorry, yes you need to open a connection to your server
------
'Leave this in a module
Public cn As New ADODB.Connection
Public rs As ADODB.Recordset
Public cmd As ADODB.Command
'Put this in a connecting sub somewhere...i usually leave this connection open through the duration of my application
' Specify the OLE DB provider.
cn.Provider = "sqloledb"
' Set SQLOLEDB connection properties.
cn.Properties("Data Source").value = userServer
cn.Properties("Initial Catalog").value = DATABASE_NAME
' Decision code for login authorization type:
' Windows NT or SQL Server authentication.
cn.Properties("User ID").value = userID
cn.Properties("Password"). value = userPW
' Open the database.
On Error GoTo e_trap
cn.Open
-------
i'm not sure if you need the CMD defined the the global module to allow your program to work
hope this helps
-Shane
sorry, yes you need to open a connection to your server
------
'Leave this in a module
Public cn As New ADODB.Connection
Public rs As ADODB.Recordset
Public cmd As ADODB.Command
'Put this in a connecting sub somewhere...i usually leave this connection open through the duration of my application
' Specify the OLE DB provider.
cn.Provider = "sqloledb"
' Set SQLOLEDB connection properties.
cn.Properties("Data Source").value = userServer
cn.Properties("Initial Catalog").value = DATABASE_NAME
' Decision code for login authorization type:
' Windows NT or SQL Server authentication.
cn.Properties("User ID").value = userID
cn.Properties("Password").
' Open the database.
On Error GoTo e_trap
cn.Open
-------
i'm not sure if you need the CMD defined the the global module to allow your program to work
hope this helps
-Shane
ASKER
Sorry I need to be more specific
I'm writing this in an ActiveX dll FYI
This is the code I have
************************** ********** ********** *********
Dim objConn
Dim objRecSet
Dim objStream
Set objConn = CreateObject("ADODB.Connec tion")
objConn.Open Connection_String
Set objRS = CreateObject("ADODB.Record Set")
objRS.Open SQL_String, objConn
Set objStream = CreateObject("ADODB.Stream ")
objStream.Write objRS("binary_field")
objStream.saveToFile Full_File_Path, SaveCreateOverWrite
Set objStream = Nothing
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = nothing
************************** ********** ********** ******
What am I doing wrong?
I'm writing this in an ActiveX dll FYI
This is the code I have
**************************
Dim objConn
Dim objRecSet
Dim objStream
Set objConn = CreateObject("ADODB.Connec
objConn.Open Connection_String
Set objRS = CreateObject("ADODB.Record
objRS.Open SQL_String, objConn
Set objStream = CreateObject("ADODB.Stream
objStream.Write objRS("binary_field")
objStream.saveToFile Full_File_Path, SaveCreateOverWrite
Set objStream = Nothing
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = nothing
**************************
What am I doing wrong?
what errors are u getting?
that code is coming directly off a project i'm currently working on where I upload jpeg images to an sql field, and then a client can choose to 'save' the file in order to email it.
:o.
-shane
that code is coming directly off a project i'm currently working on where I upload jpeg images to an sql field, and then a client can choose to 'save' the file in order to email it.
:o.
-shane
ASKER
Err.Number = 3704
Err.Description = Operation is not allowed when the object is closed.
Sounds Like we are working on a very similar project.
This is so that documents of any type can be uploaded to a SQL database and then displayed or sent on request.
I have everything working accept sending the file as an attachment. The process I'm working on creates a document in a temporary folder. Creates an email with that document as an attachment. Sends the email. Then destroys the file.
The error seems to indicate that the stream object needs to be "Opened". I haven't worked with ADODB.Stream before so I may just be missing the obvious here.
Err.Description = Operation is not allowed when the object is closed.
Sounds Like we are working on a very similar project.
This is so that documents of any type can be uploaded to a SQL database and then displayed or sent on request.
I have everything working accept sending the file as an attachment. The process I'm working on creates a document in a temporary folder. Creates an email with that document as an attachment. Sends the email. Then destroys the file.
The error seems to indicate that the stream object needs to be "Opened". I haven't worked with ADODB.Stream before so I may just be missing the obvious here.
can you f8 through the process and find out which line its kicking out on?
looks like we are working on the exact same project...in fact, i'll take your activex when your done =)
I'm trying to go to pdf first by way of shell (since its a jpeg scan originally)
the reason i'm not so much help, is that i'm working on a different portion of the program and I had left the binary emailing till last :(
Sorry this is taking so long!
-shane
looks like we are working on the exact same project...in fact, i'll take your activex when your done =)
I'm trying to go to pdf first by way of shell (since its a jpeg scan originally)
the reason i'm not so much help, is that i'm working on a different portion of the program and I had left the binary emailing till last :(
Sorry this is taking so long!
-shane
ASKER
The error is happening on the following statement
objStream.Write objRS("binary_field")
is there an objStream.Open method that I need to perform before the .Write
If you can get me through this you can have the ActiveX.
objStream.Write objRS("binary_field")
is there an objStream.Open method that I need to perform before the .Write
If you can get me through this you can have the ActiveX.
:/
That code is working for me without any open statements. I do know that my connection is globally open and the recordset is being opened exactly where yours is....
if i think of anything i'll message back (sorry for a sort of incomplete answer thus far)
-shane
That code is working for me without any open statements. I do know that my connection is globally open and the recordset is being opened exactly where yours is....
if i think of anything i'll message back (sorry for a sort of incomplete answer thus far)
-shane
ASKER
Good Solution, but I did have to use a Stream.Open statement to get it to work. FYI
ASKER
3704:Operation is not allowed when the object is closed.
Do I need to open the stream object or initialize it in some way