retrieving document from SQL 2000 problem

[znubbe]

Hi,

I hope anyone can help me with this problem.

I have a field of image type in a SQL 2000 database. I'm using this
code to insert a document:

Dim conn
Dim rs
Dim oStream

Session.Codepag e = 65001
Response.Charse t = "utf-8"

response.clear
response.expire s = 0
response.buffer = true

'Response.Conte ntType = "applicatio n/pdf"
Response.Conten tType = "applicatio n/ms-word"

Set conn = CreateObject("A DODB.Connection ")
conn.Open "Provider=SQLOL EDB.1;Integrate d Security=SSPI;P ersist
Security Info=False;Init ial Catalog=PBase;D ata Source=BURKEN"

strSQL = "Select * From News where NEWS_ID=1"
Set oRS = CreateObject("A DODB.Recordset" )
Call oRS.Open(strSQL , conn, 2, 2)

Set oStream = CreateObject("A DODB.Stream")
oStream.Type = 1
oStream.Open
oStream.LoadFro mFile "C:\Inetpub\www root\PB_Kansli\ Files\luculli.d oc"
oRS.Fields("NEW S_DATA").Value = oStream.Read
oStream.Close
Set oStream =nothing
oRS.Update
oRS.Close
Set oRS =nothing

conn.Close
Set conn = nothing
Data is put in the field and I'm using this code to retrieve it:

Set conn = CreateObject("A DODB.Connection ")
conn.Open "Provider=SQLOL EDB.1;Integrate d Security=SSPI;P ersist
Security Info=False;Init ial Catalog=PBase;D ata Source=BURKEN"

strSQL = "Select * From News where NEWS_ID=1"
Set oRS = CreateObject("A DODB.Recordset" )
Call oRS.Open(strSQL , conn, 2, 2)

Response.AddHea der "Content-Disposition", "inline;filenam e=test.doc"
Response.Binary Write oRS("NEWS_DATA" )
oRS.Close
Set oRS =nothing

conn.Close
Set conn = nothing

The problem is that the output is nothing like the orginal. Instead of
a word document I get this:

