please guru- help me how to tweak code for fileupload

I have created a db in sql server 2005 and when I insert the file from asp.net app it inserts wonderful and saves the file in the db.  My db has 3 columns that are important for  document.  
Filename
Contenttype
Fylebytes

With the code below i can easily click on a link and the file opens in word.  
somehow i would like to add a case stament where
if contenttype in my db  =application/msword   then use  context.Response.ContentType = "application/msword"
if  contenttype=application/vnd.ms-excel   then use context.Response.ContentType = "application/vnd.ms-excel"
if contenttype=application/pdf  then use  context.Response.ContentType = "application/pdf"
else use context.Response.ContentType = "application/msword"

______________code to open in word__________________________

Public Class FileHandler
    Implements IHttpHandler
 
    Const conString As String = "Data Source=xxxxInitial Catalog=JMI;Persist Security Info=True;User ID=xxx;Password=xxxx"
 
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        context.Response.ContentType = "application/msword"
 
        Dim con As SqlConnection = New SqlConnection(conString)
        Dim cmd As SqlCommand = New SqlCommand("SELECT FileBytes FROM dbo.jobreferral WHERE Id=@Id", con)
        cmd.Parameters.AddWithValue("@Id", context.Request("Id"))
        Using con
            con.Open()
            Dim file() As Byte = CType(cmd.ExecuteScalar(), Byte())
            context.Response.BinaryWrite(file)
        End Using
    End Sub
 
    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property
 
End Class
_____________________________________________________________________
LVL 1
gianitooAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

samtran0331Commented:
1. add contenttype to your sql statement.
2. you can't use executescalar, use executereader to return both columns (contenttype and filebytes)
then the contenttype will be available for your select/case statement

the below is a bit sloppy, but it will be something like:

Dim cmd As SqlCommand = New SqlCommand("SELECT Contenttype, FileBytes FROM dbo.jobreferral WHERE Id=@Id", con)
        cmd.Parameters.AddWithValue("@Id", context.Request("Id"))
dim dr as datareader
        Using con
            con.Open()
dr = cmd.executereader
if dr.hasrows
dim strContentType as string = dr("Contenttype")
            Dim file() As Byte = CType(dr("FileBytes"),Byte())
select case strContentType
case "application/msword"
  context.Response.ContentType = "application/msword"
case "something else"
end select  
            context.Response.BinaryWrite(file)
end if
        End Using

0
gianitooAuthor Commented:
i am getting this error
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30154: Class 'FileHandler' must implement 'ReadOnly Property IsReusable() As Boolean' for interface 'System.Web.IHttpHandler'. Implementing property must have matching 'ReadOnly' or 'WriteOnly' specifiers.

Source Error:

 

Line 7:  
Line 8:  Public Class FileHandler
Line 9:      Implements IHttpHandler
Line 10:  
Line 11:     Const conString As String = "Data Source=XXXXXX;Initial Catalog=JMI;Persist Security Info=True;User ID=cholo;Password=pele@indy"
 
0
gianitooAuthor Commented:
i took that part out and i got this

Compiler Error Message: BC30451: Name 'con' is not declared.

Source Error:

 

Line 11:     Const conString As String = "Data Source=JMI-DB03;Initial Catalog=JMI;Persist Security Info=True;User ID=cholo;Password=pele@indy"
Line 12:  
Line 13:     Dim cmd As SqlCommand = New SqlCommand("SELECT Contenttype, FileBytes FROM dbo.jobreferral WHERE Id=@Id", con)
Line 14:         cmd.Parameters.AddWithValue("@Id", context.Request("Id"))
Line 15:     Dim dr As datareader
 
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

gianitooAuthor Commented:
so far this is my code

<%@ WebHandler Language="VB" Class="FileHandler" %>
 
Imports System
Imports System.Web
Imports System.Data
Imports System.Data.SqlClient
 
Public Class FileHandler

 
    Const conString As String = "Data Source=JMI-DB03;Initial Catalog=JMI;Persist Security Info=True;User ID=cholo;Password=pele@indy"
 
    Dim cmd As SqlCommand = New SqlCommand("SELECT Contenttype, FileBytes FROM dbo.jobreferral WHERE Id=@Id", con)
        cmd.Parameters.AddWithValue("@Id", context.Request("Id"))
    Dim dr As datareader
        Using con
            con.Open()
dr = cmd.executereader
if dr.hasrows
    Dim strContentType As String = dr("Contenttype")
    Dim file() As Byte = CType(dr("FileBytes"), Byte())
select case strContentType
case "application/msword"
  context.Response.ContentType = "application/msword"
case "application/vnd.ms-excel"
context.Response.ContentType = "application/vnd.ms-excel"
    case "application/pdf"
context.Response.ContentType = "application/pdf"
    else
      context.Response.ContentType = "application/msword"

end select  
            context.Response.BinaryWrite(file)
end if
        End Using
 
End Class
0
gianitooAuthor Commented:
please samtran0331 dont leave me alone
0
samtran0331Commented:
lol...sorry...have some real work to do at the moment...will get back to this as soon as I can!
0
gianitooAuthor Commented:
thanks man.  u still my idol
0
gianitooAuthor Commented:
is this possible still?  or you think will not be possible?
0
samtran0331Commented:
this works when connected to a Northwind database.
think about this example with your data....
where i have the column CustomerID:
                    CustomerId = CType(dr("CustomerID"), String)

yours would be the contenttype column
and the case statement I have would be where you set
context.Response.ContentType

example:


    Const conString As String = "Data Source=dotnetdevpc01\sql2000;Initial Catalog=Northwind;Integrated Security=True"
    Public Sub SetContentType()
        Dim con As New SqlConnection(conString)
        Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM CUSTOMERS", con)
        Dim dr As SqlDataReader
        Using con
            con.Open()
            dr = cmd.ExecuteReader
            If dr.HasRows Then
                Dim CustomerId As String = ""
                While dr.Read
                    CustomerId = CType(dr("CustomerID"), String)
                    Select Case CustomerId
                        Case "ALFKI"
                            Response.Write("alfki row<br/>")
                        Case "BOLID"
                            Response.Write("bolid row<br/>")
                        Case Else
                            Response.Write("other row<br/>")
                    End Select
                End While
            End If
        End Using
    End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
samtran0331Commented:
i doubt this will work for you without your adjustments, but the logic should be something like:

    Const conString As String = "Data Source=xxxxInitial Catalog=JMI;Persist Security Info=True;User ID=xxx;Password=xxxx"
    Public Sub SetContentType()
        Dim con As New SqlConnection(conString)
        Dim cmd As SqlCommand = New SqlCommand("SELECT Contenttype, FileBytes FROM dbo.jobreferral WHERE Id=@Id", con)
        cmd.Parameters.AddWithValue("@Id", Context.Request("Id"))
        Dim dr As SqlDataReader

        Using con
            con.Open()
            dr = cmd.ExecuteReader
            If dr.HasRows Then
                Dim strContentType As String = ""
                While dr.Read
                    strContentType = CType(dr("Contenttype"), String)
                    Select Case strContentType
                        Case "application/msword"
                            Context.Response.ContentType = "application/msword"
                        Case "application/vnd.ms-excel"
                            Context.Response.ContentType = "application/vnd.ms-excel"
                        Case "application/pdf"
                            Context.Response.ContentType = "application/pdf"
                    End Select
                    Dim file() As Byte = CType(dr("", Byte())
                    Context.Response.BinaryWrite(file())
                    Exit Sub
                End While
            End If
        End Using
    End Sub
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.