?
Solved

Need help with Query and some code

Posted on 2005-04-30
49
Medium Priority
?
301 Views
Last Modified: 2008-01-09
I need help with an sql query and some code.  
Two MS SQL tables.
1.  Info
info_id   (Primary Key) Identifier
info_date
class_name
notes

2.  Files
file_id   (Primary Key) Identifier
info_id   (Foreign Key to Info's info_id)
file_name
file_size
file_path
upload_date

Info can have many Files
Files can have one Info

I had a web page that you select a date, select a class_name and then type notes in a textBox and it saves it to the Info table.
Now I have added file uploading to this page and a dataGrid.
The file uploading works.  It wrights the file_id, file_name, file_size, file_path and upload_date to the Files table.
Now this is where you guys come in.  I want the dataGrid to show only the files associated with the record I have on the screen.  I can't get the dataGrid to work.  
The problem is this:  If I upload a file before saving the form, I have no way to know what the info_id is going to be so I can't save anything in the Files info_id field.  As a result nothing will display in this record for the dataGrid after the save.
My query is probably wrong anyway.
Here is my code-behind and html from my page.
******************************************************************************************

Imports System.IO
Imports System.Collections
Public Class _Class
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.filesAdapter = New System.Data.SqlClient.SqlDataAdapter
        Me.filesConnection = New System.Data.SqlClient.SqlConnection
        Me.FilesDataSet1 = New Test.filesDataSet
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
        CType(Me.FilesDataSet1, System.ComponentModel.ISupportInitialize).BeginInit()
        '
        'filesAdapter
        '
        Me.filesAdapter.DeleteCommand = Me.SqlDeleteCommand1
        Me.filesAdapter.InsertCommand = Me.SqlInsertCommand1
        Me.filesAdapter.SelectCommand = Me.SqlSelectCommand1
        Me.filesAdapter.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "Files", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("file_id", "file_id"), New System.Data.Common.DataColumnMapping("info_id", "info_id"), New System.Data.Common.DataColumnMapping("file_name", "file_name"), New System.Data.Common.DataColumnMapping("file_path", "file_path")})})
        Me.filesAdapter.UpdateCommand = Me.SqlUpdateCommand1
        '
        'filesConnection
        '
        Me.filesConnection.ConnectionString = "workstation id=DELL;packet size=4096;integrated security=SSPI;data source=DELL;pe" & _
        "rsist security info=False;initial catalog=classes"
        '
        'FilesDataSet1
        '
        Me.FilesDataSet1.DataSetName = "filesDataSet"
        Me.FilesDataSet1.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT Files.file_id, Files.info_id, Files.file_name, Files.file_path FROM Files " & _
        "INNER JOIN Info ON Files.info_id = Info.info_id AND Files.info_id = Info.info_id" & _
        ""
        Me.SqlSelectCommand1.Connection = Me.filesConnection
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO Files(info_id, file_name, file_path) VALUES (@info_id, @file_name, @f" & _
        "ile_path)"
        Me.SqlInsertCommand1.Connection = Me.filesConnection
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@info_id", System.Data.SqlDbType.Int, 4, "info_id"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_name", System.Data.SqlDbType.VarChar, 100, "file_name"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_path", System.Data.SqlDbType.VarChar, 100, "file_path"))
        '
        'SqlUpdateCommand1
        '
        Me.SqlUpdateCommand1.CommandText = "UPDATE Files SET info_id = @info_id, file_name = @file_name, file_path = @file_pa" & _
        "th WHERE (file_id = @Original_file_id) AND (file_name = @Original_file_name) AND" & _
        " (file_path = @Original_file_path) AND (info_id = @Original_info_id OR @Original" & _
        "_info_id IS NULL AND info_id IS NULL)"
        Me.SqlUpdateCommand1.Connection = Me.filesConnection
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@info_id", System.Data.SqlDbType.Int, 4, "info_id"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_name", System.Data.SqlDbType.VarChar, 100, "file_name"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_path", System.Data.SqlDbType.VarChar, 100, "file_path"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_id", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_name", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_name", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_path", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_path", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_info_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "info_id", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlDeleteCommand1
        '
        Me.SqlDeleteCommand1.CommandText = "DELETE FROM Files WHERE (file_id = @Original_file_id) AND (file_name = @Original_" & _
        "file_name) AND (file_path = @Original_file_path) AND (info_id = @Original_info_i" & _
        "d OR @Original_info_id IS NULL AND info_id IS NULL)"
        Me.SqlDeleteCommand1.Connection = Me.filesConnection
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_id", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_name", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_name", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_path", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_path", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_info_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "info_id", System.Data.DataRowVersion.Original, Nothing))
        CType(Me.FilesDataSet1, System.ComponentModel.ISupportInitialize).EndInit()

    End Sub
    Protected WithEvents HyperLink1 As System.Web.UI.WebControls.HyperLink
    Protected WithEvents calendar As System.Web.UI.WebControls.Calendar
    Protected WithEvents classDownList As System.Web.UI.WebControls.DropDownList
    Protected WithEvents btnSave As System.Web.UI.WebControls.Button
    Protected WithEvents btnCancel As System.Web.UI.WebControls.Button
    Protected WithEvents lblIdentity As System.Web.UI.WebControls.Label
    Protected WithEvents lblMessage As System.Web.UI.WebControls.Label
    Protected WithEvents notesText As FreeTextBoxControls.FreeTextBox
    Protected WithEvents File1 As System.Web.UI.HtmlControls.HtmlInputFile
    Protected WithEvents Submit1 As System.Web.UI.HtmlControls.HtmlInputButton
    Protected WithEvents filesAdapter As System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents filesConnection As System.Data.SqlClient.SqlConnection
    Protected WithEvents FilesDataSet1 As Test.filesDataSet
    Protected WithEvents filesGrid As System.Web.UI.WebControls.DataGrid
    Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        filesAdapter.Fill(FilesDataSet1)
        If Not IsPostBack Then
            filesGrid.DataSource = FilesDataSet1.Files.DefaultView
            filesGrid.DataKeyField = "file_id"
            filesGrid.DataBind()
        End If

        If User.Identity.IsAuthenticated Then
            lblIdentity.Text = ("The current user is " + User.Identity.Name)
        Else
            lblIdentity.Text = "The current user is not authenticated."
        End If


        If Not IsPostBack Then
            If Not (Request.QueryString("info_id") Is Nothing) Then
                DropDownPopulate()
                GetInfo(Request.QueryString("info_id").ToString)
            Else
                calendar.SelectedDate = DateTime.Now
                DropDownPopulate()
            End If
        End If
    End Sub
    Private Sub DropDownPopulate()
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim myReader As SqlClient.SqlDataReader
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        myCommand.CommandText = "SELECT DISTINCT class_name FROM CLASSES"
        myConn.Open()

        Try
            myReader = myCommand.ExecuteReader()
            classDownList.DataSource = myReader
            classDownList.DataTextField = "class_name"
            classDownList.DataValueField = "class_name"
            classDownList.DataBind()
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()

    End Sub

    Private Sub GetInfo(ByVal strClassName As String)
        strClassName = Request.QueryString("info_id").ToString

        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim myReader As SqlClient.SqlDataReader
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        myCommand.CommandText = "SELECT * FROM INFO " & _
        "WHERE info_id = '" & strClassName & "'"
        myConn.Open()

        Try
            myReader = myCommand.ExecuteReader()

            While myReader.Read
                classDownList.SelectedValue = myReader("class_name").ToString
                calendar.SelectedDate = myReader("info_date").ToString
                notesText.Text = myReader("notes").ToString.Trim

            End While
            lblMessage.Text = "Record Successfully Read"
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()

    End Sub

    Protected Sub testing(ByVal sender As Object, ByVal e As EventArgs)
        Dim ck1 As CheckBox = CType(sender, CheckBox)
        Dim dgItem As DataGridItem = CType(ck1.NamingContainer, DataGridItem)
        If ck1.Checked Then
            dgItem.BackColor = Color.Gold
        Else
            dgItem.BackColor = Color.White
        End If
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        'uid=USER_NAME;pwd=PASSWORD;"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand

        If Not (Request.QueryString("info_id") Is Nothing) Then    '<---------******* Modified
            myCommand.CommandText = "UPDATE INFO SET class_name = '" & classDownList.SelectedValue & "', info_date = '" & calendar.SelectedDate & "', notes = '" & Left(Replace(notesText.Text, "'", ""), 7000) & "' WHERE info_id='" & Request.QueryString("info_id").ToString & "'"

        Else
            myCommand.CommandText = "INSERT INTO INFO(class_name, info_date, notes) VALUES ('" & classDownList.SelectedValue & "', '" & calendar.SelectedDate & "', '" & Left(Replace(notesText.Text, "'", ""), 7000) & "')"
        End If

        myConn.Open()
        Try
            myCommand.ExecuteNonQuery()
            lblMessage.Text = "Record Successfully Added"
            myConn.Close()
            Response.Redirect("Default.aspx")
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()                  '<------------------------Make sure to close your connection.

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Response.Redirect("Default.aspx")
    End Sub

    Private Sub Submit1_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit1.ServerClick
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        'uid=USER_NAME;pwd=PASSWORD;"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then
            Dim dateNow As String = Date.Today
            Dim properDate As String = Replace(dateNow, "/", "-")
            Dim fn As String = System.IO.Path.GetFileName(File1.PostedFile.FileName)
            Dim fileSize As String = File1.PostedFile.ContentLength
            Dim SaveLocation As String = Server.MapPath("Data") & "\" & properDate & "\" & fn
            Dim folderLocation As String = Server.MapPath("Data") & "\" & properDate
            myCommand.CommandText = "INSERT INTO FILES(file_name, file_size, file_path, upload_date) VALUES ('" & fn & "', '" & fileSize & "', '" & SaveLocation & "', '" & DateTime.Now & "')"
            Try
                If Directory.Exists(folderLocation) Then
                    File1.PostedFile.SaveAs(SaveLocation)
                Else
                    Directory.CreateDirectory(folderLocation)
                    File1.PostedFile.SaveAs(SaveLocation)
                End If
                Response.Write("The file has been uploaded.")
                myConn.Open()
                myCommand.ExecuteNonQuery()
                lblMessage.Text = "Record Successfully Added"
                myConn.Close()
                filesAdapter.Fill(FilesDataSet1)
                If IsPostBack Then
                    filesGrid.DataSource = FilesDataSet1.Files.DefaultView
                    filesGrid.DataKeyField = "file_id"
                    filesGrid.DataBind()
                End If
            Catch Exc As Exception
                Response.Write("Error: " & Exc.Message)
            End Try
        Else
            Response.Write("Please select a file to upload.")
        End If
    End Sub

    Private Sub filesGrid_Download(ByVal source As Object, _
    ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) _
    Handles filesGrid.ItemCommand
        If e.CommandName = "Download" Then
            Download(e.Item.Cells(4).Text)
        End If
    End Sub

    Private Sub Download(ByVal filePath As String)
        If File.Exists(filePath) Then
            Dim myFileInfo As FileInfo
            Dim FileSize As Long

            Dim ContentType As String
            Dim ContentDisposition As String
            Dim Extension As String

            myFileInfo = New FileInfo(filePath)
            FileSize = myFileInfo.Length
            Extension = myFileInfo.Extension.ToLower

            ContentDisposition = "attachment; filename=" & myFileInfo.Name
            Select Case Extension
                Case ".asf"
                    ContentType = "video/x-ms-asf"
                Case ".avi"
                    ContentType = "video/avi"
                Case ".doc"
                    ContentType = "application/msword"
                Case ".zip"
                    ContentType = "application/zip"
                Case ".xls"
                    ContentType = "application/vnd.ms-excel"
                Case ".gif"
                    ContentType = "image/gif"
                Case ".jpg", "jpeg"
                    ContentType = "image/jpeg"
                Case ".wav"
                    ContentType = "audio/wav"
                Case ".mp3"
                    ContentType = "audio/mpeg3"
                Case ".mpg", "mpeg"
                    ContentType = "video/mpeg"
                Case ".pdf"
                    ContentType = "application/pdf"
                Case ".rtf"
                    ContentType = "application/rtf"
                Case ".htm", "html"
                    ContentType = "text/html"
                Case ".asp"
                    ContentType = "text/asp"
                Case Else
                    'Handle All Other Files
                    ContentType = "application/octet-stream"
                    'make sure they get "save as" box
                    ContentDisposition = "attachment; filename=" & myFileInfo.Name
            End Select

            Response.Clear()
            Response.ClearHeaders()
            Response.ClearContent()

            Response.ContentType = ContentType
            Response.AppendHeader("Content-disposition", ContentDisposition)
            Response.AddHeader("Content-Length", myFileInfo.Length.ToString())
            Response.WriteFile(filePath)
            Response.End()
        Else
            'file doesn't exist
        End If

    End Sub


    Private Sub filesGrid_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles filesGrid.SortCommand
        Dim SortView As DataView = FilesDataSet1.Files.DefaultView
        Dim CurrentSort As String = ""
        If Not ViewState("Sort") Is Nothing Then
            CurrentSort = ViewState("Sort")
        End If
        If CurrentSort.StartsWith(e.SortExpression) Then
            If CurrentSort.EndsWith("DESC") Then
                SortView.Sort = e.SortExpression
            Else
                SortView.Sort = e.SortExpression & " DESC"
            End If
        Else
            SortView.Sort = e.SortExpression
        End If
        ViewState("Sort") = SortView.Sort
        filesGrid.DataSource = SortView
        filesGrid.DataBind()
    End Sub

    Private Sub filesGrid_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles filesGrid.DeleteCommand
        Dim filePath As String = e.Item.Cells(4).Text               ' Replace n with Column Number of you file_path
        System.IO.File.Delete(filePath)
        FilesDataSet1.Files.Rows.Item(e.Item.ItemIndex).Delete()
        filesAdapter.Update(FilesDataSet1)
        filesGrid.DataSource = FilesDataSet1.Files.DefaultView
        filesGrid.EditItemIndex = -1
        filesGrid.DataBind()
    End Sub

End Class
*******************************************************************************************
<%@ Register TagPrefix="ftb" Namespace="FreeTextBoxControls" Assembly="FreeTextBox" %>
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="Main_info.aspx.vb" Inherits="Test._Class" ValidateRequest="False" %>
<%@ Register TagPrefix="uc1" TagName="header" Src="header.ascx" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
      <HEAD>
            <title>Main Info</title>
            <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
            <meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
            <meta content="JavaScript" name="vs_defaultClientScript">
            <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
            <LINK href="Styles.css" type="text/css" rel="stylesheet">
      </HEAD>
      <body onload="document.getElementById('notesText').focus();">
            <form id="Form1" method="post" encType="multipart/form-data" runat="server">
                  <table width="100%">
                        <tr>
                              <td colSpan="2"><uc1:header id="Header1" runat="server"></uc1:header></td>
                        </tr>
                        <tr>
                              <td colSpan="2"><asp:calendar id="calendar" runat="server" CssClass="calendar"></asp:calendar></td>
                        </tr>
                        <tr>
                              <td colSpan="2"><asp:dropdownlist id="classDownList" runat="server" Height="88px" Width="232px"></asp:dropdownlist></td>
                        </tr>
                        <tr>
                              <td style="HEIGHT: 41px" colSpan="2"><asp:hyperlink id="HyperLink1" runat="server" Height="21px" Width="80px" NavigateUrl="Classes.aspx">Edit Classes</asp:hyperlink></td>
                        </tr>
                        <tr>
                              <td colSpan="2"><ftb:freetextbox id="notesText" runat="server"></ftb:freetextbox></td>
                        </tr>
                        <TR>
                              <TD width="10%"><asp:button id="btnSave" runat="server" Height="40px" Width="80px" Text="Save"></asp:button></TD>
                              <td width="90%"><asp:button id="btnCancel" runat="server" Height="40px" Width="72px" Text="Cancel"></asp:button></td>
                        </TR>
                        <tr>
                              <td colSpan="2"><asp:label id="lblMessage" runat="server"></asp:label></td>
                        </tr>
                        <tr>
                              <td colSpan="2"><asp:label id="lblIdentity" runat="server" Height="88px" Width="248px"></asp:label></td>
                        </tr>
                        <tr>
                              <td colSpan="2"><INPUT id="File1" type="file" name="File1" runat="server">
                                    <br>
                                    <input id="Submit1" type="submit" value="Upload" name="Submit1" runat="server"></td>
                        </tr>
                        <tr>
                              <td colSpan="2"><asp:datagrid id="filesGrid" runat="server" Width="448px" BorderColor="#999999" BorderStyle="None"
                                          BorderWidth="1px" BackColor="White" CellPadding="3" GridLines="Vertical" AutoGenerateColumns="False" AllowSorting="True">
                                          <FooterStyle ForeColor="Black" BackColor="#CCCCCC"></FooterStyle>
                                          <SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#008A8C"></SelectedItemStyle>
                                          <AlternatingItemStyle BackColor="Gainsboro"></AlternatingItemStyle>
                                          <ItemStyle ForeColor="Black" BackColor="#EEEEEE"></ItemStyle>
                                          <HeaderStyle Font-Bold="True" HorizontalAlign="Center" ForeColor="White" BackColor="#000084"></HeaderStyle>
                                          <Columns>
                                                <asp:ButtonColumn Text="Download" HeaderText="Download" CommandName="Download"></asp:ButtonColumn>
                                                <asp:ButtonColumn Text="Delete" HeaderText="Delete" CommandName="Delete"></asp:ButtonColumn>
                                                <asp:BoundColumn DataField="file_name" SortExpression="file_name" HeaderText="File Name">
                                                      <HeaderStyle Wrap="False"></HeaderStyle>
                                                      <ItemStyle Wrap="False"></ItemStyle>
                                                </asp:BoundColumn>
                                          </Columns>
                                          <PagerStyle HorizontalAlign="Center" ForeColor="Black" BackColor="#999999" Mode="NumericPages"></PagerStyle>
                                    </asp:datagrid></td>
                        </tr>
                  </table>
            </form>
      </body>
</HTML>
0
Comment
Question by:jettman26
  • 26
  • 23
49 Comments
 

Author Comment

by:jettman26
ID: 13905409
Anyone want to take a stab at this?
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13906419
inside this method,
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

There is no code to populate your Datagrid, only update and insert , need select to populate your datagrid.

0
 

Author Comment

by:jettman26
ID: 13906553
Thanks so much for replying.
Things have changed since my last post.  From a different page I am passing the info_id for a specific existing record.  Then after I upload a file it inserts the info_id I passes into the FILES table and the information I want shows.  The problem is when I insert a brand new record.  
For a new record nothing gets passed from another page.  I save the current information that into the INFO table.  Then I upload a file and I use the IDENT_CURRENT('INFO') to get the most recent info_id from the INFO table to insert it into the FILES table.  This all works.  The problem is that I can't get these newly uploaded files to show in the dataGrid for a new INFO record.  

If anyone wants I can upload my project and database.  Just let me know.  
Here is my new code for this page.

******************************************************************************************

Imports System.IO
Imports System.Collections
Public Class _Class
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.filesAdapter = New System.Data.SqlClient.SqlDataAdapter
        Me.filesConnection = New System.Data.SqlClient.SqlConnection
        Me.FilesDataSet1 = New Test.filesDataSet
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
        Me.filesAdapter2 = New System.Data.SqlClient.SqlDataAdapter
        Me.FilesDataSet21 = New Test.filesDataSet2
        Me.SqlSelectCommand2 = New System.Data.SqlClient.SqlCommand
        Me.SqlInsertCommand2 = New System.Data.SqlClient.SqlCommand
        Me.SqlUpdateCommand2 = New System.Data.SqlClient.SqlCommand
        Me.SqlDeleteCommand2 = New System.Data.SqlClient.SqlCommand
        CType(Me.FilesDataSet1, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.FilesDataSet21, System.ComponentModel.ISupportInitialize).BeginInit()
        '
        'filesAdapter
        '
        Me.filesAdapter.DeleteCommand = Me.SqlDeleteCommand1
        Me.filesAdapter.InsertCommand = Me.SqlInsertCommand1
        Me.filesAdapter.SelectCommand = Me.SqlSelectCommand1
        Me.filesAdapter.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "Files", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("file_id", "file_id"), New System.Data.Common.DataColumnMapping("info_id", "info_id"), New System.Data.Common.DataColumnMapping("file_name", "file_name"), New System.Data.Common.DataColumnMapping("file_path", "file_path")})})
        Me.filesAdapter.UpdateCommand = Me.SqlUpdateCommand1
        '
        'filesConnection
        '
        Me.filesConnection.ConnectionString = "workstation id=DELL;packet size=4096;integrated security=SSPI;data source=DELL;pe" & _
        "rsist security info=False;initial catalog=classes"
        '
        'FilesDataSet1
        '
        Me.FilesDataSet1.DataSetName = "filesDataSet"
        Me.FilesDataSet1.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT Files.file_id, Files.info_id, Files.file_name, Files.file_path FROM Files " & _
        "INNER JOIN Info ON Files.info_id = Info.info_id AND Files.info_id = Info.info_id" & _
        " WHERE (Files.info_id = @Param2)"
        Me.SqlSelectCommand1.Connection = Me.filesConnection
        Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param2", System.Data.SqlDbType.Int, 4, "info_id"))
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO Files(info_id, file_name, file_path) VALUES (@info_id, @file_name, @f" & _
        "ile_path)"
        Me.SqlInsertCommand1.Connection = Me.filesConnection
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@info_id", System.Data.SqlDbType.Int, 4, "info_id"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_name", System.Data.SqlDbType.VarChar, 100, "file_name"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_path", System.Data.SqlDbType.VarChar, 100, "file_path"))
        '
        'SqlUpdateCommand1
        '
        Me.SqlUpdateCommand1.CommandText = "UPDATE Files SET info_id = @info_id, file_name = @file_name, file_path = @file_pa" & _
        "th WHERE (file_id = @Original_file_id) AND (file_name = @Original_file_name) AND" & _
        " (file_path = @Original_file_path) AND (info_id = @Original_info_id OR @Original" & _
        "_info_id IS NULL AND info_id IS NULL)"
        Me.SqlUpdateCommand1.Connection = Me.filesConnection
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@info_id", System.Data.SqlDbType.Int, 4, "info_id"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_name", System.Data.SqlDbType.VarChar, 100, "file_name"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_path", System.Data.SqlDbType.VarChar, 100, "file_path"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_id", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_name", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_name", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_path", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_path", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_info_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "info_id", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlDeleteCommand1
        '
        Me.SqlDeleteCommand1.CommandText = "DELETE FROM Files WHERE (file_id = @Original_file_id) AND (file_name = @Original_" & _
        "file_name) AND (file_path = @Original_file_path) AND (info_id = @Original_info_i" & _
        "d OR @Original_info_id IS NULL AND info_id IS NULL)"
        Me.SqlDeleteCommand1.Connection = Me.filesConnection
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_id", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_name", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_name", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_path", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_path", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_info_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "info_id", System.Data.DataRowVersion.Original, Nothing))
        '
        'filesAdapter2
        '
        Me.filesAdapter2.DeleteCommand = Me.SqlDeleteCommand2
        Me.filesAdapter2.InsertCommand = Me.SqlInsertCommand2
        Me.filesAdapter2.SelectCommand = Me.SqlSelectCommand2
        Me.filesAdapter2.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "Files", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("file_id", "file_id"), New System.Data.Common.DataColumnMapping("info_id", "info_id"), New System.Data.Common.DataColumnMapping("file_name", "file_name"), New System.Data.Common.DataColumnMapping("file_path", "file_path")})})
        Me.filesAdapter2.UpdateCommand = Me.SqlUpdateCommand2
        '
        'FilesDataSet21
        '
        Me.FilesDataSet21.DataSetName = "filesDataSet2"
        Me.FilesDataSet21.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'SqlSelectCommand2
        '
        Me.SqlSelectCommand2.CommandText = "SELECT Files.file_id, Files.info_id, Files.file_name, Files.file_path FROM Files " & _
        "INNER JOIN Info ON Files.info_id = Info.info_id AND Files.info_id = Info.info_id" & _
        ""
        Me.SqlSelectCommand2.Connection = Me.filesConnection
        '
        'SqlInsertCommand2
        '
        Me.SqlInsertCommand2.CommandText = "INSERT INTO Files(info_id, file_name, file_path) VALUES (@info_id, @file_name, @f" & _
        "ile_path)"
        Me.SqlInsertCommand2.Connection = Me.filesConnection
        Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@info_id", System.Data.SqlDbType.Int, 4, "info_id"))
        Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_name", System.Data.SqlDbType.VarChar, 100, "file_name"))
        Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_path", System.Data.SqlDbType.VarChar, 100, "file_path"))
        '
        'SqlUpdateCommand2
        '
        Me.SqlUpdateCommand2.CommandText = "UPDATE Files SET info_id = @info_id, file_name = @file_name, file_path = @file_pa" & _
        "th WHERE (file_id = @Original_file_id) AND (file_name = @Original_file_name) AND" & _
        " (file_path = @Original_file_path) AND (info_id = @Original_info_id OR @Original" & _
        "_info_id IS NULL AND info_id IS NULL)"
        Me.SqlUpdateCommand2.Connection = Me.filesConnection
        Me.SqlUpdateCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@info_id", System.Data.SqlDbType.Int, 4, "info_id"))
        Me.SqlUpdateCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_name", System.Data.SqlDbType.VarChar, 100, "file_name"))
        Me.SqlUpdateCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_path", System.Data.SqlDbType.VarChar, 100, "file_path"))
        Me.SqlUpdateCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_id", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_name", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_name", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_path", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_path", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_info_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "info_id", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlDeleteCommand2
        '
        Me.SqlDeleteCommand2.CommandText = "DELETE FROM Files WHERE (file_id = @Original_file_id) AND (file_name = @Original_" & _
        "file_name) AND (file_path = @Original_file_path) AND (info_id = @Original_info_i" & _
        "d OR @Original_info_id IS NULL AND info_id IS NULL)"
        Me.SqlDeleteCommand2.Connection = Me.filesConnection
        Me.SqlDeleteCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_id", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_name", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_name", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_path", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_path", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_info_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "info_id", System.Data.DataRowVersion.Original, Nothing))
        CType(Me.FilesDataSet1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.FilesDataSet21, System.ComponentModel.ISupportInitialize).EndInit()

    End Sub
    Protected WithEvents HyperLink1 As System.Web.UI.WebControls.HyperLink
    Protected WithEvents calendar As System.Web.UI.WebControls.Calendar
    Protected WithEvents classDownList As System.Web.UI.WebControls.DropDownList
    Protected WithEvents btnSave As System.Web.UI.WebControls.Button
    Protected WithEvents btnCancel As System.Web.UI.WebControls.Button
    Protected WithEvents lblIdentity As System.Web.UI.WebControls.Label
    Protected WithEvents lblMessage As System.Web.UI.WebControls.Label
    Protected WithEvents notesText As FreeTextBoxControls.FreeTextBox
    Protected WithEvents File1 As System.Web.UI.HtmlControls.HtmlInputFile
    Protected WithEvents Submit1 As System.Web.UI.HtmlControls.HtmlInputButton
    Protected WithEvents filesAdapter As System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents filesConnection As System.Data.SqlClient.SqlConnection
    Protected WithEvents FilesDataSet1 As Test.filesDataSet
    Protected WithEvents filesGrid As System.Web.UI.WebControls.DataGrid
    Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents filesAdapter2 As System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents FilesDataSet21 As Test.filesDataSet2
    Protected WithEvents SqlSelectCommand2 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlInsertCommand2 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlUpdateCommand2 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlDeleteCommand2 As System.Data.SqlClient.SqlCommand

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        If User.Identity.IsAuthenticated Then
            lblIdentity.Text = ("The current user is " + User.Identity.Name)
        Else
            lblIdentity.Text = "The current user is not authenticated."
        End If


        If Not IsPostBack Then
            If Not (Request.QueryString("info_id") Is Nothing) Then
                filesAdapter.SelectCommand.Parameters("@Param2").Value = Request.QueryString("info_id")
                filesAdapter.Fill(FilesDataSet1)
                DropDownPopulate()
                GetInfo(Request.QueryString("info_id").ToString)
            Else
                filesAdapter.SelectCommand.Parameters("@Param2").Value = ""
                filesAdapter.Fill(FilesDataSet1)
                'filesAdapter2.Fill(FilesDataSet21)
                calendar.SelectedDate = DateTime.Now
                DropDownPopulate()
            End If
        End If

        If Not IsPostBack Then
            filesGrid.DataSource = FilesDataSet1.Files.DefaultView
            filesGrid.DataKeyField = "file_id"
            filesGrid.DataBind()
        End If
    End Sub
    Private Sub DropDownPopulate()
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim myReader As SqlClient.SqlDataReader
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        myCommand.CommandText = "SELECT DISTINCT class_name FROM CLASSES"
        myConn.Open()

        Try
            myReader = myCommand.ExecuteReader()
            classDownList.DataSource = myReader
            classDownList.DataTextField = "class_name"
            classDownList.DataValueField = "class_name"
            classDownList.DataBind()
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()

    End Sub

    Private Sub GetInfo(ByVal strClassName As String)
        strClassName = Request.QueryString("info_id").ToString

        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim myReader As SqlClient.SqlDataReader
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        myCommand.CommandText = "SELECT * FROM INFO " & _
        "WHERE info_id = '" & strClassName & "'"
        myConn.Open()

        Try
            myReader = myCommand.ExecuteReader()

            While myReader.Read
                classDownList.SelectedValue = myReader("class_name").ToString
                calendar.SelectedDate = myReader("info_date").ToString
                notesText.Text = myReader("notes").ToString.Trim

            End While
            lblMessage.Text = "Record Successfully Read"
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        'uid=USER_NAME;pwd=PASSWORD;"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand

        If Not (Request.QueryString("info_id") Is Nothing) Then    '<---------******* Modified
            myCommand.CommandText = "UPDATE INFO SET class_name = '" & classDownList.SelectedValue & "', info_date = '" & calendar.SelectedDate & "', notes = '" & Left(Replace(notesText.Text, "'", ""), 7000) & "' WHERE info_id='" & Request.QueryString("info_id").ToString & "'"

        Else
            myCommand.CommandText = "INSERT INTO INFO(class_name, info_date, notes) VALUES ('" & classDownList.SelectedValue & "', '" & calendar.SelectedDate & "', '" & Left(Replace(notesText.Text, "'", ""), 7000) & "')"
        End If

        myConn.Open()
        Try
            myCommand.ExecuteNonQuery()
            lblMessage.Text = "Record Successfully Added"
            myConn.Close()
            'Response.Redirect("Default.aspx")
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()                  '<------------------------Make sure to close your connection.

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Response.Redirect("Default.aspx")
    End Sub

    Private Sub Submit1_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit1.ServerClick
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        'uid=USER_NAME;pwd=PASSWORD;"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then
            Dim dateNow As String = Date.Today
            Dim properDate As String = Replace(dateNow, "/", "-")
            Dim fn As String = System.IO.Path.GetFileName(File1.PostedFile.FileName)
            Dim fileSize As String = File1.PostedFile.ContentLength
            Dim SaveLocation As String = Server.MapPath("Data") & "\" & properDate & "\" & fn
            Dim folderLocation As String = Server.MapPath("Data") & "\" & properDate
            'myCommand.CommandText = "INSERT INTO FILES(file_name, file_size, file_path, upload_date) VALUES ('" & fn & "', '" & fileSize & "', '" & SaveLocation & "', '" & DateTime.Now & "')"
            Try
                If Directory.Exists(folderLocation) Then
                    File1.PostedFile.SaveAs(SaveLocation)
                Else
                    Directory.CreateDirectory(folderLocation)
                    File1.PostedFile.SaveAs(SaveLocation)
                End If
                Response.Write("The file has been uploaded.")
                myConn.Open()
                If Not (Request.QueryString("info_id") Is Nothing) Then
                    Dim strClassName = Request.QueryString("info_id").ToString
                    myCommand.CommandText = "INSERT INTO FILES(info_id, file_name, file_size, file_path, upload_date) VALUES ('" & strClassName & "', '" & fn & "', '" & fileSize & "', '" & SaveLocation & "', '" & DateTime.Now & "')"
                    myCommand.ExecuteNonQuery()
                    lblMessage.Text = "Record Successfully Added"
                    myConn.Close()
                    filesAdapter.Fill(FilesDataSet1)
                    If IsPostBack Then
                        filesGrid.DataSource = FilesDataSet1.Files.DefaultView
                        filesGrid.DataKeyField = "file_id"
                        filesGrid.DataBind()
                    End If
                Else
                    myCommand.CommandText = "INSERT INTO FILES(info_id, file_name, file_size, file_path, upload_date) VALUES (IDENT_CURRENT('INFO'), '" & fn & "', '" & fileSize & "', '" & SaveLocation & "', '" & DateTime.Now & "')"
                    myCommand.ExecuteNonQuery()
                    myConn.Close()
                    filesAdapter.Fill(FilesDataSet1)
                    If IsPostBack Then
                        filesGrid.DataSource = FilesDataSet1.Files.DefaultView
                        filesGrid.DataKeyField = "file_id"
                        filesGrid.DataBind()
                    End If
                End If

               
            Catch Exc As Exception
                Response.Write("Error: " & Exc.Message)
            End Try
        Else
            Response.Write("Please select a file to upload.")
        End If
    End Sub

    Private Sub filesGrid_Download(ByVal source As Object, _
    ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) _
    Handles filesGrid.ItemCommand
        If e.CommandName = "Download" Then
            Download(e.Item.Cells(4).Text)
        End If
    End Sub

    Private Sub Download(ByVal filePath As String)
        If File.Exists(filePath) Then
            Dim myFileInfo As FileInfo
            Dim FileSize As Long

            Dim ContentType As String
            Dim ContentDisposition As String
            Dim Extension As String

            myFileInfo = New FileInfo(filePath)
            FileSize = myFileInfo.Length
            Extension = myFileInfo.Extension.ToLower

            ContentDisposition = "attachment; filename=" & myFileInfo.Name
            Select Case Extension
                Case ".asf"
                    ContentType = "video/x-ms-asf"
                Case ".avi"
                    ContentType = "video/avi"
                Case ".doc"
                    ContentType = "application/msword"
                Case ".zip"
                    ContentType = "application/zip"
                Case ".xls"
                    ContentType = "application/vnd.ms-excel"
                Case ".gif"
                    ContentType = "image/gif"
                Case ".jpg", "jpeg"
                    ContentType = "image/jpeg"
                Case ".wav"
                    ContentType = "audio/wav"
                Case ".mp3"
                    ContentType = "audio/mpeg3"
                Case ".mpg", "mpeg"
                    ContentType = "video/mpeg"
                Case ".pdf"
                    ContentType = "application/pdf"
                Case ".rtf"
                    ContentType = "application/rtf"
                Case ".htm", "html"
                    ContentType = "text/html"
                Case ".asp"
                    ContentType = "text/asp"
                Case Else
                    'Handle All Other Files
                    ContentType = "application/octet-stream"
                    'make sure they get "save as" box
                    ContentDisposition = "attachment; filename=" & myFileInfo.Name
            End Select

            Response.Clear()
            Response.ClearHeaders()
            Response.ClearContent()

            Response.ContentType = ContentType
            Response.AppendHeader("Content-disposition", ContentDisposition)
            Response.AddHeader("Content-Length", myFileInfo.Length.ToString())
            Response.WriteFile(filePath)
            Response.End()
        Else
            'file doesn't exist
        End If

    End Sub


    Private Sub filesGrid_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles filesGrid.SortCommand
        Dim SortView As DataView = FilesDataSet1.Files.DefaultView
        Dim CurrentSort As String = ""
        If Not ViewState("Sort") Is Nothing Then
            CurrentSort = ViewState("Sort")
        End If
        If CurrentSort.StartsWith(e.SortExpression) Then
            If CurrentSort.EndsWith("DESC") Then
                SortView.Sort = e.SortExpression
            Else
                SortView.Sort = e.SortExpression & " DESC"
            End If
        Else
            SortView.Sort = e.SortExpression
        End If
        ViewState("Sort") = SortView.Sort
        filesGrid.DataSource = SortView
        filesGrid.DataBind()
    End Sub

    Private Sub filesGrid_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles filesGrid.DeleteCommand
        Dim filePath As String = e.Item.Cells(3).Text               ' Replace n with Column Number of you file_path
        System.IO.File.Delete(filePath)
        FilesDataSet1.Files.Rows.Item(e.Item.ItemIndex).Delete()
        filesAdapter.Update(FilesDataSet1)
        filesGrid.DataSource = FilesDataSet1.Files.DefaultView
        filesGrid.EditItemIndex = -1
        filesGrid.DataBind()
    End Sub

