Import excel into SQL 2005 working in development but not in production

I have a simple code that uses a the file upload control to read an excel sheet and upload the data into a SQL 2005db.

I'm using Visual Web Developer and Sql's express edition to test it. It works fine when I test. However, when I push it up to the production server and try it via the any other pc it does not. The page loads fine. However, when it starts to upload it errors out.

Any reason why? I've never seen this happen.

Here's the code. Thanks in advance.

Protected Sub BtnUpload2_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles BtnUpload2.Click UploadTextDocument()End Sub Private Sub UploadTextDocument()Dim locationAs String = FileUpload1.PostedFile.FileName.ToString' Connection String to Excel WorkbookTry Dim excelConnectionStringAs String =String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", location)' Create Connection to Excel Workbook Using connectionAs Data.OleDb.OleDbConnection =New Data.OleDb.OleDbConnection(excelConnectionString)Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select BuilderID,SeriesID,OptionLevel,CommunityID,PhaseID,PlanID,ElevationID,OptionID,CurrentSalesPrice,LocalComments,Active,DateAdded,DateAvailable,DateInactive,SalesPriceEffective,SalesPriceExpires,PreviousSalesPrice,CutOffNotBefore,CutOffNotAfter FROM [Data$]", connection) connection.Open()' Create DbDataReader to Data Worksheet Using drAs Data.Common.DbDataReader = command.ExecuteReader()' SQL Server Connection StringDim connectionStringAs String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString' Bulk Copy to SQL Server Using bulkCopyAs SqlBulkCopy =New SqlBulkCopy(connectionString) bulkCopy.DestinationTableName ="ExcelData" bulkCopy.WriteToServer(dr)End UsingEnd Using connection.Close()End Using LBError.Text ="The spreadsheet was successfully uploaded."Catch LBError.Text ="There was an error. Check the spreadsheet for correct format."End Try End Sub

whats the exact error message ur getting?

|||

Hey Karen,

I don't get one. Debugging is off on the live application. I just get the error message at "catch".

I have a prospective client looking at it as we speak and don't want to make any changes to the web.config while they are viewing it...

|||

to catch error

use

try catch.. with and throw ex,.

one good possible reason is excel work sheet contains datatype that are not a part of datatypes of ur columns in tables. ..

there would not be any error, but the writetoserver would simply exit.. that's it..so use the old sheets u used in development server and try again.

Try printing., sheetnames and fieldcount.. in reader..

|||

Actually, I'm using the exact same sheet, same data, everything. I debugged it and still can't find any errors.

It only works from developement... I haven't tried it in a reader.

|||

How bout u try saving that file first and then transferring its contents to the database?

Regards

Karen

|||

Possible problems:

You don't have the correct version of Jet installed on the production server.

The process doesn't have read permissions at the location that the file is being written to.

try turning on remote errors in the web.config file so you can see exactly what the error is.

|||

Please provide the code topcopy the data/rows in the Excel work sheet to the sql server table.

Thanks and Regards

Ravi Shanker Maduri

Hyderabad

|||

Okay. I captured the error message. When I select the excel sheet from the folder on my desk top this message appears. The path is correct, as I said, it works fine when I do it from developent...

'C:\Documents and Settings\rednelo\Desktop\IPAddress_Mapping\test.xls'. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

|||

The problem is this line here:

Dim locationAs String = FileUpload1.PostedFile.FileName.ToString

You are using the filename that was on the client. You need the filename of the temporary file that the server created.

|||

Hi

I want to Import The Excel Data to the sql server using T-Sql Query ,i will run somany PL-sql statements for each project i want to automate it , i want to insert the default data to a table using excel sheet to the sql server table.

Please if any one know the solution for moving the excel data to the sql server using T-Sql Statement only.

Thanks and regards

Ravishanker Maduri

|||

Motley:

You need the filename of the temporary file that the server created.

All that makes sense. How would I get to the name of the temporary file?

|||

strINVSRC = Path.GetFileName(FlInvSrc.PostedFile.FileName)

|||

Hey Karen,

I tried what you have and I'm getting this error when I put in on the server. Did you run into this?

The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.
However, the same code in development gives me this:

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.

So, its not finding the file. Let me recap...

Dim locationAsString

'this works in development as I said before. So I know that was finding it at least on my local machine.

location = FileUpload1.PostedFile.FileName.ToString

What you had does not appear to find the file.

location = Path.GetFileName(FileUpload1.PostedFile.FileName)

Its somewhere between the two...

|||

Take a look at this link some one gave it to me when i had the same error message

http://forums.asp.net/t/1034337.aspx

I have done it this way..

First i am getting the name of the file...

Dim strFilePath,location as string

StrFilePath = Path.GetFileName(FileUpload1.PostedFile.FileName)

and then

location = Server.Mappath("The directory where u wanna store the file" & StrFilePath.

hope this helps..

Regards

Karen