Solved

please guru- help me how to tweak code for fileupload

Posted on 2007-04-09
10
188 Views
Last Modified: 2008-02-01
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
_____________________________________________________________________
0
Comment
Question by:gianitoo
  • 6
  • 4
10 Comments
 
LVL 37

Expert Comment

by:samtran0331
ID: 18877904
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
 
LVL 1

Author Comment

by:gianitoo
ID: 18878403
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
 
LVL 1

Author Comment

by:gianitoo
ID: 18878426
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
 
LVL 1

Author Comment

by:gianitoo
ID: 18878457
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
 
LVL 1

Author Comment

by:gianitoo
ID: 18883483
please samtran0331 dont leave me alone
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 37

Expert Comment

by:samtran0331
ID: 18884115
lol...sorry...have some real work to do at the moment...will get back to this as soon as I can!
0
 
LVL 1

Author Comment

by:gianitoo
ID: 18884287
thanks man.  u still my idol
0
 
LVL 1

Author Comment

by:gianitoo
ID: 18918914
is this possible still?  or you think will not be possible?
0
 
LVL 37

Accepted Solution

by:
samtran0331 earned 500 total points
ID: 18925963
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
 
LVL 37

Expert Comment

by:samtran0331
ID: 18926043
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now