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

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

How to get dropdown value into database row for multiple uploaded files stored in database

I have a working webpage that allows users to upload multiple files at once into a SQL Server database table.  After the upload, on the same webpage, the files are appended to a gridview with links to download (or view) them.  My new requirement is to add a new database column in the row(s) that are inserted into the database when the user uploads.

I will use this new column to group the files together.  I will have the user select a string from a dropdown before they upload.  Then I can use the selectedindex of the dropdown to fill the value for each file that is uploaded at one time.

My problem is that I don't know how to successfully add this to the database row.  I will include my attempt so far but there is a lot of code.  I'm hoping that you experts can just scan it and see what the problem is.  I have commented out all the new code that pertains to adding this new column (named "Related") because it doesn't work.  

I don't quite know how to code the statement that adds the "Related" parameter.  I was able to add a paramter that I hard coded to a single value (IsItPrivate = 1) but I'm not able to use FindControl to find the selected index of the dropdown.

Here's one of the lines:
cmd.Parameters.AddWithValue("@Related", Related) = TryCast(FindControl("DropDownList1"), DropDownList).SelectedIndex

Open in new window



Here's my code behind:

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Partial Class BulkUploadPRIVATEPhysicianFiles
  Inherits System.Web.UI.Page

  'reading and writing files stored in database not filesystem


  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 constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
      Using cmd As New SqlCommand()
        cmd.CommandText = "select Id, Name from tblFiles where IsItPrivate = 1 order by name"
        'cmd.CommandText = "select Id, Name, 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)
    'Dim filename As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
    'Dim contentType As String = FileUpload1.PostedFile.ContentType
    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 constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using con As New SqlConnection(constr)
              Dim query As String = "insert into tblFiles values (@Name, @ContentType,@IsItPrivate, @Data)"
              'Dim query As String = "insert into tblFiles values (@Name, @ContentType,@IsItPrivate, @Data, @Related)"
              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
                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
    Dim IsItPrivate As Boolean
    '  Dim Related As String
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As New SqlConnection(constr)
      Using cmd As New SqlCommand()
        cmd.CommandText = "select Name, Data, ContentType, IsItPrivate from tblFiles where Id=@Id and IsItPrivate=1 order by  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) = TryCast(FindControl("DropDownList1"), DropDownList).SelectedIndex
        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()

        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 here's the markup:
<%@ 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>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>
        <asp:FileUpload ID="FileUpload1" runat="server" AllowMultiple="true" /></td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td>
        <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="Upload" /></td>

      <td>
        <hr />
        <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:TemplateField ItemStyle-HorizontalAlign="Center">
              <ItemTemplate>
                <asp:LinkButton ID="lnkDownload" 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="ReportGroup" DataValueField="id" AutoPostBack="True"></asp:DropDownList>
      <td></td>
    </tr>
    <asp:SqlDataSource ID="ReportGroupDataSource" runat="server"
      SelectCommand="SELECT * FROM [ReportGroup] ORDER BY [ReportGroup]" 
      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


The tblFiles database table structure looks like this:
tblFiles with new Related column
The table that populates the dropdown is named ReportGroup.  It has an identity column (id) and a value column "ReportGroup".

Sorry this is so long.  I believe my real question is:  How can I code the line that adds the new column "ReportGroup" to the row that is written to the tblFiles table for every file that is uploaded.

Thank you!
0
Brad Aberg
Asked:
Brad Aberg
  • 10
  • 4
1 Solution
 
CodeCruiserCommented:
Are you doing the FindControl on the correct container? Is the dropdown just in the page itself or is it contained by another control such as update panel?
0
 
ste5anSenior DeveloperCommented:
How can I code the line that adds the new column "ReportGroup" to the row that is written to the tblFiles table for every file that is uploaded.

It's simple: you don't code that.

You're describing a normal column named ReportGroup. So add it simply in the database designer. I would use UNIQUEIDENTIFIER as data type, this allows the use of Guid.NewGuid on the ASP.NET-side.
0
 
Brad AbergAuthor Commented:
Reply to CodeCruiser:  I am not using an update panel.  I have the dropdown control on the page along with the gridview that shows the files that have already been uploaded.  If there is another way for me to automatically add a unique value to each batch (multiple files) upload in the code behind, I would be fine with that.  Please not that I already have a key column(field) that is an identity that generates unique numbers for each file uploaded.  I just want to add another column to the table row so I can use it to filter groups of files (batches) uploaded together at one time.

You assistance is greatly appreciated. Thanks.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Brad AbergAuthor Commented:
Reply to Ste5an:
I don't use the database designer but, even via SSMS, if I add a unique identifier (GUID) will it allow me to have multiple rows (each row in the database table represents a file that was uploaded) with the same value?  This is what I need to achieve in order to be able to display all the files that a user uploaded at once as a separate group than all the other files that may have been upload ytogether at another time.

