• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

Need help changing from using dataAdapter wizard to doing it programatically

I want to get away from using wizards and want to do it programmatically.  Not sure where to start.  Will start with the following code which is from a page in my project.  Anyone willing to help convert this over will get the points.  I want to use a dataset over a dataReader if possible if I had a choice.


Imports System.IO
Imports System.Collections
Imports System.Data.SqlClient
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

    Public 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;database=classes; integrated security=true"
        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) & "')"
            'Imports System.Data.SqlClient first
            Dim insert_parameter As SqlParameter
            myCommand.CommandText = "sp_info"
            myCommand.CommandType = CommandType.StoredProcedure

            insert_parameter = myCommand.CreateParameter()
            insert_parameter.ParameterName = "@class_name"
            insert_parameter.Direction = ParameterDirection.Input
            insert_parameter.SqlDbType = SqlDbType.Char
            insert_parameter.Size = 30
            insert_parameter.Value = classDownList.SelectedValue.ToString.Trim
            myCommand.Parameters.Add(insert_parameter)

            insert_parameter = myCommand.CreateParameter()
            insert_parameter.ParameterName = "@info_date"
            insert_parameter.Direction = ParameterDirection.Input
            insert_parameter.SqlDbType = SqlDbType.DateTime
            insert_parameter.Value = calendar.SelectedDate
            myCommand.Parameters.Add(insert_parameter)

            insert_parameter = myCommand.CreateParameter()
            insert_parameter.ParameterName = "@notes"
            insert_parameter.Direction = ParameterDirection.Input
            insert_parameter.SqlDbType = SqlDbType.Char
            insert_parameter.Size = 7000
            insert_parameter.Value = Left(Replace(notesText.Text, "'", ""), 7000)
            myCommand.Parameters.Add(insert_parameter)

            insert_parameter = myCommand.CreateParameter()
            insert_parameter.ParameterName = "@rtn_id"
            insert_parameter.Direction = ParameterDirection.Output
            insert_parameter.SqlDbType = SqlDbType.Int
            myCommand.Parameters.Add(insert_parameter)


        End If

        myConn.Open()
        Try
            myCommand.ExecuteNonQuery()

            lblInfoID.Text = CType(myCommand.Parameters("@rtn_id").Value, String)

            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"
        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 (" & Integer.Parse(lblInfoID.Text) & ",'" & 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
jettman26
Asked:
jettman26
  • 18
  • 12
1 Solution
 
TorrwinCommented:
It looks to me like you pretty much have it down, just a minor change to using datasets instead of the data reader.  I think this is how you want your GetInfo() to look.  Let me know how it works, and if you need help with the other subs.

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 DataSet1 As New Dataset
        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
            myAdapter.Fill(Dataset1, "Results")

'I think you are trying to loop through all the records here, not quite sure.  If you aren't, and only want the one row, then take this loop out.

           Dim RowIndex as Integer = 0
           Dim RowCount as Integer = Dataset1.Tables("Results").Rows.Count

            While RowCount > RowIndex
                classDownList.SelectedValue = Dataset1.Tables("Results").Rows(0).Item("class_name")
                calendar.SelectedDate = Dataset1.Tables("Results").Rows(0).Item("info_date")
                notesText.Text = Dataset1.Tables("Results").Rows(0).Item("notes")
                lblInfoID.Text = Dataset1.Tables("Results").Rows(0).Item("info_id")
                RowIndex += 1
            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
0
 
jettman26Author Commented:
Torrwin, thanks for the info.
The subs that I really need help with are the Page_Load and the Private Sub Submit1_ServerClick.  Not sure how to do these programmatically instead of the Wizard like I currently use for them.
0
 
TorrwinCommented:
Ok, I will try to help.  I have a question though, what is your web page do overall?

Also, what is the reason of this statement? (So I know what the equivalent will be)
Request.QueryString("info_id").ToString
0
Technology Partners: We Want Your Opinion!

We value your feedback.

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

 
jettman26Author Commented:
The Request.QueryString("info_id").ToString is used to pass the info_id from another page.  Essentially, if a info_id is passed, the page will display a record from the database.
If info_id is not passed from another page, then you can fill in the fields on the page and save it to the database.

As far as the dataGrid goes, this will display and allow you to download and delete the files associated with the current record that is displayed on the rest of the page.

If you want, you can upload my project via FTP.  If you want to do that, let me know.
0
 
jettman26Author Commented:
You can get download my project by using an FTP client and going to beethe.com.
login/pass
test/test
Project is called Test
0
 
TorrwinCommented:
Ok, If on your previous page you put the info_id into a session variable, then I think you could do your page load like this:

Global Variables:
            Dim myConn As New SqlClient.SqlConnection
            Dim myCommand As New SqlClient.SqlCommand
            Dim myAdapter As New SqlClient.SqlDataAdapter
            Dim myDataset As New Dataset

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

            CS = "Server=DELL;initial catalog=classes;integrated security=true"
            myConn.ConnectionString = CS
            myCommand.Connection = myConn
            myAdapter.SelectCommand = myCommand

        '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 Session("info_id") = '' Then
                DropDownPopulate()
                GetInfo(Session("info_id")
            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
            myCommand.CommandText = "Select <your select statement> where <Param2> = '" & lblInfoID.Text & "'"
            myConn.Open
            myAdapter.Fill(myDataSet)
            myConn.Close
            If Not IsPostBack Then
                filesGrid.DataSource = myDataSet
                filesGrid.DataKeyField = "file_id"
                filesGrid.DataBind()
            End If
        Else
            If Not IsPostBack Then
                filesGrid.DataSource = myDataSet
                filesGrid.DataKeyField = "file_id"
                filesGrid.DataBind()
            End If
        End If
    End Sub
0
 
jettman26Author Commented:
Torrwin, I copied your code and something didn't like the session part of it so I put back in the (Request.QueryString("info_id").
It worked to read a record.

2 problems then appeared.  If I click the New button on my Default.aspx page (which redirects to my Main_info.aspx page and passes nothing to it) I get the following error.

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

The IListSource does not contain any data sources.
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.Web.HttpException: The IListSource does not contain any data sources.

Source Error:


Line 166:                filesGrid.DataSource = myDataset
Line 167:                filesGrid.DataKeyField = "file_id"
Line 168:                filesGrid.DataBind()
Line 169:                End If
Line 170:        End If
 

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

Stack Trace:


[HttpException (0x80004005): The IListSource does not contain any data sources.]
   System.Web.UI.DataSourceHelper.GetResolvedDataSource(Object dataSource, String dataMember)
   System.Web.UI.WebControls.DataGrid.CreateControlHierarchy(Boolean useDataSource)
   System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e)
   System.Web.UI.WebControls.BaseDataList.DataBind()
   Test._Class.Page_Load(Object sender, EventArgs e) in C:\Inetpub\wwwroot\Test\Main_info.aspx.vb:168
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +750

 **************************************************************************************
It may be easiest for you to download my project.
0
 
TorrwinCommented:
Ok, I will download it and take a look. It may take me a little bit though, I'm working too. =)
0
 
jettman26Author Commented:
no rush on this
just happy you are helping me.
0
 
jettman26Author Commented:
Torrwin, seeing how everything is going.
0
 
TorrwinCommented:
I tried downloading the project this weekend, and it wouldn't accept the username/password.  Have you had any trouble with it?
0
 
jettman26Author Commented:
strange,
try again, it is case sensitive.
login= test
password= test
0
 
TorrwinCommented:
Well, it still didn't work.  However, I tried downloading a different ftp client, and its now working. =)
0
 
jettman26Author Commented:
good deal, i was testing it using Smart FTP.
0
 
TorrwinCommented:
I'm having a little trouble getting it operational, but I know something you could try in the meantime.

In your Main.aspx page, comment everything out in your page_load sub, recompile, and see if you get an error.  If not, slowly un-comment the lines of code, and recompiling until you do get the error.  That would help pinpoint the error.
0
 
jettman26Author Commented:
k, i also put up on the ftp my project before i made any of these changes, if that helps.  Same username and password.  called copyofTest.
0
 
TorrwinCommented:
Its still not allowing me to load the project, I can open the files individually though.  I just can't see them in action.  How did the comment error-checking go?
0
 
jettman26Author Commented:
Torrwin, are you getting errors loading the project?  I put up my project without any of the changes you told me to do including the solution file in a folder called CopyofTest.  Also, in the New Folder 3 folder I added the solution for the project with your changes.

I did comment out some stuff and it definately has to do with this portion of code in the Page_Load.


'    'This is all for the DataGrid
        '    If Not lblInfoID.Text = "" Then


        '        myCommand.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 = " & lblInfoID.Text & ")"

        '        'myCommand.CommandText = "Select * from files where <Param2> = '" & lblInfoID.Text & "'"
        '        myConn.Open()
        '        myAdapter.Fill(myDataset)
        '        myConn.Close()
        '        If Not IsPostBack Then
        '            filesGrid.DataSource = myDataset
        '            filesGrid.DataKeyField = "file_id"
        '            filesGrid.DataBind()
        '            End If
        '    Else
        '        If Not IsPostBack Then
        '            filesGrid.DataSource = myDataset
        '            filesGrid.DataKeyField = "file_id"
        '            filesGrid.DataBind()
        '            End If
        '    End If
0
 
TorrwinCommented:
I see two folders at the ftp: Test, and TestSolution, am I missing something?
0
 
jettman26Author Commented:
Sorry, had them in the wrong directory.  Should work now.
0
 
TorrwinCommented:
I am getting the error "The selected file is not a valid Visual Studio solution file."

I think I may see something wrong though.  Conceptually you have:

<If the lbl isn't empty>
         <fill your dataset>
         <If page is loading for the first time>
              <bind the dataset to the datagrid>

<Otherwise, if the lbl is empty>
         <If page is loading for the first time>
              <bind the empty dataset to the datagrid>   <-------------------Problem

You can't bind an empty dataset to a datagrid.  Maybe you meant the code to be this?


Else
               If IsPostBack Then
                   filesGrid.DataSource = myDataset
                   filesGrid.DataKeyField = "file_id"
                   filesGrid.DataBind()
                   End If
End If
0
 
jettman26Author Commented:
I just tried loading my project on another computer.  This is how I got it to work.  
1.  Put the 'Test' folder in your IIS root.
2.  Go into IIS and you should see 'Test' in there.  Right click on the 'Test' folder and select Properties.  Click the Create button and then O.K. button.
3.  Double click on Test.sln in the 'TestSolution' folder.

It worked this way for me.  I am on XP Pro and using Visual Studio 2003.

As far as the code goes, when I get home I can check it out.
0
 
jettman26Author Commented:
I'm not sure what to do.  In my first post is the code that I used for the Main_info.aspx page BEFORE I made the changes that you said I should try.  It works.  There weren't that many changes that you had me do so it must be one of them.  Hopefully you can get my app to work.  The Copy_of_Test folder has my project before I made the changes and the Test(2) folder has the Solution for it.  
I don't understand ASP.NET and datasets very well so please bear with me.  
Thanks for your patience.

0
 
jettman26Author Commented:
Sorry Torrwin, I just realized in my first post the page load is wrong.  Here is what the code was when it worked before any changes for Main_info.aspx.

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

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" & _
        " 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))
        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

        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 = ""
                '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.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)
                    'filesAdapter.Fill(FilesDataSet1, "Files") '--Files is the dataset table name
                    If IsPostBack Then
                        'filesGrid.DataSource = FilesDataSet1.Tables("Files").DefaultView
                        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)
        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
        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()

    End Sub

