Upload .xls file to SQL Server table via ASP.NET

[bubberz]

Here's what I have to do:

1. Allow user to locate a .xls file on their machine

2. Upload this .xls data into an existing table on a remote SQL Server

I can pull the file from my local machine to another directory on the local machien, but can't figure out have to configure the saveas() to save on the remote db server.

It seems you have to save the the db server first on the hard drive, then you can insert the .xls file data into the table.

Here's my code so far that works to save on the local machine to another directory on that local machine:

Dim getmyFile As HttpPostedFile = myfile.PostedFile

If IsNothing(getmyFile) Then

Label2.Text = "Please select a file to upload"

Else

If getmyFile.ContentLength = 0 Then

Label2.Text = "Cannot upload zero length File"

Else

Dim ServerFileName As String = Path.GetFileName(myfile.PostedFile.FileName)

getmyFile.SaveAs("C:\TestSaving\" & ServerFileName)

Label2.Text = "Successful upload to C:\TestSaving\" & ServerFileName

sCon1.Open()

Dim strSQL As String

Dim err As Integer

strSQL = "Insert into ActivityTest Select * FROM OPENROWSET"

strSQL &= "('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testing.xls;"

strSQL &= "HDR = YES ','SELECT * FROM [sheet1$]')"

Label3.Text = strSQL.ToString()

Dim cmd As New SqlCommand(strSQL, sCon1)

Try

cmd.ExecuteNonQuery()

err = "Select @@Error"

If err <> 0 Then

Label4.Text = err.ToString()

Else

Label4.Text = "No Error...line 91!"

End If

Catch ex As Exception

Label2.Text = "Line 82 Error Updating Table: "

Label2.Text &= ex.Message

Finally

sCon1.Close()

End Try

End If

Any good books on how to do this would be appreciated too!

Thanks for the help in advance!!!!