Solved

asp.NET: Error when downloading files from gridview

Posted on 2014-10-09
12
295 Views
Last Modified: 2014-10-09
I am seeing this error when I view the webpage from Visual Studio.  I've spent most of today trying to figure out why the @Related parameter is not supplied:When trying to download by clicking download button in gridview
The table structure is:table named tblFilesThe markup  is (The upload portion works thanks to help from experts-exchange.  Now I'm working on the download.  
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="BulkUploadPRIVATEPhysicianFiles.aspx.vb" MaintainScrollPositionOnPostback="true"
  Inherits="BulkUploadPRIVATEPhysicianFiles" MasterPageFile="~/MasterPages/PhysicianTwoPartMasterPage.master" %>

<asp:Content ID="Content1" ContentPlaceHolderID="headw" runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="cpMainContent" runat="Server">

  <table>
    <tr>
      <td>
        <h6>UPLOAD FILES</h6>
      </td>
      <td>&nbsp;</td>
     
    </tr>
    <tr>
      <td>
        <asp:FileUpload ID="FileUpload1" runat="server" AllowMultiple="true" /></td>
       
        <td></td>
          </tr>
    <tr>
       <td> <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="Upload" /></td>
      <td> <hr /></td>
      <td><hr style="border-style:ridge" /></td>
    </tr>
    <tr>
 

      <td>
     
        <asp:GridView ID="GridView1" runat="server" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
          RowStyle-BackColor="#A1DCF2" AlternatingRowStyle-BackColor="White" AlternatingRowStyle-ForeColor="#000"
          AutoGenerateColumns="false" AllowSorting="true">
          <Columns>
            <asp:BoundField DataField="Related" HeaderText="Related" />
            <asp:BoundField DataField="Name" HeaderText="File Name" />
            <%--<asp:BoundField DataField="WhenUploaded" HeaderText="When Uploaded" />--%>
            <asp:TemplateField ItemStyle-HorizontalAlign="Center">
              <ItemTemplate>
                <asp:LinkButton ID="lnkDownload" ForeColor="#ff6600" BorderColor="#0082c8" runat="server" Text="Download" OnClick="DownloadFile"
                  CommandArgument='<%# Eval("Id") %>'></asp:LinkButton>
              </ItemTemplate>
            </asp:TemplateField>
          </Columns>
        </asp:GridView>
      </td>
    </tr>
    <tr>
      <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="ReportGroupDataSource" 
        DataTextField="Related" DataValueField="Related" AutoPostBack="True" Visible="false"></asp:DropDownList>
      <td></td>
    </tr>
    <asp:SqlDataSource ID="ReportGroupDataSource" runat="server"
      SelectCommand="SELECT * FROM [ReportGroup] ORDER BY Related" 
      ConnectionString='<%$ ConnectionStrings:QuantumConnectionString3 %>'>
    </asp:SqlDataSource>
  </table>
  <div class="clear"></div>

  <div id="footer">
    <div id="footer-3">
      <span id="copyright">&copy; <%=DateTime.Now.Year %> - The Physician Alliance
        <br />
        <asp:LoginName ID="LoginName1" runat="server"
          FormatString="Logged in as: {0}" />
      </span>
    </div>
  </div>
</asp:Content>

Open in new window


And in the code behind:
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Partial Class BulkUploadPRIVATEPhysicianFiles
  Inherits System.Web.UI.Page

  Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
      BindGrid()
    End If
    '  Page.Header.Title = "Bulk Upload Private Physician Files Page"

  End Sub

  Private Sub BindGrid()
    Dim QuantumConnectionString3 As String = ConfigurationManager.ConnectionStrings("QuantumConnectionString3").ConnectionString
    Using con As New SqlConnection(QuantumConnectionString3)
      Using cmd As New SqlCommand()
        'cmd.CommandText = "select Id, Name, IsItPrivate, Related, WhenUploaded from tblFiles where IsItPrivate = 1 order by related,WhenUploaded, name"
        cmd.CommandText = "select Id, Name, IsItPrivate, Related from tblFiles where IsItPrivate = 1 order by related, name"
        cmd.Connection = con
        con.Open()
        GridView1.DataSource = cmd.ExecuteReader()
        'GridView1.Sort("Related", SortDirection.Ascending)
        GridView1.DataBind()
        con.Close()
      End Using
    End Using
  End Sub


  Protected Sub Upload(sender As Object, e As EventArgs)

    If FileUpload1.HasFiles Then
      If FileUpload1.PostedFiles.Count = 1 Then
        SaveFile(FileUpload1.PostedFiles.FirstOrDefault())
      Else
        Dim related As Guid = Guid.NewGuid
        For Each file In FileUpload1.PostedFiles
          SaveFile(file, related)
        Next
      End If
    End If

  End Sub

  Private Sub SaveFile(ByRef AFile As HttpPostedFile, Optional ByVal AGuid As Guid = Nothing)
    'Private Sub SaveFile(ByRef AFile As HttpPostedFile, Optional ByVal AGuid As Guid = Guid.Empty)

    Dim QUERY As String = "INSERT INTO tblFiles VALUES ( @Name, @ContentType, @IsItPrivate, @Data,@Related )"

    Dim filename As String = Path.GetFileName(AFile.FileName)
    Dim contentType As String = AFile.ContentType

    Using fileStream As Stream = AFile.InputStream
      Using br As New BinaryReader(fileStream)
        Dim bytes As Byte() = br.ReadBytes(DirectCast(fileStream.Length, Long))
        Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("QuantumConnectionString3").ConnectionString)
          Using command As New SqlCommand(QUERY, connection)
            command.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename
            command.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = contentType
            command.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes
            command.Parameters.Add("@IsItPrivate", SqlDbType.Bit).Value = 1
            If AGuid <> Guid.Empty Then
              command.Parameters.Add("@Related", SqlDbType.VarChar).Value = AGuid.ToString()
            Else
              command.Parameters.Add("@Related", SqlDbType.VarChar).Value = DBNull.Value
              Response.Write("oh-oh")
            End If

            connection.Open()
            command.ExecuteNonQuery()
            connection.Close()
          End Using
        End Using
      End Using
    End Using

  End Sub




  Protected Sub DownloadFile(sender As Object, e As EventArgs)
    Dim id As Integer = Integer.Parse(TryCast(sender, LinkButton).CommandArgument)
    Dim bytes As Byte()
    Dim fileName As String, contentType As String, Related As String
    Dim IsItPrivate As Boolean
    'Dim WhenUploaded As DateTime
    Dim QuantumConnectionString3 As String = ConfigurationManager.ConnectionStrings("QuantumConnectionString3").ConnectionString
    Using con As New SqlConnection(QuantumConnectionString3)
      Using cmd As New SqlCommand()
        'cmd.CommandText = "select Name, Data, ContentType, IsItPrivate, Related, WhenUploaded from tblFiles where Id=@Id and IsItPrivate=1 and Related = 1 order by Related, WhenUploaded,Name"
        cmd.CommandText = "select Name, Data, ContentType, IsItPrivate, Related from tblFiles where Id=@Id and IsItPrivate=1  order by Related,Name"
        cmd.Parameters.AddWithValue("@Id", id)
        cmd.Parameters.AddWithValue("@IsItPrivate", IsItPrivate)
        cmd.Parameters.AddWithValue("@Related", Related)
        'cmd.Parameters.AddWithValue("@WhenUploaded", WhenUploaded)
        cmd.Connection = con
        con.Open()
        Using sdr As SqlDataReader = cmd.ExecuteReader()
          sdr.Read()
          bytes = DirectCast(sdr("Data"), Byte())
          contentType = sdr("ContentType").ToString()
          fileName = sdr("Name").ToString()
          IsItPrivate = sdr("IsItPrivate").ToString()
          'Related = sdr("Related").ToString()
          ' WhenUploaded = sdr("WhenUploaded").ToString()


          If (Related.ToString()) IsNot Nothing Then
            cmd.Parameters.Add("@Related", SqlDbType.VarChar).Value = Related.ToString()
          Else
            cmd.Parameters.Add("@Related", SqlDbType.VarChar).Value = DBNull.Value
            Response.Write("oh-oh")
          End If




        End Using
        con.Close()
      End Using
    End Using
    Response.Clear()
    Response.Buffer = True
    Response.Charset = ""
    Response.Cache.SetCacheability(HttpCacheability.NoCache)
    Response.ContentType = contentType
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName)
    Response.BinaryWrite(bytes)
    Response.Flush()
    Response.End()
  End Sub


  Protected Sub Page_PreRender(sender As Object, e As EventArgs) Handles Me.PreRender
    Dim aa As Label = Page.Master.FindControl("a")
    aa.Text = "Upload/Download Private Reports"
  End Sub
End Class

Open in new window


And line 102 is:  
  Using sdr As SqlDataReader = cmd.ExecuteReader()

Open in new window

Sample data:
Sample data
Thanks in advance for your help.  Please let me know if you need anything else.  I hope to get this solved by tomorrow morning even if I have to stay up all night so I will respond promptly.
0
Comment
Question by:Brad Aberg
  • 5
  • 5
  • 2
12 Comments
 
LVL 1

Assisted Solution

by:John Solomon
John Solomon earned 50 total points
ID: 40371901
Your error says Nam' expects the parameter @Related, which was not supplied. Just wondering if that is Nam' or Name?
0
 

Author Comment

by:Brad Aberg
ID: 40371917
That confused me too.  It should be "Name".  Do you suspect a typo or a misplaced comma or somesuch?  I'm sorry if that's what it turns out to be I will do a search though my code.  If we both find it, I will still give you all the points.  Thank you!
0
 
LVL 1

Expert Comment

by:John Solomon
ID: 40371923
hmm. I was pretty quick to answer this. I apologize for not having the right answer.  Upon reviewing your code, I did not see any Nam occurrences.  I suspect that a mischievous syntax is the culprit. I'm not a VB programmer, but I'll tell you any progress if I made one. Thanks :)
0
 

Author Comment

