hi, i have created a page that allows a user to uplaod an excel spreadsheet (sheet then gets renamed to sitetemplate) they can then view the site template sheet they have just uploaded and if it is in the correct format, sitetemplate will then get imported to sql server. BRILL!
however when a user tries to upload another spreadsheet, it will not overwrite the SiteTemplate that was created first as it is"being used by another process". If i close VS2010 down altogether and then open it back up and re run this file, it will import again.
please see my code below and if you have any suggestions they would be greatly appreciated
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO
Imports System.Text
Partial Class _Default
Inherits System.Web.UI.Page
Dim sFileDir As String = "C:\users\reidkz\My Documents"
Dim importButtonEnabled As Boolean = True
Protected Function ExcelConnection() As OleDbCommand
Dim xConnstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & (Server.MapPath("SiteTemplate.xls")) & ";" & _
"Extended Properties=Excel 8.0"
' create your excel connection object using the connection string
Dim objXConn As New OleDbConnection(xConnstr)
objXConn.Open()
Dim objCommand As New OleDbCommand("SELECT * FROM [importsites$]", objXConn)
Return objCommand
'objCommand = Nothing
'objXConn.Close()
'objXConn.Dispose()
End Function
Protected Sub ButtonUploadFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles ButtonUploadFile.Click
PanelUpload.Visible = True
PanelView.Visible = False
PanelImport.Visible = False
'check to see if the SiteTemplate file already exists. if so delete it
If FileUploadExcel.HasFile Then
Try
' alter path for your project
If System.IO.File.Exists("c:\users\reidkz\My Documents\Visual Studio 2010\Projects\Excel Upload\Site.Template.xls") = True Then
System.IO.File.Delete("c:\users\reidkz\My Documents\Visual Studio 2010\Projects\Excel Upload\Site.Template.xls")
MsgBox("File Deleted")
Else
End If
FileUploadExcel.SaveAs(Server.MapPath("~/SiteTemplate.xls"))
'FileUploadExcel.Dispose()
LabelUpload.Text = "Upload File Name: " & _
FileUploadExcel.PostedFile.FileName & "<br>" & _
"Type: " & _
FileUploadExcel.PostedFile.ContentType & _
" File Size: " & _
FileUploadExcel.PostedFile.ContentLength & " kb<br>"
ButtonUploadFile.Enabled = False
ButtonView.Enabled = True
'save the file to a different location with the time and date on the end of the file at upload
Dim sFileDir As String = "C:\Users\reidkz\My Documents\"
Dim sFileName As String = System.IO.Path.GetFileName(FileUploadExcel.PostedFile.FileName)
Dim TLDateTime As String
Dim TLDay As String
Dim TLMonth As Integer
Dim TLYear As Integer
Dim TLHour As Integer
Dim TLMinute As Integer
Dim TLDate As String
Dim TLTime As String
TLDay = DateTime.Now.Day
TLMonth = DateTime.Now.Month
TLYear = DateTime.Now.Year
TLHour = DateTime.Now.Hour
TLMinute = DateTime.Now.Minute
TLDate = TLMonth.ToString + TLDay.ToString + TLYear.ToString
TLTime = TLHour.ToString + TLMinute.ToString
TLDateTime = TLDate + "_" + TLTime
'delete the extension on the file name so you do not have two "." in your file
sFileName = Left([sFileName], InStrRev([sFileName], ".") - 1)
FileUploadExcel.PostedFile.SaveAs(sFileDir + sFileName + "_" + TLDateTime + ".xls")
FileUploadExcel.Dispose()
Catch ex As Exception
LabelUpload.Text = "Error: " & ex.Message.ToString
End Try
Else
LabelUpload.Text = "Please select a file to upload."
End If
End Sub
Protected Sub ButtonView_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonView.Click
importButtonEnabled = True
PanelUpload.Visible = False
PanelView.Visible = True
PanelImport.Visible = False
Dim objDataAdapter As New OleDbDataAdapter()
objDataAdapter.SelectCommand = ExcelConnection()
Dim objDataSet As New DataSet()
objDataAdapter.Fill(objDataSet)
'view the data you have uploaded in your gridview. this will check for any empty rows at this point and change the colours if necessary
GridView1.DataSource = objDataSet.Tables(0).DefaultView
GridView1.DataBind()
'objCommand.Dispose()
'objDataSet.Dispose()
'objDataAdapter.Dispose()
'objDataSet.Dispose()
'objXConn.Close()
'objXConn.Dispose()
'Catch ex As Exception
' lblgrid.Text = ex.Message
'Finally
' objDataAdapter.Dispose()
' objDataSet.Dispose()
' 'ExcelConnection.Dispose()
'End Try
btnBack.Enabled = True
ButtonImport.Enabled = importButtonEnabled
If ButtonImport.Enabled = False Then
lblerror.ForeColor = Drawing.Color.Red
lblerror.Text = "ERROR WITH FILE"
End If
End Sub
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
'change the footer from "&ndsp;" to a value - this will help you when enabling the import button
If e.Row.RowType = DataControlRowType.Footer Then
e.Row.Cells(1).Text = "END OF"
e.Row.Cells(1).BackColor = Drawing.Color.Empty
e.Row.Cells(2).Text = "DATA"
e.Row.Cells(2).BackColor = Drawing.Color.Empty
End If
'change the colour of the cells if cell value is null or "&ndsp;"
If (e.Row.Cells(1).Text = "" Or e.Row.Cells(1).Text = " ") Then
e.Row.Cells(1).BackColor = System.Drawing.Color.Salmon
importButtonEnabled = False
End If
If (e.Row.Cells(2).Text = "" Or e.Row.Cells(2).Text = " ") Then
e.Row.Cells(2).BackColor = System.Drawing.Color.Salmon
importButtonEnabled = False
End If
'if the cells are empty or contain " " then set the import button to false so user must correct their code
If (e.Row.Cells(1).Text = "" Or e.Row.Cells(1).Text = " ") Then
ButtonImport.Enabled = False
End If
If e.Row.Cells(1).Text <> "" Or e.Row.Cells(1).Text <> " " Then
ButtonImport.Enabled = True
End If
If (e.Row.Cells(2).Text = "" Or e.Row.Cells(2).Text = " ") Then
ButtonImport.Enabled = False
End If
If e.Row.Cells(2).Text <> "" Or e.Row.Cells(2).Text <> " " Then
ButtonImport.Enabled = True
End If
End Sub
Protected Sub ButtonImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonImport.Click
PanelUpload.Visible = False
PanelView.Visible = False
PanelImport.Visible = True
LabelImport.Text = "" 'reset to blank
'Dim strsql As String = ""
'strsql = "INSERT INTO tblSite (siteIWSref, siteUPRN, siteName, siteAdd1, siteAdd2, siteAdd3, sitePcode, siteContact, siteContactPos, siteContactTel, siteDesc, siteCompany, siteOccupants,siteType ) VALUES (@siteIWSref,@siteUPRN,@siteName,@siteAdd1,@siteAdd2,@siteAdd3,@sitePcode,@siteContact,@siteContactPos,@siteContactTel,@siteDesc,@siteCompany,@siteOccupants,@siteType)"
'Dim cn As New SqlClient.SqlConnection
'cn.ConnectionString = "Server=SQL2K8DEV01;Integrated Security=sspi;DataBase=IWSRiskAssessment;"
'cn.Open()
'Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=" & Server.MapPath("SiteTemplate.xls") & ";" & _
' "Extended Properties=Excel 8.0"
'Using connection As New System.Data.OleDb.OleDbConnection(excelConnectionString)
' Dim command As New System.Data.OleDb.OleDbCommand("SELECT * FROM [importsites$]", connection)
' connection.Open()
' Using reader As System.Data.OleDb.OleDbDataReader = command.ExecuteReader()
Dim objCommand As New OleDbCommand
objCommand = ExcelConnection()
'Creating the data reader
Dim reader As OleDbDataReader
reader = objCommand.ExecuteReader()
Dim counter As Integer = 0
While reader.Read()
'Dim cmd As New SqlClient.SqlCommand
'cmd.Connection = cn
'cmd.CommandType = CommandType.Text
'cmd.CommandText = strsql
counter = counter + 1
Dim siteID As Integer = 0
Dim siteIWSref As String = Convert.ToString(reader("siteIWSref"))
If siteIWSref = "" Then
siteIWSref = "o Kay"
End If
Dim siteUPRN As String = Convert.ToString(reader("siteUPRN"))
If siteUPRN = "" Then
siteUPRN = "o Kay Do kee"
End If
Dim siteName As String = Convert.ToString(reader("siteName"))
Dim siteAdd1 As String = Convert.ToString(reader("siteAdd1"))
Dim siteAdd2 As String = Convert.ToString(reader("siteAdd2"))
Dim siteAdd3 As String = Convert.ToString(reader("siteAdd3"))
Dim sitePcode As String = Convert.ToString(reader("sitePcode"))
Dim siteContact As String = Convert.ToString(reader("siteContact"))
Dim siteContactPos As String = Convert.ToString(reader("siteContactPos"))
Dim siteContactTel As String = Convert.ToString(reader("siteContactTel"))
Dim siteDesc As String = Convert.ToString(reader("siteDesc"))
Dim siteCompany As Integer = reader("siteCompany")
'Dim sitePermit As Byte = 0
'Dim sitePermitNum As String = Convert.ToString(reader("sitePermitNum"))
'Dim sitePermitTitle As String = Convert.ToString(reader("sitePermitTitle"))
Dim siteOccupants As String = Convert.ToString(reader("siteOccupants"))
'Dim siteSysAssDesc As String = Convert.ToString(reader("siteSysAssDesc"))
'Dim siteSysOpDesc As String = Convert.ToString(reader("siteSysOpDesc"))
'Dim siteSafetyArrMent As String = Convert.ToString(reader("siteSafetyArrMent"))
'Dim siteAdded As Date = Date.Now
'Dim siteActive As Byte = 0
'Dim sitePermitDet As String = Convert.ToString(reader("sitePermitDet"))
Dim siteType As String = Convert.ToString(reader("siteType"))
'Dim siteSafePolicy As Byte = 0
'Dim siteMaintScheme As Byte = 0
'Dim siteLogBook As Byte = 0
'Dim siteMonitAvail As Byte = 0
'Dim siteRegion As Integer = 0
'Dim siteImage As Byte = 0
'Dim siteImageName As String = Convert.ToString(reader("siteImageName"))
siteID = ImportIntotblSite(siteIWSref, siteUPRN, siteName, siteAdd1, siteAdd2, siteAdd3, sitePcode, siteContact, siteContactPos, siteContactTel, siteDesc, siteCompany, siteOccupants, siteType)
LabelImport.Text &= siteID & siteIWSref & " " & siteUPRN & " " & siteName & " " & siteAdd1 & " " & siteAdd2 & " " & siteAdd3 & " " & " " & sitePcode & " " & siteContact & " " & siteContactPos & " " & siteContactTel & " " & siteDesc & " " & siteCompany & " " & siteOccupants & " " & " " & siteType & "<br>"
'If counter > 86 Then ' exit early for testing, comment later...
' Exit While
'End If
End While
reader.Close()
' End Using
'End Using
'cn.Close()
'cn = Nothing
'btnBack.Enabled = True
' btnBack.Visible = True
btnSaveAndClear.Visible = True
btnSaveAndClear.Enabled = True
'Dim sourceDir As String = "c:\Users\reidkz\My Documents\"
'Dim backupDir As String = "c\users\reidkz\My Documents\BACKUP"
'Try
' Dim xlsList As String() = Directory.GetFiles(sourceDir, "*.xls")
' ' Copy picture files.
' For Each f As String In xlsList
' 'Remove path from the file name.
' Dim fName As String = f.Substring(sourceDir.Length + 1)
' ' Use the Path.Combine method to safely append the file name to the path.
' ' Will overwrite if the destination file already exists.
' File.Copy(Path.Combine(sourceDir, fName), Path.Combine(backupDir, fName), True)
' 'Remove path from the file name.
' Try
' ' Will not overwrite if the destination file already exists.
' File.Copy(Path.Combine(sourceDir, fName), Path.Combine(backupDir, fName))
' ' Catch exception if the file was already copied.
' Catch copyError As IOException
' Console.WriteLine(copyError.Message)
' End Try
' Next
' For Each f As String In xlsList
' File.Delete(f)
' Next
'Catch dirNotFound As DirectoryNotFoundException
' Console.WriteLine(dirNotFound.Message)
'End Try
End Sub
Protected Function ImportIntotblSite(ByVal siteIWSref As String, ByVal siteUPRN As String, ByVal siteName As String, ByVal siteAdd1 As String, ByVal siteAdd2 As String, ByVal siteAdd3 As String, ByVal sitePcode As String, ByVal siteContact As String, ByVal siteContactPos As String, ByVal siteContactTel As String, ByVal siteDesc As String, ByVal siteCompany As Integer, ByVal siteOccupants As String, ByVal siteType As String) As Integer
siteIWSref = Left(siteIWSref, 20)
siteUPRN = Left(siteUPRN, 20)
siteName = Left(siteName, 60)
siteAdd1 = Left(siteAdd1, 50)
siteAdd2 = Left(siteAdd2, 50)
siteAdd3 = Left(siteAdd3, 50)
sitePcode = Left(sitePcode, 10)
siteContact = Left(siteContact, 35)
siteContactPos = Left(siteContactPos, 35)
siteContactTel = Left(siteContactTel, 20)
siteDesc = Left(siteDesc, 220)
siteOccupants = Left(siteOccupants, 120)
siteType = Left(siteType, 35)
Dim siteID As Integer = 0
Try
Dim SSAdapter As New importSitesDataSetTableAdapters.tblSiteTableAdapter
Dim SSDataTable As importSitesDataSet.tblSiteDataTable = Nothing
SSDataTable = SSAdapter.GetDataByNumbers(siteIWSref, siteName, siteCompany)
If siteName = "" Then
siteName = ""
End If
'see if the category already exists in the table, if not insert it
If Not SSDataTable Is Nothing Then
If siteName = "" Then
LabelImport.Text = LabelImport.Text & _
"<font color=red>ERROR: BLANK SITE NAME: ROW NOT IMPORTED : " & _
"ID: " & siteID & _
" Name: " & siteName & " " & siteCompany & ".</font><br>"
ElseIf SSDataTable.Rows.Count > 0 Then
If Not SSDataTable(0).siteID = Nothing Then
siteID = SSDataTable(0).siteID
LabelImport.Text = LabelImport.Text & _
"<font color=blue> Recorded already exists : " & _
"ID: " & siteID & _
"Name: " & siteName & " " & siteCompany & ".</font><br>"
End If
End If
End If
If siteID = 0 And siteName <> "" Then
siteID = Convert.ToInt32(SSAdapter.InsertQuery(siteIWSref, siteUPRN, siteName, siteAdd1, siteAdd2, siteAdd3, sitePcode, siteContact, siteContactPos, siteContactTel, siteDesc, siteCompany, siteOccupants, siteType))
LabelImport.Text = LabelImport.Text & _
"<font color=green> ROW SUCCESSFULLY IMPORTED: " & _
" ID: " & siteID & _
" Name: " & siteName & " " & siteCompany & ".</font><br>"
End If
Return siteID
'SSAdapter.Dispose()
Catch ex As Exception
LabelImport.Text = LabelImport.Text & "<font color=red>" & ex.ToString & ".</font><br>"
Return 0
End Try
End Function
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
btnBack.Enabled = False
ButtonView.Enabled = False
ButtonImport.Enabled = False
btnSaveAndClear.Enabled = False
btnSaveAndClear.Visible = False
End Sub
Protected Sub btnBack_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBack.Click
Response.Redirect("Default.aspx")
End Sub
Protected Sub btnSaveAndClear_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSaveAndClear.Click
ButtonImport.Enabled = False
ButtonView.Enabled = False
btnBack.Visible = False
Dim FileToMove As String
Dim MoveLocation As String
Dim TLDateTime As String
Dim TLDay As String
Dim TLMonth As Integer
Dim TLYear As Integer
Dim TLHour As Integer
Dim TLMinute As Integer
Dim TLDate As String
Dim TLTime As String
Dim TLSeconds As String
TLDay = DateTime.Now.Day
TLMonth = DateTime.Now.Month
TLYear = DateTime.Now.Year
TLHour = DateTime.Now.Hour
TLMinute = DateTime.Now.Minute
TLSeconds = DateAndTime.Now.Second
TLDate = TLDay.ToString + "-" + TLMonth.ToString + "-" + TLYear.ToString
TLTime = TLHour.ToString + "-" + TLMinute.ToString + "-" + TLSeconds
TLDateTime = TLDate + "_" + TLTime
FileToMove = "c:\Users\reidkz\My Documents\Visual Studio 2010\Projects\ExcelUpload\SiteTemplate.xls"
MoveLocation = "C:\Users\reidkz\My Documents\backup\SiteTemplate" + "_" + TLDateTime + ".xls"
If System.IO.File.Exists(FileToMove) = True Then
System.IO.File.Copy(FileToMove, MoveLocation)
MsgBox("File Saved")
End If
FileToMove = Nothing
MoveLocation = Nothing
ExcelConnection.Dispose()
'FileToDelete = "C:\Users\reidkz\Documents\Visual Studio 2010\Projects\ExcelUpload\SiteTemplate.xls"
'If System.IO.File.Exists(FileToDelete) = True Then
' System.IO.File.Delete(FileToDelete)
' MsgBox("File Deleted")
'End If
Response.Redirect("default.aspx")
End Sub
End Class
however when a user tries to upload another spreadsheet, it will not overwrite the SiteTemplate that was created first as it is"being used by another process". If i close VS2010 down altogether and then open it back up and re run this file, it will import again.
please see my code below and if you have any suggestions they would be greatly appreciated
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO
Imports System.Text
Partial Class _Default
Inherits System.Web.UI.Page
Dim sFileDir As String = "C:\users\reidkz\My Documents"
Dim importButtonEnabled As Boolean = True
Protected Function ExcelConnection() As OleDbCommand
Dim xConnstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & (Server.MapPath("SiteTemplate.xls")) & ";" & _
"Extended Properties=Excel 8.0"
' create your excel connection object using the connection string
Dim objXConn As New OleDbConnection(xConnstr)
objXConn.Open()
Dim objCommand As New OleDbCommand("SELECT * FROM [importsites$]", objXConn)
Return objCommand
'objCommand = Nothing
'objXConn.Close()
'objXConn.Dispose()
End Function
Protected Sub ButtonUploadFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles ButtonUploadFile.Click
PanelUpload.Visible = True
PanelView.Visible = False
PanelImport.Visible = False
'check to see if the SiteTemplate file already exists. if so delete it
If FileUploadExcel.HasFile Then
Try
' alter path for your project
If System.IO.File.Exists("c:\users\reidkz\My Documents\Visual Studio 2010\Projects\Excel Upload\Site.Template.xls") = True Then
System.IO.File.Delete("c:\users\reidkz\My Documents\Visual Studio 2010\Projects\Excel Upload\Site.Template.xls")
MsgBox("File Deleted")
Else
End If
FileUploadExcel.SaveAs(Server.MapPath("~/SiteTemplate.xls"))
'FileUploadExcel.Dispose()
LabelUpload.Text = "Upload File Name: " & _
FileUploadExcel.PostedFile.FileName & "<br>" & _
"Type: " & _
FileUploadExcel.PostedFile.ContentType & _
" File Size: " & _
FileUploadExcel.PostedFile.ContentLength & " kb<br>"
ButtonUploadFile.Enabled = False
ButtonView.Enabled = True
'save the file to a different location with the time and date on the end of the file at upload
Dim sFileDir As String = "C:\Users\reidkz\My Documents\"
Dim sFileName As String = System.IO.Path.GetFileName(FileUploadExcel.PostedFile.FileName)
Dim TLDateTime As String
Dim TLDay As String
Dim TLMonth As Integer
Dim TLYear As Integer
Dim TLHour As Integer
Dim TLMinute As Integer
Dim TLDate As String
Dim TLTime As String
TLDay = DateTime.Now.Day
TLMonth = DateTime.Now.Month
TLYear = DateTime.Now.Year
TLHour = DateTime.Now.Hour
TLMinute = DateTime.Now.Minute
TLDate = TLMonth.ToString + TLDay.ToString + TLYear.ToString
TLTime = TLHour.ToString + TLMinute.ToString
TLDateTime = TLDate + "_" + TLTime
'delete the extension on the file name so you do not have two "." in your file
sFileName = Left([sFileName], InStrRev([sFileName], ".") - 1)
FileUploadExcel.PostedFile.SaveAs(sFileDir + sFileName + "_" + TLDateTime + ".xls")
FileUploadExcel.Dispose()
Catch ex As Exception
LabelUpload.Text = "Error: " & ex.Message.ToString
End Try
Else
LabelUpload.Text = "Please select a file to upload."
End If
End Sub
Protected Sub ButtonView_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonView.Click
importButtonEnabled = True
PanelUpload.Visible = False
PanelView.Visible = True
PanelImport.Visible = False
Dim objDataAdapter As New OleDbDataAdapter()
objDataAdapter.SelectCommand = ExcelConnection()
Dim objDataSet As New DataSet()
objDataAdapter.Fill(objDataSet)
'view the data you have uploaded in your gridview. this will check for any empty rows at this point and change the colours if necessary
GridView1.DataSource = objDataSet.Tables(0).DefaultView
GridView1.DataBind()
'objCommand.Dispose()
'objDataSet.Dispose()
'objDataAdapter.Dispose()
'objDataSet.Dispose()
'objXConn.Close()
'objXConn.Dispose()
'Catch ex As Exception
' lblgrid.Text = ex.Message
'Finally
' objDataAdapter.Dispose()
' objDataSet.Dispose()
' 'ExcelConnection.Dispose()
'End Try
btnBack.Enabled = True
ButtonImport.Enabled = importButtonEnabled
If ButtonImport.Enabled = False Then
lblerror.ForeColor = Drawing.Color.Red
lblerror.Text = "ERROR WITH FILE"
End If
End Sub
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
'change the footer from "&ndsp;" to a value - this will help you when enabling the import button
If e.Row.RowType = DataControlRowType.Footer Then
e.Row.Cells(1).Text = "END OF"
e.Row.Cells(1).BackColor = Drawing.Color.Empty
e.Row.Cells(2).Text = "DATA"
e.Row.Cells(2).BackColor = Drawing.Color.Empty
End If
'change the colour of the cells if cell value is null or "&ndsp;"
If (e.Row.Cells(1).Text = "" Or e.Row.Cells(1).Text = " ") Then
e.Row.Cells(1).BackColor = System.Drawing.Color.Salmon
importButtonEnabled = False
End If
If (e.Row.Cells(2).Text = "" Or e.Row.Cells(2).Text = " ") Then
e.Row.Cells(2).BackColor = System.Drawing.Color.Salmon
importButtonEnabled = False
End If
'if the cells are empty or contain " " then set the import button to false so user must correct their code
If (e.Row.Cells(1).Text = "" Or e.Row.Cells(1).Text = " ") Then
ButtonImport.Enabled = False
End If
If e.Row.Cells(1).Text <> "" Or e.Row.Cells(1).Text <> " " Then
ButtonImport.Enabled = True
End If
If (e.Row.Cells(2).Text = "" Or e.Row.Cells(2).Text = " ") Then
ButtonImport.Enabled = False
End If
If e.Row.Cells(2).Text <> "" Or e.Row.Cells(2).Text <> " " Then
ButtonImport.Enabled = True
End If
End Sub
Protected Sub ButtonImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonImport.Click
PanelUpload.Visible = False
PanelView.Visible = False
PanelImport.Visible = True
LabelImport.Text = "" 'reset to blank
'Dim strsql As String = ""
'strsql = "INSERT INTO tblSite (siteIWSref, siteUPRN, siteName, siteAdd1, siteAdd2, siteAdd3, sitePcode, siteContact, siteContactPos, siteContactTel, siteDesc, siteCompany, siteOccupants,siteType ) VALUES (@siteIWSref,@siteUPRN,@siteName,@siteAdd1,@siteAdd2,@siteAdd3,@sitePcode,@siteContact,@siteContactPos,@siteContactTel,@siteDesc,@siteCompany,@siteOccupants,@siteType)"
'Dim cn As New SqlClient.SqlConnection
'cn.ConnectionString = "Server=SQL2K8DEV01;Integrated Security=sspi;DataBase=IWSRiskAssessment;"
'cn.Open()
'Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=" & Server.MapPath("SiteTemplate.xls") & ";" & _
' "Extended Properties=Excel 8.0"
'Using connection As New System.Data.OleDb.OleDbConnection(excelConnectionString)
' Dim command As New System.Data.OleDb.OleDbCommand("SELECT * FROM [importsites$]", connection)
' connection.Open()
' Using reader As System.Data.OleDb.OleDbDataReader = command.ExecuteReader()
Dim objCommand As New OleDbCommand
objCommand = ExcelConnection()
'Creating the data reader
Dim reader As OleDbDataReader
reader = objCommand.ExecuteReader()
Dim counter As Integer = 0
While reader.Read()
'Dim cmd As New SqlClient.SqlCommand
'cmd.Connection = cn
'cmd.CommandType = CommandType.Text
'cmd.CommandText = strsql
counter = counter + 1
Dim siteID As Integer = 0
Dim siteIWSref As String = Convert.ToString(reader("siteIWSref"))
If siteIWSref = "" Then
siteIWSref = "o Kay"
End If
Dim siteUPRN As String = Convert.ToString(reader("siteUPRN"))
If siteUPRN = "" Then
siteUPRN = "o Kay Do kee"
End If
Dim siteName As String = Convert.ToString(reader("siteName"))
Dim siteAdd1 As String = Convert.ToString(reader("siteAdd1"))
Dim siteAdd2 As String = Convert.ToString(reader("siteAdd2"))
Dim siteAdd3 As String = Convert.ToString(reader("siteAdd3"))
Dim sitePcode As String = Convert.ToString(reader("sitePcode"))
Dim siteContact As String = Convert.ToString(reader("siteContact"))
Dim siteContactPos As String = Convert.ToString(reader("siteContactPos"))
Dim siteContactTel As String = Convert.ToString(reader("siteContactTel"))
Dim siteDesc As String = Convert.ToString(reader("siteDesc"))
Dim siteCompany As Integer = reader("siteCompany")
'Dim sitePermit As Byte = 0
'Dim sitePermitNum As String = Convert.ToString(reader("sitePermitNum"))
'Dim sitePermitTitle As String = Convert.ToString(reader("sitePermitTitle"))
Dim siteOccupants As String = Convert.ToString(reader("siteOccupants"))
'Dim siteSysAssDesc As String = Convert.ToString(reader("siteSysAssDesc"))
'Dim siteSysOpDesc As String = Convert.ToString(reader("siteSysOpDesc"))
'Dim siteSafetyArrMent As String = Convert.ToString(reader("siteSafetyArrMent"))
'Dim siteAdded As Date = Date.Now
'Dim siteActive As Byte = 0
'Dim sitePermitDet As String = Convert.ToString(reader("sitePermitDet"))
Dim siteType As String = Convert.ToString(reader("siteType"))
'Dim siteSafePolicy As Byte = 0
'Dim siteMaintScheme As Byte = 0
'Dim siteLogBook As Byte = 0
'Dim siteMonitAvail As Byte = 0
'Dim siteRegion As Integer = 0
'Dim siteImage As Byte = 0
'Dim siteImageName As String = Convert.ToString(reader("siteImageName"))
siteID = ImportIntotblSite(siteIWSref, siteUPRN, siteName, siteAdd1, siteAdd2, siteAdd3, sitePcode, siteContact, siteContactPos, siteContactTel, siteDesc, siteCompany, siteOccupants, siteType)
LabelImport.Text &= siteID & siteIWSref & " " & siteUPRN & " " & siteName & " " & siteAdd1 & " " & siteAdd2 & " " & siteAdd3 & " " & " " & sitePcode & " " & siteContact & " " & siteContactPos & " " & siteContactTel & " " & siteDesc & " " & siteCompany & " " & siteOccupants & " " & " " & siteType & "<br>"
'If counter > 86 Then ' exit early for testing, comment later...
' Exit While
'End If
End While
reader.Close()
' End Using
'End Using
'cn.Close()
'cn = Nothing
'btnBack.Enabled = True
' btnBack.Visible = True
btnSaveAndClear.Visible = True
btnSaveAndClear.Enabled = True
'Dim sourceDir As String = "c:\Users\reidkz\My Documents\"
'Dim backupDir As String = "c\users\reidkz\My Documents\BACKUP"
'Try
' Dim xlsList As String() = Directory.GetFiles(sourceDir, "*.xls")
' ' Copy picture files.
' For Each f As String In xlsList
' 'Remove path from the file name.
' Dim fName As String = f.Substring(sourceDir.Length + 1)
' ' Use the Path.Combine method to safely append the file name to the path.
' ' Will overwrite if the destination file already exists.
' File.Copy(Path.Combine(sourceDir, fName), Path.Combine(backupDir, fName), True)
' 'Remove path from the file name.
' Try
' ' Will not overwrite if the destination file already exists.
' File.Copy(Path.Combine(sourceDir, fName), Path.Combine(backupDir, fName))
' ' Catch exception if the file was already copied.
' Catch copyError As IOException
' Console.WriteLine(copyError.Message)
' End Try
' Next
' For Each f As String In xlsList
' File.Delete(f)
' Next
'Catch dirNotFound As DirectoryNotFoundException
' Console.WriteLine(dirNotFound.Message)
'End Try
End Sub
Protected Function ImportIntotblSite(ByVal siteIWSref As String, ByVal siteUPRN As String, ByVal siteName As String, ByVal siteAdd1 As String, ByVal siteAdd2 As String, ByVal siteAdd3 As String, ByVal sitePcode As String, ByVal siteContact As String, ByVal siteContactPos As String, ByVal siteContactTel As String, ByVal siteDesc As String, ByVal siteCompany As Integer, ByVal siteOccupants As String, ByVal siteType As String) As Integer
siteIWSref = Left(siteIWSref, 20)
siteUPRN = Left(siteUPRN, 20)
siteName = Left(siteName, 60)
siteAdd1 = Left(siteAdd1, 50)
siteAdd2 = Left(siteAdd2, 50)
siteAdd3 = Left(siteAdd3, 50)
sitePcode = Left(sitePcode, 10)
siteContact = Left(siteContact, 35)
siteContactPos = Left(siteContactPos, 35)
siteContactTel = Left(siteContactTel, 20)
siteDesc = Left(siteDesc, 220)
siteOccupants = Left(siteOccupants, 120)
siteType = Left(siteType, 35)
Dim siteID As Integer = 0
Try
Dim SSAdapter As New importSitesDataSetTableAdapters.tblSiteTableAdapter
Dim SSDataTable As importSitesDataSet.tblSiteDataTable = Nothing
SSDataTable = SSAdapter.GetDataByNumbers(siteIWSref, siteName, siteCompany)
If siteName = "" Then
siteName = ""
End If
'see if the category already exists in the table, if not insert it
If Not SSDataTable Is Nothing Then
If siteName = "" Then
LabelImport.Text = LabelImport.Text & _
"<font color=red>ERROR: BLANK SITE NAME: ROW NOT IMPORTED : " & _
"ID: " & siteID & _
" Name: " & siteName & " " & siteCompany & ".</font><br>"
ElseIf SSDataTable.Rows.Count > 0 Then
If Not SSDataTable(0).siteID = Nothing Then
siteID = SSDataTable(0).siteID
LabelImport.Text = LabelImport.Text & _
"<font color=blue> Recorded already exists : " & _
"ID: " & siteID & _
"Name: " & siteName & " " & siteCompany & ".</font><br>"
End If
End If
End If
If siteID = 0 And siteName <> "" Then
siteID = Convert.ToInt32(SSAdapter.InsertQuery(siteIWSref, siteUPRN, siteName, siteAdd1, siteAdd2, siteAdd3, sitePcode, siteContact, siteContactPos, siteContactTel, siteDesc, siteCompany, siteOccupants, siteType))
LabelImport.Text = LabelImport.Text & _
"<font color=green> ROW SUCCESSFULLY IMPORTED: " & _
" ID: " & siteID & _
" Name: " & siteName & " " & siteCompany & ".</font><br>"
End If
Return siteID
'SSAdapter.Dispose()
Catch ex As Exception
LabelImport.Text = LabelImport.Text & "<font color=red>" & ex.ToString & ".</font><br>"
Return 0
End Try
End Function
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
btnBack.Enabled = False
ButtonView.Enabled = False
ButtonImport.Enabled = False
btnSaveAndClear.Enabled = False
btnSaveAndClear.Visible = False
End Sub
Protected Sub btnBack_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBack.Click
Response.Redirect("Default.aspx")
End Sub
Protected Sub btnSaveAndClear_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSaveAndClear.Click
ButtonImport.Enabled = False
ButtonView.Enabled = False
btnBack.Visible = False
Dim FileToMove As String
Dim MoveLocation As String
Dim TLDateTime As String
Dim TLDay As String
Dim TLMonth As Integer
Dim TLYear As Integer
Dim TLHour As Integer
Dim TLMinute As Integer
Dim TLDate As String
Dim TLTime As String
Dim TLSeconds As String
TLDay = DateTime.Now.Day
TLMonth = DateTime.Now.Month
TLYear = DateTime.Now.Year
TLHour = DateTime.Now.Hour
TLMinute = DateTime.Now.Minute
TLSeconds = DateAndTime.Now.Second
TLDate = TLDay.ToString + "-" + TLMonth.ToString + "-" + TLYear.ToString
TLTime = TLHour.ToString + "-" + TLMinute.ToString + "-" + TLSeconds
TLDateTime = TLDate + "_" + TLTime
FileToMove = "c:\Users\reidkz\My Documents\Visual Studio 2010\Projects\ExcelUpload\SiteTemplate.xls"
MoveLocation = "C:\Users\reidkz\My Documents\backup\SiteTemplate" + "_" + TLDateTime + ".xls"
If System.IO.File.Exists(FileToMove) = True Then
System.IO.File.Copy(FileToMove, MoveLocation)
MsgBox("File Saved")
End If
FileToMove = Nothing
MoveLocation = Nothing
ExcelConnection.Dispose()
'FileToDelete = "C:\Users\reidkz\Documents\Visual Studio 2010\Projects\ExcelUpload\SiteTemplate.xls"
'If System.IO.File.Exists(FileToDelete) = True Then
' System.IO.File.Delete(FileToDelete)
' MsgBox("File Deleted")
'End If
Response.Redirect("default.aspx")
End Sub
End Class
Last edited: