A Simple Method for uploading Excel Data to SQL Server!

Note three points when uploading Excel Data to SQL Server!

Note: To upload Excel Data to SQL Server, you must upload the Excel Data to the server in advance.

Practice: Add a fileupload upload control in the ASP. NET environment.

E. X of the background code:
If (fileupload1.hasfile) // if you have selected the Browse button of the upload control, the file is successfully browsed.
{
This. fileupload1.saveas ("E: \ temp \" + fileupload1.filename); // save it to the server directory. You need to modify fileupload1.filename Based on the server's actual situation to automatically obtain the upload file name.

}
// OK is successfully uploaded to the E: \ Temp Directory of the server (note Error Handling and exception handling when writing it yourself. It is very important ).

Note 2: The structure (FIELD) of the table on the SQL Server server must be the same as the order of the Excel format to be uploaded.

The key code for saving to the server is as follows: E. X (SQL statement ):

Insert into employeescheck select * From OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 8.0; HDR = yes; database = E: \ temp \ "+ fileupload1.filename +"', teenie $)

// Employeescheck is the data acceptance table in SQL Server. HDR = Yes indicates that the first row of Excel content is used as the content field instead of the field column.
Teenie is the name of the current page in my Excel file (note that this must be correct; otherwise, an error will occur, and the name should be followed by the House $)

Remove SQL blocking:
SQL Server blocks access to the statement 'openrowset/OpenDataSource 'of the 'ad hoc distributed queries' component because this component has been disabled as part of the server's security configuration. The system administrator can enable 'ad hoc distributed queries 'by using sp_configure '. For more information about enabling 'ad hoc distributed querys', see "peripheral application configurator" in SQL Server books online ".
Sql2005 does not enable the 'ad hoc distributed queries 'component by default.

Exec sp_configure 'show advanced options', 1
Go
Reconfigure
Go
Exec sp_configure 'ad hoc distributed queries ', 1
Go
Reconfigure
Go

Haha! Speaking of these, this is relatively simple to deal with general applications. Because Excel Data is usually used for internal data processing within the company, it is enough to deal with it,
However, if it is a commercial or professional website that makes it difficult for any common users to upload Excel files, after all, you cannot strongly require all users to excel tables.
The names of data pages are all teenie. There is also a very troublesome SQL statement (in the form of parameters) on the Internet to solve this problem. You can refer to it as a matter of fact!

[To deal with such problems, the focus is not on upload and implementation technologies, but on error handling. Any mistakes will cause data to be XX]

Come on!