by:Brad Aberg
ID: 40371936
Ok, thank you.
0
 

Author Comment

by:Brad Aberg
ID: 40371976
I found the problem:

The line:
      cmd.CommandText = "select Name, Data, ContentType, IsItPrivate, Related from tblFiles where Id=@Id and IsItPrivate=1  order by Related,Name"

Open in new window


does not have related as a parameter so I commented out the line
  cmd.Parameters.AddWithValue("@IsItPrivate", IsItPrivate)
        ' cmd.Parameters.AddWithValue("@Related", Related)

Open in new window

I showed line above it for context.
I would like to show my new error though (same download subroutine:
New error with subroutine
Your help will be greatly appreciated!
Latest code behind:
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Partial Class BulkUploadPRIVATEPhysicianFiles
  Inherits System.Web.UI.Page

  Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
      BindGrid()
    End If
    '  Page.Header.Title = "Bulk Upload Private Physician Files Page"

  End Sub

  Private Sub BindGrid()
    Dim QuantumConnectionString3 As String = ConfigurationManager.ConnectionStrings("QuantumConnectionString3").ConnectionString
    Using con As New SqlConnection(QuantumConnectionString3)
      Using cmd As New SqlCommand()
        'cmd.CommandText = "select Id, Name, IsItPrivate, Related, WhenUploaded from tblFiles where IsItPrivate = 1 order by related,WhenUploaded, name"
        cmd.CommandText = "select Id, Name, IsItPrivate, Related from tblFiles where IsItPrivate = 1 order by related, name"
        cmd.Connection = con
        con.Open()
        GridView1.DataSource = cmd.ExecuteReader()
        'GridView1.Sort("Related", SortDirection.Ascending)
        GridView1.DataBind()
        con.Close()
      End Using
    End Using
  End Sub


  Protected Sub Upload(sender As Object, e As EventArgs)

    If FileUpload1.HasFiles Then
      If FileUpload1.PostedFiles.Count = 1 Then
        SaveFile(FileUpload1.PostedFiles.FirstOrDefault())
      Else
        Dim related As Guid = Guid.NewGuid
        For Each file In FileUpload1.PostedFiles
          SaveFile(file, related)
        Next
      End If
    End If
    Response.Redirect("BulkUploadPRIVATEPhysicianFiles.aspx")
  End Sub

  Private Sub SaveFile(ByRef AFile As HttpPostedFile, Optional ByVal AGuid As Guid = Nothing)
    'Private Sub SaveFile(ByRef AFile As HttpPostedFile, Optional ByVal AGuid As Guid = Guid.Empty)

    Dim QUERY As String = "INSERT INTO tblFiles VALUES ( @Name, @ContentType, @IsItPrivate, @Data,@Related )"

    Dim filename As String = Path.GetFileName(AFile.FileName)
    Dim contentType As String = AFile.ContentType

    Using fileStream As Stream = AFile.InputStream
      Using br As New BinaryReader(fileStream)
        Dim bytes As Byte() = br.ReadBytes(DirectCast(fileStream.Length, Long))
        Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("QuantumConnectionString3").ConnectionString)
          Using command As New SqlCommand(QUERY, connection)
            command.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename
            command.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = contentType
            command.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes
            command.Parameters.Add("@IsItPrivate", SqlDbType.Bit).Value = 1
            If AGuid <> Guid.Empty Then
              command.Parameters.Add("@Related", SqlDbType.VarChar).Value = AGuid.ToString()
            Else
              command.Parameters.Add("@Related", SqlDbType.VarChar).Value = DBNull.Value
              Response.Write("oh-oh")
            End If

            connection.Open()
            command.ExecuteNonQuery()
            connection.Close()
          End Using
        End Using
      End Using
    End Using

  End Sub




  Protected Sub DownloadFile(sender As Object, e As EventArgs)
    Dim id As Integer = Integer.Parse(TryCast(sender, LinkButton).CommandArgument)
    Dim bytes As Byte()
    Dim fileName As String, contentType As String, Related As String
    Dim IsItPrivate As Boolean
    'Dim WhenUploaded As DateTime
    Dim QuantumConnectionString3 As String = ConfigurationManager.ConnectionStrings("QuantumConnectionString3").ConnectionString
    Using con As New SqlConnection(QuantumConnectionString3)
      Using cmd As New SqlCommand()
        'cmd.CommandText = "select Name, Data, ContentType, IsItPrivate, Related, WhenUploaded from tblFiles where Id=@Id and IsItPrivate=1 and Related = 1 order by Related, WhenUploaded,Name"
        cmd.CommandText = "select Name, Data, ContentType, IsItPrivate, Related from tblFiles where Id=@Id and IsItPrivate=1  order by Related,Name"
        cmd.Parameters.AddWithValue("@Id", id)
        cmd.Parameters.AddWithValue("@IsItPrivate", IsItPrivate)
        ' cmd.Parameters.AddWithValue("@Related", Related)
        'cmd.Parameters.AddWithValue("@WhenUploaded", WhenUploaded)
        cmd.Connection = con
        con.Open()
        Using sdr As SqlDataReader = cmd.ExecuteReader()
          sdr.Read()
          bytes = DirectCast(sdr("Data"), Byte())
          contentType = sdr("ContentType").ToString()
          fileName = sdr("Name").ToString()
          IsItPrivate = sdr("IsItPrivate").ToString()
          'Related = sdr("Related").ToString()
          ' WhenUploaded = sdr("WhenUploaded").ToString()


          'If (Related.ToString()) IsNot Nothing Then
          cmd.Parameters.Add("@Related", SqlDbType.VarChar).Value = Related.ToString()
          'Else
          '  cmd.Parameters.Add("@Related", SqlDbType.VarChar).Value = DBNull.Value

          'End If




        End Using
        con.Close()
      End Using
    End Using
    Response.Clear()
    Response.Buffer = True
    Response.Charset = ""
    Response.Cache.SetCacheability(HttpCacheability.NoCache)
    Response.ContentType = contentType
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName)
    Response.BinaryWrite(bytes)
    Response.Flush()
    Response.End()
  End Sub


  Protected Sub Page_PreRender(sender As Object, e As EventArgs) Handles Me.PreRender
    Dim aa As Label = Page.Master.FindControl("a")
    aa.Text = "Upload/Download Private Reports"
  End Sub
