Binary files in DB : set name on retrieve

Hello,

I have a table that stores binary files. When I serve them up to the user, I call the following page (serveDocument.aspx?DocumentID=xxx) which holds only the following code and pass the document ID:

Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load
Dim DocumentIDAs Integer = Convert.ToInt32(Request.QueryString("DocumentID"))
'Connect to the database and bring back the image contents & MIME type for the specified picture Using myConnectionAs New SqlConnection(ConfigurationManager.ConnectionStrings("kelly_lmConnectionString1").ConnectionString)
Const SQLAs String ="SELECT [Content_Type], [Document_Data] FROM [lm_Service_Detail_Documents] WHERE [Document_id] = @.Document_id"Dim myCommandAs New SqlCommand(SQL, myConnection)
 myCommand.Parameters.AddWithValue("@.Document_id", DocumentID)
 myConnection.Open()
Dim myReaderAs SqlDataReader = myCommand.ExecuteReader
If myReader.ReadThen Response.ContentType = myReader("Content_Type").ToString()
 Response.BinaryWrite(myReader("Document_Data"))
End If myReader.Close() myConnection.Close()End Using
End Sub

It works perfectly. But heres the thing: I want users to be able to right click on the link and chose 'Save Target As'. In IE 7, this works fine. In FireFox it saves the document as 'serveDocument.aspx'. Is there any way toinjectthe filename that I want to save the document as?

Thank you.

Add this Header also

context.Response.ContentType = "application/octet-stream";
context.Response.AddHeader("Content-Disposition", "attachment; filename=\"" + filename+".txt" + "\"");

------------------

Mark as Answer if you feel

|||

What does 'context' refer to?

And, are you saying I shouldn't set the ContentType to the actual type of file?

|||

REmove the context

it uses to html encode when you are using http handler

|||

Well, that partially works. When a user clicks the linkthen saves the file, the correct name is present. But, my issue isn't that... I want the user to be able to right click the link and 'Save Link As...' with the correct filename. When I do that, it still says 'documentServe.aspx'.

|||

Hi,

But heres the thing: I want users to be able to right click on the link and chose 'Save Target As'. In IE 7, this works fine. In FireFox it saves the document as 'serveDocument.aspx'.

From your description, it seems that when you right click on the link and choose the 'save target as' in IE7, you can got the whole url with parameters, but not in FireFox, right?

If so, I'm afraid that the behavior is related to the Explorer, and based on my knowledge, there's not any workaround to fix the problem, since working mechanism for IE and FireFox is just not the same.

Thanks.

sql

Binary column

We have a binary column that stores web passwords.
What is the proper way to access this column?
The sql below is blowing up!
UPDATE AppServices.dbo.DBUsers
SET Password = 'MyPassword'
WHERE UserId ='MyLogin'Here is one way to accomplish this task.
UPDATE AppServices.dbo.DBUsers
SET Password = pwdencrypt('MyPassword')
WHERE UserId ='MyLogin'
pwdencrypt and pwdcompare are two undocumented SQL functions.
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:E269D86B-E84E-45CE-8E33-81D483391080@.microsoft.com...
> We have a binary column that stores web passwords.
> What is the proper way to access this column?
> The sql below is blowing up!
> UPDATE AppServices.dbo.DBUsers
> SET Password = 'MyPassword'
> WHERE UserId ='MyLogin'

Better Way To Handle The Code?

