I am loading XLSX Files if I load 1 file using fileUpload with allowMultiple set to true it works ok if I load 2 or more files using fileUpload I get error Cannot access closed file on this line.
Using workBook As New XLWorkbook(txt.InputStream)
Imports System.IO
Imports System.Text
Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.Xml
Imports ClosedXML.Excel
Public Class ImportStudentFile
Inherits System.Web.UI.Page
Private Shared pw As String
Private Shared ConnString As String
Private Shared schoolCode As String
Private Shared scPno As Integer
Private Shared sPno As Integer
Private Shared fnPno As Integer
Private Shared gdPno As Integer
Private Shared ePno As Integer
Private Shared agPno As Integer
Private Shared aaPno As Integer
Private Shared rPno As Integer
Private Shared ycPno As Integer
Private Shared usiPno As Integer
Private Shared snPno As Integer
Private Shared gPno As Integer
Private Shared iPno As Integer
Private Shared indexPno As Integer
Private Shared Postedfile As HttpPostedFile
Private Shared NewDir As String
Private Shared Message As String
Private Shared YNC As String
Private Shared HeadDesc As New List(Of String)
Private Shared dt As New DataTable()
Protected Sub OnYes(ByVal sender As Object, ByVal e As EventArgs)
If YNC = "I" Then
UpdateFile()
End If
End Sub
Protected Sub OnNo(ByVal sender As Object, ByVal e As EventArgs)
YNC = ""
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
pw = Session("pw")
ConnString = Session("CareerConn")
schoolCode = Session("SchoolCode")
If Session("SchoolCode") = "Combined" Then
Message = "Error : School Name Not Selected "
ClientScript.RegisterStartupScript(Me.GetType(), "Popup", "ShowPopup('" + Message + "','Information','error-dialog');", True)
Response.Redirect("..\Career\CareerMenu.aspx")
Exit Sub
End If
If Session("UserName") = "" Then
Response.Redirect("..\SchoolLogin.aspx")
Exit Sub
End If
If IsPostBack Then
Dim Script = "$(document).ready(function () { $('[id*=btnSubmit]').click(); });"
ClientScript.RegisterStartupScript(Me.GetType(), "load", Script, True)
End If
If Not IsPostBack Then
cbHeader.Text = "1"
Me.y1.DataSource = GetYear()
y1.DataValueField = "YearCode"
y1.DataTextField = "YearCode"
Me.y1.DataBind()
y1.Items.Add("")
Me.y2.DataSource = GetYear()
y2.DataValueField = "YearCode"
y2.DataTextField = "YearCode"
Me.y2.DataBind()
y2.Items.Add("")
Me.y3.DataSource = GetYear()
y3.DataValueField = "YearCode"
y3.DataTextField = "YearCode"
Me.y3.DataBind()
y3.Items.Add("")
Me.y4.DataSource = GetYear()
y4.DataValueField = "YearCode"
y4.DataTextField = "YearCode"
Me.y4.DataBind()
y4.Items.Add("")
Me.y5.DataSource = GetYear()
y5.DataValueField = "YearCode"
y5.DataTextField = "YearCode"
Me.y5.DataBind()
y5.Items.Add("")
Me.y6.DataSource = GetYear()
y6.DataValueField = "YearCode"
y6.DataTextField = "YearCode"
Me.y6.DataBind()
y6.Items.Add("")
y1.Text = ""
y2.Text = ""
y3.Text = ""
y4.Text = ""
y5.Text = ""
y6.Text = ""
Dim conn As New SqlConnection(ConnString)
Dim con As New SqlConnection(ConnString)
Using sqlComm = New SqlCommand()
With sqlComm
If con.State = ConnectionState.Open Then con.Close()
con.Open()
.CommandText = "Select *From SchoolImport Where ImportCode='Student'"
.Connection = con
.CommandType = CommandType.Text
.Parameters.Clear()
Dim reader As SqlDataReader = .ExecuteReader
Do While reader.Read = True
scPno = reader.GetInt32(1)
sPno = reader.GetInt32(2)
fnPno = reader.GetInt32(3)
gdPno = reader.GetInt32(6)
ePno = reader.GetInt32(7)
agPno = reader.GetInt32(8)
aaPno = reader.GetInt32(9)
rPno = reader.GetInt32(11)
ycPno = reader.GetInt32(12)
usiPno = reader.GetInt32(14)
snPno = reader.GetInt32(15)
gPno = reader.GetInt32(16)
iPno = reader.GetInt32(17)
If reader.GetString(18) = "Email" Then indexPno = reader.GetInt32(7)
If reader.GetString(18) = "USI" Then indexPno = reader.GetInt32(14)
If reader.GetString(18) = "Student" Then indexPno = reader.GetInt32(15)
Loop
End With
sqlComm.Dispose()
End Using
SqlConnection.ClearAllPools()
End If
End Sub
Private Function GetYear() As DataTable
Dim connection As New SqlConnection(ConnString)
Dim command As SqlCommand = connection.CreateCommand
command.CommandText = "Select YearCode from YearCode order by YearOrder"
Dim table As New DataTable
connection.Open()
table.Load(command.ExecuteReader(Data.CommandBehavior.CloseConnection))
Return table
End Function
Protected Sub bContinue_Click(sender As Object, e As EventArgs) Handles bContinue.Click
Dim ok As Boolean = True
If (f1.Text <> "") And (y1.Text = "") Then ok = False
If (f2.Text <> "") And (y2.Text = "") Then ok = False
If (f3.Text <> "") And (y3.Text = "") Then ok = False
If (f4.Text <> "") And (y4.Text = "") Then ok = False
If (f5.Text <> "") And (y5.Text = "") Then ok = False
If (f6.Text <> "") And (y6.Text = "") Then ok = False
If Not ok Then
Message = "All Year Codes not Selected"
ClientScript.RegisterStartupScript(Me.GetType(), "Popup", "ShowPopup('" + Message + "','Information','error-dialog');", True)
Exit Sub
End If
pFile.Attributes.Add("Style=", "z-index: 2; left: 10px; top: 15px; position: absolute; height: 362px; width: 537px")
pFile.Visible = False
pImport.Attributes.Add("Style=", "z-index: 1; left: 10px; top: 15px; position: absolute; height: 362px; width: 537px")
pImport.Visible = True
FileUpload1 = Session("FileUpload1")
Dim FN1 As String
Dim FileCnt As Integer
dt = New DataTable()
If FileUpload1.HasFiles Then
For Each postedfile As HttpPostedFile In FileUpload1.PostedFiles
FileCnt = FileCnt + 1
FN1 = FN1 & postedfile.FileName & " "
Dim txt = postedfile
Using workBook As New XLWorkbook(txt.InputStream)
FN1 = FN1 & postedfile.FileName & " "
'Read the first Sheet from Excel file.
Exit Sub
Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
'Loop through the Worksheet rows.
Dim firstRow As Boolean = True
If FileCnt > 1 Then firstRow = False
For Each row As IXLRow In workSheet.Rows()
'Use the first row to add columns to DataTable.
If firstRow Then
For Each cell As IXLCell In row.Cells()
dt.Columns.Add(cell.Value.ToString())
Next
firstRow = False
Else
'Add rows to DataTable.
dt.Rows.Add()
Dim l As Integer = 0
For Each cell As IXLCell In row.Cells(1, dt.Columns.Count)
Dim k = cell.Value.ToString()
dt.Rows(dt.Rows.Count - 1)(l) = cell.Value.ToString()
l += 1
Next
End If
Next
End Using
Next
GridView1.DataSource = dt
GridView1.DataBind()
YNC = "I"
Message = "Import " & FN1
ClientScript.RegisterStartupScript(Me.GetType(), "Popup", "ShowPopupYN('" & Message & "','Import','export-dialog');", True)
Exit Sub
End If
End Sub
