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

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".
Brad AbergDatabase AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul JacksonSoftware EngineerCommented:
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
Brad AbergDatabase AdministratorAuthor Commented:
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
Paul JacksonSoftware EngineerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Brad AbergDatabase AdministratorAuthor Commented:
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
Paul JacksonSoftware EngineerCommented:
This seems to work :

Private Sub SaveFile(ByRef AFile As HttpPostedFile, Optional ByVal AGuid As Guid = Nothing)
0
Brad AbergDatabase AdministratorAuthor Commented:
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
Brad AbergDatabase AdministratorAuthor Commented:
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
Paul JacksonSoftware EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brad AbergDatabase AdministratorAuthor Commented:
Absolutely right!  IT works.  Thank you very much. You're the master.
0
Brad AbergDatabase AdministratorAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.