For example:   batch 1 uploaded together would have columns:
id (identity) unique number for row to differtiate each file that was uploaded.
Content type
Data contents of file
IsItPrivate ( a row I added before that I can set to "True" or "False"/1 or 0  It's a boolean.
I was able to add this parameter because I set it to 1 in this example:
  cmd.Parameters.Add("@IsItPrivate", SqlDbType.Bit).Value = 1

I don't know how to add another parameter to capture the contents of a dropdown however.
Here's what I have:
cmd.Parameters.Add("@Related", SqlDbType.VarChar).Value = TryCast(FindControl("DropDownList1"), DropDownList).SelectedIndex
0
 
Brad AbergAuthor Commented:
The error I see is:
 BC30068: Expression is a value and therefore cannot be the target of an assignment.

The line that throws the error is:
cmd.Parameters.AddWithValue("@Related", Related) = TryCast(FindControl("DropDownList1"), DropDownList).SelectedIndex

I've looked up the error and have seen some examples but I cannot see what's wrong with mine.  It could be something simple maybe but I'm pretty new at this.

Thanks.

Thanks.
0
 
ste5anSenior DeveloperCommented:
I think I understand your logic.. but what is that control for?

cmd.Parameters.AddWithValue("@Related", Related) = TryCast(FindControl("DropDownList1"), 

Open in new window


You know when there are related files:

    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 realated As Guid = Guid.NewGuid
                For Each file In FileUpload1.PostedFiles
                    SaveFile(file, realated)
                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 );"

        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
                        End If

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

    End Sub

Open in new window

0
 
Brad AbergAuthor Commented:
Here's the syntax that finally worked in the upload sub before I saw your latest reply:
cmd.Parameters.AddWithValue("@Related", DropDownList1.SelectedValue)

I will try your code though because, if it keeps track of which files are uploaded together and gives them a grouped, that is superior to the way mine is set up now.  At this time mine requires the uploader to select a value from a drop down before uploading the files.

I will post again after I've tried your code later tonight but I'm going to mark yours as the answer.  I appreciate that you wrote the routine to show me how it's done.  It's way more sophisticated than anything that I could come up with.  I'll let you know how it works tonight.
0
 
Brad AbergAuthor Commented:
Thanks for the code example and for following up after your first comment/answer.
0
 
Brad AbergAuthor Commented:
Hello,

I substituted your Upload subroutine for mine and I initially got an error  saying that I needed to enclose parameter in parentheses do I changed the line:
 SaveFile FileUpload1.PostedFiles.FirstOrDefault()

Open in new window

with this:
 SaveFile(FileUpload1.PostedFiles.FirstOrDefault())

Open in new window

and it seemed to be ok (maybe) because I got past that error but now I see this error:
error stack trace
from this line:
  Private Sub SaveFile(ByRef AFile As HttpPostedFile, Optional ByVal AGuid As Guid = Guid.Empty)

Open in new window

 with the Guid.EMpty showing a squiggly line underneath in Visual Studio. If I hover over squiggly line in VS, says the same thing as error page:  constant expression is required.  Is this because I put parentheses around
(FileUpload1.PostedFiles.FirstOrDefault()

Open in new window

in savefile statement?

I really hope you'll check back even though I marked this as answered.  Thank you!!
0
 
ste5anSenior DeveloperCommented:
The code was quick sample to show the idea. Thus the minor errors. I've missed to add the necessary parameter and the correct handling.

You need to add a parameter for the grouping column in the SQL statement. And furthermore, you need an Else part in the Guid test to add an explict NULL value.
0
 
Brad AbergAuthor Commented:
Like this?:
  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

            End If

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

  End Sub

Open in new window

I added the @Related parameter to the sql query but what would I put in the else?If the GUID is empty, what should I do?  Is if sufficient to show an error message?

Thank you for your continued help!
0
 
ste5anSenior DeveloperCommented:
Set it to DbNull.
0
 
Brad AbergAuthor Commented:
I am still getting this error:error messageI must be using DBNull in the wrong place, right?
Here's the code now:
  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

Open in new window


Now Guid.empty to the right of the equals sign still has squiggly line under it and so does DBNull to the right of the equals sign in the else clause.  

Thank you for your patience.
0
 
Brad AbergAuthor Commented:
P.S. Do you want my markup so that you can run it to see if you get the same error?
0
 
Brad AbergAuthor Commented:
Here it 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 name}
        <br />
        <asp:LoginName ID="LoginName1" runat="server"
          FormatString="Logged in as: {0}" />
      </span>
    </div>
  </div>
</asp:Content>

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 10
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now