End Class
0
 
TorrwinCommented:
Ok, I followed your instructions and was successfully able to open the test project.

I think you need to change your page load sub to this:

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
        End If
    End Sub
0
 
jettman26Author Commented:
That works.  Now can you show me how to do the Page_Load and Submit1_ServerClick by getting rid of the 'filesAdapter', 'filesConnection' and 'FilesDataSet1'  and doing it programmatically instead?  
0
 
TorrwinCommented:
You do it just like in your GetInfoID sub:

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

Basically, everywhere you see a filesAdapter, replace it with myAdapter.  Everywhere you see filesConnection replace it with myConn, etc.
0
 
jettman26Author Commented:
Torrwin, I am getting this error.

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

Invalid attempt to FieldCount when reader is closed.
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.InvalidOperationException: Invalid attempt to FieldCount when reader is closed.

Source Error:


Line 137:                DropDownPopulate()
Line 138:                GetInfo(Request.QueryString("info_id").ToString)
Line 139:                DataBind()
Line 140:            Else
Line 141:                'filesGrid.Visible = False
 

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

Stack Trace:


[InvalidOperationException: Invalid attempt to FieldCount when reader is closed.]
   System.Data.SqlClient.SqlDataReader.get_FieldCount()
   System.Data.Common.DbEnumerator.BuildSchemaInfo()
   System.Data.Common.DbEnumerator.MoveNext()
   System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e)
   System.Web.UI.Control.DataBind()
   System.Web.UI.Control.DataBind()
   System.Web.UI.Control.DataBind()
   Test._Class.Page_Load(Object sender, EventArgs e) in C:\Inetpub\wwwroot\Test\Main_info.aspx.vb:139
   System.Web.UI.Control.OnLoad(EventArgs e) +67
   System.Web.UI.Control.LoadRecursive() +35
   System.Web.UI.Page.ProcessRequestMain() +750

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

