Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET Excel VB.NET
Hi.I am having problem with my code. I want the user to select the date and category he want to view and from there he can chose whether to view it in line or bar graph.However there's nothing coming out from the dropdown list.
 
Here's my code:
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.IO
Imports System.Configuration
Partial Class Wind_Speed
    Inherits System.Web.UI.Page
    Private include As String = "Date,Time,WindSpeed" 'the columns to be included
    Private hidden As String = "Date,Time,WindDirection,PolyCrsytalline,ThinFilm,IRAD" 'the columns to be hidden from chosing category
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Dim myfile As New StreamReader(FileUpload1.FileName) 'read the textfile specfied in textbox1
        'Dim totalRecord As Integer = 0 'stores total number of records
        'clearSession() 'clear session restart
        'Session.Add("preheader", getLineAsData(myfile.ReadLine())) 'read 1st line and pass to getLineAsData()
        'Session.Add("trgheader", getLineAsData(myfile.ReadLine())) 'read 2nd line and pass to getLineAsData()
        ''EndOfStream returns true if there is line to read
        ''EndOfStream returns false if there is no line to read
        ''if the file has things to read
        'While Not myfile.EndOfStream
        '    'rec[index] = readed line store into session
        '    Session.Add("rec[" & totalRecord & "]", getLineAsData(myfile.ReadLine()))
        '    totalRecord += 1 ' increase totalRecord by 1
        'End While
        ''store totalRecord into session
        'Session.Add("totalRecord", totalRecord)
        ''close the textfile
        'myfile.Close()
        'Response.Redirect("ViewData.aspx") 'redirects to view data page

        Dim myTable As New DataTable()  'declare database table
        Dim preheader() As String = Session("preheader") 'start header
        Dim trgheader() As String = Session("trgheader") 'trailing header
        If preheader Is Nothing Then Exit Sub
        Dim headerIsInc(preheader.Length - 1) As Boolean 'boolean to check header is included

 

        ''page_load triggers when the page load 1st time and refreshed
        ''prevent this page to continue if the page is refreshed
        If IsPostBack Then
            Return
        End If
        ''for loop from 0 to total header element - 1
        For i As Integer = 0 To preheader.Length - 1 Step 1
            Dim header1 As String = preheader(i) & trgheader(i) 'header1 = joins up start header and trailin header
            Dim header2 As String = preheader(i) & " " & trgheader(i) 'header2 = joins up start header and trailin header with a space in between
            header1 = header1.Replace(" ", "") 'replace space to join up start header and trg header
            headerIsInc(i) = isIncluded(header1) 'call isIncluded() to pass in header1
            If (headerIsInc(i)) Then
                myTable.Columns.Add(header2) 'add header 2 as column into table
                If Not (isHidden(header1)) Then
                    DropDownList2.Items.Add(header2) 'add header 2 to dropdownlist2
                End If
            End If
        Next
 
        'for loop from 0 to total record element - 1
        For x As Integer = 0 To Session("totalRecord") - 1 Step 1
            Dim myColumn() As String = Session("rec[" & x & "]") 'retrieve all row and put into myColumn string array
            Dim filter As String = "" 'filter out the wanted column data from the row
            'isDateExist returns true if date exist
            'isDateExist returns false if date does not exist
            If Not (isDateExist(myColumn(0))) Then
                DropDownList1.Items.Add(myColumn(0)) 'add the non existing date to dropdownlist1
            End If
            'this loop to filter out the column data for that row
            'for loop from 0 to total header element - 1
            For y As Integer = 0 To preheader.Length - 1 Step 1
                'if header is included
                If (headerIsInc(y)) Then
                    'check if filtered is empty
                    If String.IsNullOrEmpty(filter) Then
                        'add the column data to filter
                        filter &= myColumn(y)
                    Else
                        'add the column data to filter with '|'
                        filter &= "|" & myColumn(y)
                    End If
                End If
            Next
 
            ''split them by '|' into myColumn
            myColumn = filter.Split("|")
            ''at least of 1 column data
            If myColumn.Length > 0 Then
                ' 'add this row to the table
                myTable.Rows.Add(myColumn)
            End If
        Next
 
        ''gridview1 gets it source from mytable
        GridView1.DataSource = myTable
        GridView1.DataBind() 'command gridview1 to collect the data from mytable
    End Sub
    'return the line split by tab into string array
    Private Function getLineAsData(ByVal line As String) As String()
        Return line.Split(ControlChars.Tab)
    End Function
    'clear the session
    Private Sub clearSession()
        Session.Clear()
    End Sub
    Private Function isIncluded(ByVal header As String) As Boolean
        Dim include() As String = Me.include.Split(",") 'split the var include into array
        'i is from 0 to include total element
        For i As Integer = 0 To include.Length - 1 Step 1
            '   'compare the header and include array element
            If String.Equals(header, include(i)) Then
                Return True
            End If
        Next
        Return False
    End Function
    Private Function isHidden(ByVal header As String) As Boolean
        Dim hidden() As String = Me.hidden.Split(",") 'split the var hidden into array
        ''i is from 0 to hidden total element
        For i As Integer = 0 To hidden.Length - 1 Step 1
            '  'compare the header and hidden array element
            If String.Equals(header, hidden(i)) Then
                Return True
            End If
        Next
        Return False
    End Function
    Private Function isDateExist(ByVal strdate As String) As Boolean
        ''strdate is the date
        ''for i from 0 to total dropdownlist1 item
        For i As Integer = 0 To DropDownList1.Items.Count - 1 Step 1
            If String.Equals(DropDownList1.Items(i).Text(), strdate) Then
                Return True
            End If
        Next
        Return False
    End Function
 
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim para1 As String = DropDownList1.SelectedItem.Text 'get dropdownlist 1 item text -> Date
        Dim para2 As String = DropDownList2.SelectedItem.Text 'get dropdownlist 2 item text -> category
        para2 = para2.Replace(" ", "") 'replace space to join up start header and trg header
        'redirect and pass in the querystring to next page
        Response.Redirect("View linegraph.aspx?date=" & para1 & "&cat=" & para2)
    End Sub
 

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim para1 As String = DropDownList1.SelectedItem.Text 'get dropdownlist 1 item text -> Date
        Dim para2 As String = DropDownList2.SelectedItem.Text 'get dropdownlist 2 item text -> category
        para2 = para2.Replace(" ", "") 'replace space to join up start header and trg header
        'redirect and pass in the querystring to next page
        Response.Redirect("View BarGraph.aspx?date=" & para1 & "&cat=" & para2)
    End Sub
 
    
    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        If FileUpload1.HasFile Then
            Dim FileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
            Dim Extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
            Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")
            Dim FilePath As String = Server.MapPath(FolderPath + FileName)
            FileUpload1.SaveAs(FilePath)
            Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text)
        End If
    End Sub
    Private Sub Import_To_Grid(ByVal FilePath As String, ByVal Extension As String, ByVal isHDR As String)
        Dim conStr As String = ""
        Select Case Extension
            Case ".xlsx"
                'Excel 07 
                conStr = ConfigurationManager.ConnectionStrings("ana_1005ConString").ConnectionString
                Exit Select
        End Select
        conStr = String.Format(conStr, FilePath, isHDR)
        Dim connExcel As New OleDbConnection(conStr)
        Dim cmdExcel As New OleDbCommand()
        Dim oda As New OleDbDataAdapter()
        Dim dt As New DataTable()
        cmdExcel.Connection = connExcel
        'Get the name of First Sheet 
        connExcel.Open()
        Dim dtExcelSchema As DataTable
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
        connExcel.Close()
        'Read Data from First Sheet 
        connExcel.Open()
        cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
        oda.SelectCommand = cmdExcel
        oda.Fill(dt)
        connExcel.Close()
        'Bind Data to GridView 
        GridView1.Caption = Path.GetFileName(FilePath)
        GridView1.DataSource = dt
        GridView1.DataBind()
    End Sub
    Protected Sub PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
        Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")
        Dim FileName As String = GridView1.Caption
        Dim Extension As String = Path.GetExtension(FileName)
        Dim FilePath As String = Server.MapPath(FolderPath + FileName)
        Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text)
        GridView1.PageIndex = e.NewPageIndex
        GridView1.DataBind()
    End Sub
Posted 29-Oct-10 20:33pm
joyce16123
Edited 29-Oct-10 20:41pm
(no name)242.7K
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 215
1 George Jonsson 175
2 Kornfeld Eliyahu Peter 159
3 PIEBALDconsult 110
4 Richard MacCutchan 85
0 OriginalGriff 6,080
1 DamithSL 4,648
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,624
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 30 Oct 2010
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100