Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

asp.NET: Error when downloading files from gridview

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
Brad Aberg
Asked:
Brad Aberg
  • 5
  • 5
  • 2
2 Solutions
 
John SolomonCommented:
Your error says Nam' expects the parameter @Related, which was not supplied. Just wondering if that is Nam' or Name?
0
 
Brad AbergAuthor Commented:
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
 
John SolomonCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Brad AbergAuthor Commented:
Ok, thank you.
0
 
Brad AbergAuthor Commented:
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
 
Paul JacksonCommented:
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
 
John SolomonCommented:
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
 
John SolomonCommented:
ooh! there! @Paul Jackson has the precise answer! :) Sorry I am not a VB person
0
 
Brad AbergAuthor Commented:
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
 
Brad AbergAuthor Commented:
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
 
Paul JacksonCommented:
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
 
John SolomonCommented:
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now