1.  I changed the Page Load to this:

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

        Dim CS As String

        CS = "Server=DELL;initial catalog=classes;integrated security=true"
        myConn.ConnectionString = CS
        myCommand.Connection = myConn
        myAdapter.SelectCommand = myCommand

        '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)
                DataBind()           <--------------------------------------------ADDED THIS LINE
            Else
                'filesGrid.Visible = False
                'File1.Visible = False
                'Submit1.Visible = False
                calendar.SelectedDate = DateTime.Now
                DropDownPopulate()
            End If
        End If
    End Sub

2.  Added this function:

Sub BindData()
        Dim strConn As String = "server=DELL;database=classes;integrated security=true"
        Dim MySQL As String = "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 = lblInfoID.Text)"
        Dim MyConn As New SqlConnection(strConn)
        Dim ds As DataSet = New DataSet
        Dim Cmd As New SqlDataAdapter(MySQL, MyConn)
        Cmd.Fill(ds, "FILES")
        filesGrid.DataSource = ds.Tables("FILES").DefaultView
        filesGrid.DataBind()
    End Sub
0
 
jettman26Author Commented:
Nevermind, I just figured it out.
0
 
jettman26Author Commented:
Thanks Torrwin!
I now have done it programmatically.  Here is your well deserved 500 points.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

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

  • 18
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now