End Class
0
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.

 
LVL 11

Expert Comment

by:lijunguo
ID: 13906576
<<The problem is when I insert a brand new record.  
For a new record nothing gets passed from another page.
>>

What does it mean?
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13906589
You want to see the result from datagrid after you click the "save" button? is that right?
0
 

Author Comment

by:jettman26
ID: 13906693
For a new record, meaning from a page called Default.aspx, I click on a button called New that takes me to a blank Main_info.aspx page(No data showing).


I want to see the uploaded files associated with the record that is current displayed on the Main_info.aspx page.  So for example:
In the situation that I click the New button on Default.aspx which takes me to a blank Main_info.aspx, the DataGrid should be empty.  But when you upload a file, this newly updated file should show in the DataGrid immediately after you click the upload button.  
 
0
 

Author Comment

by:jettman26
ID: 13906713
You can download my project and sql database by going to beethe.com with an FTP client
login/pass
test/test
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13906748
ok, I'll go there to download it. Because I got counfused about your business logic. It should not be that hard.
0
 

Author Comment

by:jettman26
ID: 13906759
thanks
it will be the easiest if you set Default.aspx as the start page and that way you can do exactly what I am doing.
To log into the database (because of forms authentication) it is:
login/pass
bbeethe/jettman26
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13906795
You want to see the result from datagrid after you click 'upload' instead of 'save' button now, is that right?
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13906797
If it is, I think the problem is the following code. It will never run when you click 'upload'.

        If Not IsPostBack Then
            filesGrid.DataSource = FilesDataSet1.Files.DefaultView
            filesGrid.DataKeyField = "file_id"
            filesGrid.DataBind()
        End If
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13906800
I'll have my lunch and come back in half an hour.
0
 

