oledb bulkcopy from excel to sql server

[AgnesPandaEyes]

I am using the following to copy data from excel sheet into sql table. It works fine when I run the webpage locally. But when I publish it and run it. The method fails. I noticed the file path of sExcelFileName is fully qualified when i run it locally such as "C:\Documents.....", but when I run it published the filename becomes only the excel file name "11232000.xls" Is the httpinputfile not picking the full path or something. Any help would be great. Thanks



Dim Com As New CommomUtil
Dim filename As String = System.IO.Path.GetFileName(File1.PostedFile.FileNa me)
Dim file As String() = System.IO.Path.GetFileName(File1.PostedFile.FileNa me).Split("\")

Dim sSQLTable As String = "tblRecoveryTemp"
Dim sExcelFileName As String = File1.PostedFile.FileName
Dim sWorkBook As String = "[Sheet1$]"

'Create our connection strings
Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sExcelFileName & ";Extended Properties=""Excel 8.0;HDR=YES;"""
Dim sSQLConnectionString As String = AppSettings("DBConnectProd")

Try
MessageBox(sExcelFileName)
'Copy Excel File to Temp Table
Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
Dim OleDbCmd As OleDbCommand = New OleDbCommand(("SELECT NETWORK_ID, TRANSACTION_DATE, DATE_POSTED_CMS, REJECT_CODE, CMS_ADJ_DESC, CMS_AMOUNT FROM " & sWorkBook), OleDbConn)
OleDbConn.Open()

Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sSQLConnectionString)
bulkCopy.DestinationTableName = sSQLTable
bulkCopy.WriteToServer(dr)
OleDbConn.Close()

The following is the error message. I am guessing it's because the full path isn't there so it's erroring out. Is there a way to get the full path because this is fine when running locally


System.Data.OleDb.OleDbException: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteReader() at Collections.Upload.Submit1_ServerClick(Object sender, EventArgs e) in C:\Documents and Settings\lmoy\Desktop\pcms2\pcms\Upload.aspx.vb:line 101