Solved

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

Posted on 2014-10-08
10
134 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
  • 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

759 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

22 Experts available now in Live!

Get 1:1 Help Now