Author Comment

by:jettman26
ID: 13906805
is that code in the Page_Load?
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13906834
yes,
0
 

Author Comment

by:jettman26
ID: 13906843
I knew that it wouldn't hit hit that part of the code when you clicked upload.  That is why I have the following in the Submit1_ServerClick Procedure.
It seems to me my dataset is wrong but not for sure.


Else
                    myCommand.CommandText = "INSERT INTO FILES(info_id, file_name, file_size, file_path, upload_date) VALUES (IDENT_CURRENT('INFO'), '" & fn & "', '" & fileSize & "', '" & SaveLocation & "', '" & DateTime.Now & "')"
                    myCommand.ExecuteNonQuery()
                    myConn.Close()
                    filesAdapter.Fill(FilesDataSet1)
                    If IsPostBack Then
                        filesGrid.DataSource = FilesDataSet1.Files.DefaultView
                        filesGrid.DataKeyField = "file_id"
                        filesGrid.DataBind()
                    End If
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13906849
I think so.
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13906862
change it to something like this

filesAdapter.Fill(FilesDataSet1,"Files") --Files is the dataset table name
                   
filesGrid.DataSource = FilesDataSet1.Tables("Files").DefaultView
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13906873
also remove
If IsPostBack Then
end if
only those 2 lines, leave 3 lines between them

