Solved

error when trying to use guid to mark groups of files as belonging together when uploaded together

Posted on 2014-10-08
10
141 Views
Last Modified: 2014-10-09
I am using asp.net, (VB but C# code ok for answer if you prefer), to write a web page that allows a user to upload multiple files and mark them so that they c an be grouped together/filtered for downloading.

The code behind for this page is:
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.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 = Guid.Empty)

    Const 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("constr").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
            Else
              command.Parameters.Add("@Related", SqlDbType.VarChar).Value = DBNull
              Response.Write("oh-oh")
            End If

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

  End Sub




  Protected Sub UploadOrig(sender As Object, e As EventArgs)
    If FileUpload1.HasFiles Then
      For Each file In FileUpload1.PostedFiles
        Using fs As Stream = file.InputStream
          Dim filename As String = Path.GetFileName(file.FileName)
          Dim contentType As String = file.ContentType
          Using br As New BinaryReader(fs)
            Dim bytes As Byte() = br.ReadBytes(DirectCast(fs.Length, Long))
            'Dim WhenUploaded As DateTime = DateTime.ParseExact(System.DateTime.Now, "yyyy-MM-dd", Nothing)
            Dim WhenUploaded As DateTime = System.DateTime.Now
            Dim QuantumConnectionString3 As String = ConfigurationManager.ConnectionStrings("QuantumConnectionString3").ConnectionString
            Using con As New SqlConnection(QuantumConnectionString3)
              Dim query As String = "insert into tblFiles values (@Name, @ContentType,@IsItPrivate, @Data, @Related, @WhenUploaded)"
              Using cmd As New SqlCommand(query)
                cmd.Connection = con
                cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename
                cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = contentType
                cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes
                cmd.Parameters.Add("@IsItPrivate", SqlDbType.Bit).Value = 1
                'cmd.Parameters.Add("@Related", SqlDbType.VarChar).Value = TryCast(FindControl("DropDownList1"), DropDownList).SelectedIndex
                'cmd.Parameters.Add("@Related", SqlDbType.VarChar).Value = "ReportGroup2"
                cmd.Parameters.AddWithValue("@Related", DropDownList1.SelectedValue)
                cmd.Parameters.Add("@WhenUploaded", SqlDbType.DateTime).Value = WhenUploaded
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
              End Using
            End Using
          End Using
        End Using
      Next
      Response.Redirect(Request.Url.AbsoluteUri)
    End If
  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.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()
        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 the markup is:
<%@ 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="Report Group" />
            <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"></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 %> - {tiny nonprofit}
        <br />
        <asp:LoginName ID="LoginName1" runat="server"
          FormatString="Logged in as: {0}" />
      </span>
    </div>
  </div>
</asp:Content>

Open in new window


The error i'm seeing is:

error when running webpage from Visual Studio

My ultimate goal is to allow a user to upload a batch of files that will all be marked with the related field so that on that same page, the files will appear in the gridview ready for downloading. My next step is to have the downloadable files in a gridview on a webpage that the physician user sees so that he can tell which supporting files go with which report.  THe user who uploads the files (a clerical) will be instructed to upload all related files (report and supporting docs together).  The physician will see the gridview sorted such that the files that belong together will have the same guid.

Thank you if you're still with me, reading.  My management told me the doctors are helpless/too busy so it must be something that they can tell at a glance.  Hence my requirement that everything be "automatic".
0
Comment
Question by:Brad Aberg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 500 total points
ID: 40369876
The way you're using a constant is not really correct, just use a normal string variable :

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

Author Comment

by:Brad Aberg
ID: 40369888
Thanks for your reply Paul Jackson.  I changed the Const to Dim but, unfortunately, I still have the same error.  I have "squiggly lines" under two things in Visual Studio: 1) under the Guid.empty to the right of the equals sign in the first line of the save file subroutine and farther down 2),in the same subroutine, the  DBNull that I have in the else clause.

I'll still leave the Dim instead of the Const.  Thanks for that insight.
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 40369905
For the 1st instance for the savefile function use new Guid() instead :

  Private Sub SaveFile(ByRef AFile As HttpPostedFile, Optional ByVal AGuid As Guid = new Guid())

For the second instance try using DBNull.Value :

command.Parameters.Add("@Related", SqlDbType.VarChar).Value = DBNull.Value
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Brad Aberg
ID: 40369920
Thank you.  Using DBNull.Value cleared the squiggly line from the second instance. Unfortunately I still get "constant expression is expected" for the ...AGuid As Guid = new Guid())  line.  Thanks for your continued assistance!
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 40369930
This seems to work :

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

Author Comment

by:Brad Aberg
ID: 40369959
Thank you.  Yes, it seems like Nothing appeased the VB gods.  But, now if I view in the browser I get an error about an invalid conversion from a GUID to a string.  Here's the stack trace:stack trace 'Object must implement IConvertible'
Should I be opening a new question because I am working through a series of errors and you have had to spend a lot of time on this?  I mean I could mark as answered so you'd get the points and then start a new question if you'd like.  Thanks!
Brad
0
 

Author Comment

by:Brad Aberg
ID: 40369960
Forgot to mention a critical point:  The page displays in the browser, I select a couple of files to upload, and WHEN I CLICK THE SUBMIT BUTTON I get this error.
0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 total points
ID: 40370500
In your save function I'm guessing you need to convert the Guid assigned to the sql parameter to a string using
.ToString() :

If AGuid <> Guid.Empty Then
              command.Parameters.Add("@Related", SqlDbType.VarChar).Value = AGuid.ToString()
0
 

Author Comment

by:Brad Aberg
ID: 40370987
Absolutely right!  IT works.  Thank you very much. You're the master.
0
 

Author Closing Comment

by:Brad Aberg
ID: 40370995
Great help. Multiple replies as I got different errors working through this problem.  Exceptionally fast responses to each of mny questions.  One expert helped me all the way through with this question.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

717 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