retrieving document from SQL 2000 problem

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.Codepage = 65001
Response.Charset = "utf-8"

response.clear
response.expires = 0
response.buffer = true

'Response.ContentType = "application/pdf"
Response.ContentType = "application/ms-word"

Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=PBase;Data Source=BURKEN"

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

Set oStream = CreateObject("ADODB.Stream")
oStream.Type = 1
oStream.Open
oStream.LoadFromFile "C:\Inetpub\wwwroot\PB_Kansli\Files\luculli.do c"
oRS.Fields("NEWS_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("ADODB.Connection")
conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=PBase;Data Source=BURKEN"

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

Response.AddHeader "Content-Disposition", "inline;filename=test.doc"
Response.BinaryWrite 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:

Ð�ࡱက>?ku
?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

re: retrieving document from SQL 2000 problem

znubbe@hotmail.com wrote:[color=blue]
> 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.Codepage = 65001
> Response.Charset = "utf-8"
>
> response.clear
> response.expires = 0
> response.buffer = true
>
> 'Response.ContentType = "application/pdf"
> Response.ContentType = "application/ms-word"
>
> Set conn = CreateObject("ADODB.Connection")
> conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
> Security Info=False;Initial Catalog=PBase;Data Source=BURKEN"
>
> strSQL = "Select * From News where NEWS_ID=1"
> Set oRS = CreateObject("ADODB.Recordset")
> Call oRS.Open(strSQL, conn, 2, 2)
>
> Set oStream = CreateObject("ADODB.Stream")
> oStream.Type = 1
> oStream.Open
> oStream.LoadFromFile "C:\Inetpub\wwwroot\PB_Kansli\Files\luculli.do c"
> oRS.Fields("NEWS_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("ADODB.Connection")
> conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
> Security Info=False;Initial Catalog=PBase;Data Source=BURKEN"
>
> strSQL = "Select * From News where NEWS_ID=1"[/color]

Why retrieve all the columns? It's usually a good idea to specify the column
you wish to retrieve...
[color=blue]
> Set oRS = CreateObject("ADODB.Recordset")
> Call oRS.Open(strSQL, conn, 2, 2)
>
> Response.AddHeader "Content-Disposition", "inline;filename=test.doc"
> Response.BinaryWrite oRS("NEWS_DATA")
> oRS.Close
> Set oRS =nothing
>
> conn.Close
> Set conn = nothing
>[/color]
I think you need to use a Stream object. Try this:
If Not ors.eof then
Set FStream = Server.CreateObject("ADODB.Stream")
FStream.Type = adTypeBinary
FStream.Open
FStream.write rs("NEWS_DATA").Value
FStream.Position = 0
Response.AddHeader "Content-Length", FStream.size
Response.Charset = "UTF-8"
Response.BinaryWrite 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.

re: retrieving document from SQL 2000 problem

znubbe@hotmail.com wrote:[color=blue]
> Set oStream = CreateObject("ADODB.Stream")
> oStream.Type = 1
> oStream.Open
> oStream.LoadFromFile "C:\Inetpub\wwwroot\PB_Kansli\Files\luculli.do c"
> oRS.Fields("NEWS_DATA").Value = oStream.Read[/color]

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_AttachDocument (
@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.CreateObject("ADODB.Command")
Set CMD.ActiveConnection = conn 'Should already be open
CMD.CommandType = adCmdStoredProc
CMD.CommandText = "News_AttachDocument"
CMD.Parameters.Append(CMD.CreateParameter("@ID",ad Integer,adParamInput,4))
CMD.Parameters.Append(CMD.CreateParameter("@Data", adVarBinary,adParamInput,16))
CMD.Parameters.Append(CMD.CreateParameter("@Conten tType",adVarChar,adParamInput,50))
CMD.Parameters("@ID").Value = 1 'Your record ID
CMD.Parameters("@Data").Size = oStream.Size
CMD.Parameters("@Data").Value = 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("SELECT NEWS_DATA, CONTENT_TYPE " _
"FROM News WHERE NEWS_ID = 1")

Response.ContentType = RS.Fields("CONTENT_TYPE").Value
Response.BinaryWrite(RS.Fields("NEWS_DATA").Value)



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.

re: retrieving document from SQL 2000 problem

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

re: retrieving document from SQL 2000 problem

znubbe@hotmail.com wrote:[color=blue]
> 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?[/color]

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.[color=blue]
>
> 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?
>[/color]
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.

re: retrieving document from SQL 2000 problem

Hi again,

I changed to this line:

Response.AddHeader "Content-Disposition",
"attachment;filename=test.doc"

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