0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13906913
add the following line above
filesAdapter.SelectCommand.Parameters("@Param2").Value = the new value of Files.info_id

this line

filesAdapter.Fill(FilesDataSet1,"Files") --Files is the dataset table name
0
 

Author Comment

by:jettman26
ID: 13907001
While you were at lunch, i got it to work.  But then my Delete and Download links quit working.

1.  I added this function to get me the last info_id entered for the INFO table.

Private Sub GetInfoID()
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim myReader As SqlClient.SqlDataReader
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        myCommand.CommandText = "SELECT info_id FROM INFO WHERE info_id = IDENT_CURRENT('INFO')"
        myConn.Open()

        Try
            myReader = myCommand.ExecuteReader()
            While myReader.Read
                lblInfoID.Text = myReader("info_id").ToString
                'classDownList.DataBind()
            End While
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
     myConn.Close()

2.  Added the following 2 lines.

GetInfoID()
filesAdapter.SelectCommand.Parameters("@Param2").Value = lblInfoID.Text

What this does is the GetInfoID() gets the last info_id in the INFO table and it puts it in the lblInfoID lablel.
Then that info_id gets put into the @Param2.  

I can try your ideas in the morning.  It is getting very late here and I need to sleep.
0
 

Author Comment

by:jettman26
ID: 13908288
I tried what your ideas and the datagrid doesn't show anything.  probably because I don't know what to put after the = sign on this line.

