I am trying to create to upload a spreadsheet template directy into and sql database.
On the upload I want to validate every row to make sure the data is valid. Unfortunately I have no idiea where to begin so I am asking for help. So far I have managed to be able to upload the spreadsheet to a datagrid. But I want to go the step further and have it upload to the database.
This is what I have so far.
' upload the spreasheet into the uploads folder
Dim fileUpload As String
Dim fileUploadPath As String
If Myfile.PostedFile.ContentLength <= 0 Then
lblMessage.Text = "you must upload a file"
Else
If Myfile.PostedFile.ContentLength > 0 Then
fileUpload = Myfile.PostedFile.FileName.Substring(Myfile.PostedFile.FileName.LastIndexOf("\"))
fileUploadPath = Path.Combine(Server.MapPath("~"), "Uploads") & fileUpload
' If File.Exists(fileUploadPath & "change") Then File.Delete(fileUploadPath & "change")
Myfile.PostedFile.SaveAs(fileUploadPath)
lblMessage.Text = "File has successfully been loaded"
Dim connectionString As String
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileUploadPath & ";Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"""
Dim excelConnection As New System.Data.OleDb.OleDbConnection(connectionString)
Dim comStr As String = "SELECT * FROM [change$] " '"SELECT CHRNumber AS [Character Number], ClientID AS [Client ID], SiteID AS [Site ID], Engineer AS Engineer, ContactID AS [Contact ID], StartDTM AS [Start DTM], StopDTM AS [Stop DTM], Notes AS Notes, CreatedByLogin AS [Created By], CreatedDTM AS [Created DTM], Status AS Status, Title AS title FROM from [change$]"
Dim selDA As New System.Data.OleDb.OleDbDataAdapter(comStr, excelConnection)
Dim ds As New DataSet
' Delete if already exists
' If File.Exists(FileUploadPath) Then File.Delete(FileUploadPath)
' Import all of the data from the spreadshe
excelConnection.Open()
selDA.SelectCommand.ExecuteNonQuery()
selDA.Fill(ds)
End If
End If