Hi experts,
I have a table which stores data in multi-rows basis for a particular
record. The structure of the table is as exhibit:
CREATE TABLE [dbo].[Table_Header] (
[Rec_Id] [numeric](18, 0) NOT NULL ,
[Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
Example data is shown as follows:
Rec_Id Param_Name Param_Value
1 Product TV
1 Category Electrical
2 Product Telephone
2 Category Electrical
3 Product TV
3 Category Electrical
In case I would like to filter out those records (Rec_Id) where Product = TV
and Category = Electrical, one way I can think of using T-SQL is:
Select A.Rec_Id From Table_Header A, Table_Header B
Where A.Rec_Id = B.Rec_Id And
A.Param_Name = 'Product' And
A.Param_Value = 'TV' And
B.Param_Name = 'Category' And
B.Param_Value = 'Electrical'
The Select statement above returns me results: 1 and 3. But it will grow to
be quite complicated if I have many parameters to filter in my select
statement. Any better way to handle this coding?
ThanksLBT
What is a PK on your table?
Do you have any indexes defined on the table?
"LBT" <LBT@.discussions.microsoft.com> wrote in message
news:A1E1B426-7986-4535-BF4B-BDD35532F273@.microsoft.com...
> Hi experts,
> I have a table which stores data in multi-rows basis for a particular
> record. The structure of the table is as exhibit:
> CREATE TABLE [dbo].[Table_Header] (
> [Rec_Id] [numeric](18, 0) NOT NULL ,
> [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> Example data is shown as follows:
> Rec_Id Param_Name Param_Value
> 1 Product TV
> 1 Category Electrical
> 2 Product Telephone
> 2 Category Electrical
> 3 Product TV
> 3 Category Electrical
> In case I would like to filter out those records (Rec_Id) where Product =
TV
> and Category = Electrical, one way I can think of using T-SQL is:
> Select A.Rec_Id From Table_Header A, Table_Header B
> Where A.Rec_Id = B.Rec_Id And
> A.Param_Name = 'Product' And
> A.Param_Value = 'TV' And
> B.Param_Name = 'Category' And
> B.Param_Value = 'Electrical'
> The Select statement above returns me results: 1 and 3. But it will grow
to
> be quite complicated if I have many parameters to filter in my select
> statement. Any better way to handle this coding?
> Thanks
>
>
>
>|||Hi
You can normalize your table.
You can have 2 tables here Product and Category
Product:
RecID | Product_Name
1 | TV
2 | Telephone
3 | TV
Category:
RecID | Category_Name
1 | Electrical
2 | Electrical
3 | Electrical
Based on the req. u can query as
Select Category.Rec_id
FROM Category
INNER JOIN Product ON Product.Rec_id = Category.Rec_id
AND Product_Name IN ('TV')
WHERE Category_Name IN ('Electrical')
This will solve the purpose
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"LBT" wrote:

> Hi experts,
> I have a table which stores data in multi-rows basis for a particular
> record. The structure of the table is as exhibit:
> CREATE TABLE [dbo].[Table_Header] (
> [Rec_Id] [numeric](18, 0) NOT NULL ,
> [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> Example data is shown as follows:
> Rec_Id Param_Name Param_Value
> 1 Product TV
> 1 Category Electrical
> 2 Product Telephone
> 2 Category Electrical
> 3 Product TV
> 3 Category Electrical
> In case I would like to filter out those records (Rec_Id) where Product =
TV
> and Category = Electrical, one way I can think of using T-SQL is:
> Select A.Rec_Id From Table_Header A, Table_Header B
> Where A.Rec_Id = B.Rec_Id And
> A.Param_Name = 'Product' And
> A.Param_Value = 'TV' And
> B.Param_Name = 'Category' And
> B.Param_Value = 'Electrical'
> The Select statement above returns me results: 1 and 3. But it will grow t
o
> be quite complicated if I have many parameters to filter in my select
> statement. Any better way to handle this coding?
> Thanks
>
>
>
>|||> I have a table which stores data in multi-rows basis for a particular

> record
Why? That's usually a really poor design. There are better ways to
represent types and subtypes. Example:
CREATE TABLE Products (sku INTEGER PRIMARY KEY, product_type_code
CHAR(2) NOT NULL CHECK (product_type_code IN ('EL','EN','SP')),
productname VARCHAR(40) NOT NULL UNIQUE, UNIQUE (sku,product_type_code)
/* Columns common to all products */)
CREATE TABLE EntertainmentProducts (sku INTEGER PRIMARY KEY,
product_type_code CHAR(2) DEFAULT 'EN' NOT NULL CHECK
(product_type_code='EN'), medium CHAR(3) NOT NULL CHECK (medium IN
('DVD','VHS','CD')), FOREIGN KEY (sku, product_type_code) REFERENCES
Products (sku, product_type_code) /* Columns specific to this
subtype... */)
CREATE TABLE ElectronicProducts (sku INTEGER PRIMARY KEY,
product_type_code CHAR(2) DEFAULT 'EL' NOT NULL CHECK
(product_type_code='EL'), FOREIGN KEY (sku, product_type_code)
REFERENCES Products (sku, product_type_code) /* Columns specific to
this subtype */)
CREATE TABLE SportsProducts (sku INTEGER PRIMARY KEY, product_type_code
CHAR(2) DEFAULT 'SP' NOT NULL CHECK (product_type_code='SP'), FOREIGN
KEY (sku, product_type_code) REFERENCES Products (sku,
product_type_code) /* Columns specific to this subtype */)

> it will grow to
> be quite complicated if I have many parameters to filter in my select

> statement.
Correct! That's why it's better to go for a more normalized approach.
David Portas
SQL Server MVP
--|||The PKs are Rec_Id and Param_Name
"Uri Dimant" wrote:

> LBT
> What is a PK on your table?
> Do you have any indexes defined on the table?
> "LBT" <LBT@.discussions.microsoft.com> wrote in message
> news:A1E1B426-7986-4535-BF4B-BDD35532F273@.microsoft.com...
> ,
> TV
> to
>
>|||Thanks for the suggestion. In fact, there will be a lot of parameters in my
actual application where I think normalization is not my exact solution
(sorry that not to make my question clear in previous post). And because of
the norm that the parameters list will change from time to time, I need to
store data in the mentioned manner (or I will have to change table structure
everytime parameters change).
"Chandra" wrote:
> Hi
> You can normalize your table.
> You can have 2 tables here Product and Category
> Product:
> RecID | Product_Name
> 1 | TV
> 2 | Telephone
> 3 | TV
> Category:
> RecID | Category_Name
> 1 | Electrical
> 2 | Electrical
> 3 | Electrical
> Based on the req. u can query as
> Select Category.Rec_id
> FROM Category
> INNER JOIN Product ON Product.Rec_id = Category.Rec_id
> AND Product_Name IN ('TV')
> WHERE Category_Name IN ('Electrical')
> This will solve the purpose
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "LBT" wrote:
>|||1. What objects are being stored in this table? It looks like a generic
System table - holding attribute bnames and values, for a database. I would
recommend you get a good book on Relational Data modeling, and take a quick
look at it. You might consider normalizing the data structure a bit..
Instead of having a Param_Name column in a table, you might want to create a
Products Table and a Categorys Table, but I don;t know what exactly you are
doing so it's hard to tell..
"LBT" wrote:

> Hi experts,
> I have a table which stores data in multi-rows basis for a particular
> record. The structure of the table is as exhibit:
> CREATE TABLE [dbo].[Table_Header] (
> [Rec_Id] [numeric](18, 0) NOT NULL ,
> [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> Example data is shown as follows:
> Rec_Id Param_Name Param_Value
> 1 Product TV
> 1 Category Electrical
> 2 Product Telephone
> 2 Category Electrical
> 3 Product TV
> 3 Category Electrical
> In case I would like to filter out those records (Rec_Id) where Product =
TV
> and Category = Electrical, one way I can think of using T-SQL is:
> Select A.Rec_Id From Table_Header A, Table_Header B
> Where A.Rec_Id = B.Rec_Id And
> A.Param_Name = 'Product' And
> A.Param_Value = 'TV' And
> B.Param_Name = 'Category' And
> B.Param_Value = 'Electrical'
> The Select statement above returns me results: 1 and 3. But it will grow t
o
> be quite complicated if I have many parameters to filter in my select
> statement. Any better way to handle this coding?
> Thanks
>
>
>
>|||I think I have provided a confusing example. My mistake :)
Actually the table structure is designed in such a way to store variety of
data structure sent in by user. Say for example, user might send in "Rec_Id
[1], Product [TV], Category [Electrical]", "Rec_Id [2], Product [Telephone],
Category [Electrical]", etc.
At later time, user can send in data structure with new add-in parameter(s)
- "Rec_Id [3], Product [TV], Category [Electrical], Power [1000kW], Color
[Black]". The challenge is I cannot change the table structure (this is the
dynamic solution requested by customer) everytime new parameter is being
introduced. With the table design, I can simply store "Power" into Param_Nam
e
and "1000kW" into Param_Value (no adding of column to table structure is
allowed).
So with this table design, the only way I can perform records filtering (the
filtering parameters however will be fixed which are Product and Category in
this case) is as stated in my post? I just wonder whether there is any bette
r
way to perform the query.
In fact, I have to create and configure few reference/definition tables so
that my application will be dynamic enough to cater for this requirement. Bu
t
it will be lengthy to explain the entire situation.
Thanks
"CBretana" wrote:
> 1. What objects are being stored in this table? It looks like a generic
> System table - holding attribute bnames and values, for a database. I wou
ld
> recommend you get a good book on Relational Data modeling, and take a qui
ck
> look at it. You might consider normalizing the data structure a bit..
> Instead of having a Param_Name column in a table, you might want to create
a
> Products Table and a Categorys Table, but I don;t know what exactly you a
re
> doing so it's hard to tell..
> "LBT" wrote:
>|||In a way, you are in the dilemma which often results from trying to achieve
conflicting objectives...
The entire concept of Relational Databases is, in one sense (I need to be
careful here) designed to allow you to "Structure" your data so that it
closely matches the real world data objects or abstractions which represent
those objects, in order to allow the kind of data "processing" (like
arbitrary filtering, sorting, etc. that you are trying to accomplish. Old
style (pre-Relational) Database systems were comparitively inadeguate at
these sorts of things, *because* the data was not stored in a structured
relational way.
But you are *Trying* to store your data in an unstructured way to allow as
much flexibility in what kind of data is allowed in to the database. These
two objectives are fundamentally inconsistent, and that is one reason why yo
u
are experiencing difficulties
"LBT" wrote:
> I think I have provided a confusing example. My mistake :)
> Actually the table structure is designed in such a way to store variety of
> data structure sent in by user. Say for example, user might send in "Rec_I
d
> [1], Product [TV], Category [Electrical]", "Rec_Id [2], Product [Telephone],
> Category [Electrical]", etc.
> At later time, user can send in data structure with new add-in parameter(s
)
> - "Rec_Id [3], Product [TV], Category [Electrical], Power [1000kW], Color
> [Black]". The challenge is I cannot change the table structure (this is the
> dynamic solution requested by customer) everytime new parameter is being
> introduced. With the table design, I can simply store "Power" into Param_N
ame
> and "1000kW" into Param_Value (no adding of column to table structure is
> allowed).
> So with this table design, the only way I can perform records filtering (t
he
> filtering parameters however will be fixed which are Product and Category
in
> this case) is as stated in my post? I just wonder whether there is any bet
ter
> way to perform the query.
> In fact, I have to create and configure few reference/definition tables so
> that my application will be dynamic enough to cater for this requirement.
But
> it will be lengthy to explain the entire situation.
> Thanks
> "CBretana" wrote:
>|||If the customer DEMANDS a design like this then they should understand
and accept its weaknesses with regard to data integrity, performance
and increased complexity. However, I would always discuss the
alternatives with them first. Change control for adding new attributes
should be their friend not their enemy. If they are sing a zero
maintenance solution then tell them to forget it - of course no such
thing exists - or at least when a legacy application reaches that stage
of maturity then it's probably already in need of replacement.
The reason that proper change control is required is that users
generally make very poor database designers. If you allow users to
decide the format of business data you will lose any integrity and
future value therein. Do you really expect users to comprehenend and
analyze functional dependencies and standardise and conform attributes
in the system? If they were capable of that then they must be database
pros in which case they wouldn't need this kind of kludge. Corporate
data is too important to be left to users to manage.
David Portas
SQL Server MVP
--

Better query?

I have a table from a 3rd party application that stores the time that
events occurred as the number of seconds since Jan 1 1970. I want to
perform an aggregate function on the values stored in each event, grouped
by date and present the sorted results. Here's what I have:
SELECT
DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') AS PointDate,
Avg(Value)
FROM Data
GROUP BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM')
ORDER BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') ASC
Is there an easier way to write this without duplicating the DateAdd
calculation three times' I can't use Stored Procedures (I'm not allowed
to modify the database in any way.)
-mdb>> have a table from a 3rd party application that stores the time that even
ts occurred as the number of seconds since 1970-01-01 <<
Oh yeah, that makes sense :) !! I always hated the term "legacy data"
when what we meant was "family curse from Hell!" instead.
Build a table like this:
CREATE TABLE FoobarDates
(foobar_date CHAR (10) NOT NULL,
start_second INTEGER NOT NULL,
finish_second INTEGER NOT NULL
PRIMARY KEY(start_second, finish_second ));
1) Avoid math in SQL-- it is designed to do JOINs. not algebra.
2) An ORDER BY on a SELECT is **meaningless** -- read any basic SQL
book. Indexes matter.
3) Use a spreadsheet to do the computations for the table. Easy and
faster than code.|||you can use a derived table Michael...
SELECT PointDate, Avg_Value = Avg( Value )
FROM (
SELECT PointDate, DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
Value
FROM Data ) AS dt
GROUP BY PointDate
ORDER BY PointDate ASC
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Michael Bray" <mbray@.makeDIntoDot_ctiusaDcom> wrote in message
news:Xns9739BF2454556mbrayctiusacom@.207.46.248.16...
>I have a table from a 3rd party application that stores the time that
> events occurred as the number of seconds since Jan 1 1970. I want to
> perform an aggregate function on the values stored in each event, grouped
> by date and present the sorted results. Here's what I have:
> SELECT
> DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') AS PointDate,
> Avg(Value)
> FROM Data
> GROUP BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM')
> ORDER BY DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM') ASC
> Is there an easier way to write this without duplicating the DateAdd
> calculation three times' I can't use Stored Procedures (I'm not allowed
> to modify the database in any way.)
> -mdb|||Sorry, that should be...
SELECT PointDate, Avg_Value = Avg( Value )
FROM (
SELECT PointDate = DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
Value
FROM Data ) AS dt
GROUP BY PointDate
ORDER BY PointDate ASC
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eaoeBd4CGHA.532@.TK2MSFTNGP15.phx.gbl...
> you can use a derived table Michael...
> SELECT PointDate, Avg_Value = Avg( Value )
> FROM (
> SELECT PointDate, DateAdd(dd, Time/86400, '1/1/1970 12:00:00 AM'),
> Value
> FROM Data ) AS dt
> GROUP BY PointDate
> ORDER BY PointDate ASC
> Tony
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Michael Bray" <mbray@.makeDIntoDot_ctiusaDcom> wrote in message
> news:Xns9739BF2454556mbrayctiusacom@.207.46.248.16...
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in
news:1135732983.997588.94360@.o13g2000cwo.googlegroups.com:
> Oh yeah, that makes sense :) !! I always hated the term "legacy data"
> when what we meant was "family curse from Hell!" instead.
As a matter of fact, this is NOT legacy Data. In fact, it is an
application that would not have existed 10 years ago, because the
technology that is represented by the data in the database didn't exist.
(I'm not going to mention the vendor, but it is an IP Telephony monitoring
system.)
The rest of your post sounds like a troll, so I'm not going to comment,
except for this comment.
-mdb|||"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in
news:eaoeBd4CGHA.532@.TK2MSFTNGP15.phx.gbl:

> you can use a derived table Michael...
>
Wow... you know I had always tried to do something similar, but I never
could get it to work because I didn't know that I had to include the 'AS
<alias>'. Thanks! Now I get to go back and re-write a bunch of stored
procedures in other databases I've worked on that I used memory tables in!
:)
-mdb