filesAdapter.SelectCommand.Parameters("@Param2").Value = the new value of Files.info_id
What should I put in place of 'the new value of Files.info_id'?

I think if you can help figure out my download and delete problem, I would be good to go.  Somehow I messed up the datagrid.
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13913764
<<
What should I put in place of 'the new value of Files.info_id'?
>>

You should get the value you insert into table for that user. You have to find a way to implement it. One way is to use store procedure with return value. Your way is working for one user, not sure working for multiusers. After inserting a new value into a table, the other users may just insert a new value into that table, then you read it. You got the later info_id, it's another user's, it's not what you expected.
0
 

Author Comment

by:jettman26
ID: 13913907
This is just a very small app that only I will use so it won't be multiuser.  I have never used or tried to use a stored procedure before.  Sounds rather tough.  My biggest problem is not being able to delete now.  It used to work.  Must have been something that changed with the datagrid?
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13913935
You mean it's working before, but not now. What's the error message?
0
 

Author Comment

by:jettman26
ID: 13913968
Correct, it was working before but it is not now.  In my project is a Edit_files.aspx that works when you click Delete.

Here is the error I am getting when I click Delete.



Server Error in '/Test' Application.
--------------------------------------------------------------------------------

There is no row at position 2.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.IndexOutOfRangeException: There is no row at position 2.

