• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • 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 SolomonSoftware DeveloperCommented:
Your error says Nam' expects the parameter @Related, which was not supplied. Just wondering if that is Nam' or Name?
0
 
Brad AbergDatabase AdministratorAuthor 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 SolomonSoftware DeveloperCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Brad AbergDatabase AdministratorAuthor Commented:
Ok, thank you.
0
 
Brad AbergDatabase AdministratorAuthor 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 JacksonSoftware EngineerCommented:
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 SolomonSoftware DeveloperCommented:
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 SolomonSoftware DeveloperCommented:
ooh! there! @Paul Jackson has the precise answer! :) Sorry I am not a VB person
0
 
Brad AbergDatabase AdministratorAuthor 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 AbergDatabase AdministratorAuthor 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 JacksonSoftware EngineerCommented:
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 SolomonSoftware DeveloperCommented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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