468,241 Members | 1,718 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,241 developers. It's quick & easy.

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

Feb 17 '06 #1
5 1797
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.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"
Why retrieve all the columns? It's usually a good idea to specify the column
you wish to retrieve...
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

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.
Feb 17 '06 #2
zn****@hotmail.com wrote:
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


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.
Feb 17 '06 #3

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

Feb 17 '06 #4
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.
Feb 17 '06 #5
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.

Feb 19 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.