End Class

Open in new window

0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 450 total points
ID: 40371989
You've commented out line 108 where the related parameter is assigned to a local variable which is then being used in line 113.
So your problem is that the Related variable I not defined on line 113 before you call the .ToString() method.
Did you mean to comment out line 113 as well?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Expert Comment

by:John Solomon
ID: 40371996
Congratulations! the first exception would have pointed me to syntax errors. but yeah! it is good to know you've found it.
 
hmmm.... the second error is a null object.

I see that you assign the Related parameter to a variable. Then the error occurs on line 113.

I just dont know what this ' means in front of a code line.

could this line be throwing the error?  
 'Related = sdr("Related").ToString()

Open in new window


what does ' do? Im not a VB programmer but I think that's a comment out?
0
 
LVL 1

Expert Comment

by:John Solomon
ID: 40371999
ooh! there! @Paul Jackson has the precise answer! :) Sorry I am not a VB person
0
 

Author Comment

by:Brad Aberg
ID: 40372051
Paul Jackson:  Thank you!  Yes, you're exactly right.  That solved it!!

John Solomon:  Thank you for all your posts.  The single quote in VB is to comment out a line.  Do you program in C#  or something else?  I can code some in SQL but this .NET has humbled me because I'm trying to do things that are too advanced for me at this point.
0
 

Author Closing Comment

by:Brad Aberg
ID: 40372059
Paul - I gave you 450 and John 50.  Paul, your answer was correct but I wanted to reward John for helping and posting.  I hope this ok.  I'm a newbie at EE and I'm not sure what the etiquette is.  Thanks again!
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 40372062
That's fine Brad, the etiquette is for you to decide who gets what points depending on how much they have helped you or pointed you in the right direction. I would say the share of points in this case is perfectly fine.
0
 
LVL 1

Expert Comment

by:John Solomon
ID: 40372069
Brad, I appreciate your kindness for sharing some points to me. Paul, Thank you for your kind understanding too! I am a C# developer and a recent graduate who just recently got a full time job as a junior software developer. I am trying to continuously learn and create some presence on the web. Thank you all! :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

757 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

21 Experts available now in Live!

Get 1:1 Help Now