How do I Upload a spreasheet template into SQL Database using VB.net?

Jul 3 2006 4:11 AM
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