Source Error:


Line 399:        Dim filePath As String = e.Item.Cells(3).Text               ' Replace n with Column Number of you file_path
Line 400:        System.IO.File.Delete(filePath)
Line 401:        FilesDataSet1.Files.Rows.Item(e.Item.ItemIndex).Delete()
Line 402:        filesAdapter.Update(FilesDataSet1)
Line 403:        filesGrid.DataSource = FilesDataSet1.Files.DefaultView
 

Source File: C:\Inetpub\wwwroot\Test\Main_info.aspx.vb    Line: 401

Stack Trace:


[IndexOutOfRangeException: There is no row at position 2.]
   System.Data.DataRowCollection.get_Item(Int32 index)
   Test._Class.filesGrid_DeleteCommand(Object source, DataGridCommandEventArgs e) in C:\Inetpub\wwwroot\Test\Main_info.aspx.vb:401
   System.Web.UI.WebControls.DataGrid.OnDeleteCommand(DataGridCommandEventArgs e)
   System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e)
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
   System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e)
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e)
   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
   System.Web.UI.Page.ProcessRequestMain() +1292

 
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13913996
<<In my project is a Edit_files.aspx that works when you click Delete.>>

We have been working with datagrid in file main_info.aspx, now datagrid could show correct info. after you make changes. Now you're working with another page, is that correct?
0
 

Author Comment

by:jettman26
ID: 13914008
no, still working on main_info.aspx.  That is where the delete doesn't work.I just mentioned the Edit_files.aspx in case you wanted to see that the delete in the datagrid does work there.  
I won't leave the main_info.aspx page till it works :)
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13914043
change this line
 FilesDataSet1.Files.Rows.Item(e.Item.ItemIndex).Delete()

to
 FilesDataSet1.Tables("Files").Rows.Item(e.Item.ItemIndex).Delete()

give it a try
0
 

Author Comment

by:jettman26
ID: 13914074
I changed it to FilesDataSet1.Tables("Files").Rows.Item(e.Item.ItemIndex).Delete() and got the same error.
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13914099
try
FilesDataSet1.Tables(0).Rows.Item(e.Item.ItemIndex).Delete()
or
FilesDataSet1.Tables(0).Rows(0).Item(e.Item.ItemIndex).Delete()
0
 

Author Comment

by:jettman26
ID: 13914111
Same error with those two.
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13914146
debug and show the value of
e.Item.ItemIndex
0
 

Author Comment

by:jettman26
ID: 13914168
e.Item.ItemIndex = 0

Then I get the same error again

Server Error in '/Test' Application.
--------------------------------------------------------------------------------

There is no row at position 0.
....
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13914198
try this
DataSet_dpac.Tables(0).Rows(0).Delete()
if it does not delete anything, then it means there is no record in the dataset.

Or you could use this line to see how many rows in your dataset.

DataSet_dpac.Tables(0).Rows.Count()
0
 

Author Comment

by:jettman26
ID: 13914228
VS.NET says that.
"Name 'DataSet_dpac' is not declared."
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13914246
Sorry about it.
replace with

FilesDataSet1,

not
DataSet_dpac
0
 

Author Comment

by:jettman26
ID: 13914285
when I use FilesDataSet1.Tables(0).Rows(0).Delete() I get the same error.

When I use FilesDataSet1.Tables(0).Rows.Count()
VS.NET says "Property Access must assign to the property or use its value."
0
 
LVL 11

Accepted Solution

by:
lijunguo earned 2000 total points
ID: 13914322
It seems the dataset is empty. It means there is no row in that dataset, so you can not delete.

For double check.
comment out that delete line.
use this to debug.
                    Dim i As Short
                    i = FilesDataSet1.Tables(0).Rows.Count()
it i is 0, it means the dataset is empty.
0
 

Author Comment

by:jettman26
ID: 13914356
i=0
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13914378
it means there is nothing in your Dataset, how can you delete a row from an empty dataset. Just like a table is empty, how can you delete a row from a table.
 
0
 

Author Comment

by:jettman26
ID: 13914392
I don't understand this because it is showing the correct records in the dataGrid.  How can the DataSet be empty then?
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13914427
It's possibly another dataset shows the record from datagrid, not  FilesDataSet1
0
 

Author Comment

by:jettman26
ID: 13914665
I placed the following into the page in a different spot and it worked.

filesAdapter.SelectCommand.Parameters("@Param2").Value = Request.QueryString("info_id")
        filesAdapter.Fill(FilesDataSet1)

Now the page_load looks like this.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        If User.Identity.IsAuthenticated Then
            lblIdentity.Text = ("The current user is " + User.Identity.Name)
        Else
            lblIdentity.Text = "The current user is not authenticated."
        End If


        If Not IsPostBack Then
            If Not (Request.QueryString("info_id") Is Nothing) Then
                DropDownPopulate()
                GetInfo(Request.QueryString("info_id").ToString)
                filesAdapter.SelectCommand.Parameters("@Param2").Value = Request.QueryString("info_id")
                filesAdapter.Fill(FilesDataSet1)
            Else
                'filesAdapter.SelectCommand.Parameters("@Param2").Value = ""
                'filesAdapter2.Fill(FilesDataSet21)
                calendar.SelectedDate = DateTime.Now
                DropDownPopulate()
            End If
        End If


        filesAdapter.SelectCommand.Parameters("@Param2").Value = Request.QueryString("info_id")
        filesAdapter.Fill(FilesDataSet1)
        If Not IsPostBack Then
            filesGrid.DataSource = FilesDataSet1.Files.DefaultView
            filesGrid.DataKeyField = "file_id"
            filesGrid.DataBind()
        End If
    End Sub

******************************************************************************************
I am still having problems.  Will try to fix them quick.
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13914749
because of the variable scope, after populate datagrid from dataset, then you fire detele event, from delete function block, the dataset is empty. That's why there are no rows.

Datagrid is for display only.
0
 

Author Comment

by:jettman26
ID: 13914881
lijunguo,

It officially works now.
You can check out the code behind for my Main_info.aspx page.
If you think you could find a better way to do ANY of this, let me know and I will open up another 500 point question for you.  Especially for a better way to retrieve info_id like a stored procedure.
Thanks.

Imports System.IO
Imports System.Collections
Public Class _Class
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.filesAdapter = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
        Me.filesConnection = New System.Data.SqlClient.SqlConnection
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
        Me.FilesDataSet1 = New Test.filesDataSet
        CType(Me.FilesDataSet1, System.ComponentModel.ISupportInitialize).BeginInit()
        '
        'filesAdapter
        '
        Me.filesAdapter.DeleteCommand = Me.SqlDeleteCommand1
        Me.filesAdapter.InsertCommand = Me.SqlInsertCommand1
        Me.filesAdapter.SelectCommand = Me.SqlSelectCommand1
        Me.filesAdapter.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "Files", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("file_id", "file_id"), New System.Data.Common.DataColumnMapping("info_id", "info_id"), New System.Data.Common.DataColumnMapping("file_name", "file_name"), New System.Data.Common.DataColumnMapping("file_path", "file_path")})})
        Me.filesAdapter.UpdateCommand = Me.SqlUpdateCommand1
        '
        'SqlDeleteCommand1
        '
        Me.SqlDeleteCommand1.CommandText = "DELETE FROM Files WHERE (file_id = @Original_file_id) AND (file_name = @Original_" & _
        "file_name) AND (file_path = @Original_file_path) AND (info_id = @Original_info_i" & _
        "d OR @Original_info_id IS NULL AND info_id IS NULL)"
        Me.SqlDeleteCommand1.Connection = Me.filesConnection
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_id", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_name", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_name", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_path", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_path", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_info_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "info_id", System.Data.DataRowVersion.Original, Nothing))
        '
        'filesConnection
        '
        Me.filesConnection.ConnectionString = "workstation id=DELL;packet size=4096;integrated security=SSPI;data source=DELL;pe" & _
        "rsist security info=False;initial catalog=classes"
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO Files(info_id, file_name, file_path) VALUES (@info_id, @file_name, @f" & _
        "ile_path)"
        Me.SqlInsertCommand1.Connection = Me.filesConnection
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@info_id", System.Data.SqlDbType.Int, 4, "info_id"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_name", System.Data.SqlDbType.VarChar, 100, "file_name"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_path", System.Data.SqlDbType.VarChar, 100, "file_path"))
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT Files.file_id, Files.info_id, Files.file_name, Files.file_path FROM Files " & _
        "INNER JOIN Info ON Files.info_id = Info.info_id AND Files.info_id = Info.info_id" & _
        " WHERE (Files.info_id = @Param2)"
        Me.SqlSelectCommand1.Connection = Me.filesConnection
        Me.SqlSelectCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Param2", System.Data.SqlDbType.Int, 4, "info_id"))
        '
        'SqlUpdateCommand1
        '
        Me.SqlUpdateCommand1.CommandText = "UPDATE Files SET info_id = @info_id, file_name = @file_name, file_path = @file_pa" & _
        "th WHERE (file_id = @Original_file_id) AND (file_name = @Original_file_name) AND" & _
        " (file_path = @Original_file_path) AND (info_id = @Original_info_id OR @Original" & _
        "_info_id IS NULL AND info_id IS NULL)"
        Me.SqlUpdateCommand1.Connection = Me.filesConnection
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@info_id", System.Data.SqlDbType.Int, 4, "info_id"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_name", System.Data.SqlDbType.VarChar, 100, "file_name"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@file_path", System.Data.SqlDbType.VarChar, 100, "file_path"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_id", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_name", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_name", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_file_path", System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "file_path", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_info_id", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "info_id", System.Data.DataRowVersion.Original, Nothing))
        '
        'FilesDataSet1
        '
        Me.FilesDataSet1.DataSetName = "filesDataSet"
        Me.FilesDataSet1.Locale = New System.Globalization.CultureInfo("en-US")
        CType(Me.FilesDataSet1, System.ComponentModel.ISupportInitialize).EndInit()

    End Sub
    Protected WithEvents HyperLink1 As System.Web.UI.WebControls.HyperLink
    Protected WithEvents calendar As System.Web.UI.WebControls.Calendar
    Protected WithEvents classDownList As System.Web.UI.WebControls.DropDownList
    Protected WithEvents btnSave As System.Web.UI.WebControls.Button
    Protected WithEvents btnCancel As System.Web.UI.WebControls.Button
    Protected WithEvents lblIdentity As System.Web.UI.WebControls.Label
    Protected WithEvents lblMessage As System.Web.UI.WebControls.Label
    Protected WithEvents notesText As FreeTextBoxControls.FreeTextBox
    Protected WithEvents File1 As System.Web.UI.HtmlControls.HtmlInputFile
    Protected WithEvents Submit1 As System.Web.UI.HtmlControls.HtmlInputButton
    Protected WithEvents filesAdapter As System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents filesConnection As System.Data.SqlClient.SqlConnection
    Protected WithEvents FilesDataSet1 As Test.filesDataSet
    Protected WithEvents filesGrid As System.Web.UI.WebControls.DataGrid
    Protected WithEvents lblInfoID As System.Web.UI.WebControls.Label
    Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Shows the Identity of the logged in user
        If User.Identity.IsAuthenticated Then
            lblIdentity.Text = ("The current user is " + User.Identity.Name)
        Else
            lblIdentity.Text = "The current user is not authenticated."
        End If

        'This is for the Info Table portion of the page
        If Not IsPostBack Then
            If Not (Request.QueryString("info_id") Is Nothing) Then
                DropDownPopulate()
                GetInfo(Request.QueryString("info_id").ToString)
            Else
                filesGrid.Visible = False
                File1.Visible = False
                Submit1.Visible = False
                calendar.SelectedDate = DateTime.Now
                DropDownPopulate()
            End If
        End If

        'This is all for the DataGrid
        If Not lblInfoID.Text = "" Then
            filesAdapter.SelectCommand.Parameters("@Param2").Value = lblInfoID.Text
            filesAdapter.Fill(FilesDataSet1)
            If Not IsPostBack Then
                filesGrid.DataSource = FilesDataSet1.Files.DefaultView
                filesGrid.DataKeyField = "file_id"
                filesGrid.DataBind()
            End If
        Else
            If Not IsPostBack Then
                filesGrid.DataSource = FilesDataSet1.Files.DefaultView
                filesGrid.DataKeyField = "file_id"
                filesGrid.DataBind()
            End If
        End If
    End Sub
    Private Sub DropDownPopulate()
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim myReader As SqlClient.SqlDataReader
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        myCommand.CommandText = "SELECT DISTINCT class_name FROM CLASSES"
        myConn.Open()

        Try
            myReader = myCommand.ExecuteReader()
            classDownList.DataSource = myReader
            classDownList.DataTextField = "class_name"
            classDownList.DataValueField = "class_name"
            classDownList.DataBind()
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()

    End Sub

    Private Sub GetInfo(ByVal strClassName As String)
        strClassName = Request.QueryString("info_id").ToString

        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim myReader As SqlClient.SqlDataReader
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        myCommand.CommandText = "SELECT * FROM INFO " & _
        "WHERE info_id = '" & strClassName & "'"
        myConn.Open()

        Try
            myReader = myCommand.ExecuteReader()

            While myReader.Read
                classDownList.SelectedValue = myReader("class_name").ToString
                calendar.SelectedDate = myReader("info_date").ToString
                notesText.Text = myReader("notes").ToString.Trim
                lblInfoID.Text = myReader("info_id").ToString
            End While
            lblMessage.Text = "Record Successfully Read"
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        'If the info_id parameter was passed from Default.aspx
        If Not (Request.QueryString("info_id") Is Nothing) Then
            myCommand.CommandText = "UPDATE INFO SET class_name = '" & classDownList.SelectedValue & "', info_date = '" & calendar.SelectedDate & "', notes = '" & Left(Replace(notesText.Text, "'", ""), 7000) & "' WHERE info_id='" & Request.QueryString("info_id").ToString & "'"
            'If the info_id parameter was not passed from Default.aspx
        Else
            myCommand.CommandText = "INSERT INTO INFO(class_name, info_date, notes) VALUES ('" & classDownList.SelectedValue & "', '" & calendar.SelectedDate & "', '" & Left(Replace(notesText.Text, "'", ""), 7000) & "')"
        End If

        myConn.Open()
        Try
            If Not (Request.QueryString("info_id") Is Nothing) Then
                myCommand.ExecuteNonQuery()
                lblMessage.Text = "Record Successfully Added"
                myConn.Close()
                Response.Redirect("Default.aspx")
            Else
                GetInfoID()
                myCommand.ExecuteNonQuery()
                lblMessage.Text = "Record Successfully Added"
                myConn.Close()
                Response.Redirect("Default.aspx")
            End If
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()                  '<------------------------Make sure to close your connection.

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Response.Redirect("Default.aspx")
    End Sub

    Private Sub Submit1_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit1.ServerClick
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then
            Dim dateNow As String = Date.Today
            Dim properDate As String = Replace(dateNow, "/", "-")
            Dim fn As String = System.IO.Path.GetFileName(File1.PostedFile.FileName)
            Dim fileSize As String = File1.PostedFile.ContentLength
            Dim SaveLocation As String = Server.MapPath("Data") & "\" & properDate & "\" & fn
            Dim folderLocation As String = Server.MapPath("Data") & "\" & properDate
            Try
                If Directory.Exists(folderLocation) Then
                    File1.PostedFile.SaveAs(SaveLocation)
                Else
                    Directory.CreateDirectory(folderLocation)
                    File1.PostedFile.SaveAs(SaveLocation)
                End If
                Response.Write("The file has been uploaded.")
                myConn.Open()
                If Not (Request.QueryString("info_id") Is Nothing) Then
                    Dim strClassName = Request.QueryString("info_id").ToString
                    myCommand.CommandText = "INSERT INTO FILES(info_id, file_name, file_size, file_path, upload_date) VALUES ('" & strClassName & "', '" & fn & "', '" & fileSize & "', '" & SaveLocation & "', '" & DateTime.Now & "')"
                    myCommand.ExecuteNonQuery()
                    lblMessage.Text = "Record Successfully Added"
                    myConn.Close()
                    filesAdapter.SelectCommand.Parameters("@Param2").Value = Request.QueryString("info_id")
                    filesAdapter.Fill(FilesDataSet1)
                    If IsPostBack Then
                        filesGrid.DataSource = FilesDataSet1.Files.DefaultView
                        filesGrid.DataKeyField = "file_id"
                        filesGrid.DataBind()
                    End If
                Else
                    myCommand.CommandText = "INSERT INTO FILES(info_id, file_name, file_size, file_path, upload_date) VALUES (IDENT_CURRENT('INFO'), '" & fn & "', '" & fileSize & "', '" & SaveLocation & "', '" & DateTime.Now & "')"
                    myCommand.ExecuteNonQuery()
                    myConn.Close()
                    GetInfoID()
                    filesAdapter.SelectCommand.Parameters("@Param2").Value = lblInfoID.Text
                    filesAdapter.Fill(FilesDataSet1)
                    If IsPostBack Then
                        filesGrid.DataSource = FilesDataSet1.Files.DefaultView
                        filesGrid.DataKeyField = "file_id"
                        filesGrid.DataBind()
                    End If
                End If


            Catch Exc As Exception
                Response.Write("Error: " & Exc.Message)
            End Try
        Else
            Response.Write("Please select a file to upload.")
        End If
    End Sub

    Private Sub filesGrid_Download(ByVal source As Object, _
    ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) _
    Handles filesGrid.ItemCommand
        If e.CommandName = "Download" Then
            Download(e.Item.Cells(3).Text)
        End If
    End Sub

    Private Sub Download(ByVal filePath As String)
        If File.Exists(filePath) Then
            Dim myFileInfo As FileInfo
            Dim FileSize As Long

            Dim ContentType As String
            Dim ContentDisposition As String
            Dim Extension As String

            myFileInfo = New FileInfo(filePath)
            FileSize = myFileInfo.Length
            Extension = myFileInfo.Extension.ToLower

            ContentDisposition = "attachment; filename=" & myFileInfo.Name
            Select Case Extension
                Case ".asf"
                    ContentType = "video/x-ms-asf"
                Case ".avi"
                    ContentType = "video/avi"
                Case ".doc"
                    ContentType = "application/msword"
                Case ".zip"
                    ContentType = "application/zip"
                Case ".xls"
                    ContentType = "application/vnd.ms-excel"
                Case ".gif"
                    ContentType = "image/gif"
                Case ".jpg", "jpeg"
                    ContentType = "image/jpeg"
                Case ".wav"
                    ContentType = "audio/wav"
                Case ".mp3"
                    ContentType = "audio/mpeg3"
                Case ".mpg", "mpeg"
                    ContentType = "video/mpeg"
                Case ".pdf"
                    ContentType = "application/pdf"
                Case ".rtf"
                    ContentType = "application/rtf"
                Case ".htm", "html"
                    ContentType = "text/html"
                Case ".asp"
                    ContentType = "text/asp"
                Case Else
                    'Handle All Other Files
                    ContentType = "application/octet-stream"
                    'make sure they get "save as" box
                    ContentDisposition = "attachment; filename=" & myFileInfo.Name
            End Select

            Response.Clear()
            Response.ClearHeaders()
            Response.ClearContent()

            Response.ContentType = ContentType
            Response.AppendHeader("Content-disposition", ContentDisposition)
            Response.AddHeader("Content-Length", myFileInfo.Length.ToString())
            Response.WriteFile(filePath)
            Response.End()
        Else
            'file doesn't exist
        End If

    End Sub


    Private Sub filesGrid_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles filesGrid.SortCommand
        Dim SortView As DataView = FilesDataSet1.Files.DefaultView
        Dim CurrentSort As String = ""
        If Not ViewState("Sort") Is Nothing Then
            CurrentSort = ViewState("Sort")
        End If
        If CurrentSort.StartsWith(e.SortExpression) Then
            If CurrentSort.EndsWith("DESC") Then
                SortView.Sort = e.SortExpression
            Else
                SortView.Sort = e.SortExpression & " DESC"
            End If
        Else
            SortView.Sort = e.SortExpression
        End If
        ViewState("Sort") = SortView.Sort
        filesGrid.DataSource = SortView
        filesGrid.DataBind()
    End Sub

    Private Sub filesGrid_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles filesGrid.DeleteCommand
        Dim filePath As String = e.Item.Cells(3).Text               ' Replace n with Column Number of you file_path
        System.IO.File.Delete(filePath)
        'Dim i As Short
        'i = FilesDataSet1.Tables(0).Rows.Count()
        FilesDataSet1.Files.Rows.Item(e.Item.ItemIndex).Delete()
        filesAdapter.Update(FilesDataSet1)
        filesGrid.DataSource = FilesDataSet1.Files.DefaultView
        filesGrid.EditItemIndex = -1
        filesGrid.DataBind()
    End Sub

    Private Sub GetInfoID()
        Dim myConn As New SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myAdapter As New SqlClient.SqlDataAdapter
        Dim myReader As SqlClient.SqlDataReader
        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand
        'IDENT_CURRENT('INFO') gets the last Identity entered into the INFO table
        myCommand.CommandText = "SELECT info_id FROM INFO WHERE info_id = IDENT_CURRENT('INFO')"
        myConn.Open()

        Try
            myReader = myCommand.ExecuteReader()
            While myReader.Read
                lblInfoID.Text = myReader("info_id").ToString
            End While
        Catch
            lblMessage.Text = "ERROR: " & Err.Description      '<----- Add a label called lblMessage to your form.
        End Try
        myConn.Close()

    End Sub

End Class
0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13914977
well, good to see everything is working and thanks for the points.
1.
Use drag & drop to generate sqldataadapter, dataset, and generate select, update, delete,insert is easy to do at the beginning, but it's quite hard to debug later. If you want to change it, the whole structure will change. If you want to do it, let me know.

2.
use store procedure, when you do the insert, it returns the id from upload.
this is easier than setp 1 to do.

0
 
LVL 11

Expert Comment

by:lijunguo
ID: 13914982
If you want, we may start step 2 first.
0
 

Author Comment

by:jettman26
ID: 13915027
Step 2 it is then.  I will post a question for it now.
0
 

Author Comment

by:jettman26
ID: 13915045
It is posted.
0

Featured Post

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.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month15 days, 14 hours left to enroll

850 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