January 7, 2004 at 5:33 am
I had been using Access Database all these days,
We are moving to Sql Server. in my access db using OLE Object i have one database column to store Files.
In Sql server what must be Data type if i want to store a file into table.
iam not intrested in storing file Path.
Thanks
January 7, 2004 at 6:08 am
Use the image data type.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 10, 2004 at 12:21 am
I did used image datatype previously but its best suited if iam storing only image files.
but i want something which can store any format of file (.doc,.ppt,.xls,.pdf etc)
January 10, 2004 at 12:46 pm
Once again, use the image data type. It's for all kind of binary data.
And take a look at this one.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 10, 2004 at 10:43 pm
I used image datatype, the problem is word,excel and powerpoint file data is shown as junk representation.
What might be the reason...
Thanks
January 11, 2004 at 1:25 pm
I use the image data type to store xls, doc, pdf, ppt and I don't know what else.
Front end is some asp application which opens the data in the associated application with that file extension. Work great.
What are you doing?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 11, 2004 at 10:19 pm
iam also using asp application (Front End) .
File data is shown in junk format.
January 12, 2004 at 12:57 am
Can you post your asp code?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 12, 2004 at 10:56 pm
-------------------------------------file.asp---------------------------------
<%
' Retrieves binary files from the database
Response.Buffer = True
' ID of the file to retrieve
Dim ID
ID = Request("ID")
If Len(ID) < 1 Then
ID = 7
End If
' Connection String
Dim connStr
connStr = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Database name;"
connStr = connStr & "Data Source=servername"
' Recordset Object
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
' opening connection
rs.Open "select [Filen] from File_store where ID = " & _
ID, connStr, 2, 4
If Not rs.EOF Then
Response.BinaryWrite rs("Filen")
End If
rs.Close
Set rs = Nothing
%>
-----------------------------------------------------------------------------
Catalog=Database name;"
connStr = connStr & "Data Source=servername"
are changed by me for posting code.
January 13, 2004 at 1:18 am
I think you need to state the appropriate content type like this (Excel in my example)
Response.Clear Response.Buffer = true
Response.Expires = 0
Set oConn = ConnectAttachment()
Set oRecSet = Server.CreateObject("ADODB.Recordset")
lpszTableName = Request.QueryString("section")
lpszMsgID = Request.QueryString("id")
SQL="SELECT img FROM " & lpszTableName & " WHERE id=" & lpszMsgID
oRecSet.Open SQL, oConn
If oRecSet.BOF And oRecSet.EOF Then
Response.End
End If
Response.ContentType = "application/vnd.ms-excel"
Response.BinaryWrite oRecSet.Fields("img")
oRecSet.Close
Set oRecSet = Nothing
oConn.Close
Set oConn = Nothing
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 13, 2004 at 10:54 pm
It worked and its working fine now.
Thanks for your help.
and i really appreciate the support extended by you.
Thanks indeed.
Take Care
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply