Solved

please guru- help me how to tweak code for fileupload

Posted on 2007-04-09
10
191 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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
 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

821 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