Ð�ࡱက>?k u
?jx怀?????? ??????????????? ??????????????? ??????????????? ??????????????? ??????????????? ??????????????? ??????????�3
??bjbjC?C? ⃀!퀀!퀀v ,???l\
\
\
\
?
?
?
?vvv8 ? ??T
..
⅀⅀⅀⅀ ?Ig sÓˆÕˆÕˆÕˆÕˆÕ ˆÕˆ$? ?
???
{''"{{ ??\
\
â…€?
 ???{\
â…€\
8â …€Óˆ?{Óˆ??w\
?
wâ…€Ö…
ೌ,̳�??v{ Xww\$ 0T w* Ӈ(* w???\
\
\
\


etc etc etc etc etc

What am I doing wrong?

Please help, I'm going crazy over this :-(

Thanks for your help,

Znubbe

[Bob Barrows [MVP]]

zn****@hotmail. com wrote:

Hi,

I hope anyone can help me with this problem.

I have a field of image type in a SQL 2000 database. I'm using this
code to insert a document:

Dim conn
Dim rs
Dim oStream

Session.Codepag e = 65001
Response.Charse t = "utf-8"

response.clear
response.expire s = 0
response.buffer = true

'Response.Conte ntType = "applicatio n/pdf"
Response.Conten tType = "applicatio n/ms-word"

Set conn = CreateObject("A DODB.Connection ")
conn.Open "Provider=SQLOL EDB.1;Integrate d Security=SSPI;P ersist
Security Info=False;Init ial Catalog=PBase;D ata Source=BURKEN"

strSQL = "Select * From News where NEWS_ID=1"
Set oRS = CreateObject("A DODB.Recordset" )
Call oRS.Open(strSQL , conn, 2, 2)

Set oStream = CreateObject("A DODB.Stream")
oStream.Type = 1
oStream.Open
oStream.LoadFro mFile "C:\Inetpub\www root\PB_Kansli\ Files\luculli.d oc"
oRS.Fields("NEW S_DATA").Value = oStream.Read
oStream.Close
Set oStream =nothing
oRS.Update
oRS.Close
Set oRS =nothing

conn.Close
Set conn = nothing
Data is put in the field and I'm using this code to retrieve it:

Set conn = CreateObject("A DODB.Connection ")
conn.Open "Provider=SQLOL EDB.1;Integrate d Security=SSPI;P ersist
Security Info=False;Init ial Catalog=PBase;D ata Source=BURKEN"

strSQL = "Select * From News where NEWS_ID=1"
Why retrieve all the columns? It's usually a good idea to specify the column
you wish to retrieve...
Set oRS = CreateObject("A DODB.Recordset" )
Call oRS.Open(strSQL , conn, 2, 2)

Response.AddHea der "Content-Disposition", "inline;filenam e=test.doc"
Response.Binary Write oRS("NEWS_DATA" )
oRS.Close
Set oRS =nothing

conn.Close
Set conn = nothing

I think you need to use a Stream object. Try this:
If Not ors.eof then
Set FStream = Server.CreateOb ject("ADODB.Str eam")
FStream.Type = adTypeBinary
FStream.Open
FStream.write rs("NEWS_DATA") .Value
FStream.Positio n = 0
Response.AddHea der "Content-Length", FStream.size
Response.Charse t = "UTF-8"
Response.Binary Write FStream.Read
Response.flush
end if

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Dave Anderson]

zn****@hotmail. com wrote:

Set oStream = CreateObject("A DODB.Stream")
oStream.Type = 1
oStream.Open
oStream.LoadFro mFile "C:\Inetpub\www root\PB_Kansli\ Files\luculli.d oc"
oRS.Fields("NEW S_DATA").Value = oStream.Read

I have not done it with this with a recordset, but I have done it with a
stored procedure. And in that case, it was necessary to set the value AND
size of the parameter. Consider this example:

CREATE PROCEDURE dbo.News_Attach Document (
@ID INT,
@Data IMAGE,
@ContentType VARCHAR(50)
) AS
UPDATE News
SET NEWS_DATA = @Data,
CONTENT_TYPE = @ContentType
WHERE NEWS_ID = @ID
GO

To insert the data, use something like this. I will attempt to use objects
and names from your example:

Set CMD = Server.CreateOb ject("ADODB.Com mand")
Set CMD.ActiveConne ction = conn 'Should already be open
CMD.CommandType = adCmdStoredProc
CMD.CommandText = "News_AttachDoc ument"
CMD.Parameters. Append(CMD.Crea teParameter("@I D",adInteger,ad ParamInput,4))
CMD.Parameters. Append(CMD.Crea teParameter("@D ata",adVarBinar y,adParamInput, 16))
CMD.Parameters. Append(CMD.Crea teParameter("@C ontentType",adV arChar,adParamI nput,50))
CMD.Parameters( "@ID").Valu e = 1 'Your record ID
CMD.Parameters( "@Data").Si ze = oStream.Size
CMD.Parameters( "@Data").Va lue = oStream.Read()
CMD.Parameters( "@ContentType") .Value = 'Appropriate doctype
CMD.Execute()

Note that the important step here is setting both the size and value of the
blob parameter. You can then extract it with something along these lines:

Set RS = conn.Execute("S ELECT NEWS_DATA, CONTENT_TYPE " _
"FROM News WHERE NEWS_ID = 1")

Response.Conten tType = RS.Fields("CONT ENT_TYPE").Valu e
Response.Binary Write(RS.Fields ("NEWS_DATA").V alue)

I have never had a problem doing it this way. There may be typos or other
syntax errors due to translation from my preferred JScript.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

[znubbe]

Hi Bob,

Thanks for your reply, but unfortunately it is the same result :-(

Do you know if the insert action adds some kind of header to the data
that might corrupt it?

At the end of the gibberish it there is this text "Word-dokument
MSWordDocWord. Document.8" as well as "Microsoft Word 9.0s". Could it
be a unicode problem?

Best Regards,
Znubbe

[Bob Barrows [MVP]]

zn****@hotmail. com wrote:

Hi Bob,

Thanks for your reply, but unfortunately it is the same result :-(

Do you know if the insert action adds some kind of header to the data
that might corrupt it?
Not in my experience. We use aspSmartUpload so perhaps my experience is not
typical.
I know a header is inserted when storing a Word Doc in an Access Memo field
but I've never seen an issue with SQL Server.
At the end of the gibberish it there is this text "Word-dokument
MSWordDocWord. Document.8" as well as "Microsoft Word 9.0s". Could it
be a unicode problem?

Maybe, but I doubt it.Sorry i can't help.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Znubbe]

Hi again,

I changed to this line:

Response.AddHea der "Content-Disposition",
"attachment;fil ename=test.doc"

and it worked like charm :-) Not sure why inline doesn't work though.